Update Revised Sequence # to reconcile Dr and Cr

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 2008 Forums

Transact-SQL (2008)

Update Revised Sequence # to reconcile Dr and Cr

Author

Topic

cvipin
Yak Posting Veteran

51 Posts

Posted - 2015-03-05 : 20:27:50

Hi,
I have following data with me. I want to write a sql to generated the output mentioned below:

CREATE TABLE #CrDr (Sequence int, DBCR varchar(1), Amount decimal(18,2), RevisedSeq int)
INSERT INTO #CrDr (Sequence,DBCR,Amount) VALUES (310,'C',200)
INSERT INTO #CrDr (Sequence,DBCR,Amount) VALUES (315,'D',100)
INSERT INTO #CrDr (Sequence,DBCR,Amount) VALUES (320,'D',100)
INSERT INTO #CrDr (Sequence,DBCR,Amount) VALUES (325,'C',350)
INSERT INTO #CrDr (Sequence,DBCR,Amount) VALUES (330,'D',350)
INSERT INTO #CrDr (Sequence,DBCR,Amount) VALUES (335,'C',2797.04)
INSERT INTO #CrDr (Sequence,DBCR,Amount) VALUES (340,'D',2797.04)
INSERT INTO #CrDr (Sequence,DBCR,Amount) VALUES (345,'C',100)
INSERT INTO #CrDr (Sequence,DBCR,Amount) VALUES (350,'C',50)
INSERT INTO #CrDr (Sequence,DBCR,Amount) VALUES (355,'D',150)
INSERT INTO #CrDr (Sequence,DBCR,Amount) VALUES (360,'C',264.56)
INSERT INTO #CrDr (Sequence,DBCR,Amount) VALUES (365,'C',300)
INSERT INTO #CrDr (Sequence,DBCR,Amount) VALUES (370,'D',490)
INSERT INTO #CrDr (Sequence,DBCR,Amount) VALUES (375,'D',74.56)
INSERT INTO #CrDr (Sequence,DBCR,Amount) VALUES (380,'C',50)
INSERT INTO #CrDr (Sequence,DBCR,Amount) VALUES (385,'D',50)
INSERT INTO #CrDr (Sequence,DBCR,Amount) VALUES (390,'C',278.59)
INSERT INTO #CrDr (Sequence,DBCR,Amount) VALUES (395,'D',256.97)
INSERT INTO #CrDr (Sequence,DBCR,Amount) VALUES (400,'D',17.23)
INSERT INTO #CrDr (Sequence,DBCR,Amount) VALUES (405,'D',4.39)

Expected output: (Generate ParentSeq #)

Sequence DBCR Amount ParentSeq
310 C 200 1
315 D 100 1
320 D 100 1
325 C 350 2
330 D 350 2
335 C 2797.04 3
340 D 2797.04 3
345 C 100 4
350 C 50 4
355 D 150 4
360 C 264.56 5
365 C 300 5
370 D 490 5
375 D 74.56 5
380 C 50 6
385 D 50 6
390 C 278.59 7
395 D 256.97 7
400 D 17.23 7
405 D 4.39 7

Can you please help with this query.

Thanks
Vipin

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-03-06 : 00:43:08

Can you explain the column ParentSeq ? what is the logic for it ?

sabinWeb MCP

cvipin
Yak Posting Veteran

51 Posts

Posted - 2015-03-06 : 12:22:01

The ParentSeq # should group the Credit and Debit entries ordered by Sequence. The next ParentSeq should be next number when there is a change from D to C. Basically the Credit and Debit entris should balnce to each other when we order the data by Sequence.

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-03-09 : 02:16:44

[code]
;WITH aCTE
AS
(
SELECT A.Sequence,ROW_NUMBER() OVER(ORDER BY A.Sequence) AS RevisedSeq
FROM
#CrDr AS A
OUTER APPLY
(SELECT TOP(1)
B.DBCR
FROM #CrDr AS B
WHERE
A.Sequence>B.Sequence
ORDER BY B.Sequence DESC)B
WHERE
A.DBCR ='C'
AND (B.DBCR ='D' OR B.DBCR IS NULL)
)

SELECT
A.Sequence,A.DBCR,A.Amount , B.RevisedSeq
FROM
#CrDr AS A
CROSS APPLY
(
SELECT TOP(1)
RevisedSeq
FROM
ACTE as b
WHERE
B.Sequence <= A.Sequence
ORDER BY B.Sequence dESC
)B

[/code]

I think will exits a better solution, but for the moment this is what I come up

sabinWeb MCP

cvipin
Yak Posting Veteran

51 Posts

Posted - 2015-03-09 : 15:14:32

Thank you very much sabinWeb. This works perfect for now. Do post if you find other solution :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-03-10 : 01:38:20

[code]-- SwePeso
SELECT [Sequence],
DBCR,
Amount,
DENSE_RANK() OVER (PARTITION BY DBCR ORDER BY grp) AS grp
FROM (
SELECT [Sequence],
DBCR,
Amount,
ROW_NUMBER() OVER (ORDER BY [Sequence]) - DENSE_RANK() OVER (PARTITION BY DBCR ORDER BY [Sequence]) AS grp
FROM #crdr
) AS d
ORDER BY [Sequence];[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

cvipin
Yak Posting Veteran

51 Posts

Posted - 2015-03-10 : 15:39:09

Thanks SwePeso, that was very innovative.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources