CODEWITHSUNDEEP
sqlsql-server
Josh Kirkpatrick
Josh Kirkpatrick

Reputation: 403

Combining SQL querys in to one query

Hi i have a set of querys for getting data

two counting rows for the day and two for night, what i would like is to combine these in to one query. 

SELECT controllerID
    ,COUNT(CardID) AS GoodCountDay
FROM ReaderData
WHERE (
        ReaderTime BETWEEN '08:00:00'
            AND '20:00:00'
        )
    AND (CardID = 'fffffff0')
    AND (DATEDIFF(DAY, DATEADD(DAY, - 0, CURRENT_TIMESTAMP), dtReading) = 0)
GROUP BY controllerID

SELECT controllerID
    ,COUNT(CardID) AS ScrapCountDay
FROM ReaderData
WHERE (
        ReaderTime BETWEEN '08:00:00'
            AND '20:00:00'
        )
    AND (CardID = '007CF00B')
    AND (DATEDIFF(DAY, DATEADD(DAY, - 0, CURRENT_TIMESTAMP), dtReading) = 0)
GROUP BY controllerID

SELECT controllerID
    ,COUNT(CardID) AS GoodCountNight
FROM ReaderData
WHERE (dtReading >= DATEADD(hour, 20, DATEADD(day, - 1, CAST(CAST(GETDATE() AS DATE) AS DATETIME))))
    AND (dtReading < DATEADD(hour, 8, CAST(CAST(GETDATE() AS DATE) AS DATETIME)))
GROUP BY controllerID

SELECT controllerID
    ,count(CardID) AS ScrapCountNight
FROM ReaderData
WHERE dtReading >= dateadd(hour, 20, dateadd(day, - 1, cast(cast(getdate() AS DATE) AS DATETIME)))
    AND dtReading < dateadd(hour, 8, cast(cast(getdate() AS DATE) AS DATETIME))
    AND (CardID = '007CF00B')
GROUP BY controllerID

All of these querys have the same out put which looks like this 

 controllerID   GoodCountDay
            2   207
            28  245
            30  267
            33  314
            35  471
            37  65
            38  17
            40  175

Is there any way i can combine these in to one query so the out put will be such 

 controllerID GoodCountDay  ScrapCountDay  GoodCountNight  ScrapCountNight
      2           207              12             123              1
      28          245              123
      30          267         
      33          314
      35          471
      37          65
      38          17
      40         175

Upvotes: 1

Views: 64

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270883

You can use conditional aggregation:

SELECT controllerID,
       SUM(CASE WHEN ReaderTime BETWEEN '08:00:00' AND '20:00:00' AND
                     DATEDIFF(DAY, DATEADD(DAY, - 0, CURRENT_TIMESTAMP), dtReading) = 0) AND
                     CardID = 'fffffff0' THEN 1 ELSE 0 END) as GoodCountDay
       SUM(CASE WHEN ReaderTime BETWEEN '08:00:00' AND '20:00:00' AND
                     DATEDIFF(DAY, DATEADD(DAY, - 0, CURRENT_TIMESTAMP), dtReading) = 0) AND
                     CardID = '007CF00B' THEN 1 ELSE 0 END) as ScrapCountDay,
       SUM(CASE WHEN dtReading >= DATEADD(hour, 20, DATEADD(day, - 1, CAST(CAST(GETDATE() AS date) AS datetime)))) AND
                     (dtReading < DATEADD(hour, 8, CAST(CAST(GETDATE() AS date) AS datetime)) AND
                     CardID = 'fffffff0' THEN 1 ELSE 0 END) as GoodCountNight
       SUM(CASE WHEN dtReading >= DATEADD(hour, 20, DATEADD(day, - 1, CAST(CAST(GETDATE() AS date) AS datetime)))) AND
                     (dtReading < DATEADD(hour, 8, CAST(CAST(GETDATE() AS date) AS datetime)) AND
                     CardID = '007CF00B' THEN 1 ELSE 0 END) as ScrapCountNight
FROM   ReaderData
group by controllerID;

Upvotes: 7

Related Questions