I have some calculations where a normal LAG function would solve it for me, which Ive tried. However. My question comes with an exception. Every january and july the value resets to 1500. On row nr 2 I need to subtract the value but with row nr 1 (this is where LAG comes in). This works but I dont know how to solve it with exception for july and then january again. See picture
Code I’ve worked with
SELECT SUM(ts.[HOURS]) as HOURS ,FORMAT(ts.STARTDATE, 'yyyyMM') as YearMonth ,YEAR(ts.STARTDATE) as Year ,MONTH(ts.STARTDATE) as Month ,LAG(SUM(ts.[HOURS])) OVER(ORDER BY FORMAT(ts.STARTDATE, 'yyyyMM') ) as lag --,IIF(MONTH(ts.STARTDATE) = 1 OR MONTH(ts.STARTDATE) = 7, 1500 - SUM(ts.[HOURS]), SUM(ts.HOURS)) as tidkvar ,LEAD(SUM(ts.[HOURS])) OVER(ORDER BY FORMAT(ts.STARTDATE, 'yyyyMM') ) as lead FROM [dbo].[TimesheetLines] AS ts WHERE ts.PROJECTID = '70000196.01.02' OR ts.PROJECTID = '70000196.02.02' OR ts.PROJECTID = '70000013.02' GROUP BY FORMAT(ts.STARTDATE, 'yyyyMM'), YEAR(ts.STARTDATE), MONTH(ts.STARTDATE) Order by YearMonth
After the answer below I tried
SELECT --SUM(ts.HOURS) FORMAT(ts.STARTDATE, 'yyyyMM') YearMonth ,1500 - sum(ts.HOURS) over (partition by year(ts.STARTDATE), (month(ts.STARTDATE) - 1) / 6 order by ts.STARTDATE) as TimmarKvar FROM FactTSTimesheetLines_UNPIVOT as ts WHERE ts.PROJECTID IN ('70000196.01.02','70000196.02.02','70000013.02') GROUP BY FORMAT(ts.STARTDATE, 'yyyyMM'), ts.STARTDATE, ts.HOURS Order by YearMonth
But I get error message that HOURS is not in an aggregate function nor GROUP BY. I added HOURS to GROUP BY but that obviously gives me rows per hour which is undesired
Advertisement
Answer
I put the months into groups and used a CTE to SUM for each group. Although Gordons answer is much simplier. Such as:
CREATE TABLE #Work (StartDate date, HOURS int) INSERT INTO #Work VALUES ('1/1/2019', 132), ('2/1/2019',258), ('3/1/2019',201), ('4/1/2019',181), ('5/1/2019',85), ('6/1/2019',186), ('7/1/2019',6), ('8/1/2019',156), ('9/1/2019',240), ('10/1/2019',419), ('11/1/2019',335), ('12/1/2019',109), ('1/1/2020',179), ('2/1/2020',228), ('3/1/2020',97) ;WITH CTE AS ( SELECT SUM(ts.[HOURS]) as HOURS ,FORMAT(ts.STARTDATE, 'yyyyMM') as YearMonth ,YEAR(ts.STARTDATE) as Year ,MONTH(ts.STARTDATE) as Month ,SUM(CASE WHEN MONTH(ts.STARTDATE) IN (1,7) THEN 1 ELSE 0 END) OVER(ORDER BY YEAR(ts.STARTDATE), MONTH(ts.STARTDATE)) GRP FROM #Work AS ts GROUP BY FORMAT(ts.STARTDATE, 'yyyyMM'), YEAR(ts.STARTDATE), MONTH(ts.STARTDATE), ts.HOURS ) SELECT * , 1500 - SUM(HOURS) OVER(PARTITION BY GRP ORDER BY MONTH) Total from CTE Order by YearMonth