January 26, 2012 at 12:31 pm
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))
)
January 26, 2012 at 1:17 pm
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.
January 26, 2012 at 1:57 pm
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
January 26, 2012 at 2:12 pm
ross.mason 49698 (1/26/2012)
That is so much more elegant that the one I wrote, and works like a champThanks for getting back so quickly. I will try to adhere to the custom in the future.
Thanks again
Awesome glad it worked!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply