2 Left Joins - 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 2008 Forums

Transact-SQL (2008)

2 Left Joins

Author

Topic

rypi
Yak Posting Veteran

55 Posts

Posted - 2011-04-04 : 15:49:11

I have the following 2 tables:

A table for employee hired and terminated date

CREATE TABLE [dbo].[TestTable](
[ID] [int] NOT NULL,
[HiredDate] [date] NOT NULL,
[TerminationDate] [date] NULL
) ON [PRIMARY]

INSERT INTO [dbo].[TestTable]
([ID]
,[HiredDate]
,[TerminationDate])
VALUES
(1, '2011-01-01', NULL),
(2, '2011-04-01', '2011-06-01'),
(3, '2011-05-01', NULL),
(4, '2011-06-01', NULL),
(5, '2011-07-01', NULL),
(6, '2011-06-01', NULL)

And a Calendar Table

CREATE TABLE [dbo].[Calendar](
[YearMonth] [datetime] NOT NULL)

INSERT INTO [dbo].[Calendar]
([YearMonth])
VALUES
('2011-01-01'),
('2011-02-01'),
('2011-03-01'),
('2011-04-01'),
('2011-05-01'),
('2011-06-01'),
('2011-07-01'),
('2011-08-01'),
('2011-09-01'),
('2011-10-01')

I am trying to create a query that will return the entire calendar table in the first column, the hired count by month in the second column and the terminated count by month in the third column.

I have tried a few different methods, but am not getting the results I want.

SELECT Calendar.YearMonth, Count(TestTable.HiredDate), Count(TestTable.TerminationDate)
FROM Calendar LEFT OUTER JOIN TestTable ON Calendar.YearMonth = TestTable.HiredDate
Group BY YearMonth

This query returns the right counts, but I want the count to show what month the employee was terminated in. This query shows the employee being terminated in 2011-04-01

OR

SELECT Calendar.YearMonth, COUNT(TestTable.HiredDate) AS Hired, COUNT(TestTable_1.TerminationDate) AS Terminated
FROM Calendar LEFT OUTER JOIN TestTable AS TestTable_1 ON Calendar.YearMonth = TestTable_1.TerminationDate LEFT OUTER JOIN
TestTable ON Calendar.YearMonth = TestTable.HiredDate
GROUP BY Calendar.YearMonth

This query doesn't return the right terminated count. It returns 2.

I am getting close, but can't quite get it figured out. Any suggestions?

Thanks.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-04-04 : 16:03:43

[code]
Declare @TestTable TABLE (
[ID] [int] NOT NULL,
[HiredDate] [date] NOT NULL,
[TerminationDate] [date] NULL
)

INSERT INTO @TestTable
VALUES
(1, '2011-01-01', NULL),
(2, '2011-04-01', '2011-06-01'),
(3, '2011-05-01', NULL),
(4, '2011-06-01', NULL),
(5, '2011-07-01', NULL),
(6, '2011-06-01', NULL)

Declare @Calendar TABLE (
[YearMonth] [datetime] NOT NULL)

INSERT INTO @Calendar
VALUES
('2011-01-01'),
('2011-02-01'),
('2011-03-01'),
('2011-04-01'),
('2011-05-01'),
('2011-06-01'),
('2011-07-01'),
('2011-08-01'),
('2011-09-01'),
('2011-10-01')

Select
YearMonth,
Hired = sum(case when YearMonth = HiredDate then 1 else 0 end),
Termd = sum(case when YearMonth = TerminationDate then 1 else 0 end)
From @Calendar A
Left Join @testTable B
On A.YearMonth = B.HiredDate
or A.YearMonth = B.TerminationDate
Group By A.YearMonth
[/code]

Corey

I Has Returned!!

Sachin.Nand

2937 Posts

Posted - 2011-04-04 : 16:31:47

Simple..Not sure...


select 
c.YearMonth,
isnull(Hired,0)Hired,
isnull(Termd,0)Termd
from @Calendar c
left join
(
select 
YearMonth,
Hired = sum(case when Dates = 'HiredDate' then 1 else 0 end),
Termd = sum(case when Dates = 'TerminationDate' then 1 else 0 end) from(
select * from @TestTable)u
unpivot
(YearMonth for Dates in(HiredDate,TerminationDate))v
group by YearMonth
)t on t.YearMonth=c.YearMonth

PBUH

rypi
Yak Posting Veteran

55 Posts

Posted - 2011-04-04 : 17:47:20

Thanks for the feedback guys, working perfectly!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources