Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Location
    Erehwon
    Posts
    18

    Unhappy Unanswered: select month/year when range spans year

    I'm using PHP with SQLServer2k to create a page containing monthly counts of episodes at a facility occurring between two user selected month/year combinations. For instance, the user could select 10/2003 and 2/2004 and facility X and get a line for each month showing the count of episodes occuring in that month.

    The problem is that the episode date is stored in three integer fields (epiday, epimonth, epiyear) and I'm having a terrible time getting them into a format where I can use them in a between statement.

    I've tried evaluating the parts of the episode date seperately like:

    Code:
    where 
    (epimonth>=10 and epiyear=2003)
    or
    (epimonth<=2 and epiyear=2004)
    and that works, but what happens when someone wants to see from 10/2002 to 2/2004?

    Any suggestions on the best way to do this?

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You can try this, I think it's close enough:

    Code:
    create procedure sp_monthlycounts (
       @DateStart varchar(10),
       @DateEnd varchar(10) )
    as
       declare @DS datetime, @DE datetime
       set @DS = replace(@DateStart, '/', '/01/')
       set @DE = dateadd(day, -1, dateadd(month, 1, cast(replace(@DateEnd, '/', '/01/'))))
    
       select * from <your_table> 
          where cast(cast(epiyear as char(4)) + 
             right('00'+cast(epimonth as char(2)), 2) + 
             right('00'+cast(epiday as char(2)), 2) as datetime) 
             between @DS and @DE

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    USE Northwind
    GO
    
    CREATE TABLE myTable99(epiday int, epimonth int, epiyear int, Facility varchar(10), Episode varchar(50))
    GO
    
    INSERT INTO myTable99(epimonth, epiday,  epiyear, Facility, Episode)
    SELECT 1,1,2003, 'Home', 'Had a Margaritta' 		UNION ALL
    SELECT 1,1,2003, 'Home', 'Fell a sleep'			UNION ALL
    SELECT 1,2,2004, 'Home', 'Had a Margaritta' 		UNION ALL
    SELECT 1,2,2004, 'Home', 'Had another Margaritta' 	UNION ALL
    SELECT 1,2,2004, 'Home', 'Fell a sleep' 		UNION ALL
    SELECT 1,3,2004, 'Home', 'Had a Margaritta' 		UNION ALL
    SELECT 1,3,2004, 'Home', 'Had another Margaritta' 	UNION ALL
    SELECT 1,3,2004, 'Home', 'Had another Margaritta' 	UNION ALL
    SELECT 1,3,2004, 'Home', 'Passed out'
    GO
    
    CREATE FUNCTION udf_IntToDate(@m int, @d int, @y int)
    RETURNS datetime
    BEGIN
    	DECLARE @dt datetime
    	SELECT 	@dt = CONVERT(datetime,  
    		  RIGHT('00'+CONVERT(char(2),@m),2)
    		+ '-'
    		+ RIGHT('00'+CONVERT(char(2),@d),2)
    		+ '-'
    		+ RIGHT('00'+CONVERT(char(4),@y),4))
    RETURN @dt
    END
    GO
    
    DECLARE @d1 datetime, @d2 datetime
    SELECT @d1 = '1/1/2000', @d2 = '1/1/2005'
    
    SELECT * 
      FROM myTable99 
     WHERE dbo.udf_IntToDate(epimonth ,epiday, epiyear) BETWEEN @d1 AND @d2
    
    SELECT @d1 = '1/1/2003', @d2 = '12/31/2003'
    SELECT * 
      FROM myTable99 
     WHERE dbo.udf_IntToDate(epimonth ,epiday, epiyear) BETWEEN @d1 AND @d2
    GO
     
    DROP FUNCTION udf_IntToDate	
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    If you have a lot of rows in the table, you might be able to add a computed column onto the table, then index the date off of that column. This will only work if you have control over the schema, and are sure that you will not break any insert statements in your code.

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Man, I can only imagine how many you usually have by the end of the month!!! But I'm not too far behind either

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I have Jimmy Buffets recepie at my blog...

    but my real recipie...

    Get tall glass..

    Fill with Ice..

    Fill with to-fill-ya (that's right, to the top)

    Swirl Ice with finger (Melt some ice)

    Big chug

    Top off with mix

    Repeat
    Last edited by Brett Kaiser; 02-25-04 at 15:59.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •