Solved

simple date range prompt in sql view

Posted on 2011-03-22
6
687 Views
Last Modified: 2012-08-13
I want my view to prompt me for a date range when I run it.  Currently the dates are hard coded.
SELECT     TOP (100) PERCENT tDate, tActivityType, tUnits AS Total
FROM         dbo.AxTransaction
WHERE     (tUnits <> 0) AND (tActivityType = 1) AND (tDate >= CONVERT(DATETIME, '2010-01-01 00:00:00', 102)) AND (tDate <= CONVERT(DATETIME, '2010-12-31 00:00:00', 102))

How do I replace hard coded dates with prompts or parameters?

Thanks
0
Comment
Question by:mbrennan_pacbell_net
6 Comments
 
LVL 40

Expert Comment

by:Sharath
ID: 35195778
You cannot do that in view. Create an sp and pass the dates as parameters.
0
 
LVL 9

Expert Comment

by:kaminda
ID: 35195793
You will have to use either a function of sp to do this. My advice is to use an Table Valued Function so it can be used as a view in your queries.

Here is TBV Function script for your view,
FUNCTION [dbo].[fnGetAxTransaction]
(
	@FromDate DateTime,
    @ToDate DateTime
)


RETURNS  TABLE
AS 
RETURN
(
SELECT     TOP (100) PERCENT tDate, tActivityType, tUnits AS Total
FROM         dbo.AxTransaction
WHERE     (tUnits <> 0) AND (tActivityType = 1) AND (tDate >= @FromDate AND (tDate <= @ToDate)
)

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35198549
And please lose the TOP (100) PERCENT, it is pointless.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:mbrennan_pacbell_net
ID: 35200260
kaminda,

Thanks your solution worked somwhat.  I was able to create the table value funcation based on your reply.  However when I try to use it in a view, I get the following error:

"Incorrect Syntax Near ","

I am a SQL nube, so be gentle with me.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35200466
The paranthesis are wrong it should be:
     WHERE  tUnits <> 0
            AND tActivityType = 1
            AND tDate BETWEEN @FromDate AND @ToDate
0
 
LVL 9

Accepted Solution

by:
kaminda earned 125 total points
ID: 35204342
It should change as below as acperkins correctly points out there is a additional paranthesis in the where clause
FUNCTION [dbo].[fnGetAxTransaction]
(
	@FromDate DateTime,
    @ToDate DateTime
)


RETURNS  TABLE
AS 
RETURN
(
SELECT     TOP (100) PERCENT tDate, tActivityType, tUnits AS Total
FROM         dbo.AxTransaction
WHERE     tUnits <> 0 AND tActivityType = 1 AND (tDate >= @FromDate AND tDate <= @ToDate)
)

Open in new window

0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question