Date Range Parameters (between)

Question

Date Range Parameters (between)

mbrady5

SSCrazy

Points: 2835
More actions
October 7, 2009 at 7:25 am

#211700

Good morning...
Is there a way in creating a report to set date parameters to query a date range? I am assuming that on the date field I would use the between function..??
Thanks

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

I don't see how this would be different that a report with 2 date parameters and the report query uses a between of those dates.

Am I missing the mark?

CEWII

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

I'm not sure what your question is? DO you want the Date parameter to be filtered to only allow a certain range of dates to be selected? Like you can only query orders made on dates within the last 90 days?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

mbrady5 SSCrazy Points: 2835 More actions · Answer 3

Thanks... what I would like to do is just add two inputs in the report that add a start date for the report and an end date. This would be bsed on my 'Date Quoted' field. Just not sure how to write the code.

SELECT Record_Id, DateCreated, LastUpdated, SubmitCount, [Date Quoted], JobStart, [Company Name], [First Name], [Last Name], Address, City, State, Zip,

[Phone Number], [Sales Rep], Zone, [Haul Rate Quoted], [Disposal Rate Quoted], [Delivery Rate Quoted], HookTruck, TonIncludedFlatRate,

DisposalSellTotal, [Flat Rate Price], BaseAmtHaulPlus, BasAmtFlatRate, FuelSurcharge, FuelSurchargeFlat, EnviroFee, EnviroFeeFlat, FeeTotal,

FeeTotalFlat, TotalSellPriceHaulPlus, TotalSellPriceFlat, TonEstimateHP, Status, [Sold Date], [CWS Division], [Disposal Destination], [Resi Comm],

[Project Type], [Waste Type], OverageCharge, OverageTons, Notes, Email, [Campaign ID], Rent, Competitor, [Overall Margin], OverallMarginFlatRate,

[Container Size], [Billing Address], [Billing City], [Billing State], [Billing Zip Code], CallBackInfo, RO_ID, ShapeID, Latitude, Longitude, LostCustReason,

HearAboutCWS

FROM New_Call

WHERE ([Sales Rep] IN (@salesrep)) AND (Status IN (@status)) AND ([CWS Division] IN (@division))

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

I think this is what you are looking for:

SELECT

Record_Id,

DateCreated,

LastUpdated,

SubmitCount,

[Date Quoted],

JobStart,

[Company Name],

[First Name],

[Last Name],

Address,

City,

State,

Zip,

[Phone Number],

[Sales Rep],

Zone,

[Haul Rate Quoted],

[Disposal Rate Quoted],

[Delivery Rate Quoted],

HookTruck,

TonIncludedFlatRate,

DisposalSellTotal,

[Flat Rate Price],

BaseAmtHaulPlus,

BasAmtFlatRate,

FuelSurcharge,

FuelSurchargeFlat,

EnviroFee,

EnviroFeeFlat,

FeeTotal,

FeeTotalFlat,

TotalSellPriceHaulPlus,

TotalSellPriceFlat,

TonEstimateHP,

Status,

[Sold Date],

[CWS Division],

[Disposal Destination],

[Resi Comm],

[Project Type],

[Waste Type],

OverageCharge,

OverageTons,

Notes,

Email

[Campaign ID],

Rent,

Competitor,

[Overall Margin],

OverallMarginFlatRate,

[Container Size],

[Billing Address],

[Billing City],

[Billing State],

[Billing Zip Code],

CallBackInfo,

RO_ID,

ShapeID,

Latitude,

Longitude,

LostCustReason,

HearAboutCWS

FROM

New_Call

WHERE

([Sales Rep] IN (@salesrep)) AND

(Status IN (@status)) AND

([CWS Division] IN (@division)) And

(

[Date Quoted] >= @start_date And

/*

using the dateadd function to get the start of the next day

from the date that is entered. That is because with DateTime/

SmallDateTime datatype SQL Server always adds 00:00:00 so if

1/1/2009 is entered SQL Server makes it 1/1/2009 00:00:00 which

means anything quoted after midnight will not show

*/

[Date Quoted] < DateAdd(Day, datediff(Day, 0, @end_date) + 1, 0)

)

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

Excellent tip/reminder on the "next day" thing, Jack.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mbrady5 SSCrazy Points: 2835 More actions · Answer 6

Thats it....thanks! Is there a way to insert date picker controls in the report?

Jack Corbett SSC Guru Points: 184394 More actions · Answer 7

mbrady5 (10/7/2009)
Thats it....thanks! Is there a way to insert date picker controls in the report?

If you add parameters to the report and select the Date datatype for them then SSRS will automatically add the Date Picker control to the parameters.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jack Corbett SSC Guru Points: 184394 More actions · Answer 8

Jeff Moden (10/7/2009)
Excellent tip/reminder on the "next day" thing, Jack.

Thanks. I used to be a BETWEEN guy, but about a year ago I had a discussion here on SSC with you and, I think, Gail about the issues with that and ever since then I use >= and < and, if the situation calls for it, make sure the end date is the start of the next day. Fortunately I can still be taught.:-P

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

mbrady5 SSCrazy Points: 2835 More actions · Answer 9

Thank you ALL so much.....

I have now learned something new. Again, I am all set with this topic. Thank you

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 10

Jack, wouldn't you want to increment the @enddate value to the next day in the variable itself, and then just use < @enddate instead of two functions in the WHERE? That way you just do the calculation once?

Maybe you create a third variable to adjust the date but retain the user input, depends on whether you're displaying @enddate in the report output. (might confuse people if you display the day after the one they entered)

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

Jack Corbett SSC Guru Points: 184394 More actions · Answer 11

jcrawf02 (10/9/2009)
Jack, wouldn't you want to increment the @enddate value to the next day in the variable itself, and then just use < @enddate instead of two functions in the WHERE? That way you just do the calculation once?
Maybe you create a third variable to adjust the date but retain the user input, depends on whether you're displaying @enddate in the report output. (might confuse people if you display the day after the one they entered)

Good points. Some of the implementation would depend on where the query is. If I'm running in a stored procedure I probably do create a local variable and assign the next data value to it. I think it makes it more readable. If the query is embedded in the report, I probably do the calculation in SSRS when assigning the report parameter to the query parameter.

I'm pretty sure that SQL Server is still only going to do the calculation once because it is against a parameter, but it would definitely make the query easier to read if you did it with a local variable.

I'd never show the calculated value anywhere. A user is going to say, I want to see last month's orders so they will enter 9/1/2009 and 9/30/2009 and they want that to be inclusive. Showing them an end date of 10/1/2009 anywhere will just confuse them.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

freezea SSC Enthusiast Points: 195 More actions · Answer 12

Hi, mbrady5.

Maybe you can have a try of RAQ Report[/url]. I think you can realize what you want with it.

Welcome to my blog[/url].:P

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 13

freezea (10/11/2009)
Hi, mbrady5.
Maybe you can have a try of RAQ Report[/url]. I think you can realize what you want with it.

Is that you Dassin? Are you still spamming RAQ or is this a new spammer? 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mstjean Hall of Fame Points: 3310 More actions · Answer 14

Jack Corbett (10/7/2009)
Jeff Moden (10/7/2009)
Excellent tip/reminder on the "next day" thing, Jack.
Thanks. I used to be a BETWEEN guy, but about a year ago I had a discussion here on SSC with you and, I think, Gail about the issues with that and ever since then I use >= and < and, if the situation calls for it, make sure the end date is the start of the next day. Fortunately I can still be taught.:-P

In addition to setting the end date parm to the start of the next day, I like to condition the start date parm by chopping the time component off [setting to 00:00:00 via DATEADD(day,datediff(day,0,@startdateparm),0) ]. I do this any time I have parms setting a date range restriction; the comfortable side effect is that you don't have to worry whether the column being tested against has a time component or not, you use the same code.

Cursors are useful if you don't know SQL