left join help

Question

Post reply

left join help

ALI-408105

Say Hey Kid

Points: 709
More actions
December 18, 2008 at 8:59 am

#126545

Following is result set from my client table
client_id schedule_date
----------------------------
3012008-01-21 00:00:00.000
3012008-01-30 00:00:00.000
3022008-01-15 00:00:00.000
3022008-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.000NULLNULL
2008-01-02 00:00:00.000NULLNULL
2008-01-03 00:00:00.000NULLNULL
2008-01-04 00:00:00.000NULLNULL
2008-01-05 00:00:00.000NULLNULL
2008-01-06 00:00:00.000NULLNULL
2008-01-07 00:00:00.000NULLNULL
2008-01-08 00:00:00.000NULLNULL
2008-01-09 00:00:00.000NULLNULL
2008-01-10 00:00:00.000NULLNULL
2008-01-11 00:00:00.000NULLNULL
2008-01-12 00:00:00.000NULLNULL
2008-01-13 00:00:00.000NULLNULL
2008-01-14 00:00:00.000NULLNULL
2008-01-15 00:00:00.000NULLNULL
2008-01-16 00:00:00.000NULLNULL
2008-01-17 00:00:00.000NULLNULL
2008-01-18 00:00:00.000NULLNULL
2008-01-19 00:00:00.000NULLNULL
2008-01-20 00:00:00.000NULLNULL
2008-01-21 00:00:00.0003012008-01-21 00:00:00.000
2008-01-22 00:00:00.000NULLNULL
2008-01-23 00:00:00.000NULLNULL
2008-01-24 00:00:00.000NULLNULL
2008-01-25 00:00:00.000NULLNULL
2008-01-26 00:00:00.000NULLNULL
2008-01-27 00:00:00.000NULLNULL
2008-01-28 00:00:00.000NULLNULL
2008-01-29 00:00:00.000NULLNULL
2008-01-30 00:00:00.0003012008-01-30 00:00:00.000
2008-01-31 00:00:00.000NULLNULL
2008-01-01 00:00:00.000NULLNULL
2008-01-02 00:00:00.000NULLNULL
2008-01-03 00:00:00.000NULLNULL
2008-01-04 00:00:00.000NULLNULL
2008-01-05 00:00:00.000NULLNULL
2008-01-06 00:00:00.000NULLNULL
2008-01-07 00:00:00.000NULLNULL
2008-01-08 00:00:00.000NULLNULL
2008-01-09 00:00:00.000NULLNULL
2008-01-10 00:00:00.000NULLNULL
2008-01-11 00:00:00.000NULLNULL
2008-01-12 00:00:00.000NULLNULL
2008-01-13 00:00:00.000NULLNULL
2008-01-14 00:00:00.000NULLNULL
2008-01-15 00:00:00.0003022008-01-15 00:00:00.000
2008-01-16 00:00:00.000NULLNULL
2008-01-17 00:00:00.000NULLNULL
2008-01-18 00:00:00.000NULLNULL
2008-01-19 00:00:00.000NULLNULL
2008-01-20 00:00:00.000NULLNULL
2008-01-21 00:00:00.0003022008-01-21 00:00:00.000
2008-01-22 00:00:00.000NULLNULL
2008-01-23 00:00:00.000NULLNULL
2008-01-24 00:00:00.000NULLNULL
2008-01-25 00:00:00.000NULLNULL
2008-01-26 00:00:00.000NULLNULL
2008-01-27 00:00:00.000NULLNULL
2008-01-28 00:00:00.000NULLNULL
2008-01-29 00:00:00.000NULLNULL
2008-01-30 00:00:00.000NULLNULL
2008-01-31 00:00:00.000NULLNULL
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')

Viewing 15 posts - 1 through 15 (of 17 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

What have you tried so far, ALI?

Thanks for providing the sample data and table creation scripts, top work.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Is there a reason you need all the duplicated rows where the dates have null values?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

The following code is a basic left join. It just doesn't return the result set you requested.

select

*

from

#date_dim d

left outer join #client c

on (d.day_date = c.schedule_date)

ALI-408105 Say Hey Kid Points: 709 More actions · Answer 4

Below 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.

select

*

from

#date_dim d

left outer join #client c

on (d.day_date = c.schedule_date)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

I did, and I asked why you need all the extra duplicate rows where you have 2008-12-01 null null?

Care to answer the question I asked? Can't help if I don't know the reason why you need what you need espescially when it doesn't make any sense to me.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Also, with the rows where dates look like 2008-12-01 null null, how do you know which one belongs to which client?

ALI-408105 Say Hey Kid Points: 709 More actions · Answer 7

Sorry for not reading your message properly.

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.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Lynn Pettis

SSC Guru

Points: 442467

December 18, 2008 at 10:29 am

#914735

So you also need to pivot the data for the report?

vick.ram79 SSCommitted Points: 1637 More actions · Answer 9

Try this....

select day_date, cl.client_id, cl2.schedule_date from (Select distinct client_id from #client) cl

cross join #date_dim dd

left join #client cl2

on cl2.schedule_date=dd.day_date

This is the closest I got to...

How To Post[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

Here you go:

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');

with clientdates(

day_date,

client_id

) as (

select

day_date,

client_id

from

#date_dim

cross join (select distinct client_id from #client) dt

)

select

cd.day_date,

c.client_id,

c.schedule_date

from

clientdates cd

left outer join #client c

on (cd.day_date = c.schedule_date

and cd.client_id = c.client_id)

drop table #date_dim

drop table #client

vick.ram79 SSCommitted Points: 1637 More actions · Answer 11

I am sorry ...i did not look too deeply...

here is the updated one...

select day_date, cl.client_id, cl2.schedule_date from (Select distinct client_id from #client) cl

cross join #date_dim dd

left join #client cl2

on cl2.schedule_date=dd.day_date

and cl.client_id=cl2.client_id

How To Post[/url]

vick.ram79 SSCommitted Points: 1637 More actions · Answer 12

This final one does exactly what you need..... though I am unsure why you want this in place of the one i sent before...

select day_date, cl2.client_id, cl2.schedule_date from (Select distinct client_id from #client) cl

cross join #date_dim dd

left join #client cl2

on cl2.schedule_date=dd.day_date

and cl.client_id=cl2.client_id

How To Post[/url]

ALI-408105 Say Hey Kid Points: 709 More actions · Answer 13

It worked, I had to add distinct to select.

I am going to try using pivot.

Thanks for your help.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 14

Why did you have to use a DISTINCT? My code returned exactly what you requested, based on your sample data 62 rows.