SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Must be a better way!
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rtown
Yak Posting Veteran

53 Posts

Posted - 12/23/2013 :  12:12:04  Show Profile  Reply with Quote
Hey guys,
I feel like there must be a better way to get this information. I am summing a money field, by month (01 through 12) and year (text fields), then accessing each variable individually. There must be a more efficient way of polling this:


SQL25 = "SELECT SUM(CASE WHEN SUBSTRING(QuoteDate,4,2) = '01' AND SUBSTRING(QuoteDate,7,4) = '2013' AND (Active='True' OR Active='False') THEN TotalValue ELSE 0 END) AS [s25] FROM [Jobs];"
Set rs25 = connection.Execute(SQL25)
SQL26 = "SELECT SUM(CASE WHEN SUBSTRING(QuoteDate,4,2) = '02' AND SUBSTRING(QuoteDate,7,4) = '2013' AND (Active='True' OR Active='False') THEN TotalValue ELSE 0 END) AS [s26] FROM [Jobs];"
Set rs26 = connection.Execute(SQL26)
SQL27 = "SELECT SUM(CASE WHEN SUBSTRING(QuoteDate,4,2) = '03' AND SUBSTRING(QuoteDate,7,4) = '2013' AND (Active='True' OR Active='False') THEN TotalValue ELSE 0 END) AS [s27] FROM [Jobs];"
Set rs27 = connection.Execute(SQL27)
SQL28 = "SELECT SUM(CASE WHEN SUBSTRING(QuoteDate,4,2) = '04' AND SUBSTRING(QuoteDate,7,4) = '2013' AND (Active='True' OR Active='False') THEN TotalValue ELSE 0 END) AS [s28] FROM [Jobs];"
Set rs28 = connection.Execute(SQL28)
SQL29 = "SELECT SUM(CASE WHEN SUBSTRING(QuoteDate,4,2) = '05' AND SUBSTRING(QuoteDate,7,4) = '2013' AND (Active='True' OR Active='False') THEN TotalValue ELSE 0 END) AS [s29] FROM [Jobs];"
Set rs29 = connection.Execute(SQL29)
SQL30 = "SELECT SUM(CASE WHEN SUBSTRING(QuoteDate,4,2) = '06' AND SUBSTRING(QuoteDate,7,4) = '2013' AND (Active='True' OR Active='False') THEN TotalValue ELSE 0 END) AS [s30] FROM [Jobs];"
Set rs30 = connection.Execute(SQL30)
SQL31 = "SELECT SUM(CASE WHEN SUBSTRING(QuoteDate,4,2) = '07' AND SUBSTRING(QuoteDate,7,4) = '2013' AND (Active='True' OR Active='False') THEN TotalValue ELSE 0 END) AS [s31] FROM [Jobs];"
Set rs31 = connection.Execute(SQL31)
SQL32 = "SELECT SUM(CASE WHEN SUBSTRING(QuoteDate,4,2) = '08' AND SUBSTRING(QuoteDate,7,4) = '2013' AND (Active='True' OR Active='False') THEN TotalValue ELSE 0 END) AS [s32] FROM [Jobs];"
Set rs32 = connection.Execute(SQL32)
SQL33 = "SELECT SUM(CASE WHEN SUBSTRING(QuoteDate,4,2) = '09' AND SUBSTRING(QuoteDate,7,4) = '2013' AND (Active='True' OR Active='False') THEN TotalValue ELSE 0 END) AS [s33] FROM [Jobs];"
Set rs33 = connection.Execute(SQL33)
SQL34 = "SELECT SUM(CASE WHEN SUBSTRING(QuoteDate,4,2) = '10' AND SUBSTRING(QuoteDate,7,4) = '2013' AND (Active='True' OR Active='False') THEN TotalValue ELSE 0 END) AS [s34] FROM [Jobs];"
Set rs34 = connection.Execute(SQL34)
SQL35 = "SELECT SUM(CASE WHEN SUBSTRING(QuoteDate,4,2) = '11' AND SUBSTRING(QuoteDate,7,4) = '2013' AND (Active='True' OR Active='False') THEN TotalValue ELSE 0 END) AS [s35] FROM [Jobs];"
Set rs35 = connection.Execute(SQL35)
SQL36 = "SELECT SUM(CASE WHEN SUBSTRING(QuoteDate,4,2) = '12' AND SUBSTRING(QuoteDate,7,4) = '2013' AND (Active='True' OR Active='False') THEN TotalValue ELSE 0 END) AS [s36] FROM [Jobs];"
Set rs36 = connection.Execute(SQL36)

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 12/23/2013 :  12:38:28  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Yes, execute only one statement at the database like this and return all calculations as columns instead.
SELECT	SUM(CASE WHEN DATEPART(MONTH, QuoteDate) = 01 THEN TotalValue ELSE 0E END) AS s25,
	SUM(CASE WHEN DATEPART(MONTH, QuoteDate) = 02 THEN TotalValue ELSE 0E END) AS s26,
	SUM(CASE WHEN DATEPART(MONTH, QuoteDate) = 03 THEN TotalValue ELSE 0E END) AS s27,
	SUM(CASE WHEN DATEPART(MONTH, QuoteDate) = 04 THEN TotalValue ELSE 0E END) AS s28,
	SUM(CASE WHEN DATEPART(MONTH, QuoteDate) = 05 THEN TotalValue ELSE 0E END) AS s29,
	SUM(CASE WHEN DATEPART(MONTH, QuoteDate) = 06 THEN TotalValue ELSE 0E END) AS s30,
	SUM(CASE WHEN DATEPART(MONTH, QuoteDate) = 07 THEN TotalValue ELSE 0E END) AS s31,
	SUM(CASE WHEN DATEPART(MONTH, QuoteDate) = 08 THEN TotalValue ELSE 0E END) AS s32,
	SUM(CASE WHEN DATEPART(MONTH, QuoteDate) = 09 THEN TotalValue ELSE 0E END) AS s33,
	SUM(CASE WHEN DATEPART(MONTH, QuoteDate) = 10 THEN TotalValue ELSE 0E END) AS s34,
	SUM(CASE WHEN DATEPART(MONTH, QuoteDate) = 11 THEN TotalValue ELSE 0E END) AS s35,
	SUM(CASE WHEN DATEPART(MONTH, QuoteDate) = 12 THEN TotalValue ELSE 0E END) AS s36
FROM	dbo.[Jobs]
WHERE	QuoteDate >= '20130101'
	AND QuoteDate < '20140101'
	AND Active IN ('True', 'False');



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/23/2013 :  12:44:19  Show Profile  Reply with Quote

SELECT  [01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12]
FROM (SELECT SUBSTRING(QuoteDate,4,2) AS [Month],SUBSTRING(QuoteDate,7,4) AS [Year],TotalValue 
FROM [Jobs] 
WHERE (Active='True' OR Active='False') 
AND SUBSTRING(QuoteDate,7,4) = '2013') j
PIVOT (SUM(TotalValue) FOR [Month] IN ([01],[02],[03],... all values upto [12]))p


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

rtown
Yak Posting Veteran

53 Posts

Posted - 12/27/2013 :  11:41:46  Show Profile  Reply with Quote
quote:
Originally posted by visakh16


SELECT  [01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12]
FROM (SELECT SUBSTRING(QuoteDate,4,2) AS [Month],SUBSTRING(QuoteDate,7,4) AS [Year],TotalValue 
FROM [Jobs] 
WHERE (Active='True' OR Active='False') 
AND SUBSTRING(QuoteDate,7,4) = '2013') j
PIVOT (SUM(TotalValue) FOR [Month] IN ([01],[02],[03],... all values upto [12]))p




Thank you visakh16, but how can I then access the data? I am currently accessing it through asp with <%=rs25("s25")%>.
Thank you!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/28/2013 :  03:31:32  Show Profile  Reply with Quote

SQL25 = "SELECT  [01] AS Mnth1Val,[02] AS Mnth2Val,[03] AS Mnth3Val,[04] AS Mnth4Val,[05] AS Mnth5Val,[06] AS Mnth6Val,[07] AS Mnth7Val,[08] AS Mnth8Val,[09] AS Mnth9Val,[10] AS Mnth10Val,[11] AS Mnth11Val,[12] AS Mnth12Val
FROM (SELECT SUBSTRING(QuoteDate,4,2) AS [Month],SUBSTRING(QuoteDate,7,4) AS [Year],TotalValue 
FROM [Jobs] 
WHERE (Active='True' OR Active='False') 
AND SUBSTRING(QuoteDate,7,4) = '2013') j
PIVOT (SUM(TotalValue) FOR [Month] IN ([01],[02],[03],... all values upto [12]))p;"
Set rs25 = connection.Execute(SQL25)
..

<%=rs25("Mnth1Val")%>
<%=rs25("Mnth2Val")%>
..


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Edited by - visakh16 on 12/28/2013 03:33:40
Go to Top of Page

rtown
Yak Posting Veteran

53 Posts

Posted - 12/31/2013 :  10:47:01  Show Profile  Reply with Quote
quote:
Originally posted by visakh16


SQL25 = "SELECT  [01] AS Mnth1Val,[02] AS Mnth2Val,[03] AS Mnth3Val,[04] AS Mnth4Val,[05] AS Mnth5Val,[06] AS Mnth6Val,[07] AS Mnth7Val,[08] AS Mnth8Val,[09] AS Mnth9Val,[10] AS Mnth10Val,[11] AS Mnth11Val,[12] AS Mnth12Val
FROM (SELECT SUBSTRING(QuoteDate,4,2) AS [Month],SUBSTRING(QuoteDate,7,4) AS [Year],TotalValue 
FROM [Jobs] 
WHERE (Active='True' OR Active='False') 
AND SUBSTRING(QuoteDate,7,4) = '2013') j
PIVOT (SUM(TotalValue) FOR [Month] IN ([01],[02],[03],... all values upto [12]))p;"
Set rs25 = connection.Execute(SQL25)
..

<%=rs25("Mnth1Val")%>
<%=rs25("Mnth2Val")%>
..




Once again visakh16, thank you for your time this worked out flawlessly.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 12/31/2013 :  11:58:48  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Beware of possible NULL values in Visakh's PIVOT. It can hurt your web page.
My CASE query does not have that problem.



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/31/2013 :  13:14:17  Show Profile  Reply with Quote
it can be handled by means of applying a simple ISNULL or COALESCE function

SQL25 = "SELECT  COALESCE([01],0) AS Mnth1Val,COALESCE([02],0) AS Mnth2Val,COALESCE([03],0) AS Mnth3Val,COALESCE([04],0) AS Mnth4Val,COALESCE([05],0) AS Mnth5Val,COALESCE([06],0) AS Mnth6Val,COALESCE([07],0) AS Mnth7Val,COALESCE([08],0) AS Mnth8Val,COALESCE([09],0) AS Mnth9Val,COALESCE([10],0) AS Mnth10Val,COALESCE([11],0) AS Mnth11Val,COALESCE([12],0) AS Mnth12Val
FROM (SELECT SUBSTRING(QuoteDate,4,2) AS [Month],SUBSTRING(QuoteDate,7,4) AS [Year],TotalValue 
FROM [Jobs] 
WHERE (Active='True' OR Active='False') 
AND SUBSTRING(QuoteDate,7,4) = '2013') j
PIVOT (SUM(TotalValue) FOR [Month] IN ([01],[02],[03],... all values upto [12]))p;"
Set rs25 = connection.Execute(SQL25)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

rtown
Yak Posting Veteran

53 Posts

Posted - 01/02/2014 :  12:17:47  Show Profile  Reply with Quote
Well you guys saw that coming before I did... with the first day of 2014 all my months have no records and I need to add some NULL protection.
That being said, I cannot get either method to protect against this. SwePeso's method still returns a null value rather than 0. Visakh your method returns "Either BOF or EOF is True" (also tried using ISNULL with the same result.


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/02/2014 :  12:47:08  Show Profile  Reply with Quote
for getting dates as static values you need to have a calendartable and left join to that.
see below link for simlar function
http://visakhm.blogspot.in/2010/02/generating-calendar-table.html
so if you want to report over a range from 1st Jan 2014 to 31st Dec 2014 make query like


SQL25 = "SELECT  [Year],COALESCE([01],0) AS Mnth1Val,COALESCE([02],0) AS Mnth2Val,COALESCE([03],0) AS Mnth3Val,COALESCE([04],0) AS Mnth4Val,COALESCE([05],0) AS Mnth5Val,COALESCE([06],0) AS Mnth6Val,COALESCE([07],0) AS Mnth7Val,COALESCE([08],0) AS Mnth8Val,COALESCE([09],0) AS Mnth9Val,COALESCE([10],0) AS Mnth10Val,COALESCE([11],0) AS Mnth11Val,COALESCE([12],0) AS Mnth12Val
FROM (SELECT REPLACE(STR(MONTH(c.{Date]),2),' ','0') AS [Month],YEAR(c.[Date]) AS [Year],COALESCE(TotalValue,0) 
FROM dbo.CalendarTable('20140101','20141231',0,1) c
LEFT JOIN [Jobs] j
ON j.QuoteDate = c.[Date]
AND (Active='True' OR Active='False') 
AND SUBSTRING(QuoteDate,7,4) = '2013'
GROUP BY REPLACE(STR(MONTH(c.{Date]),2),' ','0'),YEAR(c.[Date])) j
PIVOT (SUM(TotalValue) FOR [Month] IN ([01],[02],[03],... all values upto [12]))p;"
Set rs25 = connection.Execute(SQL25)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Edited by - visakh16 on 01/02/2014 12:48:04
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 01/03/2014 :  15:46:29  Show Profile  Visit SwePeso's Homepage  Reply with Quote
quote:
Originally posted by rtown

SwePeso's method still returns a null value rather than 0.
The only wayt for my suggestion posted 12/23/2013 : 12:38:28 to return NULL is if you are storing NULL values!
This is how your protext yourself against that
SELECT	SUM(CASE WHEN DATEPART(MONTH, QuoteDate) = 01 THEN COALESCE(TotalValue, 0E) ELSE 0E END) AS s25,
	SUM(CASE WHEN DATEPART(MONTH, QuoteDate) = 02 THEN COALESCE(TotalValue, 0E) ELSE 0E END) AS s26,
	SUM(CASE WHEN DATEPART(MONTH, QuoteDate) = 03 THEN COALESCE(TotalValue, 0E) ELSE 0E END) AS s27,
	SUM(CASE WHEN DATEPART(MONTH, QuoteDate) = 04 THEN COALESCE(TotalValue, 0E) ELSE 0E END) AS s28,
	SUM(CASE WHEN DATEPART(MONTH, QuoteDate) = 05 THEN COALESCE(TotalValue, 0E) ELSE 0E END) AS s29,
	SUM(CASE WHEN DATEPART(MONTH, QuoteDate) = 06 THEN COALESCE(TotalValue, 0E) ELSE 0E END) AS s30,
	SUM(CASE WHEN DATEPART(MONTH, QuoteDate) = 07 THEN COALESCE(TotalValue, 0E) ELSE 0E END) AS s31,
	SUM(CASE WHEN DATEPART(MONTH, QuoteDate) = 08 THEN COALESCE(TotalValue, 0E) ELSE 0E END) AS s32,
	SUM(CASE WHEN DATEPART(MONTH, QuoteDate) = 09 THEN COALESCE(TotalValue, 0E) ELSE 0E END) AS s33,
	SUM(CASE WHEN DATEPART(MONTH, QuoteDate) = 10 THEN COALESCE(TotalValue, 0E) ELSE 0E END) AS s34,
	SUM(CASE WHEN DATEPART(MONTH, QuoteDate) = 11 THEN COALESCE(TotalValue, 0E) ELSE 0E END) AS s35,
	SUM(CASE WHEN DATEPART(MONTH, QuoteDate) = 12 THEN COALESCE(TotalValue, 0E) ELSE 0E END) AS s36
FROM	dbo.[Jobs]
WHERE	QuoteDate >= '20130101'
	AND QuoteDate < '20140101'
	AND Active IN ('True', 'False');



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000