how to accomplish this? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

how to accomplish this?

Author

Topic

cojimarmiami
Starting Member

6 Posts

Posted - 2014-05-22 : 12:30:38

I have a table with the following data

SHCOMP CBLNAM SHDESC SHAMT SHTYPE

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"
Here is how my report looks

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-05-22 : 12:38:56

I'm not sure I follow. Given this set of data:

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

You want this output?

GROUP AMOUNT

NC 10

PI 20

PD 0

SR -45

SA 45

If that is the case, can you post sample data in a consumable format (create table & insert statements). And explain the logic for transforming the data into the expected result set (I.E what is a changed amount?).

Here are some other links that can be helpful for posting you data or asking future questions:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources