Find month that doesn't exist btw date range

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)

Find month that doesn't exist btw date range

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2014-03-24 : 07:58:13

Hi,

I have 2 tables.

 create table #Policies (idno int, startdate datetime,endDate datetime)
 insert into #Policies  (idno,startdate,enddate) values ( '1094567','2005-11-20','2008-04-30')
 create table #Payment (idno int ,ProcessDate datetime)
 insert into #Payment (idno,ProcessDate )
 select  1094567,	'2007-08-31 '
select 1094567, '	2007-09-30 '
select 1094567, '	2007-11-30 '
 select 1094567, '	2008-01-31 '
select 1094567, '	2008-02-29 '
select 1094567, '	2008-03-31 '

I need to find the month/year that doesn't exist in table payment between the startdate and enddate in table Policies per idno.
The startdate in the example is '2005-11-20' and enddate '2008-04-30'.
In table payments for year 2007 there are no months 10 and 12 and I see that in year 2008 month 4 is missing. I need to return the idno, months and year that is missing in the table.
How can i do that?

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-03-24 : 08:10:06

How about dates for 2007 Jan to July ? Does it consider missing ?

or the entire year 2006 ? since the policy starts in 2005

KH
[spoiler]Time is always against us[/spoiler]

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2014-03-24 : 08:21:39

Sorry, you are right-the entire year of 2005/2006 is missing and also 2007 Jan to July .

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-03-24 : 08:49:25

do you have a calendar table ?

i am using one that only contains the first day of the month

select	p.idno, c.[Date]
from	#Policies p
	inner join calendar c	on	c.[Date]	>= p.startDate
				and	c.[Date]	<=  p.endDate
where	not exists
	(
		select	*
		from	#Payment x
		where	x.idno	= p.idno
		and	x.ProcessDate	>= c.[Date]
		and	x.ProcessDate	<  dateadd(month, 1, c.[Date])
	)

also, not sure how do you want to handle the 1st and last month of the policy

KH
[spoiler]Time is always against us[/spoiler]

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2014-03-24 : 09:02:22

Hi,

Thanks. We don't have a calendar table

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-03-24 : 14:26:50

You don't need the crutch of a calendar table. You can dynamically generate a table in-line. The code below allows up to 999 months; you can shorten it to max 99 months if you prefer.



;WITH
cteDigits AS (
    SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
    SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
),
cteTally AS (
    SELECT [100s].digit * 100 + [10s].digit * 10 + [1s].digit AS tally
    FROM cteDigits [1s]
    CROSS JOIN cteDigits [10s]
    CROSS JOIN cteDigits [100s]
)
SELECT pol.idno, DATEADD(MONTH, t.tally, startMonth) AS missing_month
    ,DATEADD(MONTH, t.tally, startMonth), DATEADD(MONTH, t.tally + 1, startMonth)
FROM #Policies pol
CROSS APPLY (
    SELECT 
        DATEADD(MONTH, DATEDIFF(MONTH, 0, pol.startdate), 0) AS startMonth,
        DATEADD(MONTH, DATEDIFF(MONTH, 0, pol.endDate), 0) AS endMonth
) AS assign_alias_names_to_calcs_1
INNER JOIN cteTally t ON
    t.tally BETWEEN 0 AND DATEDIFF(MONTH, startMonth, endMonth)
LEFT OUTER JOIN (
    SELECT DISTINCT idno, DATEADD(MONTH, DATEDIFF(MONTH, 0, ProcessDate), 0) AS processMonth
    FROM #Payment
) AS pay ON
    pay.idno = pol.idno AND
    pay.processMonth = DATEADD(MONTH, t.tally, startMonth)
WHERE
    pay.idno IS NULL
ORDER BY
    idno, missing_month

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-03-24 : 14:27:40

quote:

Originally posted by ScottPletcher

You don't need the crutch of a calendar table. You can dynamically generate a table in-line. The code below allows up to 999 months; you can shorten it to max 99 months if you prefer.



;WITH
cteDigits AS (
    SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
    SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
),
cteTally AS (
    SELECT [100s].digit * 100 + [10s].digit * 10 + [1s].digit AS tally
    FROM cteDigits [1s]
    CROSS JOIN cteDigits [10s]
    CROSS JOIN cteDigits [100s]
)
SELECT pol.idno, DATEADD(MONTH, t.tally, startMonth) AS missing_month
FROM #Policies pol
CROSS APPLY (
    SELECT 
        DATEADD(MONTH, DATEDIFF(MONTH, 0, pol.startdate), 0) AS startMonth,
        DATEADD(MONTH, DATEDIFF(MONTH, 0, pol.endDate), 0) AS endMonth
) AS assign_alias_names_to_calcs_1
INNER JOIN cteTally t ON
    t.tally BETWEEN 0 AND DATEDIFF(MONTH, startMonth, endMonth)
LEFT OUTER JOIN (
    SELECT DISTINCT idno, DATEADD(MONTH, DATEDIFF(MONTH, 0, ProcessDate), 0) AS processMonth
    FROM #Payment
) AS pay ON
    pay.idno = pol.idno AND
    pay.processMonth = DATEADD(MONTH, t.tally, startMonth)
WHERE
    pay.idno IS NULL
ORDER BY
    idno, missing_month

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-03-24 : 21:52:51

quote:
Originally posted by collie

Hi,

Thanks. We don't have a calendar table

Alternative solution that does not use a calendar.


; with PolicyDates as
(
	select	idno, startDate, endDate, [Date] = dateadd(month, datediff(month, 0, startDate), 0)
	from	#Policies

	union all

	select	d.idno, d.startDate, d.endDate, [Date] = dateadd(month, 1, d.[Date])
	from	PolicyDates d
	where	d.[Date]	<= dateadd(month, datediff(month, 0, d.endDate) - 1, 0)

)
select	*
from	PolicyDates p
where	not exists
	(
		select	*
		from	#Payment x
		where	x.idno	= p.idno
		and	x.ProcessDate	>= p.[Date]
		and	x.ProcessDate	<  dateadd(month, 1, p.[Date])
	)
order by idno, [Date]