Home Ask Login Register

Developers Planet

Your answer is one click away!

halfe February 2016

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');

Answers


Code-Monk February 2016

Try MySQL function DAYNAME(date1) to check for day is sunday,If so then get difference of two days instead of one.

mysql> SELECT DAYNAME(GETDATE());
+-----------------------+
| DAYNAME('2008-05-15') |
+-----------------------+
| Thursday              | 
+-----------------------+
1 row in set (0.01 sec)

If you are using SQL server,you can try datename function to get the day name:

datename(dw,getdate()) 

Try this for SQL Server:

SELECT files from table1 
where [date]>=DATEADD(DAY,case when datename(dw,(GETDATE()-1))='SUNDAY' THEN -2 ELSE -1 END,getdate());

For MySQL Server Try:

SELECT files from table1 
where `date`>=DATE_SUB(current_Date,interval (case when DAYNAME(date_sub(current_date,interval 1 day))='SUNDAY' THEN 2 ELSE 1 END) day);


Thorsten Kettner February 2016

Look for both dates, the current and the one before. From these records take the newer one.

select files
from table1 
where date =
( 
  select max(date) 
  from table1
  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:

select files
from table1 
where date in
( 
  select date
  from table1
  order by date desc limit 2
);


OldAmigan February 2016

Hope this works, because I don't have mysql here to test (only SQL Server):

SELECT files FROM table1
WHERE CAST([date] AS DATE) IN (CURDATE() - CASE DATEPART(dw,GETDATE()) WHEN 2 /* Monday */ THEN 2 ELSE 1 END, CURDATE())

The idea is to test if the date (as date only, no time part) falls in a specific list of dates, being today and yesterday or Saturday.


Jaco February 2016

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:

SELECT  *
FROM   dbo.table1 o
       CROSS APPLY ( SELECT TOP 2 [date]
                     FROM     dbo.table1 i
                     GROUP BY [date]
                     WHERE [date]>=DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()-5),0)
                     ORDER BY  [date] DESC
            ) det
WHERE det.[date] = o.[date]

Post Status

Asked in February 2016
Viewed 2,458 times
Voted 6
Answered 4 times

Search




Leave an answer


Quote of the day: live life