Solved

sql grouping, sub-totaling

Posted on 2011-09-28
3
211 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 500 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

777 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