Need CTE query to find specific record

Question

Post reply

Need CTE query to find specific record

luissantos

SSCertifiable

Points: 7391
More actions
September 18, 2015 at 4:35 am

#399063

Hello comunity
I need to build a CTE query to find for the same Cabstamp (document) where i have different Origin.
I know that i can build this with a correlated subquery, but i´am curious about using CTE.
I post sample create Script:
create table #temp (Cabstamp varchar(10), account varchar(10),document varchar(15), origin varchar(2), debit numeric(10,2), credit numeric(10,2), datalc datetime)
insert into #temp (Cabstamp,account,Document, origin, debit, credit, datalc)
select 'ADM12345',111,'CMP-01','FO',1000,0, '20150110'
union
select 'ADM12345',112,'CMP-01','FO', 500, 0,'20150110'
union
select 'ADM12345',6811,'CMP-01','DO',0,1500,'20150110'
union
select 'ADM99999',112,'CMP-T1','FO', 250, 0, '20150314'
union
select 'ADM99999',6811,'CMP-T1','FO',0,250, '20150314'
union
select 'ADM333',112,'INV-C1','FO', 300, 0, '20150420'
union
select 'ADM333',7911,'INV-C1','FO',0,300,'20150420'
union
select 'ADM555',21101,'INV-C1300','FO', 400, 0, '20150910'
union
select 'ADM555',112,'INV-C1300','DO',0,400,'20150910'
select * from #temp
Best regards
LS

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 1

Quick suggestion

😎

USE tempdb;

GO

SET NOCOUNT ON

IF OBJECT_ID(N'tempdb..#temp') IS NOT NULL DROP TABLE #temp;

create table #temp (Cabstamp varchar(10), account varchar(10),document varchar(15), origin varchar(2), debit numeric(10,2), credit numeric(10,2), datalc datetime)

insert into #temp (Cabstamp,account,Document, origin, debit, credit, datalc)

select 'ADM12345',111,'CMP-01','FO',1000,0, '20150110'

union

select 'ADM12345',112,'CMP-01','FO', 500, 0,'20150110'

union

select 'ADM12345',6811,'CMP-01','DO',0,1500,'20150110'

union

select 'ADM99999',112,'CMP-T1','FO', 250, 0, '20150314'

union

select 'ADM99999',6811,'CMP-T1','FO',0,250, '20150314'

union

select 'ADM333',112,'INV-C1','FO', 300, 0, '20150420'

union

select 'ADM333',7911,'INV-C1','FO',0,300,'20150420'

union

select 'ADM555',21101,'INV-C1300','FO', 400, 0, '20150910'

union

select 'ADM555',112,'INV-C1300','DO',0,400,'20150910'

;WITH BASE_DATA AS

(

SELECT

T.Cabstamp

,T.account

,T.document

,T.origin

,T.debit

,T.credit

,T.datalc

,ROW_NUMBER() OVER

(

PARTITION BY T.document

,T.origin

ORDER BY T.datalc

) OD_RIT

,ROW_NUMBER() OVER

(

PARTITION BY T.document

ORDER BY T.datalc

) D_RID

FROM #temp T

)

SELECT

BD.Cabstamp

,BD.account

,BD.document

,BD.origin

,BD.debit

,BD.credit

,BD.datalc

,CASE

WHEN BD.OD_RIT <> BD.D_RID THEN 'ORIGIN CHANGED'

ELSE ''

END AS CHANGE_DETECTION

FROM BASE_DATA BD;

Results

Cabstamp account document origin debit credit datalc CHANGE_DETECTION

---------- ---------- --------------- ------ --------- --------- ----------------------- ----------------

ADM12345 6811 CMP-01 DO 0.00 1500.00 2015-01-10 00:00:00.000

ADM12345 111 CMP-01 FO 1000.00 0.00 2015-01-10 00:00:00.000 ORIGIN CHANGED

ADM12345 112 CMP-01 FO 500.00 0.00 2015-01-10 00:00:00.000 ORIGIN CHANGED

ADM99999 112 CMP-T1 FO 250.00 0.00 2015-03-14 00:00:00.000

ADM99999 6811 CMP-T1 FO 0.00 250.00 2015-03-14 00:00:00.000

ADM333 112 INV-C1 FO 300.00 0.00 2015-04-20 00:00:00.000

ADM333 7911 INV-C1 FO 0.00 300.00 2015-04-20 00:00:00.000

ADM555 112 INV-C1300 DO 0.00 400.00 2015-09-10 00:00:00.000

ADM555 21101 INV-C1300 FO 400.00 0.00 2015-09-10 00:00:00.000 ORIGIN CHANGED

luissantos SSCertifiable Points: 7391 More actions · Answer 2

Hello Eirikur Eiriksson

thanks for your reply, but what i need is only return information for the same Cabstamp+document where origin is different and not all the rows.

Many thanks,

LS

Gazareth One Orange Chip Points: 27737 More actions · Answer 3

Just add WHERE BD.OD_RIT <> BD.D_RID then. Surely you could have worked that out? 🙂

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

This is a slightly different way. But it will also give different results depending on the data.

Be sure to know what you want.

WITH BASE_DATA AS

(

SELECT

T.Cabstamp

,T.account

,T.document

,T.origin

,T.debit

,T.credit

,T.datalc

,RANK() OVER

(

PARTITION BY T.Cabstamp

ORDER BY T.origin

) OD_RIT

FROM #temp T

)

SELECT

BD.Cabstamp

,BD.account

,BD.document

,BD.origin

,BD.debit

,BD.credit

,BD.datalc

FROM BASE_DATA BD

WHERE OD_RIT > 1;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

luissantos SSCertifiable Points: 7391 More actions · Answer 5

Hello again

Many thanks for all YouTube suggestions, now i will go to try each of them.

Best regards

Luis santos