complex problem - 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 2008 Forums

Transact-SQL (2008)

complex problem

Author

Topic

inbs
Aged Yak Warrior

860 Posts

Posted - 2012-10-23 : 16:52:38

[code]
Date Product Customer Classify Quantity Cumulative
1.6 a 10 S 50 50
2.6 a 10 S 10 60
3.6 a 10 r -100 -40
4.6 a 10 S 20 80
5.6 a 10 r -5 75
6.6 a 10 r -90 -15
[/code]

i have table of sales products to customers.
in this example i had just one product and one customer.
i want to get Cumulative column:
this explain of the table:
in 1.6 the customer bought 50 units
in 2.6 the customer bought 10 units so i had 60 in Cumulative
in 3.6 the customer return -100 units so i had -40 in Cumulative
in 4.6 the customer bought more 20 units so i had 80 units (20+60)
in 5.6 the customer return -5 units so i had 75 units (80-5=75)
in 6.6 the customer return -90 more ,so i had 90-75=-15

how i built Cumulative column?

thanks

chadmat
The Chadinator

1974 Posts

Posted - 2012-10-23 : 16:56:22

I don't understand how you go from -40 to 80?

-Chad

inbs
Aged Yak Warrior

860 Posts

Posted - 2012-10-23 : 17:06:48

the customer return 100 units but i sell him just 60 units (50+10)
so i had -40.
then i bought 20 units but i sell 60 and not -40 so i need to put
60+20=80
and not
-40+20---wrong

now is it understood?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-23 : 18:56:16

Nope...Your rule seems to be confusing. I really think it should be -40 + 20

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

inbs
Aged Yak Warrior

860 Posts

Posted - 2012-10-23 : 21:39:54

no, this is a rule any suggestion?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-23 : 21:42:48

quote:
Originally posted by inbs

no, this is a rule any suggestion?

why for that one row alone there's an exception? why are other return row values as expected?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

inbs
Aged Yak Warrior

860 Posts

Posted - 2012-10-24 : 00:30:11

i want to recognize all the rows that customers return quantity that bigger than what they bought,

the customer return 100 units but bought just 60 so, i put -40 in that row.

see in the new row,
in 7.6 the customer bought 10 units then the cumulative is 10+75=85
the rule is
if ABS(Quantity) of 3.6 when classif is r > cumulative of 2.6 then
than put 60
the correct result is to put 0 or -1 any flag know this row in 3.6

in 5.6 the rule is ABS(-5) is not > cumulative of 4.6 is 80 than it add -5+80=75

in 6.6 ABS(-90)>75 when classify is r than it put 0 or -1..

the correct table


Date     Product  Customer  Classify Quantity Cumulative
1.6         a      10         S        50       50
2.6         a      10         S        10       60
3.6         a      10         r       -100      0/-1
4.6         a      10         S        20       80
5.6         a      10         r        -5       75
6.6         a      10         r        -90      0/-1
7.6         a      10         s        10       85

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-24 : 01:59:47

this makses more sense. one more qn. Is date a datetime field? seeing the values i dont guess so.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

inbs
Aged Yak Warrior

860 Posts

Posted - 2012-10-24 : 04:02:45

yes

thanks visakh16

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-24 : 09:28:22

how would a datetime field has values like 1.6 etc. Are you sure on this?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2012-10-26 : 08:03:27

inbs, did you have a solution?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources