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) AND (reportsentdate <= '1/31/' + @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.0002005-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:

    http://www.sql-server-performance.com/fk_datetime.asp

    http://www.karaszi.com/SQLServer/info_datetime.asp

  • 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).

  • Try this query:

    SELECT     COUNT(*) AS Total

    FROM         records

    WHERE     year(reportsentdate) = @year

     

  • 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