CODEWITHSUNDEEP
sqlsql-server
Shmewnix
Shmewnix

Reputation: 1573

Query selecting all records for last column

I have a query that pulls multiple barcode scans for a specific day for a date range. Here is my query:

SELECT @startdate                                        AS startdate, 
       @enddate                                          AS enddate, 
       spartnumber, 
       Cast(Floor(Cast(dtcreated AS FLOAT)) AS DATETIME) AS dtCreatedDate, 
       Count(spartnumber)                                AS [Number of Scans], 
       Stuff ((SELECT ', ' 
                      + RIGHT(CONVERT(VARCHAR, dtcreated, 100), 7) AS [text()] 
               FROM   ticketsdetails AS subticket 
               WHERE  ( dtcreated BETWEEN Cast(Floor(Cast(ticketsdetails.dtcreated AS FLOAT)) AS DATETIME) 
                                          AND Cast(Floor(Cast(ticketsdetails.dtcreated AS FLOAT)) AS DATETIME) +'23:59:59' ) 
       AND ( dbltotal <= '0' ) 
       AND ( spartnumber NOT LIKE ' ' ) 
       FOR xml path('')), 1, 1, '')                      AS [Times Scanned] 
FROM   ticketsdetails 
WHERE  ( dtcreated BETWEEN @startdate AND @enddate ) 
       AND ( detail_type_id = '11' ) 
       AND ( dblamount < '0' ) 
       AND ( bpaid = 'true' ) 
GROUP  BY spartnumber, 
          Cast(Floor(Cast(dtcreated AS FLOAT)) AS DATETIME) 
HAVING ( Count(*) > 1 ) 
ORDER  BY dtcreateddate

Below is my output. I can only fit 4 of the times scanned to keep the chart, but it lists ALL of the times a barcode was scanned for that day, instead of only the two times it was scanned for that day, and is showing blank partnumbers.

Startdate  enddate spartnumber   dtcreateddate  #of Scans   Times scanned
12/1/15    12/11/15  1833        12/1/15          2     8:47,8:48,8:49 8:51
12/1/15    12/11/15  194         12/2/15          2    8:26,8:28,8:39,8:40
12/1/15    12/11/15              12/2/15          4    8:26,8:28,8:39,8:40

I'd like to only show the times a specific barcode was scanned, and not display blank barcodes. My output should be:

Startdate  enddate spartnumber   dtcreateddate  #of Scans   Times scanned
12/1/15    12/11/15  1833        12/1/15          2         11:45AM, 5:05PM
12/1/15    12/11/15  194         12/2/15          2         9:55AM, 5:50:PM

How can I fix the "Times scanned" to only display the times each specific barcode was actually scanned, not all times a barcode was scanned for that day, AND how can I remove blank barcodes (spartnumber)

Upvotes: 0

Views: 38

Answers (1)

DhruvJoshi
DhruvJoshi

Reputation: 17126

Since you have not provided the input data set, I'd go ahead with your problem statement and make appropriate changes

How can I fix the "Times scanned" to only display the times each specific barcode was actually scanned, not all times a barcode was scanned for that day, 

--added (sPartNumber=ticketsdetails.sPartNumber) in the inner STUFF's WHERE clause

how can I remove blank barcodes (spartnumber)

--shifted the WHERE CLAUSE from STUFF to outside query

Below is the SQL query.

SELECT  
    @startdate AS startdate, 
    @enddate AS enddate, 
    sPartNumber, 
    CAST(FLOOR(CAST(dtCreated AS FLOAT)) AS DATETIME) AS dtCreatedDate, 
    COUNT(sPartNumber) AS [Number of Scans], 
    STUFF
         ((SELECT     ', ' + RIGHT(CONVERT(VARCHAR, dtCreated, 100), 7) AS [text()]
                FROM    TicketsDetails AS subticket
            WHERE     
                (
                    dtCreated BETWEEN 
                                CAST(FLOOR(CAST(ticketsdetails.dtcreated AS FLOAT)) AS DATETIME) 
                                    AND 
                                CAST(FLOOR(CAST(ticketsdetails.dtcreated AS FLOAT)) AS DATETIME) + '23:59:59'
                )
                AND 
                (dblTotal <= '0') 
                AND 
                (sPartNumber=ticketsdetails.sPartNumber) -- this will get correct times
            FOR XML path('')), 1, 1, '') AS [Times Scanned]
FROM         
    TicketsDetails
WHERE     
    (dtCreated BETWEEN @startdate AND @enddate) 
    AND (Detail_Type_ID = '11') 
    AND (dblAmount < '0') 
    AND (bPaid = 'true')
    AND (sPartNumber NOT LIKE '') -- This will remove the blank partnumbers
GROUP BY sPartNumber, CAST(FLOOR(CAST(dtCreated AS FLOAT)) AS DATETIME)
HAVING      (COUNT(*) > 1)
ORDER BY dtcreateddate

Upvotes: 1

Related Questions