STORED PROC & PREVIOUS ROW - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

STORED PROC & PREVIOUS ROW

Author

Topic

noblemfd
Starting Member

38 Posts

Posted - 2014-05-16 : 05:59:49

I HAVE A TABLE AND WANT TO CREATE STORED PROCEDURE FROM IT. SEE THE TABLE SCRIPT BELOW.
CREATE TABLE tblreconcile
(
DOCID NVARCHAR(50),
RECONDATE DATETIME,
TOTALDR MONEY,
TOTALCR MONEY,
CONTROL1 BIGINT IDENTITY(1,1)
)

I WANT TO CREATE A STORED PROCEDURE CALLED spRECON FROM THIS TABLE. IT SHOULD GROUP IT BY DOCID & ORDER BY CONTROL1. IT SHOULD HAVE AN ADDITIONAL FIELD CALLED BEGDATE DATETIME. IF FIRST ROW IS SELECTED THE VALUE OF BEGDATE SHOULD BE 01/01/YEAR ELSE IT SHOULD BE VALUE OF RECONDATE FROM PREVIOUS ROW.
THE FIELDS FOR THE STORED PROCEDURE ARE:- RECID,BEGDATE,RECONDATE,TOTALDR,TOTALCR,CONTROL1. THANKS

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-16 : 11:55:55

Show us what you have so far. With the description all in caps, I'm unable to read it.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

noblemfd
Starting Member

38 Posts

Posted - 2014-05-22 : 02:51:33

quote:
Originally posted by tkizer

Show us what you have so far. With the description all in caps, I'm unable to read it.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Am very sorry please. What I want to do is that I want to access previous row from the field "ReconDate" and put in a field call "BegDate".

This is my initial table:
CREATE TABLE tblreconcile
(
DocID NVARCHAR(50),
ReconDate DATETIME,
TotalDR MONEY,
TotalCR MONEY,
control1 BIGINT IDENTITY(1,1)
)

DocID ReconDate TotalDR TotalCR control1
001 07/01/2014 20,000 13,000 1
002 15/01/2014 15,000 17,000 2
001 03/02/2014 14,000 21,000 3
001 03/03/2014 4,000 26,000 4
002 07/03/2014 19,000 11,000 5
001 03/04/2014 42,000 26,000 6
002 13/04/2014 34,000 31,000 7

EXPECTED RESULT:
An extra column BegDate should be created, and everything should be sorted by DocID, ReconDate and control1

See the Expected result below:

DocID BegDate ReconDate TotalDR TotalCR control1
001 NULL 07/01/2014 20,000 13,000 1
001 07/01/2014 03/02/2014 14,000 21,000 3
001 03/02/2014 03/03/2014 4,000 26,000 4
001 03/03/2014 03/04/2014 42,000 26,000 6
002 NULL 15/01/2014 15,000 17,000 2
002 15/01/2014 07/03/2014 19,000 11,000 5
002 07/03/2014 13/04/2014 34,000 31,000 7

Also where you have NULL in the BegDate should be replaced by the date - firstday/firstmonth/currentyear e.g 01/01/2014

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources