-2

I have a SQL Server table that has columns

[Username], [Team], [ID] (* primary key), [DateEntered], [Task], [T_ID], [ValueAmount]

The [T_ID] can be grouped as there could be 5 different tasks which all have the same T_ID example (a,b,c,d,e with the T_ID of 1).

What I am trying to do is see the [ValueAmount], LastWeeks_ValueAmount side by side but can't figure it out.

I have tried the following SQL

SELECT
    UserName, Team, ID, [DateEntered], [Task], T_ID, ValueAmount,
    LAG(ValueAmount) OVER (ORDER BY DateEntered) AS PreviousWeek,
    DATEADD(dd, ((DATEDIFF(dd, '17530101', DateEntered) / 7) * 7) - 7, '17530101') StartPW,
    DATEADD(dd, ((DATEDIFF(dd, '17530101', DateEntered) / 7) * 7) , '17530101') EndPW,
    ROW_NUMBER() OVER (PARTITION BY [T_ID] ORDER BY DateEntered DESC) AS rn
FROM
    Table

This returns the previous row, but not for the previous week so it's still not working. I think maybe a CTE with a case statement might work, but I can't figure it out and I hope someone can help?

I have tried to use the post below cut still can't get it to work:

https://stackoverflow.com/questions/65315982/using-sql-to-pad-values-to-calculate-previous-week-values

To add some more detail there can be a multiple rows within the same week for example the one week there could be 2 rows for the same Task, Team etc, one being T_ID 1 and one being T_ID 26 the expected result would pick up the value for both on the previous week as they are for the same Team, Task and UserName.

New2Programming
  • 351
  • 1
  • 4
  • 17
  • 5
    Sample data and expected results will really help us help you here. – Thom A Aug 08 '23 at 14:00
  • @New2Programming the idea is to give you some guidance to solve the problem at hand. Without looking at the data, we cannot write a query for you that you can copy paste. The idea is you join the table to itself, on whatever columns you need to, and also use the date column in the join condition with D- Week to get the values from a week ago, instead of using LAG function. Try to understand the idea people are suggesting instead of looking for a COPY PASTE answer :) – M.Ali Aug 10 '23 at 11:31

1 Answers1

1

It is not tested, but I think it should give you the data

WITH cte AS (
    SELECT UserName, Team, ID, [DateEntered], [Task], T_ID, ValueAmount
    , DATEPART(WK, [DateEntered]) AS WeekNo
)
SELECT ThisWeek.*, LastWeek.ValueAmount AS LastWeek_ValueAmount
FROM (
    SELECT UserName, Team, T_ID, WeekNo, SUM(ValueAmount) AS ValueAmount
    FROM cte
    GROUP BY UserName, Team, T_ID, WeekNo
) AS ThisWeek
LEFT JOIN (
    SELECT UserName, Team, T_ID, WeekNo, SUM(ValueAmount)
    FROM cte
    GROUP BY UserName, Team, T_ID, WeekNo
) AS LastWeek ON LastWeek.UserName = ThisWeek.UserName AND LastWeek.Team = ThisWeek.Team 
    AND LastWeek.T_ID = ThisWeek.T_ID AND LastWeek.WeekNo = ThisWeek.WeekNo - 1
Eric
  • 3,165
  • 1
  • 19
  • 25
  • Hi Eric, Thanks for this, I have added in the as ValueAmount on the LastWeek Join and from table on the cte and I am now just playing with the query to get it to work but this is very close so I will update when it gives me the correct result and working. – New2Programming Aug 09 '23 at 07:00
  • It is now working thank you @Eric. I removed the sum from ValueAmount and inlcudedin the group by also replaced T_ID with Task on LastWeek.T_ID = ThisWeek.T_ID – New2Programming Aug 09 '23 at 07:47