CODEWITHSUNDEEP
sqlsql-serverdate
Erin
Erin

Reputation: 1

SQL Server performing same calculations on different dates in same query

I have two separate (basic) queries that are essentially the same except one is calculating a month to date sum and the the other is calculating a year to date sum. Instead of having two separate tables, I would like each employee to have a month to date and year to date column next to their name. I've tried unions and some subqueries, but I'm still having trouble. I've looked at a lot of resources but haven't found what I can use.

--Query 1:
SELECT 
    A.EMPLOYEENUMBER AS 'Employee No.', 
    B.ENTITYNAME AS 'Employee Office', 
    B.NAME1 AS 'Employee Information', 
    SUM(A.NUMBEROF) AS 'Ent. Qty'
FROM prod.DAILYTIMESHEETLINE A
JOIN prod.EMPLOYEE B on A.EMPLOYEENUMBER = B.EMPLOYEENUMBER
WHERE A.THEDATE BETWEEN '2017.04.01' AND '2017.04.30'
GROUP BY A.EMPLOYEENUMBER, B.NAME1, B.ENTITYNAME
ORDER BY A.EMPLOYEENUMBER


--Query 2:
SELECT 
    A.EMPLOYEENUMBER AS 'Employee No.', 
    B.ENTITYNAME AS 'Employee Office', 
    B.NAME1 AS 'Employee Information', 
    SUM(A.NUMBEROF) AS 'Ent. Qty'
FROM prod.DAILYTIMESHEETLINE A
JOIN prod.EMPLOYEE B on A.EMPLOYEENUMBER = B.EMPLOYEENUMBER
WHERE A.THEDATE BETWEEN '2017.01.01' AND '2017.04.30'
GROUP BY A.EMPLOYEENUMBER, B.NAME1, B.ENTITYNAME
ORDER BY A.EMPLOYEENUMBER

Upvotes: 0

Views: 59

Answers (1)

Esperento57
Esperento57

Reputation: 17462

try this :

SELECT 
    A.EMPLOYEENUMBER AS 'Employee No.', 
    B.ENTITYNAME AS 'Employee Office', 
    B.NAME1 AS 'Employee Information',
    SUM(case when A.THEDATE BETWEEN '2017.01.01' AND '2017.04.30' then A.NUMBEROF else null end) AS 'Ent. Qty1', 
    SUM(case when A.THEDATE BETWEEN '2017.04.01' AND '2017.04.30' then A.NUMBEROF else null end) AS 'Ent. Qty2'
FROM prod.DAILYTIMESHEETLINE A
JOIN prod.EMPLOYEE B on A.EMPLOYEENUMBER = B.EMPLOYEENUMBER
WHERE A.THEDATE BETWEEN '2017.01.01' AND '2017.04.30'
GROUP BY A.EMPLOYEENUMBER, B.NAME1, B.ENTITYNAME
ORDER BY A.EMPLOYEENUMBER

Be carefull to where : I let BETWEEN '2017.01.01' AND '2017.04.30' because '2017.01.01' < '2017.04.01' , Modify in consequence ;)

Upvotes: 1

Related Questions