sql - self join - 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 2005 Forums

Transact-SQL (2005)

sql - self join

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2009-12-08 : 05:05:50

Hi,
The temp table below shows the data within two dates and shows the changes of L field. i.e. The L field of Code 'XSDB68X' has changed from NULL to 3
Question
Could you please let me know how to get the RESULT section into a table?
I have tried doing a self inner join but I do not get the results I am looking for.
Thanks

create table #tblData
(
Code varchar(20), BID smallint, RID smallint, [SID] smallint,
S bigint, L tinyint, HowMuch decimal(15, 2), ImportDate smalldatetime
)

insert into #tblData
select 'XTL017X', 1, 1, 1, 1123211, 1, 1432.99, '2009-11-30 00:00:00'
union
select 'XTL017X', 1, 1, 1, 1123211, 1, 7676.55, '2009-12-01 00:00:00'
union
select 'XSDB68X', 1, 1, 1, 1010911301, NULL, 5432.56, '2009-12-01 00:00:00'
union
select 'XSDB68X', 1, 1, 1, 7010911307, 3, 2754.33, '2009-11-30 00:00:00'
union
select 'XSDX26X', 1, 1, 1, 11122143, 1, 32433.11, '2009-11-30 00:00:00'
union
select 'XSDX26X', 1, 1, 1, 11122143, 3, 345676.44, '2009-12-01 00:00:00'
union
select 'XEN019X', 1, 1, 1, 6210908986, 3, 543.22, '2009-12-01 00:00:00'

select * from #tblData order by Code, ImportDate

drop table #tblData

--RESULT

select 'XEN019X', 1, 1, 1, 6210908986, NULL,3, 543.22
union
select 'XSDB68X', 1, 1, 1, 7010911307, 3, NULL, 8186.89
union
select 'XSDX26X', 1, 1, 1, 11122143, 1,3, 378109.55
union
select 'XTL017X', 1, 1, 1, 1123211, 1,1, 9109.54

Sachin.Nand

2937 Posts

Posted - 2009-12-08 : 06:25:40

select * from
(
select code,bid,rid,sid,s,l,ROW_NUMBER()over(partition by code order by bid,rid,sid) as rowid,
SUM(Howmuch)over(partition by code)as sum
from #tblData
)t where rowid=1

PBUH

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2009-12-08 : 06:36:31

Hi,
Your query returns the following records which is not quite right because it does not show the changes in L field.
Any thoughts please?
Thanks

Sachin.Nand

2937 Posts

Posted - 2009-12-08 : 07:07:56

select * from
(
select t1.code,t1.bid,t1.rid,t1.sid,t1.s,t2.l as l1,t1.l as l2,
ROW_NUMBER()over(partition by t1.code order by t2.code,t2.importdate) as rowid,
SUM(t1.Howmuch)over(partition by t1.code)as sum

from #tblData t1
left join #tblData t2 on t1.code=t2.Code

)t where rowid=1

PBUH

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2009-12-08 : 07:22:09

hello,
The sums do not seem to be correct though.

Sachin.Nand

2937 Posts

Posted - 2009-12-08 : 07:40:41

select * from
(
select t1.code,t1.bid,t1.rid,t1.sid,t1.s,t2.l as l1,t1.l as l2,
ROW_NUMBER()over(partition by t1.code order by t2.code,t2.importdate) as rowid,
(select top 1 SUM(t3.Howmuch)over(partition by t3.code) from #tblData t3 where t3.Code=t1.Code)as sum

from #tblData t1
left join #tblData t2 on t1.code=t2.Code

)t where rowid=1

I am not sure how the query will behave for more data.

PBUH

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2009-12-08 : 07:53:30

Hello,
Do you know if this problem can be solved by doing a self join?
Thanks

Sachin.Nand

2937 Posts

Posted - 2009-12-08 : 08:01:00

Which problem?& I am using self join.Please look at the original query.
"
....from #tblData t1
left join #tblData t2 on t1.code=t2.Code
"

PBUH

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2009-12-08 : 08:06:15

I mean something like:
select t1.Code, t1.Bid, t1.Rid, t1.Sid, t1.S, t1.L as L1, t2.L as L2
from #tblData as t1 inner join #tblData as t2
on (t1.Code = t2.code
and t1.Bid = t2.Bid and t1.Rid = t2.Rid and t1.Sid = t2.Sid and t1.S = t2.S)

What do you think?

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2009-12-08 : 11:22:51

Thank you all

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources