Help with SQL Server query

Question

Sorry* this is what I should have put

My query is creating duplicate entries for any record that has more than 1 instance (regardless of date)

<asp:SqlDataSource ID="EastMonthlyHealthDS" runat="server" 
    ConnectionString="<%$ ConnectionStrings:SNA_TRTTestConnectionString %>" 
    SelectCommand="SELECT [SNA_Parent_Accounts].[Company], 
                   (SELECT [Monthly_HIP_Reports].[AccountHealth] from [Monthly_HIP_Reports] where ([Monthly_HIP_Reports].[YearMonth] = @ToDtRFC) AND ([SNA_Parent_Accounts].[CompID] = [Monthly_HIP_Reports].[CompID])) as [AccountHealth],
                   [SNA_Parent_Accounts].[CompID]
                   FROM [SNA_Parent_Accounts]
                   LEFT OUTER JOIN [Monthly_HIP_Reports] ON [Monthly_HIP_Reports].[CompID] = [SNA_Parent_Accounts].[CompID]
                   WHERE (([SNA_Parent_Accounts].[Classification] = 'Business') OR ([SNA_Parent_Accounts].[Classification] = 'Business Ihn')) AND ([SNA_Parent_Accounts].[Status] = 'active') AND ([SNA_Parent_Accounts].[Region] = 'east')
                   ORDER BY [SNA_Parent_Accounts].[Company]">
    <SelectParameters>
         <asp:ControlParameter ControlID="ddMonths" Name="ToDtRFC" PropertyName="Text" Type="String"  />
    </SelectParameters>
</asp:SqlDataSource>

Using SELECT DISTINCT appears to correct the problem, but I don't consider that a solution. There are no duplicate entries in the database. So it appears my query is superfically creating duplicates.

The query should grab a list of companies that meet the criteria in the where clause, but also grab the Health status for each company in that particular [YearMonth] if present which is what the subquery is for. If an entry for that YearMonth is not present, then leave the Health status blank.

but as stated earlier.. if you have an entry say for 2009-03 for CompID 2 and an entry for 2009-04 for CompID 2.. Doesn't matter what month you select it will list that company 2-3 times.

Answer 1

This subquery:

(SELECT [Monthly_HIP_Reports].[AccountHealth] from [Monthly_HIP_Reports] where ([Monthly_HIP_Reports].[YearMonth] = @ToDtRFC)) as [AccountHealth],

is not correlated to any of SNA_Parent_Accounts columns.

Is it intentional?

Answer 2

I think you should add join between [Monthly_HIP_Reports] table and [SNA_Parent_Accounts] table. I think that is where it is getting duplicated because I don't know what these tables for... If you give more table descriptions I could definitely tell where is the problem...

Answer 3

What do you mean that it's "creating duplicate entries for any record that has more than 1 instance"? It seems that if there is more than one instance, there are already duplicate entries?

Also, I'd take a look at this part of the code:

SELECT count(*) FROM [SNA_Ticket_Detail] 
WHERE ([SNA_Ticket_Detail].[CompID] = [SNA_Parent_Accounts].[CompID]) 
   AND (CAST(CAST(YEAR([SNA_Ticket_Detail].[DtRFC]) AS VARCHAR(4)) 
      + '-0' + CAST(MONTH([SNA_Ticket_Detail].[DtRFC]) AS VARCHAR(2))
      AS VARCHAR(7)) = @ToDtRFC) 
   AND ([SNA_Ticket_Detail].[Reviewed] = '1') 
) as [TicketCount]

I'm seeing a VARCHAR(4) + '-0' + VARCHAR(2), which looks like up to 8 characters to me, stuffed into a VARCHAR(7). Are you losing a character and duplicating like that?

Answer 4

Appears I found the problem within the LEFT OUTER JOIN

I had LEFT OUTER JOIN [Monthly_HIP_Reports] ON [Monthly_HIP_Reports].[CompID] = [SNA_Parent_Accounts].[CompID]

which was grabbing all monthly reports where the IDs matched. What I needed was

LEFT OUTER JOIN [Monthly_HIP_Reports] ON [Monthly_HIP_Reports].[CompID] = [SNA_Parent_Accounts].[CompID] AND ([Monthly_HIP_Reports].[YearMonth] = @ToDtRFC)

so it limited it to the YearMonth as well.

Thanks to all!