Datediff for each row agaimst maximum row

Question

0.00/5 (No votes)

See more:

Hello,

I have a SQL table where i have a date column and it has the following format 1yymmdd. I have converted this to datetime using following sql query.

Cast(left(right(ltrim(str(date,25,5)), LEN(ltrim(str(date,25,5)))-3),2) +'-' + left(right(ltrim(str(date,25,5)), LEN(ltrim(str(date,25,5)))-5),2) +'-' +
left(right(ltrim(str(date,25,5)), LEN(ltrim(str(date,25,5)))-1),2) as datetime)

The above query gives me date in following format YYYY-MM-DD HH:MM:SS.
But the confusion is i want to calculate the datedifference of each row with maximum date that the query returns.

For Example:

If i run the query and it returns the following 3 dates
2016-06-08 00:00:00.000
2016-06-05 00:00:00.000
2016-06-03 00:00:00.000

So in above result set the highest date is 2016-06-08 00:00:00.000 and when i calculate the difference between it and other 2 days it should return 3 and 5 days respectively,
so what i want is to calculate the difference between the max date and every other date and select only the dates which has difference of less than 3 dayys.

I am looking forward to your help as always. Thanks alot.

What I have tried:

Googled it and could not find any exact situation

Posted 30-Nov-16 19:04pm

Faran Saleem

Updated 1-Dec-16 0:49am

Add a Solution

2 solutions

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)

jamuro77 · Answer 1 · 2016-11-30T20:15:00

Solution 1

Hi, the solution is easy. You just have to use a "SQL Windowing function". Anyway, have a look to SQL Documentation.

Let's suppose we have a table with a "OrderDate" field. The query to get what you want would be:

select OrderDate, datediff(day,MAX(OrderDate) over(),OrderDate) from sales
order by OrderDate desc

Note, "over" operator above in the query.

Regards.

Posted 30-Nov-16 20:15pm

jamuro77

Comments

Faran Saleem 1-Dec-16 6:25am

Thanks a ton for your help.. i ran your query and it worked great just a couple of issues.

1. The difference between dates is coming in negetive.
2. And how can I select the records with difference between date < 7

Looking forward to your reply

Faran Saleem 1-Dec-16 6:48am

And in above example we are subtracting from the max date.. what if i want results only with 7 days difference. Like for suppose if we have 4 following dates
2016-03-02
2016-03-04
2016-03-06
2016-06-08

In above case 2016-06-08 is the max date but the difference between other 3 dates is in between 7 days.
So i want to display only those 3 dates and not 2016-06-08

jamuro77 · Answer 2 · 2016-12-01T00:48:00

Solution 2

Well,

1) To put days in positive you only have to take absolute value or switching the order of date values in datediff function.

2) To filter values you should use a Common Table Expression and then filter.

Here is the code:

;with cte as (select OrderDate, datediff(day,OrderDate,MAX(OrderDate) over()) as Days from sales)
select * from cte
where Days between 1 and 6
order by OrderDate desc

Posted 1-Dec-16 0:48am

jamuro77

Updated 1-Dec-16 0:54am

v2

Comments

Faran Saleem 2-Dec-16 2:04am

But i am already using CTE..Using them because i had to compare each row with the previous value.

So how can i do it?