Re: DateTime

From: Steve Kass (skass_at_drew.edu)
Date: 07/01/04

  • Next message: Italy: "Run same query" 
    Date: Thu, 01 Jul 2004 00:31:32 -0400

    If by "select some stuff per week" you mean calculate aggregates (sum,
    min, max, count) per week, you can do something like this:

    declare @baseSunday datetime
    set @baseSunday = '19000107'

    select
      dateadd(week, datediff(week, @baseSunday, OrderDate), @baseSunday)
        as WeekBeginning,
      count(OrderID) as numOrdersThisWeek
    from Northwind..Orders
    group by dateadd(week, datediff(week, @baseSunday, OrderDate), @baseSunday)
    order by dateadd(week, datediff(week, @baseSunday, OrderDate), @baseSunday)
    go

    If you want result rows for weeks that have no corresponding data in
    your table, you can create an auxiliary table with all the Sundays you
    would need and do

    select
      Sunday as WeekBeginning,
      count(OrderID) as numOrdersThisWeek
    from SundayTable left outer join yourDataTable
    on yourDataTable.OrderDate >= Sunday
    and yourDataTable.OrderDate < DateAdd(week, 1, Sunday)
    group by Sunday
    order by Sunday

    A search of groups.google.com for sqlserver+group+week will probably
    give you some other ideas.

    Steve Kass
    Drew University

    Crazy Pete wrote:

    >PLZ help me!!!
    >
    >i got to make a access frontend for a MSSQL server.
    >i need to select some stuff per week how can i make thet?? PLZZ HELP I GOT
    >TO KNOW IT TOMAROW
    >
    >TNX
    >
    >Peter
    >
    >
    >
    >

  • Next message: Italy: "Run same query"