Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2009
    Posts
    17

    Unanswered: how to:select in date dan time range

    hi.
    i wanna create query result to showing 24 hours transaction in a day.

    i already group my transacation data by date and time into a new table (table_1), but since no all data (from time 00:00 to 24:00) in transaction, the table1 result like this :

    Table_1
    Code:
    GroupDate  GroupTime   TotalHits
    2009-05-01 07:00       5
    2009-05-01 08:00       4
    2009-05-01 09:00       2
    2009-05-01 12:00       6
    2009-05-01 13:00       1
    2009-05-01 15:00       3
    expectation Result is :
    Code:
    GroupDate  GroupTime   TotalHits
    2009-05-01 00:00       0
    2009-05-01 01:00       0
    .
    .
    .
    2009-05-01 07:00       5
    2009-05-01 08:00       4
    2009-05-01 09:00       2
    2009-05-01 10:00       0
    2009-05-01 11:00       0
    2009-05-01 12:00       6
    2009-05-01 13:00       1
    2009-05-01 14:00       0
    2009-05-01 15:00       3
    2009-05-01 16:00       0
    2009-05-01 17:00       0
    .
    ..
    2009-05-01 23:00       0

    must i use union ???

    Code:
    select groupdate,grouptime,totalhits from table1
    UNION
      select  ????????
    thank's
    Jigu

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    how about an hour table that you would join to? This table would have entries for each hour of the day 0 - 23. Then your SQL that creates the summarizations would easiy have 0 count for hours when there was no activity.
    Dave

  3. #3
    Join Date
    Nov 2009
    Posts
    17
    Quote Originally Posted by dav1mo View Post
    how about an hour table that you would join to? This table would have entries for each hour of the day 0 - 23. Then your SQL that creates the summarizations would easiy have 0 count for hours when there was no activity.
    Dave
    Thank's Dave for your reply,
    But I don't wanna use hour table, and
    actually i have another case... same like this one., but related to "sales report in range of date".

    the problem is : i must create query to show sales report in range of date, and they wanna show 0 (Zero) if no transcaction in a day.
    the result like this
    Code:
    Date		Sales Qty
    2009-01-01		5
    2009-01-02		7
    2009-01-03		0		<--- no selling transaction
    2009-01-04		3
    2009-01-05		5
    .
    .
    .
    .
    .
    2009-01-30		8
    if my 1st problem sovle then the 2nd problem is solve too.

    thank's
    Jigujigu

  4. #4
    Join Date
    Oct 2009
    Posts
    27
    Use the script
    --------------------

    DECLARE @CurrentDate VARCHAR(20)
    SET @CurrentDate=CONVERT(VARCHAR,GETDATE(),101)
    DECLARE @i INT
    SET @i = 0
    DECLARE @Time VARCHAR(10)
    DECLARE @Count INT
    SET @Count = 24
    CREATE TABLE #a
    (
    GDate DATETIME,
    GTime VARCHAR(20),
    TotalHits INT
    )
    WHILE @Count > 0
    BEGIN
    IF @i>9
    IF EXISTS(SELECT * FROM Table_1 WHERE CONVERT(VARCHAR,GroupDate,101)= @CurrentDate AND LEFT(CONVERT(VARCHAR,GroupDate,8),2)=@i)
    BEGIN
    INSERT INTO #a(GDate, GTime, TotalHits)
    SELECT
    CONVERT(VARCHAR,GroupDate,23) AS GroupDate,
    LEFT(CONVERT(VARCHAR,GroupDate,8),5) AS GroupTime,
    TotalHits
    FROM Table_1
    WHERE CONVERT(VARCHAR,GroupDate,101)= @CurrentDate
    AND LEFT(CONVERT(VARCHAR,GroupDate,8),2)=@i
    END
    ELSE
    BEGIN
    SET @Time = CONVERT(VARCHAR,@i)+':00'
    INSERT INTO #a(GDate, GTime, TotalHits)
    VALUES(CONVERT(VARCHAR,@CurrentDate,23),@Time,0)
    END
    ELSE
    IF EXISTS(SELECT * FROM Table_1 WHERE CONVERT(VARCHAR,GroupDate,101)= @CurrentDate AND LEFT(CONVERT(VARCHAR,GroupDate,8),2)=@i)
    BEGIN
    INSERT INTO #a(GDate, GTime, TotalHits)
    SELECT
    CONVERT(VARCHAR,GroupDate,23) AS GroupDate,
    LEFT(CONVERT(VARCHAR,GroupDate,8),5) AS GroupTime,
    TotalHits
    FROM Table_1
    WHERE CONVERT(VARCHAR,GroupDate,101)= @CurrentDate
    AND LEFT(CONVERT(VARCHAR,GroupDate,8),2)=@i
    END
    ELSE
    BEGIN
    SET @Time = '0'+CONVERT(VARCHAR,@i)+':00'
    INSERT INTO #a(GDate, GTime, TotalHits)
    VALUES(CONVERT(VARCHAR,@CurrentDate,23),@Time,0)
    END
    SET @Count=@Count-1
    SET @i=@i+1
    END
    SELECT
    CONVERT(VARCHAR,GDate,23) AS GroupDate,
    GTime AS GroupTime,
    TotalHits
    FROM #a
    DROP TABLE #a

  5. #5
    Join Date
    Nov 2009
    Posts
    17
    Thank's for your replay.

    But this script will be use or call from many client..
    is it will be problem? coz i create table #a ???
    if 2 or more client create #a, is it problem or not ?

    thank's
    Jigu

Posting Permissions

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