July 7, 2010 at 10:52 am
The problem is the Insurance company is getting repeated, there should be just one row for each insurace type, How can i achive this. Very new to SQL T-Query
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 is
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
Warning: Null value is eliminated by an aggregate or other SET operation.
July 7, 2010 at 11:51 am
not sure why you're using a CTE. You can just use a simple select and group by. One of the issues you're having is because you are gouping by the datename which will provide 1 row for each month. try this
SELECT
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
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 7, 2010 at 11:59 am
Thanks much... that really worked, should nt hvae kept another group by clause
July 7, 2010 at 12:06 pm
If you did want to use a pivot table, here's the syntax for that, but it's a little more difficult. Both examples had the same explain plan
Select InsurerID, [January], [February], [March], [April], [May], [June],
[July], [August], [September], [October], [November], [December]
FROM
(select InsurerID, DATENAME(MONTH,LoginDate) LoginDate, SumAssured
FROM #tblPolicyDetails) AS SourceTable
PIVOT
(
sum(SumAssured)
for LoginDate in ([January], [February], [March], [April], [May], [June],
[July], [August], [September], [October], [November], [December])
) as PivotTable;
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 7, 2010 at 12:12 pm
If i put a filter " WHERE DATENAME(YEAR,LoginDate) = '2010'
I lose out on all the insurance company, It should lost out everypossible insurance company if no value the it should be null....
I know i am being too greedy and asking to much, But for sure this is last one ;p
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
BHARTI AXA GENERAL INSURANCE CO. LTD. NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
LIC 310000.00 NULL NULL NULL NULL 2168410.00 NULL NULL NULL NULL NULL NULL
RELAINCE GENERAL INSURANCE CO. LTD 23.00 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
July 7, 2010 at 12:20 pm
The main reason your first approach failed (at least as far I can see):
You have DATENAME(MONTH,LoginDate) in your GROUP BY clause.
This made the SUM() aggregation obsolete leading to the results you ended up with.
Without the DATENAME() function the query returns the aggregated data like Mike demonstrated.
July 7, 2010 at 12:24 pm
I'm not sure what you did or what you are looking for. Can you post what you did? If you are looking for all records for the Year 2010, then you need to put that in the the criteria in the correct place (not sure where you put it). If you want to know all date possibilities, but only populate the year 2010 records and leave all others null, then that's a different story. Let me know what you tried.
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 7, 2010 at 12:31 pm
This is what i tried, O/P is all the insurance companies that did some business in 2010, but i want all of them, If they have not done any business be it null
SELECT
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
WHERE DATENAME(YEAR,LoginDate) = '2010'
GROUP BY I.InsurerDesc
July 7, 2010 at 12:41 pm
That won't work since it will only return 2010 data. You need to add the filter to the sum statement. Keep in mind that since there is no where clause, this will do table scans on all of the tables involved. If the tables are large, this could take a considerable amount of time
SELECT
I.InsurerDesc as Insurer,
SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'January' and DATENAME(YEAR,LoginDate) = '2010' THEN SumAssured END) AS January,
SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'February' and DATENAME(YEAR,LoginDate) = '2010' THEN SumAssured END) AS February,
SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'March' and DATENAME(YEAR,LoginDate) = '2010' THEN SumAssured END) AS March,
SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'April' and DATENAME(YEAR,LoginDate) = '2010' THEN SumAssured END) AS April,
SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'May' and DATENAME(YEAR,LoginDate) = '2010' THEN SumAssured END) AS May,
SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'June' and DATENAME(YEAR,LoginDate) = '2010' THEN SumAssured END) AS June,
SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'July' and DATENAME(YEAR,LoginDate) = '2010' THEN SumAssured END) AS July,
SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'August' and DATENAME(YEAR,LoginDate) = '2010' THEN SumAssured END) AS August,
SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'September' and DATENAME(YEAR,LoginDate) = '2010' THEN SumAssured END) AS September,
SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'October' and DATENAME(YEAR,LoginDate) = '2010' THEN SumAssured END) AS October,
SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'November' and DATENAME(YEAR,LoginDate) = '2010' THEN SumAssured END) AS November,
SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'December' and DATENAME(YEAR,LoginDate) = '2010' 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
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 7, 2010 at 1:35 pm
O/P for your query and my is same, Just that the filter is applied at different place.
What i intended to get it " All the insurance companies that are listed with me, The sum of revenues that i could generate out of them.
In that context If there is any insurance company that has done generated 0 revenue it will not be listed in our query output. And this is required. Although xyz company has not given me any business but in the report i want to see null or 0 $.
Thanks for your help till here... I just wanted to explain you the whole scenario 🙂
July 7, 2010 at 1:59 pm
here is the setup that I am using. The final query returns all insurers regardless of activity. Then if the activity is in the year 2010 it is added otherwise it is not. Can you validate that this is what you are looking for? Notice Insurer 6 doesn't have any activity, but still shows in the total. Also, Insurer 7 has activity, but in year 2011, so it doesn't show either.
create table #tblProductDetails
(ProductID int,
InsurerID int not null,
ProductDesc varchar(30))
go
insert into #tblProductDetails
select 1, 1, 'Product 1' union all
select 2, 1, 'Product 2' union all
select 3, 1, 'Product 3' union all
select 4, 1, 'Product 4' union all
select 5, 2, 'Product 5' union all
select 6, 3, 'Product 6' union all
select 7, 3, 'Product 7' union all
select 8, 5, 'Product 8' union all
select 9, 7, 'Product 9'
create table #tblPolicyDetails
(ProductID int not null,
LoginDate smalldatetime not null,
SumAssured int not null)
insert into #tblPolicyDetails
select 1, '1/1/2010', 100 union all
select 1, '2/1/2010', 200 union all
select 1, '3/1/2010', 300 union all
select 1, '4/1/2010', 400 union all
select 1, '5/1/2010', 500 union all
select 1, '6/1/2010', 600 union all
select 1, '7/1/2010', 700 union all
select 1, '8/1/2010', 800 union all
select 1, '9/1/2010', 900 union all
select 1, '10/1/2010', 1000 union all
select 1, '11/1/2010', 1100 union all
select 1, '12/1/2010', 1200 union all
select 1, '1/1/2010', 100 union all
select 2, '2/1/2010', 200 union all
select 3, '3/1/2010', 300 union all
select 4, '4/1/2010', 400 union all
select 5, '5/1/2010', 500 union all
select 6, '6/1/2010', 60 union all
select 7, '7/1/2010', 70 union all
select 8, '8/1/2010', 80 union all
select 8, '9/1/2010', 90 union all
select 1, '10/1/2010', 100 union all
select 1, '11/1/2010', 110 union all
select 1, '12/1/2010', 120 union all
select 9, '10/1/2011', 100000 union all
select 9, '11/1/2011', 110000 union all
select 9, '12/1/2011', 120000
Create table #Insurer (InsurerID int not null, InsurerDesc varchar(30))
INSERT INTO #Insurer
select 1, 'Insurer 1' union all
select 2, 'Insurer 2' union all
select 3, 'Insurer 3' union all
select 4, 'Insurer 4' union all
select 5, 'Insurer 5' union all
select 6, 'Insurer 6' union all
select 7, 'Insurer 7'
SELECT i.InsurerDesc,
SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'January' and DATENAME(YEAR,LoginDate) = '2010' THEN SumAssured else 0 END) AS January,
SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'February' and DATENAME(YEAR,LoginDate) = '2010' THEN SumAssured else 0 END) AS February,
SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'March' and DATENAME(YEAR,LoginDate) = '2010' THEN SumAssured else 0 END) AS March,
SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'April' and DATENAME(YEAR,LoginDate) = '2010' THEN SumAssured else 0 END) AS April,
SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'May' and DATENAME(YEAR,LoginDate) = '2010' THEN SumAssured else 0 END) AS May,
SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'June' and DATENAME(YEAR,LoginDate) = '2010' THEN SumAssured else 0 END) AS June,
SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'July' and DATENAME(YEAR,LoginDate) = '2010' THEN SumAssured else 0 END) AS July,
SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'August' and DATENAME(YEAR,LoginDate) = '2010' THEN SumAssured else 0 END) AS August,
SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'September' and DATENAME(YEAR,LoginDate) = '2010' THEN SumAssured else 0 END) AS September,
SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'October' and DATENAME(YEAR,LoginDate) = '2010' THEN SumAssured else 0 END) AS October,
SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'November' and DATENAME(YEAR,LoginDate) = '2010' THEN SumAssured else 0 END) AS November,
SUM(CASE WHEN DATENAME(MONTH,LoginDate) = 'December' and DATENAME(YEAR,LoginDate) = '2010' THEN SumAssured else 0 END) AS December
FROM #tblPolicyDetails PolD
inner join #tblProductDetails PD on PolD.ProductID = Pd.ProductID
RIGHT OUTER JOIN #Insurer I on I.InsurerID = PD.InsurerID
GROUP BY I.InsurerDesc
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 7, 2010 at 2:07 pm
PERFECT 10... waooo!!!
You drilled it so well, I Spent more than 8 hrs and was still struggling to get it rite 🙂
Can i mark this post as answer 🙂
July 7, 2010 at 2:08 pm
I think you just did!! Glad I could help!
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply