SQL Date Parameter

[Tarrant64]

Good Morning!

Example table:

Columns are Date, Free, Total, Drive (tracking disk space)

Jul 9 2009 7:40AM 195 2263 F:\
Jul 9 2009 7:40AM 19 200 G:\
Jul 9 2009 7:40AM 54 684 I:\
Jul 9 2009 7:40AM 3 4 K:\
Jul 9 2009 4:40AM 196 2263 F:\
Jul 9 2009 4:40AM 19 200 G:\
Jul 9 2009 4:40AM 54 684 I:\
Jul 9 2009 4:40AM 3 4 K:\

Trying to create a report where I can choose the the date/time (like above 7:40 or 4:40) and then choose the Drive and have it return the Free and Total values.

Do I have to create a query first to pull the different date values, and then use that query to pull the other information? SQL Reporting Services doesn't do some of the things it looks like it would and I've had to adjust queries to make things work.

I know this is probably an easy one, but I'm not able to wrap my head around it this morning.

 

[imported_Dhaval00]

You probably want to create a drillthrough report (or a subreport)? Look at the samples here: http://www.gotreportviewer.com/

 

[KIAman]

Depends on how you want to allow user input and what front end you are using. You can do this in 3 simple queries.

1. Select distinct Date from Table -- provide list of dates for user to choose

2. Select Drive from Table where Date = @Date -- take user date input as parameter

3. Select Free, Total from Table where Date = @Date and Drive = @Drive -- combine all user inputs

 

[nakedfrog]

Look into the datepart function.

 

[Tarrant64]

Originally posted by: KIAman
Depends on how you want to allow user input and what front end you are using. You can do this in 3 simple queries.

1. Select distinct Date from Table -- provide list of dates for user to choose

2. Select Drive from Table where Date = @Date -- take user date input as parameter

3. Select Free, Total from Table where Date = @Date and Drive = @Drive -- combine all user inputs

I pretty much went this route. I ended up creating 3 data sets similar to the queries above. All is good now.


Thanks everyone!

nakedFrog: I did do the datepart for a previous project I was working on, thanks.