2

I have spent all morning on this and just can't get it right... I'd really appreciate the help of someone more knowledgable than myself to get this working.

I have a table with some data in that looks like this:

MonthYear WeekBeg.  Week  Value  
Dec-10  27/12/2010  1   66.66  
Jan-11  3/01/2011   2   50  
Jan-11  10/01/2011  3   17.5  
Jan-11  17/01/2011  4   20  
Jan-11  24/01/2011  5   0  
Jan-11  31/01/2011  6   50  
Feb-11  7/02/2011   7   0  
Feb-11  14/02/2011  8   74  
Feb-11  21/02/2011  9   100

I'm sorry the table above doesn't look better... I need to calculate the difference between the values from week to week - so the results column in this case would be:

16.66
32.5
2.5
20
50
50
74
26

I've looked at lots of code on the net - (e.g. from this site) but can't seem to make it work. I added in the ABS function to make sure the differences were absolute values and got this working but the numbers themselves just aren't right.

I haven't posted what I ended up with as it just got into a bigger and bigger mess, but what I started with was the link above. Again, I'd be really grateful for any insight anyone is able to offer.

Many thanks

ADDED:

Thanks so much for the fast reply. Got this working easily - added a few bits:

SELECT T1.MonthYear AS [From], T2.MonthYear AS [To], T1.Week AS Week, T1.WeekBeg AS WeekBeg, ABS(T1.Value - T2.Value) AS Difference FROM Test AS T1 LEFT JOIN Test AS T2 ON T2.Week = T1.Week + 1

Only thing is the resulting difference values need to be in the second of the two rows whereas here they are in the first of the two. Is there any easy way of modifying this?

Many thanks again.

ADDED:

Would definitely be worth using the second option if possible as can't always guarantee weeks won't be missed out. I am probably missing something, but when I run the second option from Thomas, I get the message:

'The specified field [T1].[Datavalue] could refer to more than one table listed in the FROM clause of your SQL statement'.

I thought this might be to do with the field in the table being VALUE not DataValue, but when I change it, I get 'Type Mismatch in Expression' instead.

Many thanks.

David
  • 21
  • 3

2 Answers2

3

Presuming the Week column is perfectly sequential:

Select T1.MonthYear As T1Year
    , T1.WeekBeg As T1WeekBeg
    , T2.MonthYear As T2Year
    , T2.WeekBeg As T2WeekBeg
    , [T2].[Value]-[T1].[Value] AS Expr1
From TableWithData AS T1 
    Left Join TableWithData AS T2 
        On T1.Week = T2.Week + 1;

It should be noted that this will not compile in the QBE designer. You will have to view and modify it purely through the SQL View (or in code)

If for some reason you could not depend on the Week number being sequential, then it gets trickier as you need to use a derived table. Again, this solution will only work in SQL View or in code:

Select T1.MonthYear, T1.WeekBeg
    , T2.MonthYear, T2.WeekBeg
    , [T2].[Value]-[T1].[Value] AS Diff
From (TableWithData AS T1 
    Inner Join (
            Select T1.WeekBeg As T1WeekBeg
                    , Min(T2.WeekBg) As T2WeekBeg
                From TableWithData As T1 
                    Left Join TableWithData AS T2 
                        On T2.WeekBeg > T1.WeekBeg
                Group By T1.WeekBeg
                ) As Query1
        On T1.WeekBeg = Query1.T1WeekBeg) 
    Inner Join TableWithData AS T2 
        On Query1.T2WeekBeg = T2.WeekBeg;
Thomas
  • 63,911
  • 12
  • 95
  • 141
  • +1 Nice call using the sequential week #. Hopefully it will gel with his biz logic. – Paul Sasik Mar 05 '11 at 01:50
  • Thanks so much for the fast reply. Got this working easily - added a few bits: SELECT T1.MonthYear AS [From], T2.MonthYear AS [To], T1.Week AS Week, T1.WeekBeg AS WeekBeg, ABS(T1.Value - T2.Value) AS Difference FROM Test AS T1 LEFT JOIN Test AS T2 ON T2.Week = T1.Week + 1; Only thing is the resulting difference values need to be in the second of the two rows whereas here they are in the first of the two. Is there any easy way of modifying this? – David Mar 05 '11 at 01:58
  • @David: Please add that comment text into your original post. It is much too difficult to read as a comment. Thx – Paul Sasik Mar 05 '11 at 02:01
  • Added another bit to the Question above. Cheers – David Mar 05 '11 at 02:32
  • @David - I revised my first query. I tested both queries against a replica of the data you posted and I get the correct results. In the first query, if you want the first row to have a value (and the last row to be null) just change the logic to be `T1.Week = T2.Week - 1` – Thomas Mar 05 '11 at 03:05
  • @David - Similarly, I corrected the column names and updated the equation to `[T2].[DataValue]-[T1].[DataValue]`. Like the first query, if you want to reverse whether the first row gets a value or the last row, just change the logic in the inner most query to `T2.WeekBeg < T1.WeekBeg` – Thomas Mar 05 '11 at 03:08
  • Thanks heaps Thomas - I really appreciate your help, I spent hours playing around with this! Cheers, David. – David Mar 05 '11 at 03:22
  • @David - Btw, if this solved your problem, it would be appreciated if you marked this as the answer. – Thomas Mar 07 '11 at 19:22
0

A version based off of the sample query from your base link. (It uses ORDERY BY on the Week field and TOP 1 too isolate a scalar value.)

SELECT  t1.Value - (SELECT TOP 1 t2.Value FROM myTable AS t2 
                               WHERE t2.Week < t1.Week
                               ORDER BY t2.Week DESC) AS t2Val
FROM myTable t1
WHERE (SELECT TOP 1 t3.Value FROM myTable AS t3
       WHERE t1.Week < t3.Week) Is Not Null
ORDER BY t1.Week;

Should be close to working but the aliasing is very error prone. I suggest that if the week numbers are indded sequential that you go with Thomas' answer.

Paul Sasik
  • 79,492
  • 20
  • 149
  • 189
  • Hi Paul; Item was in the linked example but isn't in my table - what should that be instead? Thanks for your time. Cheers, David. – David Mar 05 '11 at 02:33
  • Oops. I don't think that predicate belonged. Like I said... error prone. – Paul Sasik Mar 05 '11 at 02:58