Previous Record Value

[blimbert]

Hello - I am stumped on how to return a field value from the previous record.

I have a really simple dataset - just a Billing Date and Sales values.
BillingDate Sales
1/1/2018 1
1/2/2018 2
1/3/2018 3
1/4/2018 4
1/5/2018 5
1/6/2018 6
1/7/2018 7
1/8/2018 8
1/9/2018 9
1/10/2018 10

Here is the SQL that I have:

SELECT *, 
(SELECT Top 1 Sales from tblSales as P WHERE P.BillingDate<BillingDate) as PriorDaySales
FROM tblSales

When I run this, it is not returning the expected value, just nulls for the PriorDaySales.



Any ideas why? What am I doing wrong?

[pbaldy]

For TOP to be reliable, you need an ORDER BY clause in the subquery.

[pbaldy]

Should have added this:

http://allenbrowne.com/subquery-01.html#AnotherRecord

[CJ_London]

your sub query isn't quite right and need to include an ORDER BY. Try

SELECT *, 
(SELECT Top 1 Sales from tblSales as P WHERE BillingDate<tblSales.BillingDate ORDER BY BillingDate Desc) as PriorDaySales
FROM tblSales

[blimbert]

Thank you so much!

I found this result from another comment you had made of similar nature. You referred me to http://allenbrowne.com/subquery-01.html#AnotherRecord, which showed me the way.

After changing the query to the following, it works correctly.
SELECT *,
(SELECT Top 1 P.Sales from tblSales as P WHERE P.BillingDate<tblSales.BillingDate ORDER BY P.BillingDate DESC) as PriorDaySales
FROM tblSales

BillingDate Sales PriorDaySales
1/1/2018 1
1/2/2018 2 1
1/3/2018 3 2
1/4/2018 4 3
1/5/2018 5 4
1/6/2018 6 5
1/7/2018 7 6
1/8/2018 8 7
1/9/2018 9 8
1/10/2018 10 9


Thanks!
Brian

[pbaldy]

Glad you got it sorted and welcome to the site Brian!

[blimbert]

Thanks Paul! QQ - in the subquery sort, do I sort using the main table BillingDate, or the duplicate table's billing date?

eg
"ORDER BY P.BillingDate DESC"
or
ORDER BY BillingDate DESC"

[pbaldy]

It may not matter, but I'd use the duplicate (alias).

[blimbert]

Sorry for the long running thread. Next question - say I wanted to bring the values from 2 fields from the prior record back. Do I need to add another field with another subquery, or is it possible somehow to bring back two fields from one subquery?

Brian

[pbaldy]

Not a strength of mine, but as far as I know a subquery can only return a single field, so you'd need a second subquery.

[CJ_London]

As Paul says, subqueries can only return one field.

I suspect you only showing a simplified example of what you want. Data provided implies you only make one sale per day, or you have multiple sales and the data you are showing is the summed amount per day. So before going down potential dead ends, please clarify what you have and what you are trying to achieve - what is the other field?, are dates consecutive or do you have gaps? and do you have a primary key would be a start.

Otherwise your sql would look like

SELECT *,
(SELECT Top 1 P.Sales from tblSales as P WHERE P.BillingDate<tblSales.BillingDate ORDER BY P.BillingDate DESC) as PriorDaySales
(SELECT Top 1 P.OtherField from tblSales as P WHERE P.BillingDate<tblSales.BillingDate ORDER BY P.BillingDate DESC) as PriorOtherField
FROM tblSales

[blimbert]

Thanks Ajax! Yes, you are correct this is a simplified example. I needed the values of two fields from the prior record to determine how to handle this record. I built the query with two subqueries exactly as you suggest, and it works really well.

Thanks Ajax and Paul!
Brian