Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2004
    Location
    Montreal, Canada
    Posts
    151

    Unanswered: Aggragate by date time

    hello Everyone,

    I have a little issue here that i am trying to figure out and would realy aprecciate some help,


    Here his my table below :

    Date type description StockTotal
    ------------------------ ----- ------------- -----------
    2010-11-01 00:02:00.000 E P8 150
    2010-11-01 00:02:00.000 E P5 426
    2010-11-01 00:02:00.000 E P9 626
    2010-11-01 00:07:00.000 E P8 150
    2010-11-01 00:07:00.000 E P9 626
    2010-11-01 00:07:00.000 E P5 425

    what i am trying to achieve his this, Here his the example :

    Date type description StockTotal
    ------------------------ ----- ------------- -----------
    2010-11-01 00:02:00.000 E P5 426
    2010-11-01 00:02:00.000 E P9 626
    2010-11-01 00:07:00.000 E P8 150

    so basicaly i am trying to find the Max (Stocktotal) for 1 specific day, but my dates are by minute..... so how do i do it by Day...

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    use
    Code:
    convert(datetime, convert([date], varchar(10), 101))
    so you can group by the date column ("date" is a reserved word in SQL Server, by the way). You will have to use that as a subquery, in order to retrieve the rest of the row.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by hillcat View Post
    i am trying to find the Max (Stocktotal) for 1 specific day
    Code:
    SELECT MAX(Stocktotal) AS max_stocktotal
      FROM daTable
     WHERE [Date] >= '2011-03-29'
       AND [Date]  < '2011-03-30'
    example showing how to retrieve the max for march 29th this year

    use "greater than or equal" on the specific date (with no time, or with 00:00:00 if you wish to add that, but it isn't necessary), and then "less than" on the following date

    this is the only efficient method (assuming [Date] has an index), because if you apply any function to [Date] then the query does a table scan instead of an index search
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2004
    Location
    Montreal, Canada
    Posts
    151
    but i still have to group by Date and Description

    the description can be different for the same date e.g: (description column contains P8 and P9) so i need to display for a specific date say 2010-11-01 the max total() for each description wich his P9 and P8.


    2010-11-01 E P9 626

    2010-11-01 E P8 626

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by hillcat View Post
    but i still have to group by Date and Description
    no, just by description

    here you go for november 1st last year --
    Code:
    SELECT description
         , MAX(Stocktotal) AS max_stocktotal
      FROM daTable
     WHERE [Date] >= '2010-11-01'
       AND [Date]  < '2010-11-02'
    GROUP
        BY description
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jan 2004
    Location
    Montreal, Canada
    Posts
    151
    here his my code and i get a error like this :

    Msg 8120, Level 16, State 1, Line 14
    Column 'agg_Stock.Heure' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.





    declare @startdate datetime
    declare @enddate datetime
    declare @type varchar(50)
    set @startdate = '2010-11-01'
    set @enddate = '2010-11-02'
    set @type = 'E'


    select

    convert(datetime, convert(varchar(10),a.Heure,101)) as Date1,
    b.type as type1,
    b.Description as description,
    max(a.stocktotal) as StockTotal

    from agg_Stock a

    inner join dim_Stationnement b
    on a.StationnementID = b.StationnementID

    where b.Actif = 1
    and a.Heure >= @startdate
    and a.Heure < @enddate
    and (b.Type in (Select value from fn_Split(@type,',')))


    group by b.Description
    --order by a.Heure
    Last edited by hillcat; 04-06-11 at 11:23.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    change this --
    Code:
    select
     
      convert(datetime, convert(varchar(10),a.Heure,101)) as Date1,
      b.type as type1,
      b.Description as description, 
      max(a.stocktotal) as StockTotal
      
      from agg_Stock a
      
      inner join dim_Stationnement b
      on a.StationnementID = b.StationnementID
      
      where b.Actif = 1 
      and a.Heure >= @startdate 
      and a.Heure < @enddate
      and (b.Type in (Select value from fn_Split(@type,','))) 
    
      
      group by b.Description
    to this --
    Code:
    SELECT b.type as type1
         , b.Description
         , MAX(a.stocktotal) AS StockTotal
      FROM agg_Stock AS a
    INNER 
      JOIN dim_Stationnement AS b
        ON b.StationnementID = a.StationnementID
       AND b.Actif = 1 
       AND b.Type in 
           ( SELECT value FROM fn_Split(@type,',') ) 
     WHERE a.Heure >= @startdate 
       AND a.Heure < @enddate
    GROUP 
        BY b.type 
         , b.Description
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jan 2004
    Location
    Montreal, Canada
    Posts
    151
    Ok let me take it from the start


    i have this below :


    Date type type desc Stocktotal
    ------------------------- ------- ----------- ------------
    1999-01-02 20:30:00.000 Ex E-1 1
    1999-01-02 20:30:00.000 Ex E-2 2
    1999-01-02 20:30:00.000 Ex E-3 3

    1999-01-02 21:00:00.000 Ex E-1 4
    1999-01-02 21:00:00.000 Ex E-2 1
    1999-01-02 21:00:00.000 Ex E-3 2

    1999-01-02 21:30:00.000 Ex E-1 2
    1999-01-02 21:30:00.000 Ex E-2 2
    1999-01-02 21:30:00.000 Ex E-3 2



    What i need to do his for each time slice e.g : lets take the first 3 records because their the same date and time. I need to some up the total of stock total so that would give me 6 and so on for each time slice than get the max() value for one day only

    so time slice one lets say 6

    second one 7

    third 6

    the answer would be 7

    so basicaly my table would end up like this


    Date type Stocktotal
    ------------------------- ------- -----------
    1999-01-02 00:00:00.000 Ex 7

  9. #9
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Now you changed the requirement
    Code:
    declare @startdate datetime
    declare @enddate datetime
    declare @type varchar(50)
    select  @startdate = '19990102'
           ,@enddate   = '19990203'
           ,@type      = 'Ex'
    
    select @startdate, @type, max(tstock) 
    from 
    (select [date], [type] ,'tstock'=sum([Stocktotal])
     from ( -- Test data
     select convert(datetime,                       /*
     date                      type  tdesc  Stotal  */
     ------------------------- ----- -----  ------
     '1999-01-02 20:30:00.000'),'Ex' ,'E-1',      1  union all select
     '1999-01-02 20:30:00.000' ,'Ex' ,'E-2',      2  union all select
     '1999-01-02 20:30:00.000' ,'Ex' ,'E-3',      3  union all select
    
     '1999-01-02 21:00:00.000' ,'Ex' ,'E-1',      4  union all select
     '1999-01-02 21:00:00.000' ,'Ex' ,'E-2',      1  union all select
     '1999-01-02 21:00:00.000' ,'Ex' ,'E-3',      2  union all select 
    
     '1999-01-02 21:30:00.000' ,'Ex' ,'E-1',      2  union all select
     '1999-01-02 21:30:00.000' ,'Ex' ,'E-2',      2  union all select
     '1999-01-02 21:30:00.000' ,'Ex' ,'E-3',      2 )testdata(
     [Date],[type],[type desc],[Stocktotal])
     where [Date]>=@startdate
       and [Date]< @enddate
       and [type]= @type
     group by [date],[type]
    )view1

Posting Permissions

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