September 2, 2010 at 8:26 am
Hi Everyone,
I'm having a bit of an issue getting this query to work. Basically I've created a Dates table that has all the dates from 2000-01-01 until sometime during 2030.
And I've got a list of installations for certain teams (1,2,3), represented in my code below by the In_ID field. One team can have many installations throughout a month, one per a day.
I want to return a set that, for each team, shows ALL days between a start and end date that I determine with any installations.
I tried doing it in a LEFT OUTER JOIN but I only get back the dates that don't exist in the Install table, obviously.
ALSO NB due to the nature of this project, and this is wwwaaaayy beyond my control, I can not and never will be able to use CTE's to solve this particular problem.
This is what I want to achieve:
Date | In_Id
2010-09-01 - 1
2010-09-02 - 1
2010-09-03
2010-09-04
2010-09-05
2010-09-06
2010-09-07
2010-09-08
2010-09-09 - 1
2010-09-10
~~~~~~~~~
2010-09-30
2010-09-01
2010-09-02 - 2
2010-09-03
CREATE TABLE #Dates_Tbl
(Dat_Id INTEGER,
Dat_Date DATETIME,
Dat_DateName VARCHAR(40));
CREATE TABLE #Install
(In_ID INTEGER,
In_Date DATETIME)
INSERT #Install (In_ID,In_Date)
SELECT 1,'2010-09-01' UNION ALL
SELECT 1,'2010-09-02' UNION ALL
SELECT 1,'2010-09-09' UNION ALL
SELECT 2,'2010-09-02' UNION ALL
SELECT 2,'2010-09-11' UNION ALL
SELECT 2,'2010-09-20' UNION ALL
SELECT 3,'2010-09-25'
DECLARE @date DATETIME;
DECLARE @ID INTEGER;
DECLARE @cnt INTEGER;
SET @ID = 1;
SET @cnt = -1; --Start of this month,
WHILE @cnt < 28 --End of this month.
BEGIN
INSERT INTO #Dates_Tbl (Dat_Id,Dat_Date,Dat_DateName) VALUES (@ID,GETDATE()+@cnt,DATENAME(DW,GETDATE()+@cnt));
SET @cnt = @cnt + 1;
SET @ID = @ID + 1; --Create Calendar table.
END;
--SELECT * FROM #Dates_Tbl
--SELECT * FROM #Install
--DROP TABLE #Dates_Tbl
--DROP TABLE #Install
SELECT *
FROM #Dates_Tbl
LEFT JOIN #Install ON DAY(Dat_Date) = DAY(In_Date)
Any help, greatly appreciated 🙂
Cheers,
Jim.
September 2, 2010 at 9:02 am
I am rather confused using you scripts I basically get what you are looking for unless I am missing something. I ran your steps and got the below result set.
Dat_Id Dat_Date Dat_DateName In_ID In_Date
----------- ----------------------- ---------------------------------------- ----------- -----------------------
1 2010-09-01 09:58:00.703 Wednesday 1 2010-09-01 00:00:00.000
2 2010-09-02 09:58:00.703 Thursday 1 2010-09-02 00:00:00.000
2 2010-09-02 09:58:00.703 Thursday 2 2010-09-02 00:00:00.000
3 2010-09-03 09:58:00.703 Friday NULL NULL
4 2010-09-04 09:58:00.703 Saturday NULL NULL
5 2010-09-05 09:58:00.703 Sunday NULL NULL
6 2010-09-06 09:58:00.703 Monday NULL NULL
7 2010-09-07 09:58:00.703 Tuesday NULL NULL
8 2010-09-08 09:58:00.703 Wednesday NULL NULL
9 2010-09-09 09:58:00.703 Thursday 1 2010-09-09 00:00:00.000
10 2010-09-10 09:58:00.703 Friday NULL NULL
11 2010-09-11 09:58:00.703 Saturday 2 2010-09-11 00:00:00.000
12 2010-09-12 09:58:00.703 Sunday NULL NULL
13 2010-09-13 09:58:00.703 Monday NULL NULL
14 2010-09-14 09:58:00.703 Tuesday NULL NULL
15 2010-09-15 09:58:00.703 Wednesday NULL NULL
16 2010-09-16 09:58:00.703 Thursday NULL NULL
17 2010-09-17 09:58:00.703 Friday NULL NULL
18 2010-09-18 09:58:00.703 Saturday NULL NULL
19 2010-09-19 09:58:00.703 Sunday NULL NULL
20 2010-09-20 09:58:00.703 Monday 2 2010-09-20 00:00:00.000
21 2010-09-21 09:58:00.703 Tuesday NULL NULL
22 2010-09-22 09:58:00.703 Wednesday NULL NULL
23 2010-09-23 09:58:00.703 Thursday NULL NULL
24 2010-09-24 09:58:00.703 Friday NULL NULL
25 2010-09-25 09:58:00.703 Saturday 3 2010-09-25 00:00:00.000
26 2010-09-26 09:58:00.703 Sunday NULL NULL
27 2010-09-27 09:58:00.703 Monday NULL NULL
28 2010-09-28 09:58:00.703 Tuesday NULL NULL
29 2010-09-29 09:58:00.703 Wednesday NULL NULL
(30 row(s) affected)
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
September 2, 2010 at 9:36 am
Does this get you what you're looking for?
SELECT dti.Dat_Date, i.In_ID
FROM (
SELECT *
FROM #Dates_Tbl dt
CROSS JOIN (SELECT DISTINCT In_ID FROM #Install) i ) dti
LEFT JOIN #Install i
ON i.In_Date = dti.Dat_Date
AND i.In_ID = dti.In_ID
ORDER BY dti.In_ID, dti.Dat_Date
I have to admit to being confused by the "can't ever use a CTE" - the only reason I can think of for this is if the code is going to be used on a version of SQL prior to 2005.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 2, 2010 at 10:28 am
Hi Guys,
Thanks for your help!
Dan, The problem is that for each ID I want every day of the month (or selected time period). So because there are three ID's I needed three full copies of the Dates table.
Also WayneS, you just about nailed it. In the end:
SELECT * FROM #Dates_Tbl
CROSS JOIN #Install
LEFT JOIN #Install I ON DAY(Dat_Date) = DAY(I.In_Date)
has pretty much done the trick.
Why couldn't I use CTE's? You're kinda right.. I'm actually writing a Crystal report and this is the SQL I'm using in a command. Wish I could use CTE's 🙁
Thanks again for your help guys. Problem solved.
Cheers.
September 2, 2010 at 10:41 am
Jim-720070 (9/2/2010)
Hi Guys,Thanks for your help!
Dan, The problem is that for each ID I want every day of the month (or selected time period). So because there are three ID's I needed three full copies of the Dates table.
Also WayneS, you just about nailed it. In the end:
SELECT * FROM #Dates_TblCROSS JOIN #Install
LEFT JOIN #Install I ON DAY(Dat_Date) = DAY(I.In_Date)
has pretty much done the trick.
Why couldn't I use CTE's? You're kinda right.. I'm actually writing a Crystal report and this is the SQL I'm using in a command. Wish I could use CTE's 🙁
Thanks again for your help guys. Problem solved.
Cheers.
Darn it... I forgot to mention that. The DAY function returns the numerical day... if this spans multiple months, then it's a no-go. Additionally, when you use a function on a column in the join condition, you can't use indexes. So, I had changed your insert into your #Dates_Tbl to be:
set @Date = DateAdd(day, DateDiff(day, 0, GetDate()), 0);
WHILE @cnt < 28 --End of this month.
BEGIN
INSERT INTO #Dates_Tbl (Dat_Id,Dat_Date,Dat_DateName) VALUES (@ID,@Date+@cnt,DATENAME(DW,@Date+@cnt));
SET @cnt = @cnt + 1;
SET @ID = @ID + 1; --Create Calendar table.
END;
Then, the LEFT JOIN #Install I ON DAY(Dat_Date) = DAY(I.In_Date)
becomes as I had written it.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 2, 2010 at 1:12 pm
Jim-720070 (9/2/2010)
Why couldn't I use CTE's? You're kinda right.. I'm actually writing a Crystal report and this is the SQL I'm using in a command. Wish I could use CTE's 🙁
If you can create views, you can create a view using a CTE and then use that view in Crystal.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply