SQL: How to Group by Custom Year

Question

For simplicities sake, I'll make up a similar example to what I have:

Let's say a db has a table of orders with an OrderDate field and a Company field. Then there's a table of Companies and each record has a YearEndingDate (which signifies that the year ends on that date each year, e.g. 6/6).

I need to add up all of the orders for each year.

I assume it would have to be something like this but I can't quite figure it out:

SELECT SUM(orderValue),
CASE WHEN orderDate <= YearEndingDate THEN DatePart(year, orderDate)
CASE WHEN orderDate > YearEndingDate THEN DatePart(year, orderDate) + 1
END as Year
FROM Orders
INNER JOIN Company ON Company.companyID = Order.companyID
GROUP By Company, Year

Any ideas?

Answer 1

Not sure what RDMS you are using but this should do the trick. The datepart and dateadd stuff are tsql specific but I'd assume you'd have access to similar functions on whatever platform you are using. The case in the where determines which year value to use.

Answer:

select c.companyid
   ,yearendingdate + '/' + convert(varchar, datepart(yy, dateadd(yy,1,o.orderdate))) as yearending
   ,sum(ordervalue) as numberoforders
  from @orders o
    join @companies c
   on o.companyid = c.companyid
 where orderdate between case when (cast(yearendingdate + '/' + convert(varchar, datepart(yy, o.orderdate)) as datetime) >= o.orderdate)
       then yearendingdate + '/' + convert(varchar, datepart(yy, dateadd(yy,-1,o.orderdate)))
       else yearendingdate + '/' + convert(varchar, datepart(yy, o.orderdate))
        end
      and 
       case when (cast(yearendingdate + '/' + convert(varchar, datepart(yy, o.orderdate)) as datetime) >= o.orderdate)
       then yearendingdate + '/' + convert(varchar, datepart(yy, o.orderdate))
       else yearendingdate + '/' + convert(varchar, datepart(yy, dateadd(yy,1,o.orderdate)))
        end
 group by c.companyid, o.orderdate, yearendingdate

Code to figure out problem:

declare @orders table (OrderDate datetime
       ,CompanyID varchar(20)
       ,OrderValue int)

insert into @orders
values (getdate(),'MS',2)

insert into @orders
values (DateAdd(year, -1, getdate()),'MS',3)

insert into @orders
values (DateAdd(year, -1, getdate()),'MS',1)

insert into @orders
values (DateAdd(year, 1, getdate()),'MS',4)

insert into @orders
values (DateAdd(year, 1, getdate()),'Blizzard',2)

insert into @orders
values (getdate(),'MS',11)

declare @companies table (CompanyID varchar(20)
       ,YearEndingDate varchar(20))

insert into @companies
values ('MS', '05/6')

insert into @companies
values ('Blizzard', '07/01')

select c.companyid
   ,o.orderdate
   ,yearendingdate + '/' + convert(varchar, datepart(yy, o.orderdate)) as sameyear
   ,yearendingdate + '/' + convert(varchar, datepart(yy, dateadd(yy,1,o.orderdate))) as plusyear
   ,yearendingdate + '/' + convert(varchar, datepart(yy, dateadd(yy,-1,o.orderdate))) as minusyear
  from @orders o
    join @companies c
   on o.companyid = c.companyid

select c.companyid
   ,yearendingdate + '/' + convert(varchar, datepart(yy, dateadd(yy,1,o.orderdate))) as yearending
   ,sum(ordervalue) as numberoforders
  from @orders o
    join @companies c
   on o.companyid = c.companyid
 where orderdate between case when (cast(yearendingdate + '/' + convert(varchar, datepart(yy, o.orderdate)) as datetime) >= o.orderdate)
       then yearendingdate + '/' + convert(varchar, datepart(yy, dateadd(yy,-1,o.orderdate)))
       else yearendingdate + '/' + convert(varchar, datepart(yy, o.orderdate))
        end
      and 
       case when (cast(yearendingdate + '/' + convert(varchar, datepart(yy, o.orderdate)) as datetime) >= o.orderdate)
       then yearendingdate + '/' + convert(varchar, datepart(yy, o.orderdate))
       else yearendingdate + '/' + convert(varchar, datepart(yy, dateadd(yy,1,o.orderdate)))
        end
 group by c.companyid, o.orderdate, yearendingdate