Re: Group by weekday
In article <1994Jul28.205025.12734@sequent.com> kevinc@sequent.com (KEVIN CLOSSON) writes:
>
>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?
>
Caveat: it is late Friday and my brain is running at about 30% capacity...
I take it you mean that you want to rollup data by "week ending Friday",
e.g. how many invoices did we process each week, totalling them all to
the next friday. (Or do you mean: I want to know how much stuff got
shipped on Fridays, last year? - that's quite different, and easier)
I tried this:
select ( inv_date + ( 5 - weekday(inv_date) ) ) date1, count(*) cnt
from inv_mast
where inv_date > today - 365
and weekday(inv_date) < 6
group by 1
union
select ( inv_date + 6 ) date1, count(*) cnt
from inv_mast
where inv_date > today - 365
and weekday(inv_date) = 6
group by 1
into temp t2 ;
select date1, sum(cnt)
from t2
group by 1
order by 1 ;
...which returned this:
date1 (sum)
07/30/1993 233
08/06/1993 1441
08/13/1993 1107
08/20/1993 944
08/27/1993 1491
09/03/1993 899
09/10/1993 1389
09/17/1993 1676
09/24/1993 1350
10/01/1993 2082
10/08/1993 1510
10/15/1993 1028
10/22/1993 1022
10/29/1993 1012
11/05/1993 1131
11/12/1993 1094
11/19/1993 1197
11/26/1993 1428
12/03/1993 1022
12/10/1993 1715
12/17/1993 1881
12/24/1993 1703
12/31/1993 2446
01/07/1994 480
01/14/1994 1204
01/21/1994 1185
01/28/1994 1294
02/04/1994 906
02/11/1994 1220
02/18/1994 1128
02/25/1994 1116
03/04/1994 711
03/11/1994 1205
03/18/1994 1187
03/25/1994 2244
04/01/1994 2258
04/08/1994 1689
04/15/1994 1125
04/22/1994 1186
04/29/1994 1372
05/06/1994 1282
05/13/1994 1251
05/20/1994 1347
05/27/1994 1797
06/03/1994 1205
06/10/1994 1025
06/17/1994 1897
06/24/1994 1759
07/01/1994 2636
07/08/1994 1065
07/15/1994 1262
07/22/1994 10
07/29/1994 6
...which seems to be right. Just to check, I ran the following queries:
select count(*) from inv_mast
where inv_date between "03/26/94" and "04/01/94" ;
select count(*) from inv_mast
where inv_date between "03/19/94" and "03/25/94" ;
select count(*) from inv_mast
where inv_date between "05/14/94" and "05/20/94" ;
and they returned:
2258
2244
1347
which seem to correspond to the weekly numbers returned by the first
query.
I feel sure that there is a way to do it without this "sunday - friday
as one case; saturday as another case" stuff that I did. weekday(inv_date)
returns 0 for a sunday, 1 for a monday .... 6 for a saturday.
If you don't treat saturday as a special case, my query will want to
rollup saturday invoices onto the totals for the PRECEDING friday instead
of the following one.
Later....
Paul