Post reply

Grouping by date range

  • July 13, 2009 at 2:41 am

    #97682

    Hi guys,

    This subject has been addressed a couple of times already on this site but not in a way that helps me work out my problem, and I'm sure many others have had the same problem.

    Basically I want to be able to count the number of rows for each year (date column) in my database, however the year starts in June and ends in May.

    I've been trying to play around with grouping by a date range but obvious issues arise with this, and I'm currently looking at a tally table but I feel there is a more effecient way of doing this.

    Any help much appreciated.

    Joe

  • July 13, 2009 at 3:03 am

    #1023736

    Here is some code with the DDL for the table used. I'll let you populate the table with test data, mine has approximately 20,000,000 rows of data. You won't need that much.

    CREATE TABLE [dbo].[LAPTest](

    [AccountID] [int] NULL,

    [Amount] [money] NULL,

    [TDate] [datetime] NULL

    ) ON [PRIMARY];

    select

    year(dateadd(mm,7,TDate)) as FiscalYear,

    min(TDate),

    max(TDate),

    count(TDate)

    from

    dbo.LAPTest

    group by

    year(dateadd(mm,7,TDate));

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply