Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Must be a better way!

Author  Topic 

rtown
Yak Posting Veteran

53 Posts
Posted - 2013-12-23 : 12:12:04
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

30421 Posts
Posted - 2013-12-23 : 12:38:28
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

52326 Posts
Posted - 2013-12-23 : 12:44:19
[code]
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
[/code]

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

rtown
Yak Posting Veteran

53 Posts
Posted - 2013-12-27 : 11:41:46
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

52326 Posts
Posted - 2013-12-28 : 03:31:32
[code]
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")%>
..
[/code]

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

rtown
Yak Posting Veteran

53 Posts
Posted - 2013-12-31 : 10:47:01
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

30421 Posts
Posted - 2013-12-31 : 11:58:48
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

52326 Posts
Posted - 2013-12-31 : 13:14:17
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 - 2014-01-02 : 12:17:47
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

52326 Posts
Posted - 2014-01-02 : 12:47:08
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2014-01-03 : 15:46:29
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
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site