joining on months ... - 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 2005 Forums

Transact-SQL (2005)

joining on months ...

Author

Topic

doubleotwo
Yak Posting Veteran

69 Posts

Posted - 2011-05-25 : 13:14:38

Dear experts ,

I cant get this query figured out ...

I have 1 table "CAR"
which contains all cars of a leasing fleet.
in this table there are the fields
carid startdate and enddate

Then i got a recursive query monthlist which will show all
months that i'll need to join to the car table

this monthlist query looks like this :

WITH MonthList (MonthDate) AS -- list of all months
(
SELECT
-- kies eerste dag vd laatste maand
DATEADD(dd,-(DAY(DATEADD(mm, 0 , @LastDay))-1),DATEADD(mm, 0 , @LastDay))
UNION ALL
SELECT DATEADD(mm, -1, MonthDate)
FROM MonthList
-- eerst dag van de laatste maand
WHERE (MonthDate >= @FirstDay)
)

this works fine.

What i would like to archieve (but my brain is too fucked up to do it)
is to get all car rows which have

car.startdate <= (DATEADD(dd,-1(DATEADD(mm,1,Monthlist.Monthdate)) -- to get the last date of the month, monthdate is always firstdate
and ISNULL(car.enddate,cast('3000-01-01' as datetime) >= Monthlist.Monthdate

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-25 : 13:29:19

This gives you first day of this month
select dateadd(month,datediff(month,0,@date),0)

and this gives you last day of last month

select dateadd(month,datediff(month,0,@date),0)

Jim

Please edit your post, we're a family-friendly site.

Everyday I learn something that somebody else already knew

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-25 : 13:31:16

WITH MonthList (MonthDate) AS -- list of all months
(
SELECT
-- kies eerste dag vd laatste maand
DATEADD(dd,-(DAY(DATEADD(mm, 0 , @LastDay))-1),DATEADD(mm, 0 , @LastDay))
UNION ALL
SELECT DATEADD(mm, -1, MonthDate)
FROM MonthList
-- eerst dag van de laatste maand
WHERE (MonthDate >= @FirstDay)
)
select *
from CAR
join MonthList
on car.startdate <= (DATEADD(dd,-1(DATEADD(mm,1,Monthlist.Monthdate))
and ISNULL(car.enddate,cast('3000-01-01' as datetime) >= Monthlist.Monthdate

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources