How to get total sum across the table and sum grouped by date ranges
-
Here is sample code and what I tried to achieve, but not getting the output, any advice?
select k.charges,
sum(case when k.range>='101' then charges else 0 end) as '101charges',
sum(case when k.range>='201' then charges else 0 end) as '201charge'
from
(select charges,substring(daterange,1,3) as range
from dbo.charges,
)k
CREATE TABLE charges (
charges money not null,
dayrange nvarchar(14) not null
);
Insert into charges (charges,dayrange)
Values (100,’0-100’);
Values (150,’101-200’);
Values (700,’201=300’);
Values (20,’301-400’)Thanks
-
Maybe try this:
select k.charges,
sum(case when k.range>='101' then charges else 0 end) as '101charges',
sum(case when k.range>='201' then charges else 0 end) as '201charge'
from
(select charges,case when substring(daterange,1,3) like '0-%' then '000' --<<--
else substring(daterange,1,3) end as range
from dbo.charges
)kSQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
-
Thanks Guru but that doesn't give me overall total and break down by days range
-
What result do you want? You never explicitly stated the results you want to see.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
-
Sorry, but you've been extremely sloppy with your example code/data.
- The insert into statement can't even pass a parse test.
- There are different column names used in the table and in the query (dayrange vs daterange).
- The example data does nothing to help us help you. It needs to be closer to your actual data (which I guess is a list of dates or something like that)
- Etc.
I have tried to change your code so that it at least will run, but I still can't figure out what to do with the emulation data as it stands.
CREATE TABLE #charges (
charges INT NOT NULL,
dayrange NVARCHAR(14) NOT NULL
);
INSERT INTO #charges (charges,dayrange)
VALUES
(100,'0-100'),
(150,'101-200'),
(700,'201-300'),
(20,'301-400');
SELECT
k.charges,
SUM(CASE WHEN k.range>='101' THEN charges ELSE 0 END) AS '101charges',
SUM(CASE WHEN k.range>='201' THEN charges ELSE 0 END) AS '201charge'
FROM (
SELECT charges,SUBSTRING(dayrange,1,3) AS RANGE
FROM #charges
)k
GROUP BY k.chargesMaybe others can where I fail... 🙂
-
I've updated the code to a) use a temp table instead of a permanent table. Here is the setup code:
DROP TABLE IF EXISTS #charges;
CREATE TABLE #charges (
charges money not null,
dayrange nvarchar(14) not null
);
Insert into #charges (charges,dayrange)
Values (100,'0-100')
, (150,'101-200')
, (700,'201=300')
, (20,'301-400')Note that the values are all part of the same statement, so they are separated by commas, not semicolons and the
VALUESkeyword is only specified once at the beginning instead of for every value.This code gets close to your screenshot.
SELECT *
, SUM(charges) OVER() AS charges
, '>=' + LEFT(LEAD(dayrange) OVER(ORDER BY dayrange), 3) AS daterange
, SUM(charges) OVER(ORDER BY dayrange ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) AS charges1
FROM #charges AS k
ORDER BY dayrange;NOTE: It's unclear why your third row doesn't have any values for the last three columns. I also left out the blank column.
NOTE 2: I specifically used
+instead ofCONCAT(), because I wanted it to create a NULL value for the last row.Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
Firstly please do not take my constructive criticism poorly, as it is meant to help you improve future questions so that you can get quicker answers as well as perhaps help better understand your own question allowing you to perhaps solve it before you even post it.
Now as has been previously mentioned and you had to clarify -- what you were trying to achieve was not initially clearly stated. Which always makes it much more difficult for us to help you. So it is always best to make sure you fully and completely state all the parameters and all your expected results clearly and concisely.
Next your script was choke full of bugs and would never have worked as it stands, as others also pointed out. Supplying broken code examples is next to worthless and only greatly delays or prevents you from getting the answer you seek.
Still, looking at your initial question and guessing that it was just a half-hearted attempt on your part -- I produced the following adjustments.
Note I also went under the assumption that the source table you provided, you are not allowed to change (which you also did not state) as frankly that table is a horribly structured. So I hope the following helps.
CREATE TABLE [dbo].[tbCharges]
( [Charges] MONEY NOT NULL
,[DayRange] VARCHAR(14) NOT NULL
);
GO
INSERT INTO [dbo].[tbCharges]
( [Charges]
,[DayRange]
)
VALUES ( 100, ’0-100’ )
,( 150, ’101-200’ )
,( 700, ’201-300’ )
,( 20, ’301-400’ );
GO
CREATE TABLE #WorkTable
( [BgnDay] INT NOT NULL
,[EndDay] INT NOT NULL
,[Charges] MONEY NOT NULL
);
GO
-- This is minor unless the table is huge
ALTER TABLE #WorkTable
ADD CONSTRAINT [PK_BgnDay]
PRIMARY KEY CLUSTERED ( BgnDay );
INSERT INTO #WorkTable
( [BgnDay]
,[EndDay]
,[Charges]
)
SELECT CAST( SUBSTRING( [DayRange], 0, CHARINDEX ('-', [DayRange] ) - 1) AS INT )
,CAST( SUBSTRING( [DayRange], CHARINDEX ('-', [DayRange] ) + 1, 10 ) AS INT )
,[Charges]
FROM [dbo].[tbCharges];
GO
-- Note your SUBSTRING( [DayRange], 1, 3 ) would break on '0-100'
/*
Expected Totals
Date Range Charges
>= 0 970
>= 101 870
>= 201 720
>= 301 20
*/SELECT SUM( CASE WHEN [BgnDay] >= 0
THEN [Charges]
ELSE 0
END
) AS 'AllCharges'
,SUM( CASE WHEN [BgnDay] >= 101
THEN [Charges]
ELSE 0
END
) AS '101+Charges'
,SUM( CASE WHEN [BgnDay] >= 201
THEN [Charges]
ELSE 0
END
) AS '201+Charges'
,SUM( CASE WHEN [BgnDay] >= 301
THEN [Charges]
ELSE 0
END
) AS '301+Charges'
,[rslt].[Charges]
FROM #WorkTable;
GO
/*
Query Results
AllCharges 101+Charges 201+Charges 301+Charges
970 870 720 20
*/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply