help with date parameters
-
Hi all! I need some help with my date parameters. I hope I can explain this clearlyJ I have a report built like so:
Column_a Column_b Column_c
Jan
Feb
Mar
Apr
May
June
July
Aug
Sep
Oct
Nov
Dec
Each value in the column will have it’s own query based on a set of date parameters. However, I would like my user to enter only the year and have it populate my table. I would like for instance the January/Column_a query to look like this: (of course coded correctlyJ)
SELECT COUNT(*) AS Total
FROM records
WHERE (reportsentdate >= '1/1/' + @year)
How do I concantenate part of the date with the year as parameter?
Does anyone have any ideas for me?
Thanks!
kristin
Kristin
-
I think this will do all you want in a single query :
Declare @StartD as datetime
declare @EndD as datetime
declare @Year as int
set @Year = 2004
set @StartD = dateadd(YYYY, @Year - 1900, 0)
set @EndD = dateadd(YYYY, 1, @StartD)
Select @Startd, @EndD
--2004-01-01 00:00:00.000 2005-01-01 00:00:00.000
Select count(col1) as Tot1, Count(col2) as Tot2, datename(m, reportsentdate) as MonthName --,...
from dbo.Records
where reportsentdate >= @StartD and reportsentdate < @EndD
GROUP BY Month(reportsentdate)
-
Maybe I should have been more specific
Ignore Column_b and Column_c - as they are values derived in a completely different way. All I really need is a way to concatenate a string with the parameter. The string being the month and day and the parameter being the year. Looks like I made it sound more complicated than it is.
Kristin
-
It's simple really, but the fact is that it's much simpler to run a single query than to run 12. My 2 count() were mere exemple for the aggregates that need to me there.
Have you tried adapting this query to your needs?
-
That's what Remi gave you (and a little more).
Declare @StartD as datetime
declare @EndD as datetime
declare @Year as int
set @Year = 2004
set @StartD = dateadd(YYYY, @Year - 1900, 0)
set @EndD = dateadd(YYYY, 1, @StartD)
Select @StartD
-------------------------
2004-01-01 00:00:00.000
Select @EndD
-------------------------
2005-01-01 00:00:00.000
and please note the @EndD since that is what you really need.
-
Hey ron... I guess it's starting to sink in now
"where reportsentdate >= @StartD and reportsentdate < @EndD"
-
This is what I got to work.....in case anyone was interested
SELECT COUNT(*) AS Total
FROM records
WHERE (DATEPART(year, reportsentdate) = @year) AND (DATEPART(month, reportsentdate) = '01')
Kristin
-
Using the DATEPART function will not allow the optimizer to use the index on the reortsentdate column so your statement will force a table scan.
For more info on how to work with sql server dates you might want to look at these articles:
-
Yes ron k and again this will scan only 1 month at the time. Unless this is exactly what you (kristin) want it to do then I would urge you to try to understand my query as it is immensly more flexible and optimized that your stored proc.
-
Please run this in query analyser to see the resultset it presents :
Declare @StartD as datetime
declare @EndD as datetime
declare @Year as int
set @Year = 2004
set @StartD = dateadd(YYYY, @Year - 1900, 0)
set @EndD = dateadd(YYYY, 1, @StartD)
Select count(*) as TotalObjects, XType, min(datename(m, refdate)) as MonthName
from dbo.SysObjects
where refdate >= @StartD and refdate = @StartD and refdate < @EndD
GROUP BY Month(refdate)
ORDER BY Month(refdate)
This solution could be very easyly adapted to accomodate date ranges on multiple years
-
This is the result set it presented and I don't understand.... I am running a summary on activity for the year...my columns being the different activities and the months - the timeframe I'm not sure what this is....
1 D June
1 D August
6 D November
30 D December
2 P June
32 P July
1 PK June
1 PK July
2 PK August
3 PK November
3 PK December
2 U June
1 U July
4 U August
1 U September
4 U November
3 U December
Kristin
-
First column is the total count, 2nd is the type of the object (could be the activity), third is the month is was created (or month of the activity).
-
If you, as I hope, pursue this solution, make sure that the date column is indexed in the activity table. The best case would be to have it be in the front of the clustered index but I'm not sure it would be the best design in this case (since I have not idea of the rest of the db).
-
-
Whoops, the last part of my suggested query was left off. This is one I would try:
SELECT COUNT(*) AS Total
FROM records
WHERE year(reportsentdate) = @year
and month(reportsentdate) = 1
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply