SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    [SQL Server 2005] Group by between interval dates

    Hi all, I have one question for you.

    In SQL Server it's possible GROUP BY between interval dates ?

    I tried this, but I have error.

    Thanks in advance for your help.
    Code:
    SELECT 
    	[DATE-E],
                 [TYPE-V]
    FROM
    	dbo_40
    WHERE
    	1 = 1
    AND [DATA-E] BETWEEN '2012-07-04'
    AND '2012-07-31'
    AND ([TYPE-V] = '5')
    GROUP BY
    	[DATE-E] BETWEEN '2012-07-04'
    AND '2012-07-31,
    [TYPE-V];
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  2. #2
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    4,813
    Mentioned
    141 Post(s)
    Tagged
    0 Thread(s)
    I may be wrong, but I would think this is where you would want to use HAVING
    Code:
    CREATE TABLE #dbo_40
    (
      [DATA-E] datetime,
      [DATE-E] datetime,
      [TYPE-V] varchar(1)
    )
    
    INSERT INTO #dbo_40 VALUES ('2012-07-03', '2012-07-03','4')
    INSERT INTO #dbo_40 VALUES ('2012-07-04', '2012-07-04','4')
    INSERT INTO #dbo_40 VALUES ('2012-07-31', '2012-07-31','4')
    INSERT INTO #dbo_40 VALUES ('2012-08-01', '2012-08-01','4')
    INSERT INTO #dbo_40 VALUES ('2012-07-03', '2012-07-03','5')
    INSERT INTO #dbo_40 VALUES ('2012-07-04', '2012-07-04','5')
    INSERT INTO #dbo_40 VALUES ('2012-07-05', '2012-07-05','5')
    INSERT INTO #dbo_40 VALUES ('2012-07-06', '2012-07-06','5')
    INSERT INTO #dbo_40 VALUES ('2012-07-07', '2012-07-07','5')
    INSERT INTO #dbo_40 VALUES ('2012-07-08', '2012-07-08','5')
    INSERT INTO #dbo_40 VALUES ('2012-07-09', '2012-07-09','5')
    INSERT INTO #dbo_40 VALUES ('2012-07-10', '2012-07-10','5')
    INSERT INTO #dbo_40 VALUES ('2012-07-11', '2012-07-11','5')
    INSERT INTO #dbo_40 VALUES ('2012-07-12', '2012-07-12','5')
    INSERT INTO #dbo_40 VALUES ('2012-07-13', '2012-07-13','5')
    INSERT INTO #dbo_40 VALUES ('2012-07-14', '2012-07-14','5')
    INSERT INTO #dbo_40 VALUES ('2012-07-15', '2012-07-15','5')
    INSERT INTO #dbo_40 VALUES ('2012-07-16', '2012-07-16','5')
    INSERT INTO #dbo_40 VALUES ('2012-07-17', '2012-07-17','5')
    INSERT INTO #dbo_40 VALUES ('2012-07-18', '2012-07-18','5')
    INSERT INTO #dbo_40 VALUES ('2012-07-19', '2012-07-19','5')
    INSERT INTO #dbo_40 VALUES ('2012-07-20', '2012-07-20','5')
    INSERT INTO #dbo_40 VALUES ('2012-07-21', '2012-07-21','5')
    INSERT INTO #dbo_40 VALUES ('2012-07-22', '2012-07-22','5')
    INSERT INTO #dbo_40 VALUES ('2012-07-23', '2012-07-23','5')
    INSERT INTO #dbo_40 VALUES ('2012-07-24', '2012-07-24','5')
    INSERT INTO #dbo_40 VALUES ('2012-07-25', '2012-07-25','5')
    INSERT INTO #dbo_40 VALUES ('2012-07-26', '2012-07-26','5')
    INSERT INTO #dbo_40 VALUES ('2012-07-27', '2012-07-27','5')
    INSERT INTO #dbo_40 VALUES ('2012-07-28', '2012-07-28','5')
    INSERT INTO #dbo_40 VALUES ('2012-07-29', '2012-07-29','5')
    INSERT INTO #dbo_40 VALUES ('2012-07-30', '2012-07-30','5')
    INSERT INTO #dbo_40 VALUES ('2012-07-31', '2012-07-31','5')
    INSERT INTO #dbo_40 VALUES ('2012-08-01', '2012-08-01','5')
    INSERT INTO #dbo_40 VALUES ('2012-07-03', '2012-07-03','6')
    INSERT INTO #dbo_40 VALUES ('2012-07-04', '2012-07-04','6')
    INSERT INTO #dbo_40 VALUES ('2012-07-31', '2012-07-31','6')
    INSERT INTO #dbo_40 VALUES ('2012-08-01', '2012-08-01','6')
    
    
    SELECT 
    	[DATE-E],
        [TYPE-V]
    FROM
    	#dbo_40
    WHERE
    	1 = 1
    AND [DATA-E] BETWEEN '2012-07-04'
    AND '2012-07-31'
    AND ([TYPE-V] = '5')
    GROUP BY
             [DATE-E], [TYPE-V]
    HAVING [DATE-E] BETWEEN '2012-07-04'
    AND '2012-07-31';
    
    DROP TABLE #dbo_40
    Be sure to congratulate xMog on earning April's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    cp. you don't need that HAVING clause, since it merely repeats your WHERE clause
    Code:
    SELECT [DATE-E]
         , [TYPE-V]
      FROM dbo_40
     WHERE 1 = 1
       AND [DATA-E] BETWEEN '2012-07-04' AND '2012-07-31'
       AND [TYPE-V] = '5'
    GROUP 
        BY [DATE-E]
         , [TYPE-V]
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    4,813
    Mentioned
    141 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    cp. you don't need that HAVING clause, since it merely repeats your WHERE clause
    Code:
    SELECT [DATE-E]
         , [TYPE-V]
      FROM dbo_40
     WHERE 1 = 1
       AND [DATA-E] BETWEEN '2012-07-04' AND '2012-07-31'
       AND [TYPE-V] = '5'
    GROUP 
        BY [DATE-E]
         , [TYPE-V]
    I thought that too, until I ran the query on a temp table that only had DATA-E and TYPE-V, then I got the error, DATE-E does not exist. DATE-E is in the BETWEEN statement he had in the GROUP BY, so I think HAVING is indeed needed.
    Be sure to congratulate xMog on earning April's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  5. #5
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    thanks for replies, but I cannot group by between interval dates...

    Code:
    SELECT [DATE-E]
         , [TYPE-V]
      FROM dbo_40
     WHERE 1 = 1
       AND [DATE-E] BETWEEN '2012-07-04' AND '2012-07-31'
       AND [TYPE-V] = '5'
    GROUP 
        BY [DATE-E]
         , [TYPE-V]
    HAVING [DATE-E] BETWEEN '2012-07-04'
    AND '2012-07-31';
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  6. #6
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    4,813
    Mentioned
    141 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cms9651 View Post
    thanks for replies, but I cannot group by between interval dates...
    Can you provide sample data and the expected result?
    Be sure to congratulate xMog on earning April's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  7. #7
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    This is my output:
    Code:
    DATE-E			TYPE-V
    2012-07-04 00.00.00	5
    2012-07-05 00.00.00	5
    2012-07-06 00.00.00	5
    2012-07-08 00.00.00	5
    2012-07-09 00.00.00	5
    2012-07-10 00.00.00	5
    2012-07-11 00.00.00	5
    2012-07-12 00.00.00	5
    2012-07-13 00.00.00	5
    2012-07-14 00.00.00	5
    2012-07-15 00.00.00	5
    2012-07-16 00.00.00	5
    2012-07-17 00.00.00	5
    2012-07-18 00.00.00	5
    2012-07-19 00.00.00	5
    2012-07-20 00.00.00	5
    2012-07-23 00.00.00	5
    2012-07-24 00.00.00	5
    2012-07-25 00.00.00	5
    2012-07-26 00.00.00	5
    2012-07-27 00.00.00	5
    2012-07-28 00.00.00	5
    2012-07-29 00.00.00	5
    2012-07-30 00.00.00	5
    I need this:
    Code:
    DATE-E-Start		DATE-E-End		TYPE-V	Total
    2012-07-04 00.00.00	2012-07-30 00.00.00	5	120
    thank you...
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  8. #8
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    4,813
    Mentioned
    141 Post(s)
    Tagged
    0 Thread(s)
    Okay, so something similar to this (may need to replace COUNT(*) with SUM([TYPE-V])
    Code:
    CREATE TABLE #dbo_40
    (
      [DATE-E] datetime,
      [TYPE-V] varchar(1)
    )
    
    INSERT INTO #dbo_40 VALUES ('2012-07-03','4')
    INSERT INTO #dbo_40 VALUES ('2012-07-04','4')
    INSERT INTO #dbo_40 VALUES ('2012-07-31','4')
    INSERT INTO #dbo_40 VALUES ('2012-08-01','4')
    INSERT INTO #dbo_40 VALUES ('2012-07-03','5')
    INSERT INTO #dbo_40 VALUES ('2012-07-04','5')
    INSERT INTO #dbo_40 VALUES ('2012-07-05','5')
    INSERT INTO #dbo_40 VALUES ('2012-07-06','5')
    INSERT INTO #dbo_40 VALUES ('2012-07-07','5')
    INSERT INTO #dbo_40 VALUES ('2012-07-08','5')
    INSERT INTO #dbo_40 VALUES ('2012-07-09','5')
    INSERT INTO #dbo_40 VALUES ('2012-07-10','5')
    INSERT INTO #dbo_40 VALUES ('2012-07-11','5')
    INSERT INTO #dbo_40 VALUES ('2012-07-12','5')
    INSERT INTO #dbo_40 VALUES ('2012-07-13','5')
    INSERT INTO #dbo_40 VALUES ('2012-07-14','5')
    INSERT INTO #dbo_40 VALUES ('2012-07-15','5')
    INSERT INTO #dbo_40 VALUES ('2012-07-16','5')
    INSERT INTO #dbo_40 VALUES ('2012-07-17','5')
    INSERT INTO #dbo_40 VALUES ('2012-07-18','5')
    INSERT INTO #dbo_40 VALUES ('2012-07-19','5')
    INSERT INTO #dbo_40 VALUES ('2012-07-20','5')
    INSERT INTO #dbo_40 VALUES ('2012-07-21','5')
    INSERT INTO #dbo_40 VALUES ('2012-07-22','5')
    INSERT INTO #dbo_40 VALUES ('2012-07-23','5')
    INSERT INTO #dbo_40 VALUES ('2012-07-24','5')
    INSERT INTO #dbo_40 VALUES ('2012-07-25','5')
    INSERT INTO #dbo_40 VALUES ('2012-07-26','5')
    INSERT INTO #dbo_40 VALUES ('2012-07-27','5')
    INSERT INTO #dbo_40 VALUES ('2012-07-28','5')
    INSERT INTO #dbo_40 VALUES ('2012-07-29','5')
    INSERT INTO #dbo_40 VALUES ('2012-07-30','5')
    INSERT INTO #dbo_40 VALUES ('2012-07-31','5')
    INSERT INTO #dbo_40 VALUES ('2012-08-01','5')
    INSERT INTO #dbo_40 VALUES ('2012-07-03','6')
    INSERT INTO #dbo_40 VALUES ('2012-07-04','6')
    INSERT INTO #dbo_40 VALUES ('2012-07-31','6')
    INSERT INTO #dbo_40 VALUES ('2012-08-01','6')
    
    
    SELECT 
    	MIN([DATE-E]) AS 'DATE-E Start',
    	MAX([DATE-E]) AS 'DATE-E End',
        [TYPE-V],
        COUNT(*) AS 'Total'
    FROM
    	#dbo_40
    WHERE
    	1 = 1
    AND [DATE-E] BETWEEN '2012-07-04'
    AND '2012-07-31'
    AND ([TYPE-V] = '5')
    GROUP BY
    	[TYPE-V]
    
    DROP TABLE #dbo_40
    Also, it will not necessary write 2012-07-04 and 2012-07-31 as the start and end dates, it will write the lowest date and the highest date it found in your between range, so if you don't have entries for 2012-07-31, you may see 2012-07-30 as the end date (you can correct this by hard coding the dates like so)
    Code:
    SELECT 
    	'2012-07-04' AS 'DATE-E Start',
    	'2012-07-31' AS 'DATE-E End',
        [TYPE-V],
        COUNT(*) AS 'Total'
    FROM
    	#dbo_40
    WHERE
    	1 = 1
    AND [DATE-E] BETWEEN '2012-07-04'
    AND '2012-07-31'
    AND ([TYPE-V] = '5')
    GROUP BY
    	[TYPE-V]
    Be sure to congratulate xMog on earning April's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  9. #9
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    thanks a lot!


Bookmarks

Posting Permissions

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