producing a date time report in SQL/DTS

28 Dec 2005 6:26 PM

Blasting Cap

I have need to produce a report (excel sheet actually) from SQL that
would run each Tuesday and each Friday of every week.

What needs to be on the Tuesday report is everything that came in from
the Friday midnight time, until the Monday midnight time. The friday
report(sheet) would have everything that came in from Midnight Monday
evening, thru midnight Thursday. The next Tuesday report would have
everything from Midnight Thursday thru midnight Monday, and so on.

I know I can schedule the jobs to run on that interval, but how do I
selectively pick the records I want? There is a datetime field on the
table, "submit date" and what I am basically doing is a select * from
tbl_literature_orders where date > x.

Any ideas?

Thanks,

BC

28 Dec 2005 6:56 PM

Christian Donner

"Blasting Cap" schrieb:
Show quote

> I have need to produce a report (excel sheet actually) from SQL that
> would run each Tuesday and each Friday of every week.
>
> What needs to be on the Tuesday report is everything that came in from
> the Friday midnight time, until the Monday midnight time. The friday
> report(sheet) would have everything that came in from Midnight Monday
> evening, thru midnight Thursday. The next Tuesday report would have
> everything from Midnight Thursday thru midnight Monday, and so on.
>
> I know I can schedule the jobs to run on that interval, but how do I
> selectively pick the records I want? There is a datetime field on the
> table, "submit date" and what I am basically doing is a select * from
> tbl_literature_orders where date > x.
>
> Any ideas?
> Thanks,
> BC

Try it with two jobs, one for Tuesday, one for Friday, and set the execution
time of the job appropriately. Search for your data by difference:
select * from MyTable where datefield > dateadd(d, -3, GetDate()) -- Friday
and
select * from MyTable where datefield > dateadd(d, -4, GetDate()) -- Tuesday

28 Dec 2005 7:13 PM

Andrew J. Kelly

Just use the DATEPART() or DATENAME() functions to determine which day it
is. Then use DATEADD() with the appropriate days to get the from and to
that you need for your WHERE clause.

--
Andrew J. Kelly SQL MVP

Show quote

"Blasting Cap" <goo***@christian.net> wrote in message
news:eiUP1w9CGHA.4080@TK2MSFTNGP09.phx.gbl...
>I have need to produce a report (excel sheet actually) from SQL that would
>run each Tuesday and each Friday of every week.
>
> What needs to be on the Tuesday report is everything that came in from the
> Friday midnight time, until the Monday midnight time. The friday
> report(sheet) would have everything that came in from Midnight Monday
> evening, thru midnight Thursday. The next Tuesday report would have
> everything from Midnight Thursday thru midnight Monday, and so on.
>
> I know I can schedule the jobs to run on that interval, but how do I
> selectively pick the records I want? There is a datetime field on the
> table, "submit date" and what I am basically doing is a select * from
> tbl_literature_orders where date > x.
>
> Any ideas?
>
> Thanks,
>
> BC