SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 STORED PROC & PREVIOUS ROW
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

noblemfd
Starting Member

Nigeria
38 Posts

Posted - 05/16/2014 :  05:59:49  Show Profile  Reply with Quote
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

USA
37287 Posts

Posted - 05/16/2014 :  11:55:55  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

noblemfd
Starting Member

Nigeria
38 Posts

Posted - 05/22/2014 :  02:51:33  Show Profile  Reply with Quote
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



Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000