CODEWITHSUNDEEP
sql-server
daniel aagentah
daniel aagentah

Reputation: 1702

SQL - carry previous Application's value onto next Application

I have a table in SQL server, returning the results below, I am having trouble carrying over my SQNCarriedOver Value into the ThisAppOFFICIAL column on the next application.

I cannot do it based on the row number because the Project Names do not list in that order:

enter image description here

I was wondering if there was a way to carry the QSNCarried over value to the ThisAppOFFICIAL column in the next application with that Project Name,

So for Example: The thisAppOfficial value on "12/06/2016" would be "10" (from in the QSCarriedOver on "12/05/2016")

I may have overlooked something, I have searched the web but I feel my question is quite specific.

Any help or advice is appreciated, Thank you in advance.

ps: the next application is the next Application Date with the Same Project Name

Upvotes: 1

Views: 83

Answers (2)

Justin
Justin

Reputation: 9724

Query:

SQLFIDDLEExample

SELECT t.Project,
       t.AppDate,
       t.thisAppOfficial,
       COALESCE((SELECT TOP 1 t2.QSCarriedOver
        FROM X t2
        WHERE t2.Project = t.Project
        AND t2.AppDate < t.AppDate
        ORDER BY t2.AppDate desc), 0) as NewColumn,
       t.QSCarriedOver
FROM X t

Result:

| Project |    AppDate | thisAppOfficial | NewColumn | QSCarriedOver |
|---------|------------|-----------------|-----------|---------------|
|       A | 2016-04-13 |              30 |         0 |             0 |
|       A | 2016-05-12 |              30 |         0 |            10 |
|       A | 2016-06-12 |              30 |        10 |             0 |
|       A | 2016-07-12 |              30 |         0 |             0 |

Upvotes: 2

OldBoyCoder
OldBoyCoder

Reputation: 876

I think I've got what you want and I'm sure that there is a better way of doing it. I've created a simpler version of your table for my needs. I then use ROW_NUMBER to put the rows into a sequence so I can do a sort of self-join to the previous row to get the carry forward figure

CREATE TABLE X (Project varchar(50), AppDate  date, thisAppOfficial int, QSCarriedOver int)
inserT INTO X VALUES('A', '13 Apr 2016', 30,0)
inserT INTO X VALUES('A', '12 May 2016', 30,10)
inserT INTO X VALUES('A', '12 Jun 2016', 30,0)
inserT INTO X VALUES('A', '12 Jul 2016', 30,0)

SELECT X0.Project, X0.AppDate, X0.thisAppOfficial, X0.QSCarriedOver, ISNULL(X2.QSCarriedOver,0) as 'Brought forward from previous row'  FROM X X0
JOIN (select ROW_NUMBER () OVER (ORDER BY Project, AppDate) as MainRow, * from X) X1 ON X1.Project = X0.Project AND X1.AppDate = X0.AppDate
LEFT OUTER JOIN (select ROW_NUMBER () OVER (ORDER BY Project, AppDate) as PrevRow, * FROM X) X2 ON X2.Project = X1.Project and X2.PrevRow = MainRow -1
order by Project, AppDate

Have a try with this and see if it is doing what you need, I'm not 100% sure I've understood your requirements so no problem if it isn't what you want.

Upvotes: 1

Related Questions