Skip to content
Advertisement

Subtract value from previous value with exception

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

enter image description here

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
User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement