October 28, 2014 at 11:01 am
I need to generate a report from a table using the values from a filed to determine what to add up.
create table fun_test(
packtype nvarchar(50),
price money)
insert into fun_test
values ('Single',''),('Monthly','25.00'),('Monthly','27.00'),('Monthly','23.50'),('Single',''),('Deposit',''),('Deposit','')
select PackType,count(*) as Records,sum(price) as Value
from fun_test
group by PackType
order by PackType
What I need is if the PackType is Deposit and need to multiply the number of records by 35, If the PackType is Monthly I need to multiply the records by 25 and if the PackType is Single I need to sum the values in the price column. Can anyone suggest a good way to do this?
October 28, 2014 at 11:16 am
Quick suggestion, use a case statement
😎
USE tempdb;
GO
create table dbo.fun_test(
packtype nvarchar(50),
price money)
insert into dbo.fun_test
values ('Single','2'),('Monthly','25.00'),('Monthly','27.00'),('Monthly','23.50'),('Single','2'),('Deposit','1'),('Deposit','2')
select
PackType
,count(*) as Records
,sum(CASE
WHEN PackType = 'Deposit' THEN 35.0
WHEN PackType = 'Monthly' THEN 25.0
ELSE 1 END * price) as Value
from dbo.fun_test
group by PackType
order by PackType
DROP TABLE dbo.fun_test;
Results
PackType Records Value
----------- ----------- ------------
Deposit 2 105.00000
Monthly 3 1887.50000
Single 2 4.00000
October 28, 2014 at 11:46 am
Great - I've adapted your statement, but using the case has solved the issue! Thanks
I worded my original query slightly wrong too! Shoudl have said single=25, deposit=35, monthly=value in price column
select PackType
,count(*) as Records
,cast(sum(CASE
WHEN PackType in ('Single') THEN 25
WHEN PackType in ('Deposit') THEN 35
ELSE price END) as Money) as Value
from dbo.fun_test
group by PackType
order by PackType
October 28, 2014 at 12:18 pm
bicky1980 (10/28/2014)
Great - I've adapted your statement, but using the case has solved the issue! ThanksI worded my original query slightly wrong too! Shoudl have said single=25, deposit=35, monthly=value in price column
select PackType,count(*) as Records
,cast(sum(CASE
WHEN PackType in ('Single') THEN 25
WHEN PackType in ('Deposit') THEN 35
ELSE price END) as Money) as Value
from dbo.fun_test
group by PackType
order by PackType
Assuming you're only dealing with one or 2 values and they don't change, the CASE is fine; otherwise you'd probably want to consider have a PackTypes table with the appropriate values.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 5, 2014 at 5:01 am
Rather than use a CASE statement, I would create a new table for the multipliers and then join them to the PackType table. If this is to be used in a single query or stored procedure then create it as an in-memory @Table; if you need to refer to in in a number of places, create it as a permanent table.
DECLARE @Multiplier AS TABLE
(
MType NVARCHAR(30)
MValue DECIMAL(10,2)
)
INSERT INTO @Multiplier (MType,MValue) VALUES ('Monthly', 25)
INSERT INTO @Multiplier (MType,MValue) VALUES ('Deposit', 35)
select FT.PackType,count(FT.PackType) as Records,sum(FT.price * M.MValue) as Value
from fun_test FT
join @Multiplier M ON M.MType = FT.PackType
group by FT.PackType
order by FT.PackType
This keeps the main query clean and makes maintenace much easier because all you need to do is edit the insert statements to modify records.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply