Can one do this? - 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 2000 Forums

Transact-SQL (2000)

Can one do this?

Author

Topic

adlo
Posting Yak Master

108 Posts

Posted - 2004-10-29 : 11:52:34

I want to left join a collection of items with a database table.
Can one do this without creating a temptable using a stored proc>

I want to do something that would do the logic of the following
Select * from
(1,2,3,4,5) MyCol
LEFT JOIN
MyTable
ON MyTable.ID=MyCol.ID

MyTable values are
1,A
3,C
5,E

I want a query that will give me the result:
1,A
2,NULL
3,C
4,NULL
5,E

Kristen
Test

22859 Posts

Posted - 2004-10-29 : 11:59:54

[code]
Select *
FROM
(
SELECT 1 AS MyCol UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5
) X
LEFT JOIN
MyTable
ON MyTable.ID=MyCol.ID
[/code]
Looks pretty awful, but maybe something along those lines helps

(I don't suppose you literally just need a sequence of numbers, do you?)

Kristen

adlo
Posting Yak Master

108 Posts

Posted - 2004-10-29 : 12:13:09

That was a simple sequential example.
I would want to use it for a much larger collection.

E.g. I have a table with some dates in it
but want a result of
2004/01/01,NULL
2004/01/02,'Value Found'
2004/01/03,NULL
2004/01/04,NULL
2004/01/05,'Value Found'
2004/01/06,NULL
2004/01/07,NULL
etc

What I really need is the above example for all the Sundays in the year.

Thanks

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-29 : 12:16:29

Create a tally table with dates ( or ints )
ranging from xxxx0101 to yyyy1231 and left join on that.
This kind of tally table comes in really handy in lots of situations.

rockmoose

adlo
Posting Yak Master

108 Posts

Posted - 2004-10-29 : 12:21:01

I want to use it to display every Sunday of the year.
So it needs to be calculated dynamically because every year the Sunday's will fall on different days.

Kristen
Test

22859 Posts

Posted - 2004-10-29 : 12:38:14

I have a (fixed) tally table with the numbers 1 to 8000 in it (8000 is my highest number 'coz I use it to split varchars)

Find the first Sunday in the year, then:

SELECT DATEADD(Day, MyNumber, @FirstSundayDate)
FROM MyNumbersTable
WHERE MyNumber % 7 = 0

That might be "off-by-one" so may need a little adjustment

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-29 : 12:45:47

adlo,
See the SET DATEFIRST function,
and use tally table + Kristen's example, that should get you set up.

Or you can use the DATEPART(dw,thedate) function, if you don't like the % operation (which i guess is faster though).

rockmoose

adlo
Posting Yak Master

108 Posts

Posted - 2004-10-29 : 13:02:34

I have a question about the tally table.
I should put in 366 values to include leap years.
What if a Sunday falls on day 366 in a non leap year?

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-29 : 14:01:50

Sql Server takes care of leap years for you.
Also why limit the tally table to just 1 year ?

Did you know that Sql Server stores dates as a number, where 1 whole number represents 1 day.

select cast(38288 as datetime) union
select cast(38289 as datetime) union
select cast(38290 as datetime) union
select cast(38290.5 as datetime)

You can use that.

If you post what you have got we can help you further.

rockmoose

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-29 : 14:44:00

put in as many numbers as you think you might need. i like 10,000, personally. sometimes it is useful if your table starts with 0, sometimes with 1. when you use them, always specify a range of numbers to return in a WHERE clause.

- Jeff

VIG
Yak Posting Veteran

86 Posts

Posted - 2004-10-29 : 15:46:10

[code]declare @y varchar(4)
set @y=str(year(getdate()),4,0)

select dateadd(d,100*c+10*b+a,@y+'0101')
from
(select 0 a union all select 1 union all select 2 union all select 3 union all select 4 union all
select 5 union all select 6 union all select 7 union all select 8 union all select 9 ) a ,
(select 0 b union all select 1 union all select 2 union all select 3 union all select 4 union all
select 5 union all select 6 union all select 7 union all select 8 union all select 9 ) b ,
(select 0 c union all select 1 union all select 2 union all select 3 ) c
where dateadd(d,100*c+10*b+a,@y+'0101')<=@y+'1231'
and datename(dw,dateadd(d,100*c+10*b+a,@y+'0101'))='sunday'[/code]
This query return all sundays for current year

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-29 : 16:07:36

Ohhh well, since I did it, I post it

-- goes up to 256*256 (65536) numbers... use the top xxxxx if you want
select top 10000 256*p1.number+p2.number
from master..spt_values p1
cross join master..spt_values p2
where p1.type = 'P' and p2.type = 'P'
order by 1

-- all dates from for years 2004-2004
select 256*p1.number+p2.number, cast(256*p1.number+p2.number as datetime)
from master..spt_values p1
cross join master..spt_values p2
where p1.type = 'P' and p2.type = 'P'
and datepart(year,256*p1.number+p2.number) between 2004 and 2004
order by 1

rockmoose

Kristen
Test

22859 Posts

Posted - 2004-10-29 : 19:46:28

[code]
DECLARE @StartDate datetime
SELECT @StartDate = '01-Jan-2004'
SELECT CONVERT(datetime, @StartDate + MyNumber)
FROM MyNumberTable
WHERE MyNumber BETWEEN 1 AND 370 -- allow for leap year and rest-of-week
AND (CONVERT(int, @StartDate) + MyNumber) % 7 = 4 -- only Sundays
-- Restrict to within-the-year only
AND DATEPART(Year, CONVERT(datetime, @StartDate + MyNumber)) = DATEPART(Year, @StartDate)
ORDER BY 1
[/code]

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2004-10-30 : 04:38:00

Hah! My tally table goes to 11000!

Kristen
Test

22859 Posts

Posted - 2004-10-30 : 07:31:52

Tally! Ho! then ...

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources