ragehulk ragehulk - 3 months ago 9
SQL Question

How to obtain the "largest day" in a week?

How to get a data when the "largest day" of the week does not exist?

For example, if Friday does not exist in my database for a particular week (assuming that Saturday and Sundays are not in my database), I would still like to be able to get the data from Thursday. If Both Friday and Thursday do not exist, I would like to get the data from Wednesday etc.

This is what I currently have (this code allows me to obtain the last day of the month from my database):

/**Select Last Day Of Month**/
SELECT * FROM [mytable]
WHERE [date] IN (SELECT MAX([date])
FROM [mytable]
GROUP BY MONTH([Date]), YEAR([Date]))


I also understand that you can use the DATEPART function to get all datas from a particular day (i.e. Friday), the thing I'm not sure about is how to get a thursday if Friday doesn't exist. I'm looking to grab all the data that has the corresponding features, not one particular week. Thanks!

As a clearer example:
Say I have four input data in my database->

1. 2016/8/19(Fri), red

2. 2016/8/18(Thu), blue

3. 2016/8/11(Thu), red

4. 2016/8/10(Wed), red

after the query is executed, I would like to have:

1. 2016/8/19(Fri), red

3. 2016/8/11(Thu), red

They are selected because the two data are the corresponding "largest" data in that week.

Answer

See if the following query helps. It displays the last day of every week in the given input data.

declare @table table(
date datetime
)

insert into @table
values
('08/01/2016'),
('08/02/2016'),
('08/03/2016'),
('08/04/2016'),
('08/05/2016'),
('08/06/2016'),
('08/07/2016'),
('08/08/2016'),
('08/09/2016'),
('08/10/2016'),
('08/11/2016'),
('08/12/2016'),
('08/13/2016'),
('08/14/2016'),
('08/15/2016'),
('08/16/2016'),
('08/17/2016'),
('08/18/2016'),
('08/19/2016'),
('08/20/2016'),
('08/21/2016'),
('08/22/2016'),
('08/23/2016')


;with cte as
(
select date, row_number() over(partition by datepart(year,date),datepart(week,date) order by date desc) as rn from @table
)

select date,datename(weekday,date) from cte
where rn = 1