SQL Server Query Help

Question

SQL Server Query Help

Sorry * this is what I had to deliver

My query creates duplicate records for any record containing more than one 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 fix the problem, but I don't see it as a solution. There are no duplicate records in the database. So it seems like my request is super duplicate creation.

The query should capture the list of companies that match the criteria in the where clause, but also captures the health status for each company in that particular [YearMonth], if present, which is a subquery. If there is no entry for this YearMonth, leave the health state blank.

but as stated earlier, if you have an entry for 2009-03 for CompID 2 and an entry for 2009-04 for CompID 2 .. It doesn't matter which month you choose, it will list that company 2-3 times.

0

join sql-server asp.net

Travis 05 May '09 at 16:46

source to share

4 answers

Quassnoi · Answer 1 · 2009-05-05T16:54:50+0000

This subquery:

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

does not correlate with any of the columns SNA_Parent_Accounts

.

Is this intentional?

Ryan · Answer 2 · 2009-05-05T17:02:55+0000

What does it mean to "create duplicate records for any record containing more than one instance"? It seems that if there is more than one instance, there are already duplicate entries?

Also, I would look at this piece of 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 see VARCHAR (4) + '-0' + VARCHAR (2), which looks like up to 8 characters to me, stuffed into VARCHAR (7). Are you losing a character and duplicating it?

THEn · Answer 3 · 2009-05-05T16:55:11+0000

I think you should add a join between the [Monthly_HIP_Reports] table and [SNA_Parent_Accounts]. I think this is where it gets duplicated because I don't know what these tables are for ... If you give more descriptions of the tables, I can definitely tell where the problem is ...

Travis · Answer 4 · 2009-05-05T17:20:12+0000

LEFT OUTER JOIN problem appears

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

which grabbed all monthly reports that had the same ID. I needed

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

so he limited it and YearMonth.

Thanks everyone!

SQL Server Query Help

More articles: