Compute Trouble - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Compute Trouble

Author

Topic

SyDiko
Starting Member

22 Posts

Posted - 2012-04-25 : 15:05:32

Hi All,

I've been ~~optimizing~~ rewriting code of a former developer and I've come up with this simple statistical query:

DECLARE
@year nvarchar(4),
@closed nvarchar(11)

SET @year = DATEPART(yyyy,getdate());
SET @closed = 'closed ' + @year;

select

f11562 as [Funder],
SUM(case when DATEDIFF(DD,f61,getdate()) = 0 then 1 else 0 END) as [Today],
SUM(case when DATEDIFF(DD,f61,getdate()) = 1 then 1 else 0 END) as [Yesterday],
SUM(case when DATEDIFF(WK,f61,getdate()) = 0 then 1 else 0 END) as [This Week],
SUM(case when DATEDIFF(WK,f61,getdate()) = 1 then 1 else 0 END) as [Last Week],
SUM(case when DATEDIFF(MM,f61,getdate()) = 0 then 1 else 0 END) as [This Month],
SUM(case when DATEDIFF(MM,f61,getdate()) = 1 then 1 else 0 END) as [Last Month],
SUM(case when DATEDIFF(YY,f61,getdate()) = 0 then 1 else 0 END) as [YTD]

from all_fields where

foldername IN ('active','cbnf',@closed)
and DATEDIFF(YYYY,f61,getdate()) = 0
and f11562 is NOT NULL

GROUP BY f11562
ORDER BY [This Month] DESC

It works great and it's pretty fast! Not only that, but I future proofed it as well with a simple variable.

The only problem that I'm having is trying to compute the columns for sum totals. I've been reading and based on what I've read, compute does not allow aliases within the compute clause.

Is the above assessment true? If not, how would one go about achieving sum totals for each column, while keeping the code intact? I know there is a trick for it, but I'm unable to figure it out.

Thanks in advance for your thuoghts and help! :)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-04-25 : 15:43:26

Check out the ROLLUP feature:

GROUP BY f11562 WITH ROLLUP

COMPUTE BY is deprecated by the way, and SQL 2008 has improved ROLLUP and adding GROUPING SETS. Check them out.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-25 : 15:47:31

why are you concerned about alias for total? where are you trying to display the data?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SyDiko
Starting Member

22 Posts

Posted - 2012-04-25 : 15:53:20

quote:
Originally posted by robvolk

Check out the ROLLUP feature:
GROUP BY f11562 WITH ROLLUP
COMPUTE BY is deprecated by the way, and SQL 2008 has improved ROLLUP and adding GROUPING SETS. Check them out.

I will look into this! :)

quote:
Originally posted by visakh16

why are you concerned about alias for total? where are you trying to display the data?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Well, if it can be done without the alias that's fine too.

Basically when all the data is returned in the column, a final row counting the totals for each column.

So for example: (I'll shorten the table results for simplicity a bit.)

Funder - Today - Yesterday
DELL - 1 - 2
HP - 2 - 4
---------------------------
2 - 3 - 6

As you can see above, the totals are added (sumed) at the bottom.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-25 : 16:12:04

you will be able to get it using cube or rollup functions. but it wont be in format you showed. You need to generated appropriate display format in front end

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SyDiko
Starting Member

22 Posts

Posted - 2012-04-25 : 16:39:09

Yup, you guys are right, rollup worked! :)

Is there anyway to name the rollup row?

actually I found an article with a way to name the column using the GROUPING function! :)

http://sqlandme.com/2011/07/07/sql-server-tsql-group-by-with-rollup/

and here's the finished code:

DECLARE
@year nvarchar(4),
@closed nvarchar(11)

SET @year = DATEPART(yyyy,getdate());
SET @closed = 'closed ' + @year;

select

CASE GROUPING([f11562])
WHEN 1 THEN 'TOTALS:'
ELSE [f11562] END AS 'FUNDER',

SUM(case when DATEDIFF(DD,f61,getdate()) = 0 then 1 else 0 END) as [Today],
SUM(case when DATEDIFF(DD,f61,getdate()) = 1 then 1 else 0 END) as [Yesterday],
SUM(case when DATEDIFF(WK,f61,getdate()) = 0 then 1 else 0 END) as [This Week],
SUM(case when DATEDIFF(WK,f61,getdate()) = 1 then 1 else 0 END) as [Last Week],
SUM(case when DATEDIFF(MM,f61,getdate()) = 0 then 1 else 0 END) as [This Month],
SUM(case when DATEDIFF(MM,f61,getdate()) = 1 then 1 else 0 END) as [Last Month],
SUM(case when DATEDIFF(YY,f61,getdate()) = 0 then 1 else 0 END) as [YTD]

from all_fields where

foldername IN ('active','cbnf',@closed)
and DATEDIFF(YYYY,f61,getdate()) = 0
and f11562 is NOT NULL

GROUP BY f11562 WITH ROLLUP

SyDiko
Starting Member

22 Posts

Posted - 2012-04-26 : 09:48:57

Visakh16 & Robvolk

Thanks for your help, this is exactly what I was looking to achieve with my code. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources