>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.