Solved

simple date range prompt in sql view

Posted on 2011-03-22
6
694 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 41

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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

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

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

688 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