Hi there,

If I have a dataset such as this:
Date           Amount $
01/01/07       $100
03/01/07       $110
11/01/07       $90
15/01/07       $100

Can anyone point me in the right direction of having this summarised by 'fortnight' (i.e.,
every two week period)?

Jeff Cook on this forum provided someone with the SQL to summarise by week, month and
quarter, so I'm just wanting to take this one step further and having fortnightly.
Jeff's solution for quarterly was:
SELECT EXTRACT(YEAR FROM TransDate) AS Year,
             IF(EXTRACT(MONTH FROM TransDate) BETWEEN 1 and 3 THEN 1
             ELSE IF(EXTRACT(MONTH FROM TransDate) BETWEEN 4 and 6 THEN 2
             ELSE IF(EXTRACT(MONTH FROM TransDate) BETWEEN 7 and 9 THEN 3
             ELSE 4))) As Qtr,
             COUNT(*) AS Count
FROM SupplierTrans
GROUP BY Year, Qtr

I've tried 'converting' this to fortnightly but with no success.

Can anyone help?

Thanks heaps.

Warm regards,
MikeR.

Mike Ryan <mike.ryan@No.SPAM.fitwebDOTcom.au> wrote in
news:3B342B20-6F06-4A4D-B8B6-CDB272D330E3@news.elevatesoft.com:

> Hi there,
>
> If I have a dataset such as this:
> Date           Amount $
> 01/01/07       $100
> 03/01/07       $110
> 11/01/07       $90
> 15/01/07       $100
>
> Can anyone point me in the right direction of having this summarised
> by 'fortnight' (i.e., every two week period)?
>
> Jeff Cook on this forum provided someone with the SQL to summarise by
> week, month and quarter, so I'm just wanting to take this one step
> further and having fortnightly. Jeff's solution for quarterly was:
> SELECT EXTRACT(YEAR FROM TransDate) AS Year,
>               IF(EXTRACT(MONTH FROM TransDate) BETWEEN 1 and 3 THEN 1
>               ELSE IF(EXTRACT(MONTH FROM TransDate) BETWEEN 4 and 6
>               THEN 2 ELSE IF(EXTRACT(MONTH FROM TransDate) BETWEEN 7
>               and 9 THEN 3 ELSE 4))) As Qtr,
>               COUNT(*) AS Count
> FROM SupplierTrans
> GROUP BY Year, Qtr
>
> I've tried 'converting' this to fortnightly but with no success.
>

SELECT EXTRACT(YEAR FROM TransDate) AS Year,
      FLOOR (EXTRACT(WEEK FROM TransDate) + 1 ) / 2) Fortnight,
      COUNT(*) AS Count
FROM SupplierTrans
GROUP BY Year, Fortnight


--
Chris
(XP-Pro + Delphi 7 Architect + DBISAM 4.25 build 4 + EDB 1.04 build 3)

Thanks heaps Chris!!
Works brilliantly.

MikeR.


Chris Erdal <chris@No-Spam-erdal.net> wrote:


SELECT EXTRACT(YEAR FROM TransDate) AS Year,
      FLOOR (EXTRACT(WEEK FROM TransDate) + 1 ) / 2) Fortnight,
      COUNT(*) AS Count
FROM SupplierTrans
GROUP BY Year, Fortnight


--
Chris
(XP-Pro + Delphi 7 Architect + DBISAM 4.25 build 4 + EDB 1.04 build 3)