-1

We enter our production data in the database next day of production, I am using the following query to get the yesterday data.

Query works fine until after weekend, it shows nothing on Monday because there is no production on weekend.

Is there anyway I can change the query to show me the last day of production.

SELECT Date, ProductCode
FROM Production
WHERE (ProductCode In ('35032','40112-I','41212-I','50112','824-5'))                   
AND (Date=Dateadd(dd,-1,Convert(char(8),Current_timestamp,112)))
Abbas
  • 75
  • 9
  • What is your DBMS? – rory.ap Dec 06 '16 at 18:31
  • 1
    instead of "(Date=Dateadd(dd,-1,Convert(char(8),Current_timestamp,112)))", you could do something like "Date = (SELECT MAX(Date) FROM Production)". Now depending on your SQL engine, the syntax may be a little bit different, but thats the general idea. Also, please note that this will work if you have dates only. If you have datetimes, you'll have to convert it so only the date part is compared, not the time part. If your table gets huge, don'T forget to add an index on your date if there isn't already one. Otherwise that could make the query slower. – Mathieu Turcotte Dec 06 '16 at 18:33
  • Possible duplicate of [Select info from table where row has max date](http://stackoverflow.com/questions/19432913/select-info-from-table-where-row-has-max-date) – C8H10N4O2 Dec 06 '16 at 18:34
  • Did you try [searching for an existing answer](https://www.google.com/search?q=sql+max+date+site%3Astackoverflow.com)? This is surely a duplicate. – C8H10N4O2 Dec 06 '16 at 18:36

1 Answers1

2

A simple solution with a CASE statement to check to see if it's a Monday, and if so, subtract 3 days rather than 1.

SELECT  Date, ProductCode
FROM    Production
WHERE   (ProductCode In ('35032','40112-I','41212-I','50112','824-5'))                   
AND     (Date=Dateadd(Day, Case When DatePart(WeekDay, Current_Timestamp) = 2 Then -3 Else -1 End, Convert(Date, Current_Timestamp)))

Another (more precise) solution would be to pull the most recent Date in the table that isn't today's Date:

SELECT  Date, ProductCode
FROM    Production
WHERE   (ProductCode In ('35032','40112-I','41212-I','50112','824-5'))                   
AND     (Date= (Select Max(Date) From Production Where Date <> Convert(Date, Current_Timestamp)))
Siyual
  • 16,415
  • 8
  • 44
  • 58
  • The problem with the first solution is that it won't take hollidays into account, and IF they do work on a WE for some reason, the report will not pull the data out... I'd rather WHERE on a select Max(Date) or INNER JOIN on a subquery rather than hardcoding the fact that saturdays and sundays are off and that all other days are business days. – Mathieu Turcotte Dec 06 '16 at 18:39
  • @MathieuTurcotte The second query in my answer already does exactly what you were suggesting. I gave both solutions as possible answers as they cover two different scenarios, and the OP can determine based on their needs which one is better. Holidays are going to be a problem regardless unless you have a table storing that information, as holidays vary not only by region, but by workplaces as well. – Siyual Dec 06 '16 at 18:50
  • My point about the holidays if that if they don't work the report won'T pull that day out. No matter if there is a holliday table or not (no data = the day is ignored). For the second query, I know thats okay, that is also what I suggested OP in a comment. You also added the "and the day is not today" which I forgot in my suggestion. – Mathieu Turcotte Dec 06 '16 at 19:05
  • Thanks Mathiew Turcotte, – Abbas Dec 06 '16 at 19:23