Making SQL little bit more clever

1

Hi,

I have below SQL statement and I would like to use some variables in order to prevent repeating select statements. I do not know how to do that, but I remember seeing something similar. So, hoping that is possible.

update saydetay 
set
sayilan = coalesce((select sum(miktar) from sayokutmalar where id = 'D95D796C-1BB1-4420-9CB7-99CEA30CE1A6' and islemturu = 'G' and iptal = 0 and sayokutmalar.urunkodu = saydetay.urunkodu), 0)
,
satilan = coalesce((select sum(miktar) from sayokutmalar where id = 'D95D796C-1BB1-4420-9CB7-99CEA30CE1A6' and islemturu = 'C' and iptal = 0 and sayokutmalar.urunkodu = saydetay.urunkodu), 0)
,
sayimmiktar = coalesce((select sum(miktar) from sayokutmalar where id = 'D95D796C-1BB1-4420-9CB7-99CEA30CE1A6' and islemturu = 'G' and iptal = 0 and sayokutmalar.urunkodu = saydetay.urunkodu), 0) - coalesce((select sum(miktar) from sayokutmalar where id = 'D95D796C-1BB1-4420-9CB7-99CEA30CE1A6' and islemturu = 'C' and iptal = 0 and sayokutmalar.urunkodu = saydetay.urunkodu), 0)
,
fark = coalesce((select sum(miktar) from sayokutmalar where id = 'D95D796C-1BB1-4420-9CB7-99CEA30CE1A6' and islemturu = 'G' and iptal = 0 and sayokutmalar.urunkodu = saydetay.urunkodu), 0) - coalesce((select sum(miktar) from sayokutmalar where id = 'D95D796C-1BB1-4420-9CB7-99CEA30CE1A6' and islemturu = 'C' and iptal = 0 and sayokutmalar.urunkodu = saydetay.urunkodu), 0) - miktar

Thanks.

2

You actually don't need any variables:

;with cte as (select urunkodu
, coalesce(sum(case islemturu when 'C' then miktar else 0 end),0) c_sum
, coalesce(sum(case islemturu when 'G' then miktar else 0 end),0) g_sum
from sayokutmalar
where id = 'D95D796C-1BB1-4420-9CB7-99CEA30CE1A6' and iptal = 0 and islemturu in('G','C')
group by urunkodu)
update s set 
sayilan=c.g_sum,
satilan=c.c_sum,
sayimmiktar=c.g_sum-c.c_sum,
fark=c.g_sum-c.c_sum-miktar
from cte c
inner join saydetay s on c.urunkodu=s.urunkodu
1 Like
3

Hello Robert,

Thank you for the example. It works as expected.

-Ertan

4

Hi Again,

It seems that I have made a logical mistake. Such as;

Solution provided above only modifies fields which are selected from sayokutmalar table. With following information in that table;

autoinc              id                                   barkod                                   urunkodu                                 islemturu miktar                 grupdisi tanimsiz iptal olusturan            olusturmazamani         degistiren           degistirmezamani        silen                silinmezamani
-------------------- ------------------------------------ ---------------------------------------- ---------------------------------------- --------- ---------------------- -------- -------- ----- -------------------- ----------------------- -------------------- ----------------------- -------------------- -----------------------
7                    D95D796C-1BB1-4420-9CB7-99CEA30CE1A6 0-0                                      0-0                                      G         10                     NULL     NULL     1     NULL                 NULL                    NULL                 NULL                    1                    2017-03-03 09:41:59.723
8                    D95D796C-1BB1-4420-9CB7-99CEA30CE1A6 xyz                                      abc                                      G         2                      NULL     NULL     0     NULL                 NULL                    NULL                 NULL                    NULL                 NULL
9                    D95D796C-1BB1-4420-9CB7-99CEA30CE1A6 2-2                                      2-2                                      G         1                      NULL     NULL     0     NULL                 NULL                    NULL                 NULL                    NULL                 NULL
10                   D95D796C-1BB1-4420-9CB7-99CEA30CE1A6 1075                                     1075                                     G         2                      NULL     NULL     0     NULL                 NULL                    NULL                 NULL                    NULL                 NULL

(4 row(s) affected)

Provided SQL doesn't select first row where iptal = 1

Logic is;

  • Newly inserted records will have iptal = 0.
  • User may decide not to include a row in calculation and he shall set iptal = 1

So, following scenario results wrong calculation for me.
1- Run update SQL when all records iptal = 0
2- Change a unique single row and set iptal = 1 (at this point sayokutmalar will have exactly above records in it)
3- Run update SQL again and barcode "0-0" i table saydetay won't have right numbers of calculations because it is not selected from sayokutmalar.

Does this put me to where I started at the first place? To select and update everything from sayokutmalar?

Thanks.

5

Some minor changes seem to fix it:

;with cte as (select urunkodu
, coalesce(sum(case islemturu when 'C' then miktar else 0 end),0) c_sum
, coalesce(sum(case islemturu when 'G' then miktar else 0 end),0) g_sum
from #sayokutmalar
where id = 'D95D796C-1BB1-4420-9CB7-99CEA30CE1A6' and iptal = 0 and islemturu in('G','C')
group by urunkodu)
update s set 
sayilan=coalesce(c.g_sum,0),
satilan=coalesce(c.c_sum,0),
sayimmiktar=coalesce(c.g_sum-c.c_sum,0),
fark=coalesce(c.g_sum-c.c_sum,0)-miktar
from cte c
right join #saydetay s on c.urunkodu=s.urunkodu

I can't say for certain that this covers all scenarios as I'm working with sample data that I generated myself. If you find it still has errors you'll need to post data from both tables that illustratre the problem.