How do I add a column of previos purchase date of each customer ?

Question

1.00/5 (1 vote)

See more:

I want the previous purchase date (the purchase done just before the p_date that is in the output) of each customer.

The output should look like this.

id merchant amount p_date last_purchase_date
101247 flip 617 05-05-2022 12-04-2022
101248 blinkit 332 25-05-2022 04-04-2022
101249 det 528 27-05-2022 04-04-2022

What I have tried:

query:
select *
from (
select * from last_pur
where p_date between '01-05-2022' and '31-05-2022'
)a
Left Join Last_pur as l on l.id=a.id and l.p_date<a.p_date

This query maps all the previous purchase dates. I just want one done just before the p_date in the output.

Posted 16-Jul-22 5:05am

Iaryan

Updated 16-Jul-22 7:39am

Add a Solution

Comments

0x01AA 16-Jul-22 11:16am

If you are looking for something which is just before a limit 'l' it needs to be a 'x' which is smaller than 'l'.
Something like pseudo SQL: 'SELECT TOP 1 FROM abc WHERE abc.TheColumn < 'l' ORDER BY abc.TheColumn DESC'

[Edit]
You can glue your SQL and that mentioned SQL together with 'UNION'. Not nice, but maybe helpful in your case.

[Edit]
The above means then adding a row more, rather than a column only ;)

Iaryan 16-Jul-22 11:27am

Thnak you. That helped.

0x01AA 16-Jul-22 11:29am

You are very welcome.

3 solutions

Solution 2

A little piece of advice...

NEVER use "SELECT *". ALWAYS spell out which columns you want, even if it's all of the columns in the table. With future updates in the code and queries, this will prevent strange errors from showing up in code that used to work fine up until you, or someone else, makes changes to the table columns.

Posted 16-Jul-22 6:38am

Dave Kreskowiak

Solution 3

In case you need something 'in between x and y' and the one just below x I see only that:

SELECT x, y, z FROM abc WHERE abc.x BETWEEN a AND b
UNION 
SELECT TOP 1 x, y, z FROM abc WHERE abc.x < a ORDER BY abc.x DESC
ORDER BY x

I hope it helps.

Posted 16-Jul-22 7:39am

0x01AA

Updated 16-Jul-22 7:40am

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard MacCutchan · Accepted Answer · 2022-07-16T05:24:00

Solution 1

Try one of these SQL SELECT TOP, LIMIT, FETCH FIRST ROWS ONLY, ROWNUM[^].

Posted 16-Jul-22 5:24am

Richard MacCutchan