SQL Query display all records in a join
I have the following query which displays Employee Birthdays for the entire month and it displays null in the employee name field if there are no birthday. This works with Adventure works 2008 R2 Database
Now I implemented similar thing with my worker schedule db
I wanted to display the particular worker schedule for entire month,
I got it working but the only problem is that it doesn't show me records for days when employee is not scheduled.
I wanted to to show all dates for entire month except the scheduled date and name should be blank.
Can someone help,
I have attached both queries as attachment
OriginalAdventureWorksR2Query.txt
MyQuery.txt
Now I implemented similar thing with my worker schedule db
I wanted to display the particular worker schedule for entire month,
I got it working but the only problem is that it doesn't show me records for days when employee is not scheduled.
I wanted to to show all dates for entire month except the scheduled date and name should be blank.
Can someone help,
I have attached both queries as attachment
OriginalAdventureWorksR2Query.txt
MyQuery.txt
BullmanTech
If you use an OUTER JOIN, you will see records where there are no matches. INNER JOIN only returns results where you have matches in the fields used in the JOIN.
ASKER
Sorry I tried Outer join but it didn't give the result or may be i am not using the outer join right place.
Can you look at my query and suggest where to put the outer join
Can you look at my query and suggest where to put the outer join
Try changing all your joins to outer joins, like this:
.....
FROM
Dates d
LEFT OUTER JOIN WorkerWorkDay ebd ON ebd.Month = d.Month AND ebd.Day = DATEPART(DAY,d.[Date])
LEFT OUTER JOIN Schedule SC2 ON DATEPART(MONTH,SC2.[Date])
LEFT OUTER JOIN Worker_Location EL ON EL.ID = SC2.Worker_LocationID
LEFT OUTER JOIN Worker EM ON EM.ID = EL.WorkerID
.....
FROM
Dates d
LEFT OUTER JOIN WorkerWorkDay ebd ON ebd.Month = d.Month AND ebd.Day = DATEPART(DAY,d.[Date])
LEFT OUTER JOIN Schedule SC2 ON DATEPART(MONTH,SC2.[Date])
LEFT OUTER JOIN Worker_Location EL ON EL.ID = SC2.Worker_LocationID
LEFT OUTER JOIN Worker EM ON EM.ID = EL.WorkerID
ASKER
nope doesn;t work if all are LEFT OUTER joins
Does it display the same amount of records as your original query?
ASKER
yes
What do you have in table #MonthDates?
ASKER
Looks like my left join not even working properly,
Even simple query like this not giving me all results from the #MonthDates Table.
#MonthDates table has only field date with all dates from day 1st of a given month to last day of that same month.
SELECT MD.date, SC.* FROM #MonthDates MD
LEFT JOIN Schedule SC ON SC.Date = MD.date
INNER JOIN Worker_Location EL ON EL.ID = SC.Worker_LocationID
INNER JOIN Worker EM ON EM.ID = EL.WorkerID
WHERE SC.Worker_LocationID = 1064
The query above returns me only 15 records as per matching with Schedule table but I want to see all dates from #MonthDates table and schedule matching records and if not matched just blank but still need dates from #MonthDates
Even simple query like this not giving me all results from the #MonthDates Table.
#MonthDates table has only field date with all dates from day 1st of a given month to last day of that same month.
SELECT MD.date, SC.* FROM #MonthDates MD
LEFT JOIN Schedule SC ON SC.Date = MD.date
INNER JOIN Worker_Location EL ON EL.ID = SC.Worker_LocationID
INNER JOIN Worker EM ON EM.ID = EL.WorkerID
WHERE SC.Worker_LocationID = 1064
The query above returns me only 15 records as per matching with Schedule table but I want to see all dates from #MonthDates table and schedule matching records and if not matched just blank but still need dates from #MonthDates
That recursive CTE (with Months and Dates) gives you all the dates between startDate and endDate, I don't think you need #MonthDates.
Try running this to see what I mean:
Your left outer join doesn't work because you're doing inner join afterwards. I think that if you change all your inner joins to left outer joins it should yield the correct results.
Take a look at the query I'm posting. If this gives you all the dates between StartDate and EndDate then there's no need for #MonthDates.
Try running this to see what I mean:
DECLARE @StartDate DATETIME, @EndDate DATETIME
SET @StartDate = '2012-01-01'
SET @EndDate = '2012-01-31';
WITH Months AS (
SELECT
[Month] = DATEPART(MONTH,DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,@StartDate),0))),
[Year] = DATEPART(YEAR,DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,@StartDate),0))),
FirstDayOfMonth = DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,@StartDate),0)),
LastDayOfMonth = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@StartDate)+1,0)),
FirstDayOfCalendar = DATEADD(DAY,-DATEPART(WEEKDAY,DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,@StartDate),0)))+1,DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,@StartDate),0))),
LastDayOfCalendar = DATEADD(DAY,6-DATEPART(WEEKDAY,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@StartDate)+1,0))),DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@StartDate)+1,0)))
UNION ALL SELECT
[Month] = DATEPART(MONTH,DATEADD(MONTH,1,FirstDayOfMonth)),
[Year] = DATEPART(YEAR,DATEADD(MONTH,1,FirstDayOfMonth)),
FirstDayOfMonth = DATEADD(MONTH,1,FirstDayOfMonth),
LastDayOfMonth = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,DATEADD(MONTH,1,FirstDayOfMonth))+1,0)),
FirstDayOfCalendar = DATEADD(DAY,-DATEPART(WEEKDAY,DATEADD(MONTH,1,FirstDayOfMonth))+1,DATEADD(MONTH,1,FirstDayOfMonth)),
LastDayOfCalendar = DATEADD(DAY,6-DATEPART(WEEKDAY,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,DATEADD(MONTH,1,FirstDayOfMonth))+1,0))),DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,DATEADD(MONTH,1,FirstDayOfMonth))+1,0)))
FROM
Months
WHERE
LastDayOfMonth < @EndDate
), Dates AS (
SELECT
[Month],
[Year],
[Date] = FirstDayOfCalendar,
FilterDate = LastDayOfCalendar
FROM
Months
UNION ALL SELECT
[Month],
[Year],
[Date] = DATEADD(DAY,1,[Date]),
FilterDate
FROM
Dates
WHERE
[Date] < FilterDate
)
SELECT * FROM DatesYour left outer join doesn't work because you're doing inner join afterwards. I think that if you change all your inner joins to left outer joins it should yield the correct results.
Take a look at the query I'm posting. If this gives you all the dates between StartDate and EndDate then there's no need for #MonthDates.
ASKER
I had this working without the MonthDates table,
I used that tabled just to do a left join to get all Dates for entire month.
How do I use this above to do a join with my Scheduling table ?
I used that tabled just to do a left join to get all Dates for entire month.
How do I use this above to do a join with my Scheduling table ?
Try this:
That should give you all the dates (from the Dates CTE). And in the fields you should get the fields from Dates and the fields from Schedule.
Let us know.
*EDIT* Changed the quote tag to code.
DECLARE @StartDate DATETIME, @EndDate DATETIME
SET @StartDate = '2012-01-01'
SET @EndDate = '2012-01-31';
WITH Months AS (
SELECT
[Month] = DATEPART(MONTH,DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,@StartDate),0))),
[Year] = DATEPART(YEAR,DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,@StartDate),0))),
FirstDayOfMonth = DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,@StartDate),0)),
LastDayOfMonth = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@StartDate)+1,0)),
FirstDayOfCalendar = DATEADD(DAY,-DATEPART(WEEKDAY,DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,@StartDate),0)))+1,DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,@StartDate),0))),
LastDayOfCalendar = DATEADD(DAY,6-DATEPART(WEEKDAY,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@StartDate)+1,0))),DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@StartDate)+1,0)))
UNION ALL SELECT
[Month] = DATEPART(MONTH,DATEADD(MONTH,1,FirstDayOfMonth)),
[Year] = DATEPART(YEAR,DATEADD(MONTH,1,FirstDayOfMonth)),
FirstDayOfMonth = DATEADD(MONTH,1,FirstDayOfMonth),
LastDayOfMonth = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,DATEADD(MONTH,1,FirstDayOfMonth))+1,0)),
FirstDayOfCalendar = DATEADD(DAY,-DATEPART(WEEKDAY,DATEADD(MONTH,1,FirstDayOfMonth))+1,DATEADD(MONTH,1,FirstDayOfMonth)),
LastDayOfCalendar = DATEADD(DAY,6-DATEPART(WEEKDAY,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,DATEADD(MONTH,1,FirstDayOfMonth))+1,0))),DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,DATEADD(MONTH,1,FirstDayOfMonth))+1,0)))
FROM
Months
WHERE
LastDayOfMonth < @EndDate
), Dates AS (
SELECT
[Month],
[Year],
[Date] = FirstDayOfCalendar,
FilterDate = LastDayOfCalendar
FROM
Months
UNION ALL SELECT
[Month],
[Year],
[Date] = DATEADD(DAY,1,[Date]),
FilterDate
FROM
Dates
WHERE
[Date] < FilterDate
)
SELECT *
FROM Dates LEFT OUTER JOIN Schedule SC2 ON
DATEPART(MONTH,SC2.[Date]) = d.Month AND DATEPART(DAY,SC2.[Date]) = DATEPART(DAY,d.[Date])That should give you all the dates (from the Dates CTE). And in the fields you should get the fields from Dates and the fields from Schedule.
Let us know.
*EDIT* Changed the quote tag to code.
ASKER
Query below doesn't display all records from the Dates Table
DECLARE @StartDate DATETIME, @EndDate DATETIME
SET @StartDate = '2012-04-01'
SET @EndDate = '2012-04-30';
WITH Months AS (
SELECT
[Month] = DATEPART(MONTH,DATEADD(s,0
[Year] = DATEPART(YEAR,DATEADD(s,0,
FirstDayOfMonth = DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,@StartDate),0
LastDayOfMonth = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@StartDate)+1
FirstDayOfCalendar = DATEADD(DAY,-DATEPART(WEEK
LastDayOfCalendar = DATEADD(DAY,6-DATEPART(WEE
UNION ALL SELECT
[Month] = DATEPART(MONTH,DATEADD(MON
[Year] = DATEPART(YEAR,DATEADD(MONT
FirstDayOfMonth = DATEADD(MONTH,1,FirstDayOf
LastDayOfMonth = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,DATEADD(MONTH
FirstDayOfCalendar = DATEADD(DAY,-DATEPART(WEEK
LastDayOfCalendar = DATEADD(DAY,6-DATEPART(WEE
FROM
Months
WHERE
LastDayOfMonth < @EndDate
), Dates AS (
SELECT
[Month],
[Year],
[Date] = FirstDayOfCalendar,
FilterDate = LastDayOfCalendar
FROM
Months
UNION ALL SELECT
[Month],
[Year],
[Date] = DATEADD(DAY,1,[Date]),
FilterDate
FROM
Dates
WHERE
[Date] < FilterDate
)
SELECT *
FROM Dates d LEFT OUTER JOIN Schedule SC2 ON
DATEPART(MONTH,SC2.[Date])
INNER JOIN Worker_Location EL ON EL.ID = SC2.Worker_LocationID
INNER JOIN Worker EM ON EM.ID = EL.WorkerID
WHERE SC2.Worker_LocationID = 1064
DECLARE @StartDate DATETIME, @EndDate DATETIME
SET @StartDate = '2012-04-01'
SET @EndDate = '2012-04-30';
WITH Months AS (
SELECT
[Month] = DATEPART(MONTH,DATEADD(s,0
[Year] = DATEPART(YEAR,DATEADD(s,0,
FirstDayOfMonth = DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,@StartDate),0
LastDayOfMonth = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@StartDate)+1
FirstDayOfCalendar = DATEADD(DAY,-DATEPART(WEEK
LastDayOfCalendar = DATEADD(DAY,6-DATEPART(WEE
UNION ALL SELECT
[Month] = DATEPART(MONTH,DATEADD(MON
[Year] = DATEPART(YEAR,DATEADD(MONT
FirstDayOfMonth = DATEADD(MONTH,1,FirstDayOf
LastDayOfMonth = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,DATEADD(MONTH
FirstDayOfCalendar = DATEADD(DAY,-DATEPART(WEEK
LastDayOfCalendar = DATEADD(DAY,6-DATEPART(WEE
FROM
Months
WHERE
LastDayOfMonth < @EndDate
), Dates AS (
SELECT
[Month],
[Year],
[Date] = FirstDayOfCalendar,
FilterDate = LastDayOfCalendar
FROM
Months
UNION ALL SELECT
[Month],
[Year],
[Date] = DATEADD(DAY,1,[Date]),
FilterDate
FROM
Dates
WHERE
[Date] < FilterDate
)
SELECT *
FROM Dates d LEFT OUTER JOIN Schedule SC2 ON
DATEPART(MONTH,SC2.[Date])
INNER JOIN Worker_Location EL ON EL.ID = SC2.Worker_LocationID
INNER JOIN Worker EM ON EM.ID = EL.WorkerID
WHERE SC2.Worker_LocationID = 1064
No... Try this:
DECLARE @StartDate DATETIME, @EndDate DATETIME
SET @StartDate = '2012-04-01'
SET @EndDate = '2012-04-30';
WITH Months AS (
SELECT
[Month] = DATEPART(MONTH,DATEADD(s,0
[Year] = DATEPART(YEAR,DATEADD(s,0,
FirstDayOfMonth = DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,@StartDate),0
LastDayOfMonth = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@StartDate)+1
FirstDayOfCalendar = DATEADD(DAY,-DATEPART(WEEK
LastDayOfCalendar = DATEADD(DAY,6-DATEPART(WEE
UNION ALL SELECT
[Month] = DATEPART(MONTH,DATEADD(MON
[Year] = DATEPART(YEAR,DATEADD(MONT
FirstDayOfMonth = DATEADD(MONTH,1,FirstDayOf
LastDayOfMonth = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,DATEADD(MONTH
FirstDayOfCalendar = DATEADD(DAY,-DATEPART(WEEK
LastDayOfCalendar = DATEADD(DAY,6-DATEPART(WEE
FROM
Months
WHERE
LastDayOfMonth < @EndDate
), Dates AS (
SELECT
[Month],
[Year],
[Date] = FirstDayOfCalendar,
FilterDate = LastDayOfCalendar
FROM
Months
UNION ALL SELECT
[Month],
[Year],
[Date] = DATEADD(DAY,1,[Date]),
FilterDate
FROM
Dates
WHERE
[Date] < FilterDate
)
SELECT * FROM Dates d
LEFT JOIN (SELECT SC.Date AS Sch_Date, EM.FullName AS FullName FROM Schedule SC INNER JOIN Worker_Location EL ON EL.ID = SC.Worker_LocationID
INNER JOIN Worker EM ON EM.ID = EL.WorkerID
WHERE SC.Worker_LocationID = 1064) t ON
DATEPART(MONTH,t.Sch_Date)
DECLARE @StartDate DATETIME, @EndDate DATETIME
SET @StartDate = '2012-04-01'
SET @EndDate = '2012-04-30';
WITH Months AS (
SELECT
[Month] = DATEPART(MONTH,DATEADD(s,0
[Year] = DATEPART(YEAR,DATEADD(s,0,
FirstDayOfMonth = DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,@StartDate),0
LastDayOfMonth = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@StartDate)+1
FirstDayOfCalendar = DATEADD(DAY,-DATEPART(WEEK
LastDayOfCalendar = DATEADD(DAY,6-DATEPART(WEE
UNION ALL SELECT
[Month] = DATEPART(MONTH,DATEADD(MON
[Year] = DATEPART(YEAR,DATEADD(MONT
FirstDayOfMonth = DATEADD(MONTH,1,FirstDayOf
LastDayOfMonth = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,DATEADD(MONTH
FirstDayOfCalendar = DATEADD(DAY,-DATEPART(WEEK
LastDayOfCalendar = DATEADD(DAY,6-DATEPART(WEE
FROM
Months
WHERE
LastDayOfMonth < @EndDate
), Dates AS (
SELECT
[Month],
[Year],
[Date] = FirstDayOfCalendar,
FilterDate = LastDayOfCalendar
FROM
Months
UNION ALL SELECT
[Month],
[Year],
[Date] = DATEADD(DAY,1,[Date]),
FilterDate
FROM
Dates
WHERE
[Date] < FilterDate
)
SELECT * FROM Dates d
LEFT JOIN (SELECT SC.Date AS Sch_Date, EM.FullName AS FullName FROM Schedule SC INNER JOIN Worker_Location EL ON EL.ID = SC.Worker_LocationID
INNER JOIN Worker EM ON EM.ID = EL.WorkerID
WHERE SC.Worker_LocationID = 1064) t ON
DATEPART(MONTH,t.Sch_Date)
ASKER
Hi LIONKING,
It works the way I wanted but its displaying dates from April 1st until May 5th.
Not sure where May came from as we specified dates on the top to be until April 30th only
Any guesses why its doing that ?
It works the way I wanted but its displaying dates from April 1st until May 5th.
Not sure where May came from as we specified dates on the top to be until April 30th only
Any guesses why its doing that ?
ASKER
I guess its the FilterDate which causes the problem I fixed it, let me work it out and will post back soon
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks