how to skip a day to previous in sql database if that day produce no files
i need to print the files From the current day plus from the day before...but if the day before is sunday then i want to print the files from saturday
i have a database containing a files of a month..each day produces some files but sunday is a holiday and at that day no files are generated.now i have done the query to pick the files in such a way that giving current date will results the files of that day and previous day. But the problem is on monday when i give the current date it produces files of monday and sunday... i dont want to print files of sunday as it is null,instead of that when am giving the current date if its is monday i should get the files of saturday instead of monday.
this is the code am using now
select files from table1 where [date]>=DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()-1),0) ";
except for sundays i have entered the files for each day
insert into dbtable(date,files) values('date value',' filename');
Look for both dates, the current and the one before. From these records take the newer one.
where date =
where date in (getdate(), getdate() - interval 1 day)
(Updated regarding Code-Monk's commment. Thanks.)
This is standard SQL except for the date manipulation which is MySQL specific. If you were mistaken with the DBMS tagged, then replace the part where a day is subtracted from current day with what's appropriate for your DBMS.)
UPDATE: You want to select from two days, the currect day and the last date before. As there should be no future records, this always means the two maximum dates in your table.
The slightly altered query using ORDER BY and LIMIT:
where date in
order by date desc limit 2
Assuming your are using SQL Server (and not MySQL), you can use a CROSS APPLY to get data for the last two [dates] with files:
FROM dbo.table1 o
CROSS APPLY ( SELECT TOP 2 [date]
FROM dbo.table1 i
GROUP BY [date]
ORDER BY [date] DESC
WHERE det.[date] = o.[date]
Asked in February 2016Viewed 2,458 timesVoted 6Answered 4 times