left join on datepart

[MicheleWeaver]

I have one table with a list of finished dates (one date for each month a date range). I have another table with a line item having a finished date. I am trying to do an outer join (so I get each month) using datepart, but the results are an inner join. I've tried splitting the date out into month and year fields, but run into the same problem. Any ideas?

CREATE TABLE #FinishedMonths(
FinishedMonth int,
FinishedYear int,
FinishedMonthDate datetime
)

SET @CurrentDate =
cast(cast(DatePart(mm,@DateStart) as varchar(2)) +
'/01/'+
cast(DatePart(yyyy,@DateStart) as varchar(4)) as DateTime)

WHILE @CurrentDate < DateAdd(m,1,@DateEnd)
BEGIN
INSERT #FinishedMonths
VALUES (DatePart(mm,@CurrentDate),DatePart(yyyy,@CurrentDate),@CurrentDate)
SET @CurrentDate = DateAdd(m,1,@CurrentDate)
END

SELECT
FinishedMonthDate,
LI.line_item_no,
LI.Finished_Date
FROM #FinishedMonths

LEFT JOIN Line_Item LI

ON DatePart(mm,FinishedMonth) = DatePart(mm,LI.Finished_Date)
AND DatePart(yyyy,FinishedMonth) = DatePart(yyyy,LI.Finished_Date)

 

[gmmastros]

The last line....

AND DatePart(yyyy,[!]FinishedMonth[/!]) = DatePart(yyyy,LI.Finished_Date)

Shouldn't that part in red be FinishedYear?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[MicheleWeaver]

Sorry, I was copying and pasting and renaming. That last part should read

ON DatePart(mm,FinishedMonthDate) = DatePart(mm,RO.Finished_Date)
AND DatePart(yyyy,FinishedMonthDate) = DatePart(yyyy,RO.Finished_Date)

The FinishMonth was my attempt to pull out the month and year into separate fields and try joining on that. That part reads

ON FinishedMonth = DatePart(mm,RO.Finished_Date)
AND FinishedYear = DatePart(yyyy,RO.Finished_Date)

Neither works. I don't get all the rows from the first table.

For the range 1/1/2006 - 3/1/2006 these are the results:

#FinishedMonths
FinishedMonth FinishedYear FinishedMonthDate
------------- ------------ -----------------------
1 2006 2006-01-01 00:00:00.000
2 2006 2006-02-01 00:00:00.000
3 2006 2006-03-01 00:00:00.000

Join Statement:
FinishedMonthDate RO Finished_Date
----------------------- ----------- -----------------------
2006-02-01 00:00:00.000 1538001 2006-02-02 16:11:00.000
2006-02-01 00:00:00.000 1539700 2006-02-02 16:10:48.000
2006-02-01 00:00:00.000 1539701 2006-02-02 16:10:37.000
2006-02-01 00:00:00.000 1539702 2006-02-02 16:10:28.000
2006-02-01 00:00:00.000 1539703 2006-02-02 16:10:17.000
2006-02-01 00:00:00.000 1539704 2006-02-02 16:10:04.000
2006-02-01 00:00:00.000 1539705 2006-02-02 16:09:50.000
2006-02-01 00:00:00.000 1539706 2006-02-22 15:59:39.000
2006-02-01 00:00:00.000 1539707 2006-02-02 16:09:33.000
2006-02-01 00:00:00.000 1539708 2006-02-02 16:08:52.000
2006-02-01 00:00:00.000 1539709 2006-02-02 16:11:33.000
2006-02-01 00:00:00.000 1539710 2006-02-02 16:11:24.000
2006-02-01 00:00:00.000 1539711 2006-02-02 16:11:12.000
2006-02-01 00:00:00.000 1530041 2006-02-02 16:12:01.000
2006-02-01 00:00:00.000 1530045 2006-02-02 16:11:52.000
2006-02-01 00:00:00.000 1533601 2006-02-14 12:44:29.000
2006-02-01 00:00:00.000 1533609 2006-02-20 14:42:48.000

 

[maswien]

SELECT
FinishedMonthDate,
LI.line_item_no,
LI.Finished_Date
FROM #FinishedMonths

LEFT JOIN Line_Item LI

......

In your Select list, there are nothing from the second table in the from clause, so the results should be the same as inner join.

 

[maswien]

sorry for the typo, it's actually:


" there are nothing from the first table ...."

 

[MicheleWeaver]

FinishedMonthDate is from the first table.