[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

sql grouping, sub-totaling

Posted on 2011-09-28
3
Medium Priority
?
241 Views
Last Modified: 2012-05-12
the attached script works fine -- except that the results for ABC & XYZ are pulling back sum (totals) for entire date-range -- what I am trying to do is for those to returning only back the current date range sum (set by overall grouping parameter).

current data format returned:

27.08.2011      24 hr      419.23      12820.37      3275.70
28.08.2011      24 hr      430.84      12820.37      3275.70
29.08.2011      24 hr      492.70      12820.37      3275.70
30.08.2011      24 hr      466.77      12820.37      3275.70
31.08.2011      24 hr      494.62      12820.37      3275.70

the last 2 columns are totalling the same dataset but with 1 additional filter - currently returning entire date range seleted as opposed to returning per-day as with the overall total column.  columns 4 & 5 for example should always be smaller than column 3.


USE [db1]

DECLARE @endDate   datetime
DECLARE @startDate datetime

SET @startDate = '2011-08-27 00:00:00.000'
SET @endDate   = '2011-09-30 23:59:59.999'
	    
select 

convert(varchar, CONVERT(DATETIME,(CAST(DATEPART(MONTH,SetupTime) AS VARCHAR)  +'-' + CAST(DATEPART(DAY,SetupTime) AS VARCHAR) + '-' + CAST(DATEPART(YEAR,SetupTime) AS VARCHAR))), 104) AS 'REPORTING DATE'

, '24 hr' AS 'PERIOD'

,cast( Cast(isnull(SUM(ChargeableAmount), 0) AS DECIMAL(18,2)) / 100000 AS DECIMAL(18,2)) AS 'TOTAL'
       
,(select cast( Cast(isnull(SUM(ChargeableAmount), 0) AS DECIMAL(18,2)) / 100000 AS DECIMAL(18,2))
	FROM [db1].[dbo].[CDRData]  
	WHERE (Direction = 1) AND (COSID =95) 
	  AND (SetupTime BETWEEN @startDate AND @endDate) 
	  AND (LocationZoneName = 'UK ECO ABC')) AS 'ABC' 
	  
,(select cast( Cast(isnull(SUM(ChargeableAmount), 0) AS DECIMAL(18,2)) / 100000 AS DECIMAL(18,2))
	FROM [db1].[dbo].[CDRData]  
	WHERE (Direction = 1) AND (COSID =95) 
	  AND (SetupTime BETWEEN @startDate AND @endDate) 
	  AND (LocationZoneName = 'UK ECO XYZ')) AS 'XYZ' 
			  
from [CDRData] 

WHERE (Direction = 1) 
	AND (COSID =95) 
	AND (SetupTime BETWEEN @startDate AND @endDate)
      
group by datepart(day,SetupTime), datepart(month,SetupTime), datepart(year,SetupTime)
order by CONVERT(DATETIME,(CAST(DATEPART(MONTH,SetupTime) AS VARCHAR)  +'-' + CAST(DATEPART(DAY,SetupTime) AS VARCHAR) + '-' + CAST(DATEPART(YEAR,SetupTime) AS VARCHAR)))

Open in new window

0
Comment
Question by:amillyard
  • 2
3 Comments
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 2000 total points
ID: 36715975
like this
USE [db1]

DECLARE @endDate   datetime
DECLARE @startDate datetime

SET @startDate = '2011-08-27 00:00:00.000'
SET @endDate   = '2011-09-30 23:59:59.999'
	    
select 

convert(char(10), SetupTime,104) as 'REPORTING DATE'

, '24 hr' AS 'PERIOD'

,cast( Cast(isnull(SUM(ChargeableAmount), 0) AS DECIMAL(18,2)) / 100000 AS DECIMAL(18,2)) AS 'TOTAL'
       
, cast( Cast(isnull(SUM(case locationzonename 
                            when 'UK ECO ABC' then
                          ChargeableAmount end), 0) AS DECIMAL(18,2)) / 100000 AS DECIMAL(18,2))
	 AS 'ABC' 
	  
, cast( Cast(isnull(SUM(case locationzonename
                         when 'UK ECO XYZ' then
                    ChargeableAmount end), 0) AS DECIMAL(18,2)) / 100000 AS DECIMAL(18,2))
	  AS 'XYZ' 
			  
from [CDRData] 

WHERE (Direction = 1) 
	AND (COSID =95) 
	AND (SetupTime BETWEEN @startDate AND @endDate)
      
group by convert(char(10), SetupTime,104)
order by 1

Open in new window

0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36715984
ps the end date should be expressed as
SET @endDate   = '2011-09-30 23:59:59.997'

or you will round up to 2011-10-01 00:00:00.000

0
 

Author Closing Comment

by:amillyard
ID: 36715996
Lowfatspread:  thats was an excellent solution -- appreciate yuor time and efforts :-)
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

590 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question