Compare values in same table on different records

Question

Compare values in same table on different records

ross.mason 49698

SSC-Addicted

Points: 468
More actions
January 26, 2012 at 12:31 pm

#267216

Is this script the best way to compare OrderAmount to the previous months OrderAmount and then show both this months and last months OrderAmount in the same snapshot table?
Thx
SELECT A.Cust_id
, A.OrderAmount
, A.TakenMonth
, C.OrderAmount AS PrvAmt
FROM dbo.tbl_ssi_Snapshot AS A INNER JOIN
dbo.tbl_ssi_Snapshot AS C ON A.Cust_id = C.Cust_id AND C.TakenMonth = 11
WHERE (A.TakenMonth = 12) AND (A.Cust_id IN
(
SELECT Cust_id
FROM dbo.tbl_ssi_Snapshot AS B
WHERE (TakenMonth = 11) AND (OrderAmount <> A.OrderAmount))
)

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

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

patrickmcginnis59 SSCertifiable Points: 6483 More actions · Answer 1

ross.mason 49698 (1/26/2012)
Is this script the best way to compare OrderAmount to the previous months OrderAmount and then show both this months and last months OrderAmount in the same snapshot table?
Thx
SELECT A.Cust_id
, A.OrderAmount
, A.TakenMonth
, C.OrderAmount AS PrvAmt
FROM dbo.tbl_ssi_Snapshot AS A INNER JOIN
dbo.tbl_ssi_Snapshot AS C ON A.Cust_id = C.Cust_id AND C.TakenMonth = 11
WHERE (A.TakenMonth = 12) AND (A.Cust_id IN
(
SELECT Cust_id
FROM dbo.tbl_ssi_Snapshot AS B
WHERE (TakenMonth = 11) AND (OrderAmount <> A.OrderAmount))
)

I would be interested in how this version does compared to the one you wrote:

SELECT A.Cust_id

, A.OrderAmount

, A.TakenMonth

, C.OrderAmount AS PrvAmt

FROM dbo.tbl_ssi_Snapshot AS A INNER JOIN

dbo.tbl_ssi_Snapshot AS C

ON A.Cust_id = C.Cust_id

AND A.TakenMonth = 12 And C.TakenMonth = 11

WHERE A.OrderAmount <> C.OrderAmount

Easier to see if it would work if there were actual tables to test against! The custom here is to produce ready to use scripts with example tables when asking questions, you'll notice this if you read some prior threads.

ross.mason 49698 SSC-Addicted Points: 468 More actions · Answer 2

That is so much more elegant that the one I wrote, and works like a champ

Thanks for getting back so quickly. I will try to adhere to the custom in the future.

Thanks again

patrickmcginnis59 SSCertifiable Points: 6483 More actions · Answer 3

ross.mason 49698 (1/26/2012)
That is so much more elegant that the one I wrote, and works like a champ
Thanks for getting back so quickly. I will try to adhere to the custom in the future.
Thanks again

Awesome glad it worked!