SQL Query Question
-
Hello All,
I have a SQL question that I need some assistance with …
I need to query a table that contains several million records. I need the result set to return a single row for each distinct PID, EntryDate, EMPID combination with an aggregated column that represents the sum of the forecasted/actual hours for each row. However, the sum of the hours must only sum the rows with “actual” hours when the month of the EntryDate in that row is < the current month and sum the rows with “forecast” hours when the month of the EntryDate in that row is >= the current month. (There may be several forecast rows and actual rows in the same month for each distinct combination listed above)
Below is a sample of how the table looks and how I need the result set to look, can someone please assist with the appropriate SQL?
Thank you!
-
Sounds like a homework assignment. what did you try?
Also, if you post your question like this, you're more likely to get answers...
Nice runnable code to recreate your problem....
CREATE TABLE Finance(PID INT NOT NULL,
EntryDate DATE NOT NULL,
EmpID VARCHAR(5) NOT NULL,
EntryType VARCHAR(8) NOT NULL,
Hrs TINYINT);
GO
INSERT INTO Finance
VALUES (1234, '1/1/16', 'E023', 'Forecast', 5)
,(1234, '1/1/16', 'E023', 'Actual', 0)
,(1234, '2/1/16', 'E023','Forecast',12)
,(1234, '2/1/16', 'E023','Actual',3)
,(1234, '3/1/16', 'E023', 'Actual',3);
-
does seem a little like homework.....hmmm
anyways and only because I have been playing around with converting screen grabs into usuable create table statements.....
----http://www.sqlservercentral.com/Forums/Topic1784995-391-1.aspx
CREATE TABLE #Finance(
PID INTEGER NOT NULL
,EntryDate DATETIME NOT NULL
,EmpID VARCHAR(5) NOT NULL
,acTYPE VARCHAR(9) NOT NULL
,HRS INTEGER NOT NULL
);
INSERT INTO #Finance(PID,EntryDate,EmpID,acTYPE,HRS) VALUES (1234,'1/1/16','E023','Forecast',5);
INSERT INTO #Finance(PID,EntryDate,EmpID,acTYPE,HRS) VALUES (1234,'1/1/16','E023','Actual',0);
INSERT INTO #Finance(PID,EntryDate,EmpID,acTYPE,HRS) VALUES (1234,'2/1/16','E023','Forecast',12);
INSERT INTO #Finance(PID,EntryDate,EmpID,acTYPE,HRS) VALUES (1234,'2/1/16','E023','Actual',3);
INSERT INTO #Finance(PID,EntryDate,EmpID,acTYPE,HRS) VALUES (1234,'3/1/16','E023','Forecast',0);
INSERT INTO #Finance(PID,EntryDate,EmpID,acTYPE,HRS) VALUES (1234,'3/1/16','E023','Actual',23);
INSERT INTO #Finance(PID,EntryDate,EmpID,acTYPE,HRS) VALUES (1234,'4/1/16','E023','Actual',4);
INSERT INTO #Finance(PID,EntryDate,EmpID,acTYPE,HRS) VALUES (1234,'5/1/16','E023','Forecast',12);
INSERT INTO #Finance(PID,EntryDate,EmpID,acTYPE,HRS) VALUES (1234,'6/1/16','E023','Actual',5);
INSERT INTO #Finance(PID,EntryDate,EmpID,acTYPE,HRS) VALUES (1234,'7/1/16','E023','Actual',0);
INSERT INTO #Finance(PID,EntryDate,EmpID,acTYPE,HRS) VALUES (1234,'7/1/16','E023','Forecast',46);
INSERT INTO #Finance(PID,EntryDate,EmpID,acTYPE,HRS) VALUES (1234,'7/1/16','E023','Forecast',12);
INSERT INTO #Finance(PID,EntryDate,EmpID,acTYPE,HRS) VALUES (1234,'7/1/16','E023','Forecast',2);
INSERT INTO #Finance(PID,EntryDate,EmpID,acTYPE,HRS) VALUES (1234,'8/1/16','E023','Forecast',1);
INSERT INTO #Finance(PID,EntryDate,EmpID,acTYPE,HRS) VALUES (1234,'8/1/16','E023','Actual',0);
INSERT INTO #Finance(PID,EntryDate,EmpID,acTYPE,HRS) VALUES (2222,'2/1/16','MD123','Forecast',23);
INSERT INTO #Finance(PID,EntryDate,EmpID,acTYPE,HRS) VALUES (2222,'2/1/16','MD123','Forecast',4);
INSERT INTO #Finance(PID,EntryDate,EmpID,acTYPE,HRS) VALUES (2222,'2/1/16','MD123','Forecast',12);
INSERT INTO #Finance(PID,EntryDate,EmpID,acTYPE,HRS) VALUES (2222,'2/1/16','MD123','Actual',5);
INSERT INTO #Finance(PID,EntryDate,EmpID,acTYPE,HRS) VALUES (2222,'3/1/16','MD123','Actual',9);
INSERT INTO #Finance(PID,EntryDate,EmpID,acTYPE,HRS) VALUES (2222,'3/1/16','MD123','Actual',0);
INSERT INTO #Finance(PID,EntryDate,EmpID,acTYPE,HRS) VALUES (2222,'4/1/16','MD123','Forecast',12);
INSERT INTO #Finance(PID,EntryDate,EmpID,acTYPE,HRS) VALUES (2222,'5/1/16','MD123','Forecast',6);
INSERT INTO #Finance(PID,EntryDate,EmpID,acTYPE,HRS) VALUES (2222,'6/1/16','MD123','Forecast',4);
INSERT INTO #Finance(PID,EntryDate,EmpID,acTYPE,HRS) VALUES (2222,'7/1/16','MD123','Actual',3);
INSERT INTO #Finance(PID,EntryDate,EmpID,acTYPE,HRS) VALUES (2222,'7/1/16','MD123','Forecast',1);
INSERT INTO #Finance(PID,EntryDate,EmpID,acTYPE,HRS) VALUES (2222,'7/1/16','MD123','Actual',0);
INSERT INTO #Finance(PID,EntryDate,EmpID,acTYPE,HRS) VALUES (2222,'7/1/16','MD123','Forecast',2);
-- larger data set here
--WITH ED as (
--SELECT EntryDate = DATEADD(MM, DATEDIFF(MM, 0, '20160801')-n, 0)
--FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11))a(n)
--)
--,
--AC as (
--SELECT acTYPE = t FROM (VALUES('Actual'),('Forecast')) b(t)
--)
--,
-- e(n) AS (
--SELECT n
--FROM ( VALUES(1), (1), (1), (1), (1), (1), (1), (1), (1), (1) ) t(n)
--)
--,
--PID as (
--SELECT TOP(100000) ROW_NUMBER()OVER(ORDER BY ( SELECT NULL) ) PID
--FROM e a, e b, e c, e d, e e, e f
--)
--SELECT AC.acTYPE, ED.EntryDate, PID.PID, HRS = CAST(Rand(Checksum(Newid())) * 20 AS INT)
--INTO #Finance
--FROM AC CROSS JOIN ED CROSS JOIN PID
--CREATE CLUSTERED INDEX [CI_Finance] ON #Finance
--([PID] ASC,
--[EntryDate] ASC
--)
-- possible solution
DECLARE @mthstart as DATETIME
SET @mthstart = (SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))
SELECT EntryDate,
PID,
ISNULL((CASE
WHEN EntryDate < @mthstart
THEN SUM(CASE WHEN acTYPE = 'Actual' THEN HRS END)
ELSE SUM(CASE WHEN acTYPE = 'Forecast' THEN HRS END)
END), 0) as TTL_HRS
--INTO #dump
FROM #Finance
GROUP BY EntryDate, PID
ORDER BY PID, EntryDate;
--DROP TABLE #dump
DROP TABLE #Finance
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day -
J Livingston has a good solution. I'd suggest you look through specific records and types, and then modify his code to help you. The CASE statement is what you'll use to decide which values to SUM.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply