CODEWITHSUNDEEP
sql-serversql-server-2008stored-procedures
A.Goutam
A.Goutam

Reputation: 3494

calculate value in new column

Below are my sample table data. There is one order number and the table is sorted by column ITEM. Column BREAK_M only is a running count each time BREAKOUT is True. You can see that other rows for BREAK_MCT show up as NULL. I want a query like the column LIKETHIS where the BREAK_M repeats itself until the next value begins and repeats itself.

ORDERNO      BREAKOUT  ITEM_ID   BREAK_M    LIKETHIS

2411       True       9290       1              1

2411       False      9291      NULL           1

2411       False      9292      NULL           1

2411       False      9293      NULL           1    

2411       True       9300      2              2

2411      False      9301      NULL           2

2411       False      9302     NULL           2

2411       False      9303     NULL           2

Thanks for your help in advance.

Upvotes: 0

Views: 54

Answers (3)

DancingFool
DancingFool

Reputation: 1267

I'm sure there is a better way to do this, but something like this should work

select *, 
    (Select top 1 BREAK_M 
            from sampleTable 
            where Item_ID <= T.Item_ID 
                and Break_M is not null 
            order by item_ID desc
    ) as LIKETHIS
from sampleTable T
order by item_ID

EDIT: after looking at fiddle in comment

Try this one http://sqlfiddle.com/#!6/8974b/25. The problems were

(1) your fiddle does not have the same columns as your question above, I had to change Break_M to Count. (2) Your nulls in the fiddle are not really nulls, they are a string that says 'NULL' which is not the same thing. Depending on what your real database has you may need (is not null) for real nulls, or '<> 'NULL' if they are not. It may be better to ignore this column and use Breakout = 1 (true) instead, like this

select *, 
    (Select top 1 BREAK_M  -- OR COUNT, WHATEVER THE REAL COLUMN NAME IS
            from BREAK4 
            where Item_ID <= T.Item_ID 
                and BREAK_OUT = 1
            order by item_ID desc
    ) as LIKETHIS
from BREAK4 T
ORDER BY ITEM_ID

Upvotes: 0

KumarHarsh
KumarHarsh

Reputation: 5094

Try this,

create table #t(ORDERNO int,BREAKOUT varchar(10),ITEM_ID int,BREAK_M int, LIKETHIS int)
insert into #t VALUES
(2411,'True',9290,  1     ,null)
,(2411,'False',9291, NULL  ,null)
,(2411,'False',9292, NULL  ,null)
,(2411,'False',9293, NULL  ,null)   
,(2411,'True',9300, 2     ,null)
,(2411,'False',9301, NULL  ,null)
,(2411,'False',9302,NULL   ,null)
,(2411,'False',9303,NULL   ,null)

select ORDERNO ,BREAKOUT ,ITEM_ID ,t.BREAK_M 
, ca.BREAK_M LIKETHIS  from #t t

outer apply(select top 1 t1.BREAK_M 
from #t t1 where t1.ITEM_ID<=t.ITEM_ID and  t1.BREAK_M is not NULL
 order by t1.ITEM_ID desc )ca

drop table #t

Upvotes: 0

Nitesh Kumar
Nitesh Kumar

Reputation: 1774

You can try this query to get the desired result.

SELECT ORDERNO, BREAKOUT, ITEM_ID, BREAK_M, 
    (SELECT MAX(BREAK_M)
            FROM SampleTable 
            WHERE ITEM_ID <= ST.ITEM_ID 
    ) AS LIKETHIS
FROM SampleTable AS ST

Upvotes: 1

Related Questions