May 22, 2014 at 10:32 am
I have a table with the following data
SHCOMP CBLNAM SHDESC SHAMT SHTYPE NOTES GROUP
123 cust1 desc1 45 A
123 cust1 desc1 -45 D
121 cust2 desc2 10 T
121 cust2 desc3 -10 F
121 cust2 desc3 10 A
234 cust3 desc4 60 T
234 cust3 desc4 -60 F
234 cust3 desc5 30 A
235 cust3 desc5 30 A
235 cust4 desc6 -50 F
I have a report on Crystal where I added NOTES and GROUP columns
I created those columns using this formula in Crystal.
if {CUST.CSTRDT} >= tonumber(totext(({@firstday}-14),'yyyyMMdd')) and
{CUST.CSTRDT} <= tonumber(totext(({@lastday}-7),'yyyyMMdd')) and {SHST.SHTYPE} = "A" then "New Customer" else
if ({CUST.CSTRDT} <= tonumber(totext(({@firstday}-14),'yyyyMMdd')) or
{CUST.CSTRDT} >= tonumber(totext(({@lastday}-7),'yyyyMMdd'))) and {SHST.SHTYPE} = "A" then "Services Added" else
if {SHST.SHTYPE} = "D" then "Services Removed" else
if {@CUST#} = Previous({@CUST#}) and {SHST.SHDESC} = Previous({SHST.SHDESC}) and {SHST.SHAMT} > Previous({SHST.SHAMT}) then "Price Increase" else
if {@CUST#} = Previous ({@CUST#}) and {SHST.SHDESC} = Previous ({SHST.SHDESC}) and {SHST.SHAMT} < Previous ({SHST.SHAMT}) then "Price Decrease"
SHCOMP CBLNAM SHDESC SHAMT SHTYPE NOTES GROUP
123 cust1 desc1 45 A services added SA
123 cust1 desc12 -45 D services removed SR
121 cust2 desc2 10 T
121 cust2 desc2 -10 F price decrease PD
121 cust2 desc2 10 A new customer NC
234 cust3 desc4 -60 F
234 cust3 desc4 60 T price increase PI
235 cust3 desc5 30 A
235 cust4 desc6 -50 F
235 cust4 desc6 70 T price increase PI
what I need to create is an stored procedure that save in a temp table GROUP column and also another column that save the changed amount. For example
GROUP AMOUNT
NC 10
PI 20
PD 0 (if previous customer, service and amount are the same does not count because will be 0 balance)
SR -45
SA 45
May 22, 2014 at 2:12 pm
Please include some DDL next to so as to get a faster response (note the link in my signature for getting help here).
That said, I think this is what you are looking for: (note my comments)
-- first let's prepare some ddl so we know what we're working with
IF OBJECT_ID('tempdb..#yourtable') IS NOT NULL DROP TABLE #yourtable;
IF OBJECT_ID('tempdb..#groupSummary') IS NOT NULL DROP TABLE #groupSummary;
-- the table you are currently working with
CREATE TABLE #yourtable
(
SHCOMP int not null,
CBLNAM varchar(10) not null,
SHDESC varchar(10) not null,
SHAMT int not null,
SHTYPE char(1) not null,
NOTES varchar(1000) null,
[GROUP] varchar(5) null
);
-- the temp table that will store your "Group" results
CREATE TABLE #groupSummary
(
SH_group varchar(20) not null,
SH_amt int not null
)
GO
-- add values
INSERT #yourtable
SELECT *
FROM
(values
(123, 'cust1', 'desc1', 45, 'A', 'services added', 'SA'),
(123, 'cust1', 'desc1', -45, 'D', 'services removed', 'SR'),
(121, 'cust2', 'desc2', 10, 'T', NULL, NULL),
(121, 'cust2', 'desc3', -10, 'F', 'price decrease', 'PD'),
(121, 'cust2', 'desc3', 10, 'A', 'new customer', 'NC'),
(234, 'cust3', 'desc4', 60, 'T', NULL, NULL),
(234, 'cust3', 'desc4', -60, 'F', 'price increase', 'PI'),
(234, 'cust3', 'desc5', 30, 'A', NULL, NULL),
(235, 'cust4', 'desc6', -50, 'F', 'price increase', 'PI'),
(235, 'cust4', 'desc6', 70, 'T', 'price increase', 'PI')
) x (SHCOMP, CBLNAM, SHDESC, SHAMT, SHTYPE, NOTES, [GROUP]);
-- reveiew the results, make sure everything was entered correctly
--SELECT * FROM #yourtable;
-- solution
INSERT #groupSummary
SELECTcoalesce([GROUP],'<unknown>'), -- SH_Group
sum(SHAMT) -- SH_Amount
FROM #yourtable
GROUP BY [GROUP];
SELECT * FROM #groupSummary;
Edit: Added a couple additional comments to the solution
-- Itzik Ben-Gan 2001
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply