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])
GROUP BY MONTH([Date]), YEAR([Date]))
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