quote:

---

Originally posted by singhswat

MY report is not giving me the desired O/P, THe problem is Insurance compnay is getting repeated :( How can i get a consolidate on and filter it year wise

WITH Consolidated_MonthWise(Insurer, January, February, March, April, May, June, July, August, September, October, November, December)
as
(
SELECT
distinct(I.InsurerDesc) as Insurer,
SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'January' THEN SumAssured END) AS January,
SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'February' THEN SumAssured END) AS February,
SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'March' THEN SumAssured END) AS March,
SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'April' THEN SumAssured END) AS April,
SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'May' THEN SumAssured END) AS May,
SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'June' THEN SumAssured END) AS June,
SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'July' THEN SumAssured END) AS July,
SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'August' THEN SumAssured END) AS August,
SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'September' THEN SumAssured END) AS September,
SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'October' THEN SumAssured END) AS October,
SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'November' THEN SumAssured END) AS November,
SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'December' THEN SumAssured END) AS December
FROM tblPolicyDetails PolD
inner join tblProductDetails PD on PolD.ProductDescID = Pd.ProductDescID
right outer join tblInsurer I on I.InsurerID = PD.InsurerID
--group by I.InsurerDesc, Datename(month,PolD.LoginDate)
GROUP BY I.InsurerDesc,DATENAME(MONTH,LoginDate)
)
select * from Consolidated_MonthWise


O/P looks like this

Insurer January February March April May June July August September October November December
-------------------------------------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- ---------------------
NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
0.00 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
APOLLO MUNICH INSURANCE CO. LTD. NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
BAJAJ ALLIANZ GENERAL INSURANCE CO. LTD. NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
BHARTI AXA GENERAL INSURANCE CO. LTD. NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
CHOLA MANDALAM GENERAL INSURANCE CO.LTD. NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
FUTER GENERALI INSURANCE CO .LTD 120000.00 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
HDFC ERGO GENERAL INSURANCE CCCO.LTD 2052525.00 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
ICICI LOMBARD GENERAL INSURANCE CO. LTD NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
IFFCO TOKIO INSURANCE CO. LTD NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
LIC NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
LIC NULL NULL NULL NULL NULL 2168410.00 NULL NULL NULL NULL NULL NULL
LIC 34827620.00 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
NATIONAL ASSURANCE CO. LTD NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
NEW INDIA ASSURANCE CO. LTD. NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
ORIENTAL INSURANCE CO. LTD NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
RELAINCE GENERAL INSURANCE CO. LTD NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
RELAINCE GENERAL INSURANCE CO. LTD 181273.00 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
ROYAL SUNDRAM GENERAL INSURANCE CO. LTD NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
STAR HEALTH AND ALLIED INSURANCE CO .LTD NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
TATA AIG GENERAL INSURANCE CO. LTD. NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
UNITED INDIA INSURANCE CO. LTD NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

---