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
    5,118
    Mentioned
    152 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

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 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]
    rudy.ca | @rudydotca
    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
    5,118
    Mentioned
    152 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.

  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
    5,118
    Mentioned
    152 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?

  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
    5,118
    Mentioned
    152 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]

  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
  •