left join help - 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)

left join help

Author

Topic

safderalimd
Starting Member

32 Posts

Posted - 2008-12-18 : 11:02:23

Following is result set from my client table

client_id schedule_date
----------------------------
301 2008-01-21 00:00:00.000
301 2008-01-30 00:00:00.000
302 2008-01-15 00:00:00.000
302 2008-01-21 00:00:00.000

I am looking for below result set by joining client table to date_dim. SQL code to create and load these tables is at bottom of the message.

day_date client_id schedule_date
--------------------------------------
2008-01-01 00:00:00.000 NULL NULL
2008-01-02 00:00:00.000 NULL NULL
2008-01-03 00:00:00.000 NULL NULL
2008-01-04 00:00:00.000 NULL NULL
2008-01-05 00:00:00.000 NULL NULL
2008-01-06 00:00:00.000 NULL NULL
2008-01-07 00:00:00.000 NULL NULL
2008-01-08 00:00:00.000 NULL NULL
2008-01-09 00:00:00.000 NULL NULL
2008-01-10 00:00:00.000 NULL NULL
2008-01-11 00:00:00.000 NULL NULL
2008-01-12 00:00:00.000 NULL NULL
2008-01-13 00:00:00.000 NULL NULL
2008-01-14 00:00:00.000 NULL NULL
2008-01-15 00:00:00.000 NULL NULL
2008-01-16 00:00:00.000 NULL NULL
2008-01-17 00:00:00.000 NULL NULL
2008-01-18 00:00:00.000 NULL NULL
2008-01-19 00:00:00.000 NULL NULL
2008-01-20 00:00:00.000 NULL NULL
2008-01-21 00:00:00.000 301 2008-01-21 00:00:00.000
2008-01-22 00:00:00.000 NULL NULL
2008-01-23 00:00:00.000 NULL NULL
2008-01-24 00:00:00.000 NULL NULL
2008-01-25 00:00:00.000 NULL NULL
2008-01-26 00:00:00.000 NULL NULL
2008-01-27 00:00:00.000 NULL NULL
2008-01-28 00:00:00.000 NULL NULL
2008-01-29 00:00:00.000 NULL NULL
2008-01-30 00:00:00.000 301 2008-01-30 00:00:00.000
2008-01-31 00:00:00.000 NULL NULL
2008-01-01 00:00:00.000 NULL NULL
2008-01-02 00:00:00.000 NULL NULL
2008-01-03 00:00:00.000 NULL NULL
2008-01-04 00:00:00.000 NULL NULL
2008-01-05 00:00:00.000 NULL NULL
2008-01-06 00:00:00.000 NULL NULL
2008-01-07 00:00:00.000 NULL NULL
2008-01-08 00:00:00.000 NULL NULL
2008-01-09 00:00:00.000 NULL NULL
2008-01-10 00:00:00.000 NULL NULL
2008-01-11 00:00:00.000 NULL NULL
2008-01-12 00:00:00.000 NULL NULL
2008-01-13 00:00:00.000 NULL NULL
2008-01-14 00:00:00.000 NULL NULL
2008-01-15 00:00:00.000 302 2008-01-15 00:00:00.000
2008-01-16 00:00:00.000 NULL NULL
2008-01-17 00:00:00.000 NULL NULL
2008-01-18 00:00:00.000 NULL NULL
2008-01-19 00:00:00.000 NULL NULL
2008-01-20 00:00:00.000 NULL NULL
2008-01-21 00:00:00.000 302 2008-01-21 00:00:00.000
2008-01-22 00:00:00.000 NULL NULL
2008-01-23 00:00:00.000 NULL NULL
2008-01-24 00:00:00.000 NULL NULL
2008-01-25 00:00:00.000 NULL NULL
2008-01-26 00:00:00.000 NULL NULL
2008-01-27 00:00:00.000 NULL NULL
2008-01-28 00:00:00.000 NULL NULL
2008-01-29 00:00:00.000 NULL NULL
2008-01-30 00:00:00.000 NULL NULL
2008-01-31 00:00:00.000 NULL NULL

create table #client(client_id int, schedule_date datetime)

insert into #client values( 301, '2008-1-21')
insert into #client values( 301, '2008-1-30')
insert into #client values( 302, '2008-1-15')
insert into #client values( 302, '2008-1-21')

create table #date_dim(day_date datetime)

insert into #date_dim values('2008-1-1')
insert into #date_dim values('2008-1-2')
insert into #date_dim values('2008-1-3')
insert into #date_dim values('2008-1-4')
insert into #date_dim values('2008-1-5')
insert into #date_dim values('2008-1-6')
insert into #date_dim values('2008-1-7')
insert into #date_dim values('2008-1-8')
insert into #date_dim values('2008-1-9')
insert into #date_dim values('2008-1-10')
insert into #date_dim values('2008-1-11')
insert into #date_dim values('2008-1-12')
insert into #date_dim values('2008-1-13')
insert into #date_dim values('2008-1-14')
insert into #date_dim values('2008-1-15')
insert into #date_dim values('2008-1-16')
insert into #date_dim values('2008-1-17')
insert into #date_dim values('2008-1-18')
insert into #date_dim values('2008-1-19')
insert into #date_dim values('2008-1-20')
insert into #date_dim values('2008-1-21')
insert into #date_dim values('2008-1-22')
insert into #date_dim values('2008-1-23')
insert into #date_dim values('2008-1-24')
insert into #date_dim values('2008-1-25')
insert into #date_dim values('2008-1-26')
insert into #date_dim values('2008-1-27')
insert into #date_dim values('2008-1-28')
insert into #date_dim values('2008-1-29')
insert into #date_dim values('2008-1-30')
insert into #date_dim values('2008-1-31')

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-18 : 11:17:09

SELECT dd.ScheduleDate, c.ClientID, c.ScheduleDate
FROM DateDim AS dd
LEFT JOIN Client AS c ON c.DayDate = dd.ScheduleDate
ORDER BY dd.ScheduleDate

E 12°55'05.63"
N 56°04'39.26"

safderalimd
Starting Member

32 Posts

Posted - 2008-12-18 : 12:28:26

Above SQL gives me 32 rows only. I am looking for 62 rows , 31 rows - one day per client for entire month. client-id 301 should have 31 days from jan and same time client 302 must have 31 rows. Please look closely at my desired result set in initial post.

I have a report which has days of a given month as columns. Each client will have row and his schedule_date will be marked as yes against column that macthes his schedule date. In my example client 301 will have check mark yes for jan 21 and jan 30. client 302 will have check mark yes for jan 15 and jan 21. All remaing days(columns) will be blank. Column headers in the report is day of the month like 1,2,3,....30,31.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-18 : 13:03:24

[code]SELECT d.DayDate,
x.ClientID,
x.ScheduleDate
FROM (
SELECT ClientID
FROM Clients
GROUP BY ClientID
) AS c
CROSS JOIN (
SELECT DayDate
FROM DimDate
GROUP BY DayDate
) AS d
LEFT JOIN (
SELECT ClientID,
ScheduleDate
FROM Clients
GROUP BY ClientID,
ScheduleDate
) AS x ON x.ClientID = c.ClientID
AND x.ScheduleDate = d.DayDate
ORDER BY c.ClientID,
d.DayDate[/code]

E 12°55'05.63"
N 56°04'39.26"

safderalimd
Starting Member

32 Posts

Posted - 2008-12-18 : 14:40:25

Thakns. It worked.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources