declare @countryKey int, @reportedDateKey int, @fromYYYYdashMM char(7) , @toYYYYdashMM char(7)

set @countryKey = 100041
set @reportedDateKey = 20120731
set @fromYYYYdashMM = '2006-01'
set @toYYYYdashMM = '2012-06'



SELECT 
		[MP Allocation Group Key] = case when camp.[mp allocation group key] > 0 then camp.[mp allocation group key] else sp.[mp allocation group key] end
		,[Description] = case when camp.[mp allocation group key] > 0 then camp_mpag.[description] else spons_mpag.[description] end
		,fact.[Book date key]
		,conv_launch_date= convert(char(7),dateadd(m,3, convert(datetime,cast(split.[actual launch date key] as char(8)))),120)  
		,convert_rpt_date= convert(char(7),dateadd(d, 1, convert(datetime,cast(fact.[reported date key] as char(8)))),120)
--		,a=convert(char(7),dateadd(m,3, convert(datetime,cast(split.[actual launch date key] as char(8)))),120) > convert(char(7),dateadd(d, 1, convert(datetime,cast(fact.[reported date key] as char(8)))),120)
--		,b= split.[actual launch date key]		
--		,c=convert(datetime,cast(split.[actual launch date key] as char(8)))
--		,d=dateadd(m,3, convert(datetime,cast(split.[actual launch date key] as char(8))))
--		,e = convert(char(7),dateadd(m,3, convert(datetime,cast(split.[actual launch date key] as char(8)))),120)  
		,f = case when convert(char(7),dateadd(m,3, convert(datetime,cast(split.[actual launch date key] as char(8)))),120) > convert(char(7),dateadd(d, 1, convert(datetime,cast(fact.[reported date key] as char(8)))),120) then 'yes' else 'error' end		
		,[month] = convert(char(7),dateadd(m,case when camp.[mp allocation group key] > 0 then camp_mpag.[month shift] else spons_mpag.[month shift] end, convert(datetime,cast([book date key] as char(8)))),120)
		,[month shift] = case when camp.[mp allocation group key] > 0 then camp_mpag.[month shift] else spons_mpag.[month shift] end
		,GWP = Sum(fact.[Direct Premium]) 
	FROM  [Fact Direct Premium] fact 
		INNER JOIN [Dim Campaign Split] split on fact.[campaign split key] = split.[campaign split key]
		INNER JOIN [Dim Campaign] camp on split.[Campaign Key] = camp.[Campaign Key] 
		INNER JOIN [Dim Sponsor] sp on camp.[Sponsor Key] = sp.[Sponsor Key]
		LEFT OUTER JOIN [Master Policy Allocation Group] AS spons_mpag 
			ON sp.[MP Allocation Group Key] = spons_mpag.[MP Allocation Group Key] 
		LEFT JOIN [Master Policy Allocation Group] AS camp_mpag ON camp.[MP Allocation Group Key] = camp_mpag.[MP Allocation Group Key]
	WHERE camp.[country key] = @countryKey
		and camp.[fiscal year] < '2013' --[Dim Campaign] has data for dates beyond 2012, need to resolve this, this is a emporary solution
		and sp.[Sponsor Group Key] in (100001,100002)  
		and (camp.[Line Of Business Key] in (100001) or (@countryKey = 100020 and camp.[Line Of Business Key] in (100001,100016)))  --RDW removed 100016 (Travel) to fix Australia 8/4/2012, updated to include travel for HK
		and fact.[Reported Date Key]=@reportedDateKey
		and fact.[book date key] > 20050000 and fact.[Book Date Key] < 20150000 
		and fact.Flavor=1
		and not (case when camp.[mp allocation group key] > 0 then camp.[mp allocation group key] else sp.[mp allocation group key] end = 30 and fact.[vintage]<2011) -- not Presto (Chile) prior to 2011 campaigns
		and not (fact.[country key] = 100007 and fact.[vintage] < 2009) -- not Brazil prior to 2009 campaigns
--		and	(sp.[last actual delta] = 0 or 
--			(sp.[last actual delta] = -3 and convert(char(7),dateadd(m,3, convert(datetime,cast(split.[actual launch date key] as char(8)))),120)    <  convert(char(7),dateadd(d, 1, convert(datetime,cast(fact.[reported date key] as char(8)))),120))) 
--		and dateadd(m,3, convert(datetime,cast(split.[actual launch date key] as char(8)))) > dateadd(d, 1, convert(datetime,cast(fact.[reported date key] as char(8))))
		and	dateadd(m,3,cast(split.[actual launch date key] as char(8))) > dateadd(d, 1,cast(fact.[reported date key] as char(8)))	
	GROUP BY 
		case when camp.[mp allocation group key] > 0 then camp.[mp allocation group key] else sp.[mp allocation group key] end
		,case when camp.[mp allocation group key] > 0 then camp_mpag.[description] else spons_mpag.[description] end
		,fact.[Book date key]
		,convert(char(7),dateadd(m,case when camp.[mp allocation group key] > 0 then camp_mpag.[month shift] else spons_mpag.[month shift] end, convert(datetime,cast([book date key] as char(8)))),120)
		,case when camp.[mp allocation group key] > 0 then camp_mpag.[month shift] else spons_mpag.[month shift] end
		
		,split.[actual launch date key]		
		,fact.[reported date key]

--	order by 1,2

-- IN SELECT
dateadd(m,3, convert(datetime,cast(split.[actual launch date key] as char(8))))
-- IN WHERE CLAUSE
dateadd(m,3,cast(split.[actual launch date key] as char(8)))

SET DATEFORMAT mdy;
SELECT ISDATE('30/9/2012')
SET DATEFORMAT dmy;
SELECT ISDATE('30/9/2012')

SET DATEFORMAT dmy;
SELECT * FROM [Dim Campaign Split] WHERE  ISDATE([actual launch date key]) = 0

quote:

---

Originally posted by rdw72777

Thanks sunitabeck. I was being a goof and only checking for bad data for the campaign split key field in my main data table (fact direct premium). Because the formula looks at the [dim campaign split) table I was not checking entries without data, and low and behold there are invalid dates in there.

On the plus side, my code was right, just the data was wrong. A victory for my intelligence with scripting (we'll overlook my ignorance in checking the data for now :-)

---