cursor and age calculation (Network Steve Forum)

cursor and age calculation

select * INTO #temp
from (
select  1 as id,'1/1/2015' as actualdate, 1111 as B1trade,2222 b2trade, 23.56 as Net,Null as Age
UNION
select 2 as id,'1/2/2015' as actualdate, 1111 as B1trade,2222 b2trade,23.56 as Net, Null as Age
UNION
select 3 as id,'1/3/2015' as actualdate, 1111 as B1trade,2222 b2trade,0 as Net, Null as Age
UNION
select 4 as id,'1/4/2015' as actualdate, 1111 as B1trade,2222 b2trade,23.56 as Net, Null as Age
UNION
select 5 as id,'1/5/2015' as actualdate, 1111 as B1trade,2222 b2trade,23.56 as Net, Null as Age
)a

select * from #temp

Age is the new column added to the exisiting table, need to calculate the age based on NET value

My age calculation should start from 1/1/2015, there is a data int he table prior to 2015 as well but we dont need the age of that.

My requirement is whenever there

is anet value >1 or <0 then i should give the age as 1 and if the net is still not zero for the next day, the age should increase by 1 until i see the zero net. For the next NET value it should start with 1 again

ID AGE

1 1

2 2

3 0

4 1

5 2

unique key is actualdate + B1trade+b2trade

I am using a cursor to calculate , but i am not getting how assign the value for first record

Please help me

July 28th, 2015 3:55pm

Like this?

DECLARE @temp TABLE (id INT, actualDate DATE, b1trade VARCHAR(5), b2Trade VARCHAR(5), net DECIMAL(10,2), age INT)
INSERT INTO @temp
SELECT *
  FROM (
select  1 as id,'1/1/2015' as actualdate, 1111 as B1trade,2222 b2trade, 23.56 as Net,Null as Age
UNION
select 2 as id,'1/2/2015' as actualdate, 1111 as B1trade,2222 b2trade,23.56 as Net, Null as Age
UNION
select 3 as id,'1/3/2015' as actualdate, 1111 as B1trade,2222 b2trade,0 as Net, Null as Age
UNION
select 4 as id,'1/4/2015' as actualdate, 1111 as B1trade,2222 b2trade,23.56 as Net, Null as Age
UNION
select 5 as id,'1/5/2015' as actualdate, 1111 as B1trade,2222 b2trade,23.56 as Net, Null as Age
)a

;WITH rCTE AS (
select t.id, t.actualDate, t.b1Trade, t.b2Trade, t.net, CASE WHEN net = 0 THEN 0 ELSE 1 END AS age
  from @temp t
 WHERE net = 0 OR id = (SELECT MIN(id) FROM @temp WHERE b1Trade = t.b1Trade AND b2Trade = t.b2Trade)
UNION ALL
SELECT r.id, r.actualDate, a.b1Trade, a.b2Trade, r.net, a.age + 1 AS age
  FROM rCTE a
    INNER JOIN @temp r
	  ON a.id +1 = r.ID
	  AND r.net <> 0
)

SELECT *
  FROM rCTE
 ORDER BY id

A set based approach will almost always be better.

Free Windows Admin Tool Kit Click here and download it now

July 28th, 2015 4:04pm

select * INTO #temp
from (
select 1 as id,'1/1/2015' as actualdate, 1111 as B1trade,2222 b2trade, 23.56 as Net,Null as Age
UNION
select 2 as id,'1/2/2015' as actualdate, 1111 as B1trade,2222 b2trade,23.56 as Net, Null as Age
UNION
select 3 as id,'1/3/2015' as actualdate, 1111 as B1trade,2222 b2trade,0 as Net, Null as Age
UNION
select 4 as id,'1/4/2015' as actualdate, 1111 as B1trade,2222 b2trade,23.56 as Net, Null as Age
UNION
select 5 as id,'1/5/2015' as actualdate, 1111 as B1trade,2222 b2trade,23.56 as Net, Null as Age
)a

;with mycte as (
select id, actualdate, b1trade,b2trade,Net,
SUM(case when Net>1 or Net<0 Then 0 Else 1 End)Over(Partition by B1trade,b2trade Order by id ) grp
from #temp)

Select id, actualdate, b1trade,b2trade,Net,
SUM(case when Net>1 or Net<0 Then 1 Else 0 End)Over(Partition by B1trade,b2trade, grp Order by id ) age
from mycte

drop table #temp

Edited by Jingyang LiMicrosoft community contributor, Moderator 10 hours 48 minutes ago

July 28th, 2015 4:19pm

DECLARE @temp TABLE (id INT, actualDate DATE, b1trade VARCHAR(5), b2Trade VARCHAR(5), net DECIMAL(10,2), age INT)
INSERT INTO @temp
SELECT *
  FROM (
select  1 as id,'1/1/2015' as actualdate, 1111 as B1trade,2222 b2trade, 23.56 as Net,Null as Age
UNION
select 2 as id,'1/2/2015' as actualdate, 1111 as B1trade,2222 b2trade,23.56 as Net, Null as Age
UNION
select 3 as id,'1/3/2015' as actualdate, 1111 as B1trade,2222 b2trade,0 as Net, Null as Age
UNION
select 4 as id,'1/4/2015' as actualdate, 1111 as B1trade,2222 b2trade,23.56 as Net, Null as Age
UNION
select 5 as id,'1/5/2015' as actualdate, 1111 as B1trade,2222 b2trade,23.56 as Net, Null as Age
)a


;with cte as
(
 select id,actualdate,b1trade,b2Trade, case when net =0 then 0 else 1 end as age
 from @temp
)

SELECT id,actualdate,b1trade,b2Trade,
      case when age = 0 then 0 else age+lag(age,1,0) over (partition by b1Trade,b2Trade order by actualdate)end  age
  FROM CTE
 ORDER BY id

If SQL 2012 onwards.

Free Windows Admin Tool Kit Click here and download it now

July 28th, 2015 4:26pm

select * INTO #temp
from (
select 1 as id,'1/1/2015' as actualdate, 1111 as B1trade,2222 b2trade, 23.56 as Net,Null as Age
UNION
select 2 as id,'1/2/2015' as actualdate, 1111 as B1trade,2222 b2trade,23.56 as Net, Null as Age
UNION
select 3 as id,'1/3/2015' as actualdate, 1111 as B1trade,2222 b2trade,0 as Net, Null as Age
UNION
select 4 as id,'1/4/2015' as actualdate, 1111 as B1trade,2222 b2trade,23.56 as Net, Null as Age
UNION
select 5 as id,'1/5/2015' as actualdate, 1111 as B1trade,2222 b2trade,23.56 as Net, Null as Age
)a

;with mycte as (
select id, actualdate, b1trade,b2trade,Net,
SUM(case when Net>1 or Net<0 Then 0 Else 1 End)Over(Partition by B1trade,b2trade Order by id ) grp
from #temp)

Select id, actualdate, b1trade,b2trade,Net,
SUM(case when Net>1 or Net<0 Then 1 Else 0 End)Over(Partition by B1trade,b2trade, grp Order by id ) age
from mycte

drop table #temp

Jingyang,

I think that you have to order by actualdate not by id because the following case will not work:

select * INTO #temp
 from (
 select  1 as id,'1/1/2015' as actualdate, 1111 as B1trade,2222 b2trade, 23.56 as Net,Null as Age
 UNION
 select 2 as id,'1/2/2015' as actualdate, 1111 as B1trade,2222 b2trade,23.56 as Net, Null as Age
 UNION
 select 3 as id,'1/3/2015' as actualdate, 1111 as B1trade,2222 b2trade,0 as Net, Null as Age
 UNION
 select 4 as id,'1/5/2015' as actualdate, 1111 as B1trade,2222 b2trade,23.56 as Net, Null as Age
 UNION
 select 5 as id,'1/4/2015' as actualdate, 1111 as B1trade,2222 b2trade,23.56 as Net, Null as Age
 )a

5 is inserted before 4 January.

July 28th, 2015 4:35pm

Edited by Jingyang LiMicrosoft community contributor, Moderator Tuesday, July 28, 2015 8:19 PM

Free Windows Admin Tool Kit Click here and download it now

July 28th, 2015 8:16pm

I have to use only B1trade, B2trade and actualdate. ID not exists.

select * INTO #temp
from (
select  1 as id,'1/1/2015' as actualdate, 1111 as B1trade,2222 b2trade, 23.56 as Net,Null as Age, Null as agestart
UNION
select 2 as id,'1/2/2015' as actualdate, 1111 as B1trade,2222 b2trade,23.56 as Net, Null as Age,Null as agestart
UNION
select 3 as id,'1/3/2015' as actualdate, 1111 as B1trade,2222 b2trade,0 as Net, Null as Age,Null as agestart
UNION
select 4 as id,'1/4/2015' as actualdate, 1111 as B1trade,2222 b2trade,23.56 as Net, Null as Age,Null as agestart
UNION
select 5 as id,'1/5/2015' as actualdate, 1111 as B1trade,2222 b2trade,23.56 as Net, Null as Age,Null as agestart
)a

select * from #temp

I have to calculate the agestart as well.Agestart will be the actualdate when the break occurs and the same startdate should carry over until the break 0.

So age startdate will be like this, I am not getting how to carry the same date.

1/1/2015

NULL

1/4/2015

July 29th, 2015 5:47pm

You shouldn't include columns not actually in your dataset in example data, as it leads to confusion like this.

Here's an example that adds it's own ID column into the mix, which is based on actualdate.

DECLARE @temp TABLE (id INT, actualDate DATE, b1trade VARCHAR(5), b2Trade VARCHAR(5), net DECIMAL(10,2), age INT)
INSERT INTO @temp
SELECT *
  FROM (
select  1 as id,'1/1/2015' as actualdate, 1111 as B1trade,2222 b2trade, 23.56 as Net,Null as Age
UNION
select 2 as id,'1/2/2015' as actualdate, 1111 as B1trade,2222 b2trade,23.56 as Net, Null as Age
UNION
select 3 as id,'1/3/2015' as actualdate, 1111 as B1trade,2222 b2trade,0 as Net, Null as Age
UNION
select 4 as id,'1/4/2015' as actualdate, 1111 as B1trade,2222 b2trade,23.56 as Net, Null as Age
UNION
select 5 as id,'1/5/2015' as actualdate, 1111 as B1trade,2222 b2trade,23.56 as Net, Null as Age
)a

;WITH base AS(
SELECT ROW_NUMBER() OVER (ORDER BY actualdate) AS id, t.actualDate, t.b1Trade, t.b2Trade, t.net
  FROM @temp t
),rCTE AS (
select t.id, t.actualDate, t.b1Trade, t.b2Trade, t.net, CASE WHEN net = 0 THEN 0 ELSE 1 END AS age
  from base t
 WHERE net = 0 OR id = (SELECT MIN(id) FROM @temp WHERE b1Trade = t.b1Trade AND b2Trade = t.b2Trade)
UNION ALL
SELECT r.id, r.actualDate, a.b1Trade, a.b2Trade, r.net, a.age + 1 AS age
  FROM rCTE a
    INNER JOIN base r
	  ON a.id +1 = r.ID
	  AND r.net <> 0
)

SELECT *
  FROM rCTE
 ORDER BY id

Free Windows Admin Tool Kit Click here and download it now

July 29th, 2015 6:04pm

This topic is archived. No further replies will be accepted.