George George - 4 months ago 13
SQL Question

Access/SQL Interval breakout

EDIT: SIMPLIED CODE

CODE START END TLY
X 7/12/2016 10:30:00 AM 7/12/2016 11:00:00 AM 68
X 8/14/2016 09:30:00 AM 8/14/2016 11:00:00 AM 11


I was looking for some assistance with an odd table that I have pulled into Access (using SQL to query). The underlying issue I have is sometimes the data can be every 30 minutes as represented in line one (10:00-10:30) or it could be multiple intervals such as line two 09:30-11:00.

Ideally, I'd like to represent this as every 30 minute period they are reporting for so the data would look as follows:

CODE START END TLY
X 7/12/2016 10:30:00 AM 7/12/2016 11:00:00 AM 68
X 8/14/2016 09:30:00 AM 8/14/2016 10:00:00 AM 11
X 8/14/2016 10:00:00 AM 8/14/2016 10:30:00 AM 11
X 8/14/2016 10:30:00 AM 8/14/2016 11:00:00 AM 11


Code used:

SELECT
SGT.CODE AS CODE, CVDate((SGT.START_MOMENT-240)/1440) AS START,
CVDate((SGT.STOP_MOMENT-240)/1440) AS END,
SGT.TLY AS TLY
FROM
STF_GRP_TLY AS SGT


Any thoughts or suggestions that I could try?

Thank you!

George

Answer

Leaving aside your joins and date/time math [... - 240)/1440] for the moment, if you have a data table

[tblIntervals]

CODE          START                END                  TLY
------------  -------------------  -------------------  ---
Group_Sample  2016-07-12 10:30:00  2016-07-12 11:00:00   68
Group_Sample  2016-08-14 09:30:00  2016-08-14 11:00:00   11

and you create a "numbers table" that starts with zero and counts up (by one) to more than the maximum number of half-hour subintervals you would ever expect to encounter, e.g.

[tblNumbers]

 n
--
 0
 1
 2
 3
 4
 5
 6
 7
 8
 9

then you can create a query that uses a cross-join to list the individual sub-intervals like so:

SELECT 
    tblIntervals.CODE,
    DateAdd("n", 30 * tblNumbers.n, tblIntervals.START) AS dtmSTART,
    DateAdd("n", 30 * (tblNumbers.n + 1), tblIntervals.START) AS dtmEND,
    tblIntervals.TLY
FROM
    tblIntervals, tblNumbers
WHERE
    DateAdd("n", 30 * tblNumbers.n, tblIntervals.START) < tblIntervals.END
ORDER BY 1, 2

returning

CODE          dtmSTART             dtmEND               TLY
------------  -------------------  -------------------  ---
Group_Sample  2016-07-12 10:30:00  2016-07-12 11:00:00   68
Group_Sample  2016-08-14 09:30:00  2016-08-14 10:00:00   11
Group_Sample  2016-08-14 10:00:00  2016-08-14 10:30:00   11
Group_Sample  2016-08-14 10:30:00  2016-08-14 11:00:00   11

Edit

To incorporate the conversion from "nominal" to "real" date/time values you could use a query like this:

SELECT 
    STF_GRP_TLY.STF_GRP_SK, 
    DateAdd("n", 30 * tblNumbers.n, CDate((STF_GRP_TLY.START_MOMENT-240)/1440)) AS dtmSTART, 
    DateAdd("n", 30 * (tblNumbers.n + 1), CDate((STF_GRP_TLY.START_MOMENT-240)/1440)) AS dtmEND, 
    STF_GRP_TLY.TLY 
FROM STF_GRP_TLY, tblNumbers 
WHERE 
DateAdd("n", 30 * tblNumbers.n, CDate((STF_GRP_TLY.START_MOMENT-240)/1440)) < CDate((STF_GRP_TLY.STOP_MOMENT-240)/1440);