Day of the week

18 Aug 2005 5:31 PM

qjlee

I have a table whcih contains order Id (orderid_c), and order date
(orderdate_d).
Is there anywhere I can program to count the number of order from Monday to
the day the report is run, for example, when I run the report on Wednesday,
the report will cover from Monday to Wednesday and when I run the report on
Thursday, the report will cover from Monday to Thursday. I will have to run
the report several time during the business hour.

Thanks,

18 Aug 2005 5:50 PM

Chris

set datefirst 1
select count(orderid_c) from table
where datepart(wk,orderdate_d) = datepart(wk,getdate())

Show quote

"qjlee" <qj***@discussions.microsoft.com> wrote in message
news:9FCC02A9-29B8-48B1-B888-091BBC502CFD@microsoft.com...
> I have a table whcih contains order Id (orderid_c), and order date
> (orderdate_d).
> Is there anywhere I can program to count the number of order from Monday
to
> the day the report is run, for example, when I run the report on
Wednesday,
> the report will cover from Monday to Wednesday and when I run the report
on
> Thursday, the report will cover from Monday to Thursday. I will have to
run
> the report several time during the business hour.
>
>
> Thanks,
>
>

18 Aug 2005 5:53 PM

DBA

sp_who will tell you who and what database

Show quote

"qjlee" wrote:

> I have a table whcih contains order Id (orderid_c), and order date
> (orderdate_d).
> Is there anywhere I can program to count the number of order from Monday to
> the day the report is run, for example, when I run the report on Wednesday,
> the report will cover from Monday to Wednesday and when I run the report on
> Thursday, the report will cover from Monday to Thursday. I will have to run
> the report several time during the business hour.
>
>
> Thanks,
>
>

18 Aug 2005 10:00 PM

Hugo Kornelis

On Thu, 18 Aug 2005 10:31:01 -0700, qjlee wrote:

>I have a table whcih contains order Id (orderid_c), and order date
>(orderdate_d).
>Is there anywhere I can program to count the number of order from Monday to
>the day the report is run, for example, when I run the report on Wednesday,
>the report will cover from Monday to Wednesday and when I run the report on
>Thursday, the report will cover from Monday to Thursday. I will have to run
>the report several time during the business hour.

Hi qjlee,

Here's how to select data between "last monday" and "now":

SELECT ....
FROM   ....
WHERE TheDate >= DATEADD(day, DATEDIFF(day, '20050103',
CURRENT_TIMESTAMP) / 7 * 7, '20050103')
AND    TheDate <= CURRENT_TIMESTAMP
AMD    ....

Best, Hugo

--

(Remove _NO_ and _SPAM_ to get my e-mail address)