Configure default date/time parameter in query

27 Jul 2006 6:33 PM

Timothy.Rybak

I have a query that depends on the user entering a start and end
date/time string to return the desired results. I want to create a new
version that will run automatically in Reporting Services and email
itself to some users. I need the query to default to:

Start date/time = yesterday at 6AM
End date/time = today at 6AM

Here is the query I have to work with:

SELECT     ALCCode, COUNT(ALCCode) AS ALCCount
FROM         [Broadcast]
WHERE     (ProcessDate BETWEEN @StDate AND @EnDate)
GROUP BY ALCCode
ORDER BY ALCCode

Thank you in advance for any help!

27 Jul 2006 6:37 PM

Tibor Karaszi

A query doesn't have default values. How about make it a stored procedure, have default params as
NULL and check against that value in the proc and do the appropriate substitution? Or if you use a
data-driven subscription, you can create the parameter values for the report in the query that
drives the subscription.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

<Timothy.Ry***@gmail.com> wrote in message

Show quote

news:1154025195.614404.276430@i3g2000cwc.googlegroups.com...
>I have a query that depends on the user entering a start and end
> date/time string to return the desired results. I want to create a new
> version that will run automatically in Reporting Services and email
> itself to some users. I need the query to default to:
>
> Start date/time = yesterday at 6AM
> End date/time = today at 6AM
>
> Here is the query I have to work with:
>
> SELECT     ALCCode, COUNT(ALCCode) AS ALCCount
> FROM         [Broadcast]
> WHERE     (ProcessDate BETWEEN @StDate AND @EnDate)
> GROUP BY ALCCode
> ORDER BY ALCCode
>
> Thank you in advance for any help!
>

27 Jul 2006 7:02 PM

Scott Morris

<Timothy.Ry***@gmail.com> wrote in message

news:1154025195.614404.276430@i3g2000cwc.googlegroups.com...
>I have a query that depends on the user entering a start and end
> date/time string to return the desired results. I want to create a new
> version that will run automatically in Reporting Services and email
> itself to some users. I need the query to default to:
>
> Start date/time = yesterday at 6AM
> End date/time = today at 6AM

The following should get you moving in the right direction:

declare @test datetime
set @test = current_timestamp
select
   @test as 'now', convert(char(8), @test, 112) as 'today',
   dateadd(hour, 6, convert(char(8), @test, 112)) as 'today @6am',
   dateadd(hour, -18, convert(char(8), @test, 112)) as 'yesterday @6am'

27 Jul 2006 7:23 PM

Aaron Bertrand [SQL Server MVP]

> declare @test datetime
> set @test = current_timestamp
> select
>   @test as 'now', convert(char(8), @test, 112) as 'today',
>   dateadd(hour, 6, convert(char(8), @test, 112)) as 'today @6am',
>   dateadd(hour, -18, convert(char(8), @test, 112)) as 'yesterday @6am'

While in a query that runs once a day it will be largely inconsequential, as
a general rule and 'best practice' I try to avoid unnecessary conversions
to/from char as they are very expensive (I also have an obsessive compulsive
need to use SMALLDATETIME wherever I don't need sub-minute accuracy). This
isn't quite as pretty, but does the same thing using only date operations:

DECLARE
@test SMALLDATETIME,
@today SMALLDATETIME;

SELECT
@test = CURRENT_TIMESTAMP,
@today = DATEDIFF(DAY, 0, @test);

SELECT
[now] = @test,
[today] = @today,
[today @6am] = DATEADD(HOUR, 6, @today),
[yesterday @6am] = DATEADD(HOUR, 6, @today - 1);

Right now people are saying, tomato, tomato. But run each version in a loop
100,000 times, and compare the results. The cost of both the explicit
conversions to char and the implicit conversions back to datetime will add
up.

27 Jul 2006 8:22 PM

Timothy.Rybak

Aaron,

After entering your code, I should change my @StDate and @EnDate to:

Between 'yesterday @6am' AND 'today @6am'

Is this correct?

Tim

Aaron Bertrand [SQL Server MVP] wrote:
Show quote

> > declare @test datetime
> > set @test = current_timestamp
> > select
> >   @test as 'now', convert(char(8), @test, 112) as 'today',
> >   dateadd(hour, 6, convert(char(8), @test, 112)) as 'today @6am',
> >   dateadd(hour, -18, convert(char(8), @test, 112)) as 'yesterday @6am'
>
> While in a query that runs once a day it will be largely inconsequential, as
> a general rule and 'best practice' I try to avoid unnecessary conversions
> to/from char as they are very expensive (I also have an obsessive compulsive
> need to use SMALLDATETIME wherever I don't need sub-minute accuracy). This
> isn't quite as pretty, but does the same thing using only date operations:
>
> DECLARE
> @test SMALLDATETIME,
> @today SMALLDATETIME;
>
> SELECT
> @test = CURRENT_TIMESTAMP,
> @today = DATEDIFF(DAY, 0, @test);
>
> SELECT
> [now] = @test,
> [today] = @today,
> [today @6am] = DATEADD(HOUR, 6, @today),
> [yesterday @6am] = DATEADD(HOUR, 6, @today - 1);
>
> Right now people are saying, tomato, tomato. But run each version in a loop
> 100,000 times, and compare the results. The cost of both the explicit
> conversions to char and the implicit conversions back to datetime will add
> up.

27 Jul 2006 8:42 PM

Aaron Bertrand [SQL Server MVP]

No, that was just for demonstration purposes.

DECLARE
    @start SMALLDATETIME,
    @end SMALLDATETIME;

SELECT
    @end = DATEADD(HOUR, 6, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP)),
    @start = @end - 1;

SELECT
    ...
    WHERE ProcessDate >= @start
    AND ProcessDate < @end
    ...

<Timothy.Ry***@gmail.com> wrote in message
Show quote

news:1154031730.358851.285370@s13g2000cwa.googlegroups.com...
> Aaron,
>
> After entering your code, I should change my @StDate and @EnDate to:
>
> Between 'yesterday @6am' AND 'today @6am'
>
> Is this correct?
>
> Tim
>
> Aaron Bertrand [SQL Server MVP] wrote:
>> > declare @test datetime
>> > set @test = current_timestamp
>> > select
>> >   @test as 'now', convert(char(8), @test, 112) as 'today',
>> >   dateadd(hour, 6, convert(char(8), @test, 112)) as 'today @6am',
>> >   dateadd(hour, -18, convert(char(8), @test, 112)) as 'yesterday @6am'
>>
>> While in a query that runs once a day it will be largely inconsequential,
>> as
>> a general rule and 'best practice' I try to avoid unnecessary conversions
>> to/from char as they are very expensive (I also have an obsessive
>> compulsive
>> need to use SMALLDATETIME wherever I don't need sub-minute accuracy).
>> This
>> isn't quite as pretty, but does the same thing using only date
>> operations:
>>
>> DECLARE
>> @test SMALLDATETIME,
>> @today SMALLDATETIME;
>>
>> SELECT
>> @test = CURRENT_TIMESTAMP,
>> @today = DATEDIFF(DAY, 0, @test);
>>
>> SELECT
>> [now] = @test,
>> [today] = @today,
>> [today @6am] = DATEADD(HOUR, 6, @today),
>> [yesterday @6am] = DATEADD(HOUR, 6, @today - 1);
>>
>> Right now people are saying, tomato, tomato. But run each version in a
>> loop
>> 100,000 times, and compare the results. The cost of both the explicit
>> conversions to char and the implicit conversions back to datetime will
>> add
>> up.
>

27 Jul 2006 8:48 PM

Arnie Rowland

If the parameters are null, you could use the coalesce function accomplish your goal.

For example,

WHERE ProcessDate BETWEEN coalesce( @StDate, (dateadd( hour, 6, convert( char(10), current_timestamp, 102 ))))
AND coalesce( @EnDate, (dateadd( hour, -18, convert( char(10), current_timestamp, 102 ))))

Alternatively, you could create a couple of functions that would provide the correct dates.

However, as has been suggested, a stored procedure will provide better control.

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

Show quote

<Timothy.Ry***@gmail.com> wrote in message news:1154025195.614404.276430@i3g2000cwc.googlegroups.com...
>I have a query that depends on the user entering a start and end
> date/time string to return the desired results. I want to create a new
> version that will run automatically in Reporting Services and email
> itself to some users. I need the query to default to:
>
> Start date/time = yesterday at 6AM
> End date/time = today at 6AM
>
> Here is the query I have to work with:
>
> SELECT     ALCCode, COUNT(ALCCode) AS ALCCount
> FROM         [Broadcast]
> WHERE     (ProcessDate BETWEEN @StDate AND @EnDate)
> GROUP BY ALCCode
> ORDER BY ALCCode
>
> Thank you in advance for any help!
>

28 Jul 2006 6:13 AM

rajalapati

Arnie Rowland wrote:
Show quote

> If the parameters are null, you could use the coalesce function accomplish your goal.
>
> For example,
>
> WHERE ProcessDate BETWEEN coalesce( @StDate, (dateadd( hour,   6, convert( char(10), current_timestamp, 102 ))))
>                       AND coalesce( @EnDate, (dateadd( hour, -18, convert( char(10), current_timestamp, 102 ))))
>
> Alternatively, you could create a couple of functions that would provide the correct dates.
>
> However, as has been suggested, a stored procedure will provide better control.
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
>
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
>
> <Timothy.Ry***@gmail.com> wrote in message news:1154025195.614404.276430@i3g2000cwc.googlegroups.com...
> >I have a query that depends on the user entering a start and end
> > date/time string to return the desired results. I want to create a new
> > version that will run automatically in Reporting Services and email
> > itself to some users. I need the query to default to:
> >
> > Start date/time = yesterday at 6AM
> > End date/time = today at 6AM
> >
> > Here is the query I have to work with:
> >
> > SELECT     ALCCode, COUNT(ALCCode) AS ALCCount
> > FROM         [Broadcast]
> > WHERE     (ProcessDate BETWEEN @StDate AND @EnDate)
> > GROUP BY ALCCode
> > ORDER BY ALCCode
> >
> > Thank you in advance for any help!
> >
> ------=_NextPart_000_02BF_01C6B183.66C29C60
> Content-Type: text/html; charset=iso-8859-1
> Content-Transfer-Encoding: quoted-printable
> X-Google-AttachSize: 3170
>
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> <HTML><HEAD>
> <META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
> <META content="MSHTML 6.00.5296.0" name=GENERATOR>
> <STYLE></STYLE>
> </HEAD>
> <BODY>
> <DIV><FONT face=Arial size=2>If the parameters are null, you could use the
> coalesce function accomplish your goal. </FONT></DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV><FONT face=Arial size=2>For example, </FONT></DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV><FONT face=Arial size=2><FONT face="Courier New">WHERE ProcessDate BETWEEN
> coalesce( @StDate, (dateadd( hour,   6, convert( char(10),
> current_timestamp, 102 )))) </FONT></FONT></DIV>
> <DIV><FONT face=Arial size=2><FONT
> face="Courier New">                     
> AND coalesce( @EnDate, (dateadd( hour, -18, convert( char(10),
> current_timestamp, 102 ))))<BR></DIV></FONT></FONT>
> <DIV><FONT face=Arial size=2>Alternatively, you could create a couple of
> functions that would provide the correct dates. </FONT></DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV><FONT face=Arial size=2>However, as has been suggested, a stored procedure
> will provide better control.</FONT></DIV>
> <DIV><FONT face=Arial size=2><BR>-- <BR>Arnie Rowland, Ph.D.<BR>Westwood
> Consulting, Inc</FONT></DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV><FONT face=Arial size=2>Most good judgment comes from experience. <BR>Most
> experience comes from bad judgment. <BR>- Anonymous</FONT></DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV><FONT face=Arial size=2><</FONT><A
> href="mailto:Timothy.Ry***@gmail.com"><FONT face=Arial
> size=2>Timothy.Ry***@gmail.com</FONT></A><FONT face=Arial size=2>> wrote in
> message </FONT><A
> href="news:1154025195.614404.276***@i3g2000cwc.googlegroups.com"><FONT
> face=Arial
> size=2>news:1154025195.614404.276430@i3g2000cwc.googlegroups.com</FONT></A><FONT
> face=Arial size=2>...</FONT></DIV><FONT face=Arial size=2>>I have a query
> that depends on the user entering a start and end<BR>> date/time string to
> return the desired results.  I want to create a new<BR>> version that
> will run automatically in Reporting Services and email<BR>> itself to some
> users.  I need the query to default to:<BR>> <BR>> Start date/time =
> yesterday at 6AM<BR>> End date/time = today at 6AM<BR>> <BR>> Here is
> the query I have to work with:<BR>> <BR>> SELECT    
> ALCCode, COUNT(ALCCode) AS ALCCount<BR>>
> FROM         [Broadcast]<BR>>
> WHERE     (ProcessDate BETWEEN @StDate AND @EnDate)<BR>>
> GROUP BY ALCCode<BR>> ORDER BY ALCCode<BR>> <BR>> Thank you in advance
> for any help!<BR>></FONT></BODY></HTML>
>
> ------=_NextPart_000_02BF_01C6B183.66C29C60--

Hey,

In RS2005 ,the parameter can take default values
If you go to report parameters ,there you can find the Option Of
Default values
Include this Expression there
For Start Date
=DateAdd(DateInterval.Hour,18,DateValue(DateAdd(DateInterval.day,-1,Today)))
For end Date
=DateAdd(DateInterval.Hour,18,DateValue(Today))

Hope it works as i am not sure of the Correct information