Friday, November 12, 2004

.NET and MS Access Query

I was developing a project in .NET with MS Access as backend database where a report was generated to show if the shipment was done within 24 hrs of order,between 24 and 48 hrs and more than 48 hrs. Usually, stores operations remain closed on weekends and thus all orders received over weekend were shipped on Monday.To give effect of weekend closure in the report, I wrote a query in MS Access:

SELECT tblshipment.*, IIf(([D1]=2 And [D2]=6),((DateDiff("h",[orderdate],[shipmentdate]))-48),IIf(([D1]=2 And [D2]=7),((DateDiff("h",[orderdate],[shipmentdate]))-24),DateDiff("h",[orderdate],[shipmentdate]))) AS DD, Format([shipmentdate],"w") AS D1, Format([orderdate],"w") AS D2, DateDiff("h",[orderdate],[shipmentdate]) AS DDxFROM tblshipment;

By this, I am first finding out hours difference in order date and shipment date and then, looking for, if order date was Friday and shipment date is Monday, then reduce 48 hrs from time difference or if order date was Saturday and shipment date is Monday, then reduce 24 hrs from time difference, to give effect for weekend closure.

No comments: