?
Solved

SQL Query display all records in a join

Posted on 2012-09-19
17
Medium Priority
?
357 Views
Last Modified: 2012-09-20
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
0
Comment
Question by:Guru Ji
  • 9
  • 7
17 Comments
 
LVL 5

Expert Comment

by:BullmanTech
ID: 38414154
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.
0
 
LVL 11

Author Comment

by:Guru Ji
ID: 38414394
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
0
 
LVL 13

Expert Comment

by:LIONKING
ID: 38414576
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]) = d.Month AND DATEPART(DAY,SC2.[Date]) = DATEPART(DAY,d.[Date])
        LEFT OUTER JOIN Worker_Location EL ON EL.ID = SC2.Worker_LocationID
       LEFT OUTER JOIN Worker EM ON EM.ID = EL.WorkerID
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 11

Author Comment

by:Guru Ji
ID: 38414637
nope doesn;t work if all are LEFT OUTER joins
0
 
LVL 13

Expert Comment

by:LIONKING
ID: 38414657
Does it display the same amount of records as your original query?
0
 
LVL 11

Author Comment

by:Guru Ji
ID: 38414703
yes
0
 
LVL 13

Expert Comment

by:LIONKING
ID: 38414850
What do you have in table #MonthDates?
0
 
LVL 11

Author Comment

by:Guru Ji
ID: 38414912
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
0
 
LVL 13

Expert Comment

by:LIONKING
ID: 38414942
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:

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

Open in new window


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.
0
 
LVL 11

Author Comment

by:Guru Ji
ID: 38414991
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 ?
0
 
LVL 13

Expert Comment

by:LIONKING
ID: 38415025
Try this:

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])

Open in new window


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.
0
 
LVL 11

Author Comment

by:Guru Ji
ID: 38415030
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,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 d LEFT OUTER JOIN Schedule SC2 ON
     DATEPART(MONTH,SC2.[Date]) = d.Month AND DATEPART(DAY,SC2.[Date]) = DATEPART(DAY,d.[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
0
 
LVL 13

Expert Comment

by:LIONKING
ID: 38415047
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,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 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) = d.Month AND DATEPART(DAY,t.Sch_Date) = DATEPART(DAY,d.[Date])
0
 
LVL 11

Author Comment

by:Guru Ji
ID: 38415069
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 ?
0
 
LVL 11

Author Comment

by:Guru Ji
ID: 38415081
I guess its the FilterDate which causes the problem I fixed it, let me work it out and will post back soon
0
 
LVL 13

Accepted Solution

by:
LIONKING earned 2000 total points
ID: 38415102
Yeah... An easy way of doing it is adding a filter to the final query, like 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,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 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) = d.Month AND DATEPART(DAY,t.Sch_Date) = DATEPART(DAY,d.[Date])
WHERE d.[Date] BETWEEN @StartDate AND @EndDate
0
 
LVL 11

Author Closing Comment

by:Guru Ji
ID: 38418648
Thanks
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question