8 Replies - 499 Views - Last Post: 16 August 2018 - 02:07 AM

#1 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6823
  • View blog
  • Posts: 28,272
  • Joined: 12-December 12

inserting while grabbing value from previous row

Posted 15 August 2018 - 07:33 AM

I have a table, let's call it tblHistory:

id int, [date] datetime2, price float, recommendation int

Here is some sample data:

	(1, '2018-07-20', 10, 1),
	(1, '2018-07-23', 10, 2),
	(1, '2018-07-24', 10, 3),
	(1, '2018-08-20', 10, 4),
	(1, '2018-08-21', 10, 5),
	(1, '2018-08-23', 10, 6),
	(3, '2018-07-20', 30, 1),
	(3, '2018-07-21', 30, 2),
	(3, '2018-07-22', 30, 3),
	(3, '2018-07-23', 30, 4),
	(3, '2018-07-27', 30, 5),
	(3, '2018-08-02', 30, 6),
	(2, '2018-07-20', 10, 1),
	(2, '2018-07-21', 10, 2),
	(2, '2018-07-22', 20, 3),
	(2, '2018-07-23', 20, 4),
	(2, '2018-07-27', 20, 5),
	(2, '2018-08-02', 20, 6),
	(4, '2018-08-02', 40, 1)

I have some new prices to insert, for example,

	(1, '2018-08-24', 100),
	(1, '2018-08-26', 101),
	(2, '2018-08-04', 200),
	(3, '2018-08-01', 300),
	(4, '2018-08-03', 400)


My challenge is to insert these new records (prices) but to also pull down - to copy - the recommendation from the row above for that company. That is, to get the latest recommendation for that company, for the latest date which is before the date that I am inserting.

For example, for company 1 and '2018-08-24' the recommendation just before this date is 6. This value of 6 should also be copied down for the new date of '2018-08-26'.

I am looking, please for some hints on how this could be successfully approached?

So far, I have a CTE (common table expression):

 ;with cteRowNumber as (
    select companyid, [Date], recommendation As LatestRec, price,
           row_number() over(partition by companyid order by [Date] desc) as RowNum
        from tblHistory
)

This gives me the most recent recommendation for each company, which I can then successfully use and insert into new rows. But I cannot use this alone because it is making the incorrect assumption that the records I will insert will always be more recent than anything already in the table.

Do you think I can expand the CTE somehow to get the latest recommendation for each date that I will be inserting? Or perhaps I should abandon the CTE and consider something like the Lag function?

Is This A Good Question/Topic? 0
  • +

Replies To: inserting while grabbing value from previous row

#2 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15356
  • View blog
  • Posts: 61,566
  • Joined: 12-June 08

Re: inserting while grabbing value from previous row

Posted 15 August 2018 - 07:36 AM

My first gut reaction would to do this in two parts.

First - insert the data, but the recommendations are 'null'.
Second - for each row with a recommendation null (we know those are the newly inserted ones), find the previous row's value and update the null to the recommendation value.

Sure.. if 'null' isn't an option maybe make it -1 or what ever to know that it is a new row.
Was This Post Helpful? 1
  • +
  • -

#3 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6823
  • View blog
  • Posts: 28,272
  • Joined: 12-December 12

Re: inserting while grabbing value from previous row

Posted 15 August 2018 - 08:04 AM

Great minds think alike! That is what I've done with other challenges. [Other people seem to be able to (or delight in) writing v. complex statements to do things in one go. I need to break it down into separate steps, with helpful comments(!), even though the process can take a long(er) time.]

I don't think I can use NULL, it can already be present in the data, I think I can set a minus price instead. (Sorry, my sample data didn't indicate this NULL possibility.)

I'll pursue this a little further, thank you, while still encouraging contributions ;) about how to grab the previous recommendation for a negative price.
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15356
  • View blog
  • Posts: 61,566
  • Joined: 12-June 08

Re: inserting while grabbing value from previous row

Posted 15 August 2018 - 08:12 AM

Contributing like a mo'fo; that's me.
Was This Post Helpful? 0
  • +
  • -

#5 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6823
  • View blog
  • Posts: 28,272
  • Joined: 12-December 12

Re: inserting while grabbing value from previous row

Posted 15 August 2018 - 08:46 AM

I almost got it kinda working.

update #tempOrig set price = -price, recommendation = PREV.prev_rec
    from #tempOrig ORIG inner join 
        (select companyid, [date], Lag(recommendation) OVER (PARTITION BY companyid ORDER BY [date]) prev_rec 
            from #tempOrig) PREV
        on ORIG.companyid = PREV.companyid and ORIG.date = PREV.date 
        where ORIG.price < 0


Not quite though. If I have two new prices in succession then, initially, the recommendations are not entered for these rows, so are null. The recommendation for the first new price correctly updates to the carried forward recommendation, but the second remains null. That is, it isn't carrying forward more than once(?).
Was This Post Helpful? 0
  • +
  • -

#6 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15356
  • View blog
  • Posts: 61,566
  • Joined: 12-June 08

Re: inserting while grabbing value from previous row

Posted 15 August 2018 - 09:18 AM

I am still thinking this needs to be a two part'er. Insert then assess.

crea te table #foo (id int, [date] datetime2, price float, recommendation int)

inse rt into #foo(id, [date], price, recommendation)
values 
(1, '2018-07-20', 10, 1),
(1, '2018-07-23', 10, 2),
(1, '2018-07-24', 10, 3),
(1, '2018-08-20', 10, 4),
(1, '2018-08-21', 10, 5),
(1, '2018-08-23', 10, 6),
(3, '2018-07-20', 30, 1),
(2, '2018-07-27', 20, 5),
(2, '2018-08-02', 20, 6),
(4, '2018-08-02', 40, 1)

 

ins ert into #foo(id, [date], price, recommendation)
values 
(1, '2018-08-24', 100, -1),
(1, '2018-08-26', 101, -1),
(2, '2018-08-04', 200, -1),
(3, '2018-08-01', 300, -1),
(4, '2018-08-03', 400, -1),
(5, '2018-08-07', 400, -1)

select *
from #foo
order by id

select a.*, b.recommendation
from #foo a
left join ( -- left join if there is no prevous row that's a null and you can handle as needed
	-- get the recommended row,  
	select  z.*
	from #foo z
	join(
		-- get the max date and ID
		select id, max(date) as max_date 
		from #foo a
		where recommendation != -1
		group by id
		) y on z.id = y.id and z.date = y.max_date
 ) b on a.id = b.id
where a.recommendation = -1 -- only with -1 recommendations

dr op table #foo



id          date                        price                  recommendation
----------- --------------------------- ---------------------- --------------
1           2018-07-20 00:00:00.0000000 10                     1
1           2018-07-23 00:00:00.0000000 10                     2
1           2018-07-24 00:00:00.0000000 10                     3
1           2018-08-20 00:00:00.0000000 10                     4
1           2018-08-21 00:00:00.0000000 10                     5
1           2018-08-23 00:00:00.0000000 10                     6
1           2018-08-24 00:00:00.0000000 100                    -1
1           2018-08-26 00:00:00.0000000 101                    -1
2           2018-08-04 00:00:00.0000000 200                    -1
2           2018-07-27 00:00:00.0000000 20                     5
2           2018-08-02 00:00:00.0000000 20                     6
3           2018-08-01 00:00:00.0000000 300                    -1
3           2018-07-20 00:00:00.0000000 30                     1
4           2018-08-03 00:00:00.0000000 400                    -1
4           2018-08-02 00:00:00.0000000 40                     1
5           2018-08-07 00:00:00.0000000 400                    -1

 
id          date                        price                  recommendation recommendation
----------- --------------------------- ---------------------- -------------- --------------
1           2018-08-24 00:00:00.0000000 100                    -1             6
1           2018-08-26 00:00:00.0000000 101                    -1             6
2           2018-08-04 00:00:00.0000000 200                    -1             6
3           2018-08-01 00:00:00.0000000 300                    -1             1
4           2018-08-03 00:00:00.0000000 400                    -1             1
5           2018-08-07 00:00:00.0000000 400                    -1             NULL

Was This Post Helpful? 1
  • +
  • -

#7 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6823
  • View blog
  • Posts: 28,272
  • Joined: 12-December 12

Re: inserting while grabbing value from previous row

Posted 16 August 2018 - 12:57 AM

Thank you very much. I shall work on this further.
Was This Post Helpful? 0
  • +
  • -

#8 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6823
  • View blog
  • Posts: 28,272
  • Joined: 12-December 12

Re: inserting while grabbing value from previous row

Posted 16 August 2018 - 01:09 AM

This is progress ;) but the original issue crops up, that I cannot assume the new data is beyond the latest date in the table (for each company).

If I add an earlier record (1, '2018-07-26', 100, -1), the recommendation for this will be 6 not 3. This is because the subquery only gives one maximum for each company, the latest one.

For each new row inserted I need to carry forward the recommendation that is just prior to the inserted date.

[I also cannot use -1; instead, I am temporarily negating the prices, but this is a minor difference.]
Was This Post Helpful? 0
  • +
  • -

#9 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6823
  • View blog
  • Posts: 28,272
  • Joined: 12-December 12

Re: inserting while grabbing value from previous row

Posted 16 August 2018 - 02:07 AM

I think I've sussed it using CROSS APPLY.

First, insert the new prices as negatives:

-- put them in the main table, negating the prices
insert into #tempOrig (companyid, [date], price)
    select companyid, [date], -price from #tempPrices


Then

update #tempOrig set price = -price, recommendation = CA_DATA.recommendation
    from #tempOrig ORIG
    cross apply (select top 1 recommendation 
        from #tempOrig  CA where CA.companyid = ORIG.companyid 
            and CA.date < ORIG.date and ORIG.price < 0 
            and (CA.price is null or CA.price >= 0)
            -- that is, ignore existing negative price rows when trying to
            -- obtain the most recent recommendation; the price might be null
            -- or non-negative
            order by CA.date desc) CA_DATA

The cross apply will, for each negative price, find the first earlier dated row that has a non-negative or null price, and grab its recommendation. Then reverse the negation and insert the found recommendation.

There is a final correction: if there were no existing records then the inserted price will remain negative:

update #tempOrig set price = -price where price < 0


Complicated stuff!
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1