CODEWITHSUNDEEP
sqlsql-servert-sql
Matthew Hait
Matthew Hait

Reputation: 65

Multiple counts and merge columns

I current have a query that grabs the number of parts made per hour between two dates:

DECLARE @StartDate datetime 
DECLARE @EndDate datetime 

SET @StartDate = '10/10/2018' 
SET @EndDate = '11/11/2018' 

SELECT 
    CONVERT(VARCHAR(10), CAST(presstimes AS DATE), 111) AS ForDate, 
    DATEPART(HOUR, presstimes) AS OnHour, 
    COUNT(*) AS Totals   
FROM
    partmasterlist 
WHERE 
    ((presstimes >= @StartDate AND presstimes < dateAdd(d, 1, @EndDate))  
    AND (((presstimes IS NOT NULL)))) 
GROUP BY
    CONVERT(VARCHAR(10), CAST(presstimes AS DATE), 111), 
    DATEPART(HOUR, presstimes) 
ORDER BY 
    CONVERT(VARCHAR(10), CAST(presstimes AS DATE), 111) ASC;

Output:

Date        Hour QTY
---------------------
2018/11/06  11   16
2018/11/06  12   20
2018/11/06  13   29
2018/11/06  14   26

Now I need to add another qty column to count where "trimmingtimes" is set.

I can't figure out how to full join the date and hour columns (e.g. presstimes might have 20qty for Hour 2, but trimmingtimes is NULL for Hour 2);

Input:

ID    presstimes                         trimmingtimes
-----------------------------------------------------------------
1     2018-10-10 01:15:23.000            2018-10-10 01:15:23.000
2     2018-10-10 01:15:23.000            NULL
3     2018-10-10 02:15:23.000            NULL
4     NULL                               2018-10-10 03:15:23.000

Output:

Date      hour    Press QTY   T QTY
------------------------------------
10/10/18  1           2         1
10/10/18  2           1         0
10/10/18  3           0         1

Upvotes: 0

Views: 41

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271181

I suspect you want something like this:

select convert(date, v.dt) as date,
       datepart(hour, v.dt) as hour,
       sum(ispress) as num_press,
       sum(istrim) as num_trim
from partmasterlist pml cross apply
     (values (pml.presstime, 1, 0), (pml.trimmingtime, 0, 1)
     ) v(dt, ispress, istrim)
group by convert(date, v.dt), datepart(hour, v.dt)
order by convert(date, v.dt), datepart(hour, v.dt);

You can add a where clause for a particular range.

Upvotes: 1

Related Questions