How do you count the related rows within a query.

Question

I am trying to make a query that pulls out all Tickets for a particular company. Within that table will be a column named [Repeat]

What I need the query to do is check to see if there are any other rows that have a matching Circuit_ID within the last 30days of that ticket.

"SELECT [MAIN_TICKET_ID], [CompID], [ActMTTR], [ActOTR], [DtCr], [DtRFC],
                CASE WHEN [PRIORITY] = 1 THEN '1' 
                     WHEN [PRIORITY] = 2 THEN '2' 
                     WHEN [PRIORITY] = 3 THEN '3' END AS [PRIORITY],
                CASE WHEN ([PRIORITY] = '1' AND [ActMTTR] >= '4' AND ([ResCd7] = 'Equipment (XX)' OR [ResCd7] = 'Lec Facilities (LEC)')) 
                       OR ([PRIORITY] = '1' AND [ActOTR] >= '14' AND ([ResCd7] = 'Equipment (XX)' OR [ResCd7] = 'Lec Facilities (LEC)')) 
                       OR ([PRIORITY] = '2' AND [ActMTTR] >= '6' AND ([ResCd7] = 'Equipment (XX)' OR [ResCd7] = 'Lec Facilities (LEC)')) 
                       OR ([PRIORITY] = '2' AND [ActOTR] >= '16' AND ([ResCd7] = 'Equipment (XX)' OR [ResCd7] = 'Lec Facilities (LEC)')) 
                       OR (([Rpt5] = '1' OR [Rpt30] = '1' OR [Chronic] = '1') AND ([ResCd7] = 'Equipment (XX)' OR [ResCd7] = 'Lec Facilities (LEC)')) THEN 'Yes' ELSE 'No' END AS [Measured],  
                CASE WHEN [Reviewed] = 1 THEN 'Yes' ELSE 'No' END AS [Reviewed],
                CASE WHEN [Rpt5] = 1 OR [Rpt30] = 1 THEN 'Yes' ELSE 'No' End As [Repeat],
                CASE WHEN [Chronic] = 1 THEN 'Yes' ELSE 'No' END AS [Chronic],
                CASE WHEN [ResCd7] = 'Equipment (XX)' THEN 'XX' 
                     WHEN [ResCd7] = 'Isolated to Customer (ITC)' THEN 'ITC' 
                     WHEN [ResCd7] = 'Information (INF)' THEN 'INF' 
                     WHEN [ResCd7] = 'Test OK (TOK)' THEN 'TOK' 
                     WHEN [ResCd7] = 'Lec Facilities (LEC)' THEN 'LEC' 
                     WHEN [ResCD7] = 'Dispatched No Trouble Found (NTF)' THEN 'NTF' 
                     WHEN [ResCD7] = 'Cleared While Testing (CWT)' THEN 'CWT' END AS [Resolution]
                FROM [SNA_Ticket_Detail] WHERE ([CompID] = @CompID)"

Above is the current query which relies on a 0 or 1 flag within the table. Seen on line CASE WHEN [Rpt5] = 1 OR [Rpt30] = 1 THEN 'Yes' ELSE 'No' End As [Repeat],

What I want to do is replace that with something along the lines of

CASE WHEN (SELECT COUNT([XX_CIRCUIT_ID]) FROM SNA_Ticket_Detail WHERE (CONVERT(CHAR(10), [DtRFC], 101) BETWEEN ([DtRFC] - 6) AND ([DtRFC])) AND (XX_CIRCUIT_ID = XX_CIRCUIT_ID)) > '1' THEN 'Yes' ELSE 'No' End As [Repeat],

This doesn't work.. It basically counts all rows instead of just the rows that match the current rows circuit id and fall within the last month.

I don't know how to code this properly to get it to work. or even if it's possible within the query.

Answer 1

You want to run a subquery against the same table, so you need to alias the two uses of the table (recent and td below). When looking for rows of interest, in this case ones with the same Circuit ID and within a certain range, you need to make sure you're not looking at the exact same row. Hence the check on [MAIN_TICKET_ID]. You could do a COUNT as per your example, or you can do an EXISTS() as below.

SELECT [MAIN_TICKET_ID], [CompID], [ActMTTR], [ActOTR], [DtCr], [DtRFC],
                CASE WHEN [PRIORITY] = 1 THEN '1' 
                     WHEN [PRIORITY] = 2 THEN '2' 
                     WHEN [PRIORITY] = 3 THEN '3' END AS [PRIORITY],
                CASE WHEN ([PRIORITY] = '1' AND [ActMTTR] >= '4' AND ([ResCd7] = 'Equipment (XX)' OR [ResCd7] = 'Lec Facilities (LEC)')) 
                       OR ([PRIORITY] = '1' AND [ActOTR] >= '14' AND ([ResCd7] = 'Equipment (XX)' OR [ResCd7] = 'Lec Facilities (LEC)')) 
                       OR ([PRIORITY] = '2' AND [ActMTTR] >= '6' AND ([ResCd7] = 'Equipment (XX)' OR [ResCd7] = 'Lec Facilities (LEC)')) 
                       OR ([PRIORITY] = '2' AND [ActOTR] >= '16' AND ([ResCd7] = 'Equipment (XX)' OR [ResCd7] = 'Lec Facilities (LEC)')) 
                       OR (([Rpt5] = '1' OR [Rpt30] = '1' OR [Chronic] = '1') AND ([ResCd7] = 'Equipment (XX)' OR [ResCd7] = 'Lec Facilities (LEC)')) THEN 'Yes' ELSE 'No' END AS [Measured],  
                CASE WHEN [Reviewed] = 1 THEN 'Yes' ELSE 'No' END AS [Reviewed],
                CASE WHEN EXISTS ( select * from SNA_Ticket_Detail recent 
              where recent.XX_CIRCUIT_ID = td.XX_CIRCUIT_ID
              AND recent.[MAIN_TICKET_ID] <> td.[MAIN_TICKET_ID]
              AND datediff( month, recent.[DtRFC], td.[DtRFC] ) < 1 
             AND recent.[DtRFC] < td.[DtRFC]) 
      THEN 'Yes' ELSE 'No' End As [Repeat],
                CASE WHEN [Chronic] = 1 THEN 'Yes' ELSE 'No' END AS [Chronic],
                CASE WHEN [ResCd7] = 'Equipment (XX)' THEN 'XX' 
                     WHEN [ResCd7] = 'Isolated to Customer (ITC)' THEN 'ITC' 
                     WHEN [ResCd7] = 'Information (INF)' THEN 'INF' 
                     WHEN [ResCd7] = 'Test OK (TOK)' THEN 'TOK' 
                     WHEN [ResCd7] = 'Lec Facilities (LEC)' THEN 'LEC' 
                     WHEN [ResCD7] = 'Dispatched No Trouble Found (NTF)' THEN 'NTF' 
                     WHEN [ResCD7] = 'Cleared While Testing (CWT)' THEN 'CWT' END AS [Resolution]
                FROM [SNA_Ticket_Detail] td WHERE ([CompID] = @CompID)

You should check the datediff does what you want - just play with some test data. Also you probably want to ensure the 'recent' line isn't actually after the one being retrieved, so I've added:

AND recent.[DtRFC] < td.[DtRFC]

Although if you know your ticket ids are sequential you could do the same thing with them instead of the date field.

Answer 2

try

SELECT(COUNT()...) > 1

instead of

SELECT(COUNT()...) > '1'

Answer 3

If you alias SNA_Ticket_Detail (e.g SNA_Ticket_Detail SNA) in the outer query, you can reference that in the subquery

for simplicity also alias SNA_Tick_Detail in the subQuery (SNA_Ticket_Detail SNA_sub)

Then, where you currently have (XX_CIRCUIT_ID = XX_CIRCUIT_ID) this would change to (SNA_sub.XX_CIRCUIT_ID = SNA.XX_CIRCUIT_ID)

Answer 4

For the minimum change to your SQL, change your sub-query like this:

SELECT ... FROM SNA_Ticket_Detail AS i WHERE ... AND i.XX_CIRCUIT_ID = [SNA_Ticket_Detail].[XX_CIRCUIT_ID]

You must somehow reference the outer table, you can't just compare XX_CIRCUIT_ID and XX_CIRCUIT_ID for equality - this will comparison always be true. ;-)

Instead, you must compare the outer XX_CIRCUIT_ID, referenced as [SNA_Ticket_Detail].[XX_CIRCUIT_ID] to the inner XX_CIRCUIT_ID, referenced as i.XX_CIRCUIT_ID for clarity.

Answer 5

That is one messy query...

You'll need to filter your "inner" query (a.k.a. subquery) to the outer query. The concept is generally called a correlated subquery. See Rory's solution.

Or, you could use a JOIN to a view (or derived table) that contains aggregated data. This is the best choice, generally, for performance.