The following query is pretty simple but the date criteria is complicated. When I run this query, I get results for the month of Feb but only mon-fri. The complicated part is when the date lands on a monday because those results include friday and saturday results, all dates represent results from the previous day.
So if I want results for a friday or saturday, the actual result file would come in on a monday. Sunday has nothing.
So say I want results for actual date 2/5 which was a friday, I would have to do importdate = 2/8 --becasue this is when we get the results for friday and processdate >=2/5
and If i want saturday's result, 2/6 I do importdate = 2/8 and processdate >=2/6
So if an importdate lands on a monday, is there a way to execute it in a way where it would produce the results for the friday and saturday as oppose to me having to execute them separately?
something like if importdate between 02/01-03/01 = monday, then select importdate = monday and processdate >=the previous friday , select importdate = monday and processdate >= the previous saturday
otherwise if it does not land on monday, ignore processdate?
Select importdate, COUNT (CASE WHEN CodeList LIKE '%56464A%'
OR FeatureCodeList LIKE '%23456AS%'
END) AS 'Cost'
where importdate between '20100201' and '20100301'
--and processdate >= 20100205
and ProcessState in ('good','great')
and promotioncode in ('107', '111', '163')
and FileName like '%N123%'
group by importdate
you could set up the and/or conditions to determine your importdate based on the processdate or vice versa, using like a dayofweek function, or even create a function which returns the output from that logic. Another idea, would be create a table that contains the processdates and what import date goes with it, then you join to that table.