How to join "multi joins"? - 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 2008 Forums

Transact-SQL (2008)

How to join "multi joins"?

Author

Topic

rasta
Starting Member

23 Posts

Posted - 2011-12-28 : 07:44:09

I have problem to count ND1 (however count D1 works fine) in such a way that the resulted values of rate and mode must correspond to these from the time of bus=observationbus-1 month (the same as in the case of D1 nn.bus=DATEADD(mm,-1,mm.observation)) and ND1=1 at the time bus=observation:

Create table test4 (ID int not null, mode int, rate int, bus datetime null,observation datetime null, ND1 bit null, D1 bit null)
Insert into test4 (ID,mode,rate,bus,observation,ND1,D1)
Values (32037913,13,4,'2009-06-01 00:00:00.000','2008-07-01 00:00:00.000',0,0)
Insert into test4 (ID,mode,rate,bus,observation,ND1,D1)
Values (32037913,13,4,'2009-07-01 00:00:00.000','2009-07-01 00:00:00.000',0,0)
Insert into test4 (ID,mode,rate,bus,observation,ND1,D1)
Values (39780776,13,4,'2009-06-01 00:00:00.000','2008-07-01 00:00:00.000',1,0)
Insert into test4 (ID,mode,rate,bus,observation,ND1,D1)
Values (39780776,13,4,'2009-07-01 00:00:00.000','2009-07-01 00:00:00.000',1,1)
Insert into test4 (ID,mode,rate,bus,observation,ND1,D1)
Values (39780776,13,4,'2009-08-01 00:00:00.000','2009-07-01 00:00:00.000',0,0)
Insert into test4 (ID,mode,rate,bus,observation,ND1,D1)
Values (39780776,13,4,'2009-09-01 00:00:00.000','2009-07-01 00:00:00.000',1,1)
Insert into test4 (ID,mode,rate,bus,observation,ND1,D1)
Values (39780776,2,5,'2010-06-01 00:00:00.000','2009-07-01 00:00:00.000',1,0)
Insert into test4 (ID,mode,rate,bus,observation,ND1,D1)
Values (39780776,11,8,'2010-07-01 00:00:00.000','2010-07-01 00:00:00.000',1,0)
Insert into test4 (ID,mode,rate,bus,observation,ND1,D1)
Values (39780776,12,9,'2010-08-01 00:00:00.000','2010-07-01 00:00:00.000',1,1)
Insert into test4 (ID,mode,rate,bus,observation,ND1,D1)
Values (39780776,13,4,'2010-09-01 00:00:00.000','2010-07-01 00:00:00.000',0,0)

select * from test4 order by id, bus

-- count of D1 works fine:
select count(*) as D1, nn.mode,nn.rate, mm.observation
from
(select p2.id,p2.bus,p2.mode,p2.rate,p1.ND1,p2.D1, p1.observation
from test4 p1 inner join
(SELECT *, rd=ROW_NUMBER() OVER (PARTITION BY id, observation
ORDER BY D1 DESC, bus ASC) FROM test4 AS m) p2
on p1.id=p2.id and p1.observation=p2.observation
where (p2.rd=1 or p2.D1=1) and
DATEDIFF(Day,p1.observation, p1.bus) between 0 and 9 and (p1.ND1=1)
) as mm
inner join test4 nn
on nn.id=mm.id and nn.bus=DATEADD(mm,-1,mm.observation)
where mm.D1=1
group by nn.mode, mm.observation,nn.rate

-- count of ND1
select count(*) as ND1,p1.mode , p1.observation,p1.rate
from test4 p1 inner join
(SELECT *, rd=ROW_NUMBER() OVER (PARTITION BY id, observation
ORDER BY D1 DESC, bus ASC) FROM test4 AS m) p2
on p1.id=p2.id and p1.observation=p2.observation
where (p2.rd=1 or p2.D1=1) and DATEDIFF(Day,p1.observation, p1.bus) between 0 and 9 and (p1.ND1=1)
group by p1.observation, p1.mode,p1.rate) as nd1
on nd1.observation = d.observation and nd1.mode = d.mode and nd1.rate=d.rate

rasta
Starting Member

23 Posts

Posted - 2011-12-28 : 07:56:01

I would expect in count ND1 the following:
1st row: ND1=1,mode=13,rate=4,observation=2009-07-01 (not 2,13,4)
2nd row: ND1=1,mode=2,rate=5,observation=2010-07-01 (not 1,11,4)

rasta
Starting Member

23 Posts

Posted - 2011-12-28 : 08:16:29

...sorry for bothering you, I rewrite it and it works fine now:

select p2.observation,p1.mode,p1.rate
from test4 p1 inner join
(SELECT *, rd=ROW_NUMBER() OVER (PARTITION BY id, observation
ORDER BY bus ASC) FROM test4 AS m) p2
on p1.id=p2.id and p1.bus=DATEADD(mm,-1,p2.observation)
where p2.rd=1 and p2.ND1=1
group by p2.observation, p1.mode, p1.rate

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources