replacement of values by logical condition by groups in sql

1105 views sql
-2

here my data

   shop_code product_id                doc_date ship_count mark_1 outputer y
1   00664???      11628 2015-01-03 00:00:00.000         12      1        8 1
2   00664???      11628 2015-01-05 00:00:00.000          7      1        8 1
3   00664???      11628 2015-01-06 00:00:00.000         24      0        8 1
4   00664???      11628 2015-01-07 00:00:00.000         18      1        8 1
5   00664???      11628 2015-01-08 00:00:00.000         12      1        8 1
6   00664???      11628 2015-01-09 00:00:00.000         18      0        8 1
7   00664???      11628 2015-01-10 00:00:00.000          6      0        6 1
8   00664???      11628 2015-01-11 00:00:00.000          6      1        6 1
9   00664???      11628 2015-01-12 00:00:00.000          6      1        6 1
10  00664???      11628 2015-01-13 00:00:00.000         18      1       12 0
11  00664???      11628 2015-01-14 00:00:00.000          6      1        6 0
12  00664???      11628 2015-01-15 00:00:00.000         18      1       12 0
13  00664???      11628 2015-01-16 00:00:00.000         12      1       12 1
14  00664???      11628 2015-01-17 00:00:00.000         18      1       12 1
15  00664???      11628 2015-01-18 00:00:00.000         12      1       12 1
16  00664???      11628 2015-01-19 00:00:00.000         10      1       10 0
17  00664???      11628 2015-01-20 00:00:00.000         24      1       12 0
18  00664???      11628 2015-01-21 00:00:00.000          6      1        6 0
19  00664???      11628 2015-01-24 00:00:00.000          6      1        6 0
20  00664???      11628 2015-01-25 00:00:00.000          6      0        6 0
21  00664???      11628 2015-01-26 00:00:00.000         10      0       10 1
22  00664???      11628 2015-01-27 00:00:00.000          6      1        6 0
23  00664???      11628 2015-01-28 00:00:00.000         10      1       10 0
24  00664???      11628 2015-01-29 00:00:00.000         70      0       12 1
25  00664???      11628 2015-01-30 00:00:00.000        100      1       12 1

similar question i have asked for R and got working solution, but now i want do it using sql

I need observe such a condition: if y = 1 and mark1 = 1, then the outputer by mark1=1 must be replaced by the first value that goes for y = 0 and mark1 = 1 in the outputer variable.

if the first value that goes for Y = 0 and mark1 = 1 in the outputer is more than the ship_count, then in outputer left the actual value of ship_count

Zero category of mark1 for outputer, we don't touch.

This operation must be done by group ship_code+product_id

So the desired output

shop_code product_id                doc_date ship_count mark_1 outputer   y
1   00664???      11628 2015-01-03 00:00:00.000        12      1       *12 1
2   00664???      11628 2015-01-05 00:00:00.000          7      1        *7 1
3   00664???      11628 2015-01-06 00:00:00.000         24      0       24 1
4   00664???      11628 2015-01-07 00:00:00.000         18      1       *12 1
5   00664???      11628 2015-01-08 00:00:00.000         12      1       *12 1
6   00664???      11628 2015-01-09 00:00:00.000         18      0       18 1
7   00664???      11628 2015-01-10 00:00:00.000          6      0        6 1
8   00664???      11628 2015-01-11 00:00:00.000          6      1        6 1
9   00664???      11628 2015-01-12 00:00:00.000          6      1        6 1
10  00664???      11628 2015-01-13 00:00:00.000         18      1       *12 0
11  00664???      11628 2015-01-14 00:00:00.000          6      1        6 0
12  00664???      11628 2015-01-15 00:00:00.000         18      1       12 0
13  00664???      11628 2015-01-16 00:00:00.000         12      1       *10 1
14  00664???      11628 2015-01-17 00:00:00.000         18      1       *10 1
15  00664???      11628 2015-01-18 00:00:00.000         12      1       *10 1
16  00664???      11628 2015-01-19 00:00:00.000         10      1       10 0
17  00664???      11628 2015-01-20 00:00:00.000         24      1       12 0
18  00664???      11628 2015-01-21 00:00:00.000          6      1        6 0
19  00664???      11628 2015-01-24 00:00:00.000          6      1        6 0
20  00664???      11628 2015-01-25 00:00:00.000          6      0        6 0
21  00664???      11628 2015-01-26 00:00:00.000         10      0       10 1
22  00664???      11628 2015-01-27 00:00:00.000          6      1        6 1
23  00664???      11628 2015-01-28 00:00:00.000         20      1       *12 0
24  00664???      11628 2015-01-29 00:00:00.000         70      1       12 0
25  00664???      11628 2015-01-30 00:00:00.000        100      1       12 1

answered question

1 Answer

4

Good evening,

You should use a case statement to do your job.

For finding the first value for the describing clauses , use a subquery in which you keep the order that you wish(order by) and select the top 1 value.

Give a try and if you face some issues ask again.

posted this

Have an answer?

JD

Please login first before posting an answer.