August 1, 2011 at 3:57 pm
Hi Everyone,
I'm running a query, and trying to set a prompt for a start date and a end date using @. I already have the normal query finished that I've included, can anyone tell me how to fix my prompt problem?
SELECT accountID, COUNT(*) AS NumOrder, CASE WHEN COUNT(reason) <= 5 THEN 'A. 1-5' ELSE CASE WHEN COUNT(reason)
<= 10 THEN 'B. 6-10' ELSE CASE WHEN COUNT(reason) <= 50 THEN 'C. 11-50' ELSE CASE WHEN COUNT(reason)
<= 100 THEN 'D. 51-100' ELSE CASE WHEN COUNT(reason) <= 500 THEN 'E. 101-500' ELSE CASE WHEN COUNT(reason)
<= 1000 THEN 'F. 501-1000' ELSE 'G. 1001 or more' END END END END END END AS CountGroup, COUNT(reason) / 5.0 AS GroupCalc, CEILING(COUNT(reason) / 5.0)
* 5 AS MaxInGroup, CEILING(COUNT(reason) / 5.0) * 5 - 4 AS MinInGroup, CAST(CEILING(COUNT(reason) / 5.0) * 5 - 4 AS varchar(15))
+ '-' + CAST(CEILING(COUNT(reason) / 5.0) * 5 AS varchar(15)) AS TextGroupName, [Date]
FROM [Sandbox].dbo.[2011.07.01_all]
WHERE ([Date] BETWEEN '= @StartDate' AND '@EndDate')
GROUP BY accountID, [date]
ORDER BY NumOrder DESC
Thank you in advance
August 1, 2011 at 5:17 pm
What do you mean by "set a prompt for a start date and a end date"?
What programming language do you use for your app development?
How do you plan to use this query? Inside a stored procedure?
Too many questions at the moment to really help you any further.
August 2, 2011 at 7:40 am
Sounds like the OP is used to working with Access where if you write a query like that and the parameter is not passed to the query it will create an input box. SQL does not have a gui component like Access and as such this type of thing has to be done in your application or you have to add the variable declaration in your script.
declare @StartDate datetime, @EndDate datetime
set @StartDate = [myDateHere]
set @EndDate = [myDateHere]
Then your select will work. As Lutz said it is hard to know how to help because we don't know how or where you are trying to use this.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 2, 2011 at 1:03 pm
I'm trying to so this on SSRS, and I know there should be a way to set it to allow the user to input the date range that they wish to view.
Thank you
August 2, 2011 at 1:10 pm
Add parameters.
August 3, 2011 at 6:03 am
Hi annie.alexander - Ninja's_RGR'us is right, but I usually find when I write my code and include something like [Date] between @StartDate and @EndDate SSRS usually gets the parameters started for me...though, I think it starts everything out as a string.
To change that or add parameters from scratch, open your report in SSRS in Layout or Dataset mode and click Report > Report Parameters. (The Report menu option is not available once you move to Preview mode. I should also say I work in 2005, so I'm not sure if there's a difference in other versions.)
Good luck!
Jessica
August 3, 2011 at 11:26 am
annie.alexander (8/2/2011)
I'm trying to so this on SSRS, and I know there should be a way to set it to allow the user to input the date range that they wish to view.
1. Write the SQL query "normally" - that is, declare your variables before your SELECT statement:
DECLARE @StartDate DATETIME, @EndDate DATETIME
(you could also use the DATE datatype if you're using SQL Server 2008 and so choose)
then use them in your WHERE clause like this:
WHERE [Date] BETWEEN @StartDate AND @EndDate
2. When you define a dataset in SSRS using this query, SSRS automatically creates report parameters with the same names as your variables and maps them to the query parameters. You then configure the report parameters, either using another query to generate the values for the drop-down list or hard-coding them in the report parameter configuration. A nice thing about a date report parameter in SSRS is that the "calendar" control (i.e., the little calendar that pops up so users can select the date values) is used by default.
BTW, there are some potential "gotchas" involved in using BETWEEN with dates, especially with DATETIME data type - these are well-documented on the web and should be easy to find with a Google search. Be sure you understand the nuances of this usage, or your results might not be what you expect!
Jason Wolfkill
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply