>>Hi
>>
>>I want a query from this data and I can't seem to get it right
>>
>>claim_id,claim_date,policy_type,policy_id
>>
>>the query should give me a total by yearmonth and policy_type where the claim_id has been claimed on in the last 3 months with no result if it hasn't
>>
>>so if I had
>>
>>1,21/01/2010,"type one",1000,201001
>>2,21/01/2010,"type two",2000,201001
>>3,23/01/2010,"type one",3000,201001
>>4,22/01/2010,"type one",4000,201001
>>
>>my output would be
>>
>>claim_count,policy_type,yearmonth
>>
>>3,"type one",201001
>>
>>Thanks
>>
>>Nick
>
>Try
>

>select count(*) as Claim_Count, Policy_Type, YearMonth from Claims where YearMonth between @StartMonth and @EndMonth
>group by Policy_Type, YearMonth
>UNION ALL
>select 0 as Claim_Count, P.Policy_Type, D.YearMonth from (select Policy_Type from Policy_Types) P, (select YearMonth from Dates) D
>where not exists (select 1 from Claims C where C.Policy_Type = P.Policy_Type and C.YearMonth between @StartMonth and @EndMonth)

>
>Assuming you have a table with distinct policy_types called Policy_Types.

Thanks Naomi

I'll check that out