Post reply

SQL to do a difference between vertical data items

  • November 6, 2008 at 6:52 am

    #193490

    i have one table with archived valuations for customers and products stored like:

    arcDate, clientName , prod , value

    05/11/2008,ABC, prod1, 10

    05/11/2008,ABC, prod2, 1

    05/11/2008,ABC, prod3, 100

    06/11/2008,ABC, prod1, 9

    06/11/2008,ABC, prod2, 2

    06/11/2008,ABC, prod3, 103

    etc..........

    how can i query to get the following output as the change in value between the 6th and the 5th. dates would be part of the select query as would the clientname :

    output:

    clientName , prod , value

    ABC, prod1, -1

    ABC, prod2, 1

    ABC, prod3, 3

    is there a quicker way of getting this into a single compact query rather than having to create a temp table or results for each date (2 tables) and then joining clientName and prod and calcing the diff between the valuations?

  • November 6, 2008 at 7:26 am

    #894847

    Self join the table on ClientName, Product, and ArcDate with a date function like ArcDate = DATEADD(m,-1,ArcDate). Then select the distinct record set.

    SELECT DISTINCT

    ClientName,

    Product,

    A.ArcDate - B.ArcDate

    FROM Tbl A INNER JOIN Tbl B ON

    (A.ClientName = B.ClientName AND

    A.Product = B.Product AND

    A.ArcDate = DATEADD(m, -1, B.ArcDate)

    You'll probably need to tweak, but its an option without temp tables.

  • November 6, 2008 at 8:00 am

    #894894

    🙂

    thats EXCELLENT

    thanks

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply