Case statement in Where Clause

[gkrenton]

I have a query that works except I need to do a date evalution that takes any document that falls before the current period & puts it into the current period.
I'm stumped as to how add this case statement

case when P.po_promise < today then today else P.po_promise end

to the where clause so that the po_promise date is evaluted & assigned to the proper week. If I put this case statement in the listing of fields it works but can't figure out what the syntax is to put it in the where clause. I managed to get it to work fine in Access but the sql syntax from access isn't translating either so I'm stumped.

select PR.store,
P.po_id,
substring (I.inv_id2 from 1 for 1) as dept,
substring(I.inv_id2 from 2 for 3) as class,
end,
W.wx_week as week,
sum (PD.oocost) as CostOnOrder,
sum (PD.oortl) as RetailOnOrder
from
ren_poord_sum as PR join po as P on P.po_id=PR.poid
join ren_onorder_podollar as PD on PR.poid=PD.poid
and PR.item=PD.item and P.po_id=PD.poid
join inv as I on I.inv_id3=PD. item,
week as W join xcontrol as X on W. wx_year=X.comp_year
where P.po_promise between W.wx_begdate and W.wx_enddate
group by PR.store, 2,3,4,5,6

thanks

gina

 

[rac2]

Maybe-

...
WHERE ( P.po_promise < getdate() 
        AND getdate() BETWEEN W.wx_begdate AND W.wx_enddate 
      )
   OR P.po_promise BETWEEN W.wx_begdate AND W.wx_enddate 
...

A WHERE clause like that should include any items promised by today if today is between begdate and enddate
plus any items promised between begdate and enddate.

If begdate and enddate are sometime in the past, then only things promised during that period will be retrieved;
also if begdate and enddate are in the future.


getdate() is the MS SQL Server equivalent of Access Now().

 

[gkrenton]

Unfortunately that doesn't quite do it. Po_promise date that's in the past needs to be assigned to the current week assignment. The suggested code works but keeps data assigned o the week in which is was originally assigned to ie PO_promise of 2/10/2007 still reports out was week 2 vs showing week 5 as the current week which I'm trying to assign it to.

The access sql which works is this:

WHERE (((IIf([po_promise]<=Date(),Date(),[po_promise])) Between [wx_begdate] And [wx_enddate]))

- Thanks

gina

 

[rac2]

...
reports out was week 2 vs showing week 5 as the current week which I'm trying to assign it to.
...

Do you mean that you wish to UPDATE the table, that is, actually change the promised date to today? From your example, wipe out the value for PO_promise of 2/10/2007 and replace it with 3/9/2007?

Or do you mean only that you wish to show 3/9/2007 for a PO_promise of 2/10/2007?


A CASE expression in SQL Server code for the Access code that you posted -

WHERE 
      CASE 
        WHEN [po_promise] <= getdate() THEN getdate()
        ELSE [po_promise]
      END BETWEEN [wx_begdate] AND [wx_enddate]

 

[gkrenton]

Not updating the table just puttind the data into the current week. So only that you wish to show 3/9/2007 for a PO_promise of 2/10/2007 which will then assign week 5 to this data vs week 2.
Which your case statement as shown above does! Hot diggety dog!
I see where my problem understanding how this work was/is -
I still kept calling the po_promise date field when in fact I see one doesn't need to specify it.

Thanks so very much.

gina