Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2010
    Posts
    10

    Unanswered: Complicated Date and Select Query

    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?

    Code:
    Select importdate, COUNT (CASE WHEN CodeList LIKE '%56464A%'
    		OR	 FeatureCodeList LIKE '%23456AS%'
    		THEN CodeList 
                    END) AS 'Cost'
    FROM 	history
    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

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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.
    Dave

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •