sql grouping, sub-totaling

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

amillyardAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
LowfatspreadConnect With a Mentor Commented:
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
 
LowfatspreadCommented:
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
 
amillyardAuthor Commented:
Lowfatspread:  thats was an excellent solution -- appreciate yuor time and efforts :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.