August 15, 2014 at 1:14 am
Hello Please help me with this new column calculation
CREATE TABLE MAIN
(
ORDERNO VARCHAR(20),
LASTUPDATEDDATE DATE,
ORDERCLIENTINITIALFEE NUMERIC ,
CLIENTINITFEE NUMERIC,
INITIALVENDORFEE NUMERIC,
VENDORFEE NUMERIC
)
VENDORFEE - INITIALVEDNORFEE FOR FIRST ROW., (DATE ASC)
VENDOR FEE COLUMN LO 2 - 1
---OUTPUT
--=======
INSERT INTO MAIN VALUES ('1000', '1/1/2014',3000,1000,700,1500)
INSERT INTO MAIN VALUES ('1000', '3/5/2014',1000,2000,650,200)
INSERT INTO MAIN VALUES ('1000', '5/10/2014',500,5000,375,125)
INSERT INTO MAIN VALUES ('1000', '11/20/2014',100,2000,400,300)
INSERT INTO MAIN VALUES ('1000', '8/20/2014',100,3500,675,1300)
INSERT INTO MAIN VALUES ('2000', '3/10/2014',100,2000,375,125)
INSERT INTO MAIN VALUES ('2000', '9/20/2014',200,2000,400,300)
INSERT INTO MAIN VALUES ('2000', '4/27/2014',300,2000,675,1300)
INSERT INTO MAIN VALUES ('3000', '3/17/2014',100,100,375,110)
INSERT INTO MAIN VALUES ('3000', '3/22/2014',500,200,450,380)
INSERT INTO MAIN VALUES ('6000', '4/23/2014',300,300,120,190)
INSERT INTO MAIN VALUES ('4000', '1/1/2014',300,400,500,600)
INSERT INTO MAIN VALUES ('5000', '1/1/2014',100,250,375,750)
A NEW COLUMN(OUTPUT1) NEEDS TO BE CALCULATE BASED ON
LASTUPDATEDATE NEED TO SORT ASCENDING
IF ORDERNO HAS MORE THAN ONE ROW THE FIRST ROW CALCULATION WILL BE ( VENDORFEE - INITIALVEDNORFEE ), THEN THE REMAIN WILL BE VENDOR COULMN 2ND VALUE - VENDOR COLUMN 1ST VALUE
IF ORDERNO has only one row then ( VENDORFEE - INITIALVEDNORFEE )
FOR EX: EXPECTED VALUES FOR ORDER NUMBER 2000 IS BELOW (OUTPUT1 COLUMN/LAST)
CREATE TABLE RESULTS
(
ORDERNO VARCHAR(20),
LASTUPDATEDDATE DATE,
ORDERCLIENTINITIALFEE NUMERIC ,
CLIENTINITFEE NUMERIC,
INITIALVENDORFEE NUMERIC,
VENDORFEE NUMERIC,
OUTPUT1 NUMERIC
)
INSERT INTO MAIN VALUES ('2000', '3/10/2014',100,2000,375,125,-250)
INSERT INTO MAIN VALUES ('2000', '4/27/2014',300,2000,675,1300,1175)
INSERT INTO MAIN VALUES ('2000', '9/20/2014',200,2000,400,300,-1000)
INSERT INTO MAIN VALUES ('4000', '1/1/2014',300,400,500,600,-100)
INSERT INTO MAIN VALUES ('5000', '1/1/2014',100,250,375,750,325)
Thank you in Advance
Dhani
August 15, 2014 at 8:12 pm
This does what you've asked. As a side bar, all caps for everything is just as bad as all lower case. Both are difficult to read. I don't even do such a thing on case sensitive servers.
WITH
cteEnumerate AS
(
SELECT RowNum = ROW_NUMBER() OVER (PARTITION BY ORDERNO ORDER BY LASTUPDATEDDATE)
,*
FROM dbo.MAIN
)
SELECT hi.ORDERNO, hi.LASTUPDATEDDATE, hi.ORDERCLIENTINITIALFEE, hi.CLIENTINITFEE, hi.INITIALVENDORFEE, hi.VENDORFEE
,Output1 = CASE
WHEN hi.RowNum > 1 THEN hi.VENDORFEE - lo.VENDORFEE
ELSE hi.VENDORFEE - hi.INITIALVENDORFEE
END
FROM cteEnumerate lo
RIGHT JOIN cteEnumerate hi ON lo.ORDERNO = hi.ORDERNO AND lo.RowNum+1 = hi.RowNum
ORDER BY ORDERNO, LASTUPDATEDDATE
;
I noticed you posted in a 2012 forum. If you actually have a 2012 system, the use of Lead/Lag would make this at least twice as fast but I'm not on a 2012 box right now. I won't post what I can't test.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 17, 2014 at 2:29 am
Here is the Window function version Jeff mentioned
😎
SELECT
M.ORDERNO
,ROW_NUMBER() OVER
(
PARTITION BY M.ORDERNO
ORDER BY M.LASTUPDATEDDATE ASC
) AS ORDERNO_RID
,M.LASTUPDATEDDATE
,M.ORDERCLIENTINITIALFEE
,M.CLIENTINITFEE
,M.INITIALVENDORFEE
,M.VENDORFEE
,CASE
WHEN (LAG(M.VENDORFEE,1,0) OVER
(
PARTITION BY M.ORDERNO
ORDER BY M.LASTUPDATEDDATE ASC
)) = 0 THEN (M.VENDORFEE - M.INITIALVENDORFEE)
ELSE M.VENDORFEE - (LAG(M.VENDORFEE,1,0) OVER
(
PARTITION BY M.ORDERNO
ORDER BY M.LASTUPDATEDDATE ASC
))
END AS OUTPUT1
FROM dbo.MAIN M;
Results
ORDERNO ORDERNO_RID LASTUPDATEDDATE ORDERCLIENTINITIALFEE CLIENTINITFEE INITIALVENDORFEE VENDORFEE OUTPUT1
-------- ------------ --------------- ---------------------- -------------- ----------------- ---------- -------
1000 1 2014-01-01 3000 1000 700 1500 800
1000 2 2014-03-05 1000 2000 650 200 -1300
1000 3 2014-05-10 500 5000 375 125 -75
1000 4 2014-08-20 100 3500 675 1300 1175
1000 5 2014-11-20 100 2000 400 300 -1000
2000 1 2014-03-10 100 2000 375 125 -250
2000 2 2014-04-27 300 2000 675 1300 1175
2000 3 2014-09-20 200 2000 400 300 -1000
3000 1 2014-03-17 100 100 375 110 -265
3000 2 2014-03-22 500 200 450 380 270
4000 1 2014-01-01 300 400 500 600 100
5000 1 2014-01-01 100 250 375 750 375
6000 1 2014-04-23 300 300 120 190 70
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply