Re: Group by weekday

>From: kevinc@sequent.com (KEVIN CLOSSON) >Subject: Group by weekday >Date: Thu, 28 Jul 94 20:50:25 GMT >X-Informix-List-Id: <news.7883> >Anyone have any crafty ways of GROUPing BY an arbitrary day of the week? >For instance, all rows over the last year grouping the data by friday >of each week? SELECT WEEKDAY(SomeDate), SomeOtherColumn FROM SomeTable WHERE SomeColumn = 'SomeValue' GROUP BY 1; Or did you mean you want all the dates from Saturday through Friday to be treated as one particular group? If that's the case, then you remember that DATE values are stored as integers, and can be manipulated along the lines of: SELECT TRUNC(((SomeDate - MDY(1,1,1994) - WEEKDAY(MDY(1,1,1994)) + 5 + 7) / 7), 0) WeekNo, SomeDate, SomeOtherColumn FROM SomeTable WHERE SomeColumn = 'SomeValue' ORDER BY 1, 2; You have to define the semantics of your weeks rather carefully; the 5 represents Friday; the 7s represent the number of days in a week (the added 7 is to ensure that the calculation doesn't involve negative values to TRUNC); the first MDY() constructor gives you a starting point, and the WEEKDAY() bit compensates for the fact that the 1st of the year generally isn't the day of the week you originally thought of. You need to verify that this does what you want before using it! Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> Given a table containing the dates from 01/01/94 through 31/01/94, the query above gives: WeekNo SomeDate SomeOtherColumn 0 01/01/1994 SomeValue 1 02/01/1994 SomeValue 1 03/01/1994 SomeValue 1 04/01/1994 SomeValue 1 05/01/1994 SomeValue 1 06/01/1994 SomeValue 1 07/01/1994 SomeValue 1 08/01/1994 SomeValue 2 09/01/1994 SomeValue 2 10/01/1994 SomeValue 2 11/01/1994 SomeValue 2 12/01/1994 SomeValue 2 13/01/1994 SomeValue 2 14/01/1994 SomeValue 2 15/01/1994 SomeValue 3 16/01/1994 SomeValue 3 17/01/1994 SomeValue 3 18/01/1994 SomeValue 3 19/01/1994 SomeValue 3 20/01/1994 SomeValue 3 21/01/1994 SomeValue 3 22/01/1994 SomeValue 4 23/01/1994 SomeValue 4 24/01/1994 SomeValue 4 25/01/1994 SomeValue 4 26/01/1994 SomeValue 4 27/01/1994 SomeValue 4 28/01/1994 SomeValue 4 29/01/1994 SomeValue 5 30/01/1994 SomeValue 5 31/01/1994 SomeValue