Need a SQL Script for Compare the Row above to that Row

[former_member1048438]

Hi ,

Looking for some Script here for resolving my issue.

Actual scenario is... A.B,C & Posting date should sort ascending  and to find the Expiration date change.

See the first two ROWs of "C" Column having 1470328PC and Posting Date 12.12.2014 and Expiration Date is 31.05.2016.

and go to the next "C" means 3rd row 1463127PC then the Expiration date of the "C" is 30.04.2016.

here when A,B is constant and C is changing then should compare the Expiration date to the previous Expiration date of "C" column.

Example...  the Expiration Date of 1463127PC is less than 1470328PC's Expiration date.

then need to make that row highlight.

can any one give some pseudo logic for the above scenario.

Thanks in advance,

Thanks,

Gundala

[henrique_pinto]

As mentioned, look for the LAG window function:

Window Functions - SAP HANA SQL and System Views Reference - SAP Library

It allows you to access the value of your columns in diff rows.

[former_member1048438]

Hi Pinto,

Thank you so much for the logic.

Thanks,

Gundala

[lbreddemann]

Gundala,

without the SQL commands to create the table and insert your demo data I am not going to write a SQL that would do what you want.

However, you should check the SQL reference and educate yourself about WINDOW functions.

- Lars

[former_member1048438]

Lars,

Thanks for your quick reply.

Actually I am working on other reports, meanwhile i just looked out for the SQL script.

Thanks,

Gundala

[lbreddemann]

Well, if you're too busy to provide the data, then better not post your question here.

This is not your "gimme my homework"-one-stop-shop.

[former_member1048438]

Lars,

If you need the Test data, I am happy to provide that.

I thought you are busy so that i said thanks.

Sorry if I misunderstood you.

Here is the test data.

A	B	C	Posting Date	Posting Time	Expiration Date
12599	UP02	1470328PC	12.12.2014	10:45:52	31.05.2016
12599	UP02	1470328PC	12.12.2014	15:29:09	31.05.2016
12599	UP02	1463127PC	15.12.2014	1:48:05	30.04.2016
12599	UP02	1489999PC	15.12.2014	14:02:29	31.08.2017
12599	UP02	1489999PC	15.12.2014	14:19:57	31.08.2017
12599	UP02	1484206PCC	17.12.2014	15:52:55	31.08.2017
12599	UP02	1463127PC	22.12.2014	15:38:01	30.04.2016
12599	UP02	1463127PC	23.12.2014	7:39:00	30.04.2016
12599	UP02	1463127PC	23.12.2014	8:05:11	30.04.2016
12599	UP02	1484206PCC	23.12.2014	12:21:53	31.08.2017
12599	UP02	1463127PC	27.12.2014	11:38:31	30.04.2016
12599	UP01	1489999PCA	17.12.2014	12:21:01	31.08.2017
12599	UP01	1447141PCG	20.01.2015	17:46:34	31.01.2016
12599	UP01	1489999PCA	28.01.2015	16:19:54	31.08.2017
15001	UP02	D1400074	09.12.2014	16:17:13	31.01.2017
15001	UP02	D1400074	09.12.2014	16:24:57	31.01.2017
15001	UP02	H1400127	12.12.2014	15:33:35	30.04.2017
15001	UP02	H1400123	15.01.2015	12:45:38	31.03.2017
15001	UP02	H1400127	15.01.2015	12:45:38	30.04.2017
15001	UP02	H1400123	23.12.2014	11:12:32	31.03.2017
15001	UP02	H1400127	23.12.2014	11:12:32	30.04.2017
15001	UP02	D1400074	23.12.2014	12:21:53	31.01.2017
15001	UP02	F1400206	23.12.2014	12:21:53	28.02.2017
15001	UP01	C1400190	09.12.2014	16:42:41	30.11.2016
15001	UP01	C1400190	27.01.2015	5:26:33	30.11.2016
15003	UP02	D1400185	15.01.2015	12:45:38	31.01.2017
15003	UP02	D1400185	23.12.2014	11:12:32	31.01.2017
15003	UP02	H1400124	23.12.2014	11:12:32	30.04.2017
15101	UP02	D1400187	23.12.2014	11:12:32	31.01.2017
15101	UP02	J1400067	23.12.2014	11:12:32	31.05.2017
15101	UP01	F1400002	27.01.2015	5:26:33	31.03.2017

Thanks,

Gundala

[lbreddemann]

So what is anybody supposed to do with this?

How do you think this works?

You just drop your requirement and some arbitrary HTML table and we do the work for you?

As this is a database question, use database tools and commands.

That would be:

CREATE TABLE...

INSERT INTO...

SELECT ...

If you're unable or unwilling to do the very least so that others can reasonably work with your problem, then you'd better just keep it to yourself.

As I already pointed you to the relevant section of the documentation you're now good to proceed.

[former_member1048438]

Hello,

I just have asked for Pseudo logic.

I did not expect you to do my work.

Understand.

Please do not respond here when you are not  comfort with the issue.

Gundala

[lbreddemann]

What kind of pseudo logic are you expecting here?

What you want to do is doable by a single SELECT statement.

The "pseudo logic" would be

  SELECT { put correct select expression here}

Read the documentation I pointed you to.

Again,"WINDOW functions" are what you want to use here.

And mate, you asked for the help without reading up on documentation or looking for similar requests first here on SCN.

Maybe you'll check the house rules again...

[former_member1048438]

Dear Lars,

Thank you so much for the Logic.