CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2014
Sam Bin Ham
Sam Bin Ham

Reputation: 449

Find count in SQL using different set of dates

I have below query to find the counts based on different date fields.

How can I get the result like below expected result?

The query shown is not returning the actual counts like the sample result.

+-------+-----------+------------+-----------+----------+
| WO_id | DateOpen  | DateFinish | DateClose | Location |
+-------+-----------+------------+-----------+----------+
|   100 | 16-Dec-18 | 18-Dec-18  | 19-Dec-18 | A        |
|   101 | 16-Dec-18 | 18-Dec-18  | 19-Dec-18 | A        |
|   102 | 17-Dec-18 | 19-Dec-18  | 20-Dec-18 | C        |
|   103 | 10-Dec-18 | 11-Dec-18  | 16-Dec-18 | D        |
|   104 | 17-Dec-18 | 19-Dec-18  | 18-Dec-18 | E        |
+-------+-----------+------------+-----------+----------+

Query (selection criteria : 16-Dec-2018 data only):

SELECT 
    COUNT(DateOpen) AS Opened,
    COUNT(DateClose) AS closed,
    COUNT(DateFinish) AS finished, 
    Location
FROM
    JOB 
WHERE 
    JOB.DateOpen BETWEEN '12/16/2018' AND DATEADD(DAY, 1, '12/16/2018')
    OR JOB.DateClose BETWEEN '12/16/2018' AND DATEADD(DAY, 1, '12/16/2018')
    OR JOB.DateFinish BETWEEN '12/16/2018' AND DATEADD(DAY, 1, '12/16/2018')
GROUP BY
    Location

Expected result:

+--------+----------+--------+----------+
| opened | finished | closed | Location |
+--------+----------+--------+----------+
|      2 |        0 |      0 | A        |
|      0 |        0 |      1 | D        |
+--------+----------+--------+----------+

Upvotes: 0

Views: 52

Answers (3)

Rachel_Wang
Rachel_Wang

Reputation: 57

In your original script, you count all rows and I think you might have the incorrect conditions. Please try following script.

create table JOB 
 (WO_id int, 
 DateOpen date,
 DateFinish date,
 DateClose date,
 Location varchar(20))
 insert into JOB values 
(100,'16-Dec-18','18-Dec-18','19-Dec-18','A'),
(101,'16-Dec-18','18-Dec-18','19-Dec-18','A'),
(102,'17-Dec-18','19-Dec-18','20-Dec-18','C'),
(103,'10-Dec-18','11-Dec-18','16-Dec-18','D'),
(104,'17-Dec-18','19-Dec-18','18-Dec-18','E')

;with cte as (
SELECT 
CASE WHEN DateOpen  = '12/16/2018' THEN 1 ELSE 0 end as Opened,
CASE WHEN DateClose  = '12/16/2018' THEN 1 ELSE 0 end as closed,
CASE WHEN DateFinish  = '12/16/2018' THEN 1 ELSE 0 end as finished,
Location
FROM JOB )
select sum(Opened) as Opened,sum(closed) as closed,sum(finished) as finished,Location 
from cte  
WHERE Opened <>0  or  closed<>0 or  finished <>0
group by Location
/*
Opened      closed      finished    Location
----------- ----------- ----------- --------------------
2           0           0           A
0           1           0           D
*/

Best Regards,

Rachel

Upvotes: 0

GreyOrGray
GreyOrGray

Reputation: 1729

This should get you the results you're looking for. It only returns the rows that match the specific date criteria. The way you have your query currently built will result in it also pulling in rows that match 12/17/2018. You can test it here: https://rextester.com/MHT79618

DECLARE @SelectionDate DATETIME = '12/16/2018'

SELECT 
    SUM (CASE WHEN DateOpen  = @SelectionDate THEN 1 ELSE 0 end) as Opened
    ,SUM (CASE WHEN DateClose  = @SelectionDate THEN 1 ELSE 0 end)as closed
    ,SUM (CASE WHEN DateFinish  = @SelectionDate THEN 1 ELSE 0 end)as finished
    ,Location
FROM JOB 
    WHERE JOB.DateOpen = @SelectionDate
    or  JOB.DateClose = @SelectionDate
    or  JOB.DateFinish = @SelectionDate
group by Location

edit damn, just saw Hogan answered while I was typing with basically the same answer.

Upvotes: 2

Hogan
Hogan

Reputation: 70528

There is a trick you can with SUM and CASE, you use case to select 1 when it matches the criteria or 0 otherwise and then sum to "count" those items -- (since summing 0 or null is like not counting something). Here is the codez: 

SELECT
  SUM(CASE WHEN JOB.DateOpen   BETWEEN '12/16/2018' AND DATEADD(DAY, 1, '12/16/2018') THEN 1 ELSE 0 END) AS opened,
  SUM(CASE WHEN JOB.DateFinish BETWEEN '12/16/2018' AND DATEADD(DAY, 1, '12/16/2018') THEN 1 ELSE 0 END) AS finished,
  SUM(CASE WHEN JOB.DateClose  BETWEEN '12/16/2018' AND DATEADD(DAY, 1, '12/16/2018') THEN 1 ELSE 0 END) AS closed,
  location
FROM JOB 
group by Location

Upvotes: 2

Related Questions