CODEWITHSUNDEEP
sqlsql-serverdatabase
vyshakh
vyshakh

Reputation: 41

A SQL query for the retrieval of result based on input month

Table 1

enter image description here

Table 2

enter image description here

My requirement is to input the Redemption month and list the tickets that has been scanned double or more.

For example Ticket No. T1 has been scanned 2 times under pickup,only once under PickupOutforDelivery and 2 times under Delivery.

Result needed like this:

enter image description here

How can I write a query to get the result like this?

Tried:

SELECT 
    Ticket, 
    COUNT(Scantype = 0) AS Pickup,
    COUNT(Scantype = 1) AS PickupOutforDelivery,
    COUNT(Scantype = 2) AS Delivery 
FROM 
    Scans
GROUP BY 
    Ticket, ScanType 
HAVING
    (Pickup > 1 OR PickupOutforDelivery > 1 OR Delivery > 1) 
    OR (Pickup >= 1 AND PickupOutforDelivery >= 1)
ORDER BY 
    Ticket

Result
enter image description here

Upvotes: 0

Views: 61

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

Because you are using the numeric value of scanType, no JOIN is needed. So, the only fix is needed for conditional aggregation:

SELECT Ticket, 
       SUM(CASE WHEN Scantype = 0 THEN 1 ELSE 0 END) as Pickup,
       SUM(CASE WHEN Scantype = 1 THEN 1 ELSE 0 END) as PickupOutforDelivery,
       SUM(CASE WHEN Scantype = 2 THEN 1 ELSE 0 END) as Delivery 
FROM Scans
WHERE redemptionMonth = 'Jan-21'
GROUP BY Ticket 
HAVING Pickup > 1 OR
       PickupOutforDelivery > 1 OR
       Delivery > 1 OR 
       (Pickup >= 1 AND PickupOutforDelivery >= 1)
ORDER BY Ticket;

Note that you can add redemptionMonth to the GROUP BY (and SELECT) to get the results for each month.

If redemptionMonth is really a date and not a string, then define the time period using a range of dates:

WHERE redemptionMonth >= '2021-01-01' AND
      redemptionMonth < '2021-02-01'

Upvotes: 0

SQLpro
SQLpro

Reputation: 5187

Assuming that RedemptionMonth has a datatype of DATE (which is clearly required); the following query will give you the result you want, except the "cosmetic" part (breaking by year month for the report part) that you have to do on your application:

SELECT YEAR(RedemptionMonth) AS [YEAR], MONTH(RedemptionMonth) AS [MONTH], TicketNo, 
       COALESCE(SUM(CASE WHEN ScanName = 'Pickup' THEN 1 ELSE 0 END), 0) AS Pickup,
       COALESCE(SUM(CASE WHEN ScanName = 'PickupOutForDelivery' THEN 1 ELSE 0 END), 0) AS PickupOutForDelivery ,
       COALESCE(SUM(CASE WHEN ScanName = 'Delivery' THEN 1 ELSE 0 END), 0) AS Delivery
FROM   [Table 1] AS T1
       JOIN [Table 2] AS T2 
          ON T1.ScanType = T2.ScanType
GROUP  BY YEAR(RedemptionMonth) AS [YEAR], MONTH(RedemptionMonth) AS [MONTH], TicketNo

Upvotes: 1

Related Questions