newbie: how to access variables across multiple queries

19 Aug 2006 4:37 PM

groups

Hi All,

I've inherited a bare-bones reporting environment that relies on
manually executing dozens of select queries in Query Analyzer each
month, then cutting and pasting results into Excel. Each month, the
date ranges for each query have to be manually adjusted.

I know there are much more efficient ways to go about this type of
reporting (DTS, Crystal, now Reporting Services...). Over time I want
to move towards that, but for now I'm simply looking for a way to store
the query date range in one place, and reference it in each query, so
that I can avoid manually adjusting the date range in each and every
query every month.

The code for two of the queries would look like this:

-- code for sales report
select [....]
where saleDate between queryBeginDate and QueryEndDate

-- code for new hires report
select [...]
where employeeHireDate between queryBeginDate and QueryEndDate

My question is, How and where could I set values for queryBeginDate and
QueryEndDate so that they will be accessible across these different
queries? We're still running SQL Server 2000. Thanks so much if you can
point me in the right direction.

19 Aug 2006 4:44 PM

ML

Pack all queries into a single procedure, then use parameters to supply the
values.

ML

---
http://milambda.blogspot.com/

19 Aug 2006 6:21 PM

KH

If you're running them for the current month (or prior month, or other
pre-defined timespan relative to the current date) you can calculate the
range using SQL's date & time functions, for example:

DECLARE
    @dt DATETIME
,   @start_dt    DATETIME
,   @end_dt    DATETIME

SET @dt = GETDATE()

-- This month
SET @start_dt   = CONVERT(char(4),YEAR(@dt)) + RIGHT('0' +
CONVERT(varchar(2),MONTH(@dt)), 2) + '01'
SET @end_dt    = DATEADD(month, 1, @start_dt)

-- Last month
SELECT
    @start_dt   = DATEADD(month, -1, @start_dt)
,   @end_dt    = DATEADD(month, -1, @end_dt)

Show quote

"gro***@jhodes.fastmail.fm" wrote:

> Hi All,
>
> I've inherited a bare-bones reporting environment that relies on
> manually executing dozens of select queries in Query Analyzer each
> month, then cutting and pasting results into Excel. Each month, the
> date ranges for each query have to be manually adjusted.
>
> I know there are much more efficient ways to go about this type of
> reporting (DTS, Crystal, now Reporting Services...). Over time I want
> to move towards that, but for now I'm simply looking for a way to store
> the query date range in one place, and reference it in each query, so
> that I can avoid manually adjusting the date range in each and every
> query every month.
>
> The code for two of the queries would look like this:
>
> -- code for sales report
> select [....]
> where saleDate between queryBeginDate and QueryEndDate
>
> -- code for new hires report
> select [...]
> where employeeHireDate between queryBeginDate and QueryEndDate
>
>
> My question is, How and where could I set values for queryBeginDate and
> QueryEndDate so that they will be accessible across these different
> queries? We're still running SQL Server 2000. Thanks so much if you can
> point me in the right direction.
>
>

20 Aug 2006 2:54 PM

amish

KH wrote:

Show quote

> If you're running them for the current month (or prior month, or other
> pre-defined timespan relative to the current date) you can calculate the
> range using SQL's date & time functions, for example:
>
> DECLARE
>     @dt DATETIME
> ,   @start_dt    DATETIME
> ,   @end_dt    DATETIME
>
> SET @dt = GETDATE()
>
> -- This month
> SET @start_dt   = CONVERT(char(4),YEAR(@dt)) + RIGHT('0' +
> CONVERT(varchar(2),MONTH(@dt)), 2) + '01'
> SET @end_dt    = DATEADD(month, 1, @start_dt)
>
> -- Last month
> SELECT
>     @start_dt   = DATEADD(month, -1, @start_dt)
> ,   @end_dt    = DATEADD(month, -1, @end_dt)
>
>
> "gro***@jhodes.fastmail.fm" wrote:
>
> > Hi All,
> >
> > I've inherited a bare-bones reporting environment that relies on
> > manually executing dozens of select queries in Query Analyzer each
> > month, then cutting and pasting results into Excel. Each month, the
> > date ranges for each query have to be manually adjusted.
> >
> > I know there are much more efficient ways to go about this type of
> > reporting (DTS, Crystal, now Reporting Services...). Over time I want
> > to move towards that, but for now I'm simply looking for a way to store
> > the query date range in one place, and reference it in each query, so
> > that I can avoid manually adjusting the date range in each and every
> > query every month.
> >
> > The code for two of the queries would look like this:
> >
> > -- code for sales report
> > select [....]
> > where saleDate between queryBeginDate and QueryEndDate
> >
> > -- code for new hires report
> > select [...]
> > where employeeHireDate between queryBeginDate and QueryEndDate
> >
> >
> > My question is, How and where could I set values for queryBeginDate and
> > QueryEndDate so that they will be accessible across these different
> > queries? We're still running SQL Server 2000. Thanks so much if you can
> > point me in the right direction.
> >
> >

If your date range is from first day of month to last day of month then
do as KH suggested.

If your date range is varied then create a table with two column
begindate and enddate.
Store begindate and enddate in that table and reference it in your
query.
Every month you have to change value once in that table.
Ex.

Create table t
(begindate datetime,
enddate datetime).

select [...]
where employeeHireDate between (select begindate from t)and (select
enddate from t)

Regards
Amish Shah
http://shahamishm.tripod.com