SQL Date looping calculation/help??

[efinnen]

Okay guys and help/advice/suggestion would be greatly appreciated. I hope this explanation I sufficient

I have a user form which requires the user to dictate a STARTDATE and ENDDATE,I also have the user have the option to dictate a STARTSTEPDATE and ENDSTEPDATE field. I need to be able to calculate all the steps between the STARTDATE and ENDDATE. So what I want it to do is make a list of the steps.. an Example would be below. This example has the step defined as being 1.5 months however it could very easily be by decade, year, two year group etc.

User inputs as follows

STARTDATE : 01/01/1990
ENDDATE : 03/01/2006

STARTSTEPDATE : 01/01/1990
ENDSTEPDATE : 02/15/1990

I’m trying to get it to make the query output something along the lines of

STEPSTART STEPEND PKEY
01/01/1990 02/15/1990 1
01/01/1991 02/15/1991 2
01/01/1992 02/15/1992 3
01/01/1993 02/15/1993 4
01/01/1994 02/15/1994 5
01/01/1995 02/15/1995 6
01/01/1996 02/15/1996 7
01/01/1997 02/15/1997 8
01/01/1998 02/15/1998 9
01/01/1999 02/15/1999 10
01/01/2000 02/15/2000 11
01/01/2001 02/15/2001 12
01/01/2002 02/15/2002 13
01/01/2003 02/15/2003 14
01/01/2004 02/15/2004 15
01/01/2005 02/15/2005 16
01/01/2006 02/15/2006 17


I can do this programmatically (VB/VBA) but I’m trying to do it in SQL and seem to be chasing my tail.

Thanks
-eric

 

[efinnen]

I couldn't figure out how to do it in SQL (or if it was even possible).. SO if anyone cares. the VB code.

Private Sub cmdStepApply_Click()
DoCmd.SetWarnings False
cmdApply.Enabled = True

Dim DateSpan As Integer
Dim DateSpanYear As Integer
Dim TotalYearGap As Integer
Dim StepEndYear As Integer
Dim StepCalc As Integer
Dim StepEndMMDD As String
Dim StepStartMMDD As String
Dim StepEndYY As String
Dim StepStartYY As String
Dim EndYY As String
Dim StepStart As Date
Dim StepEnd As Date
Dim StepCount As Integer
Dim IDStep As Integer
Dim Count As Integer

DateSpan = DateDiff("d", DTStepStart.value, DTStepEnd.value)
DateSpanYear = DateDiff("YYYY", DTStepStart.value, DTStepEnd.value) + 1
TotalYearGap = DateDiff("YYYY", DTPickerStart.value, DTPickerEnd.value)
StepEndMMDD = Format(DTStepEnd.value, "MM/DD")
StepStartMMDD = Format(DTStepStart.value, "MM/DD")
StepEndYY = Format(DTStepEnd.value, "YYYY")
StepStartYY = Format(DTStepStart.value, "YYYY")
EndYY = Format(DTPickerEnd.value, "YYYY")

StepStart = DTStepStart.value
StepEnd = DTStepEnd.value

StepCount = 0

'we need to count how many steps are involved. Its atleats yearly so that'll be our range
For cnt = 0 To TotalYearGap
If StepStart < DTPickerEnd.value Then
StepCount = StepCount + 1
End If

StepStart = StepStartMMDD & "/" & StepEndYY + 1
StepEnd = StepStart + DateSpan
StepEndYY = Format(StepEnd, "YYYY")

Next cnt

StepStart = DTStepStart.value
StepEnd = DTStepEnd.value
StepEndYY = Format(DTStepEnd.value, "YYYY")

StepStart = Format(StepStart, "MM/DD/YYYY")
StepEnd = Format(StepEnd, "MM/DD/YYYY")
'DoCmd.RunSQL "INSERT INTO tblStepLookup ( STEPSTART1, STEPEND1 ) SELECT #" & StepStart & "#, #" & StepEnd & "#;"

IDStep = 1
Count = 0


Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As ADODB.Recordset
Dim sConnString As String


sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\work\EcoGIS\mdb\vtr.mdb"


conn.Open sConnString
Set cmd.ActiveConnection = conn

For i = 0 To StepCount - 1

cmd.CommandText = "INSERT INTO tblDateLookup ( STEPID, STEPSTART1, STEPEND1 ) SELECT " & IDStep & ", #" & StepStart & "#, #" & StepEnd & "#;"
'DoCmd.RunSQL "INSERT INTO tblStepLookup ( STEPSTART1, STEPEND1 ) SELECT #" & StepStart & "#, #" & StepEnd & "#;"
StepStart = StepStartMMDD & "/" & StepEndYY + 1
StepEnd = StepStart + DateSpan
StepEndYY = Format(StepEnd, "YYYY")
Count = Count + 1
If Count = txtStepNumber.Text Then
IDStep = IDStep + 1
Count = 0
End If
cmd.CommandType = adCmdText
Set rs = cmd.Execute

Next i

conn.Close


'For i = 0 To StepCount - 1
' DoCmd.RunSQL "INSERT INTO tblDateLookup ( STEPID, STEPSTART1, STEPEND1 ) SELECT " & IDStep & ", #" & StepStart & "#, #" & StepEnd & "#;"
' 'DoCmd.RunSQL "INSERT INTO tblStepLookup ( STEPSTART1, STEPEND1 ) SELECT #" & StepStart & "#, #" & StepEnd & "#;"
' StepStart = StepStartMMDD & "/" & StepEndYY + 1
' StepEnd = StepStart + DateSpan
' StepEndYY = Format(StepEnd, "YYYY")
' Count = Count + 1
' If Count = txtStepNumber.Text Then
' IDStep = IDStep + 1
' Count = 0
' End If
'Next i

DoCmd.SetWarnings True
End Sub

 

[Golom]

Build yourself an Integers of the form
[tt]
Integers
Num
0
1
2
3
:
8
9
[/tt]
Then

PARAMETERS [Start Date] DateTime, [End Date] DateTime, [STARTSTEPDATE] DateTime, [ENDSTEPDATE] DateTime;

Select 
DateSerial(Year([Start Date]) + I.N, Month([Start Date]), Day([Start Date])) As StepStart,

DateAdd("d", DateDiff("d",[STARTSTEPDATE],[ENDSTEPDATE]), 
DateSerial(Year([Start Date]) + I.N, Month([Start Date]), Day([Start Date]))) As StepEnd,

I.N + 1 As PK

From  
     (Select 10*I1.Num + I2.Num As N
      From Integers I1, Integers I2) As I

Where 

DateSerial(Year([Start Date]) + I.N, Month([Start Date]), Day([Start Date])) < [End Date]

Order By 3

This accurately reproduces your example however, it is unclear what you would expect to see if the [STARTSTEPDATE] - [ENDSTEPDATE] range exceeds a year. For example with
[tt]
[Start Date] = 01/01/1995
[End Date] = 06/01/2006
[STARTSTEPDATE] = 01/01/1995
[STEPENDDATE] = 03/01/1996

This produces

StepStart StepEnd PK
01/01/1995 03/01/1996 1
01/01/1996 03/01/1997 2
01/01/1997 03/02/1998 3
01/01/1998 03/02/1999 4
01/01/1999 03/01/2000 5
01/01/2000 03/01/2001 6
01/01/2001 03/02/2002 7
01/01/2002 03/02/2003 8
01/01/2003 03/01/2004 9
01/01/2004 03/01/2005 10
01/01/2005 03/02/2006 11
01/01/2006 03/02/2007 12
[/tt]

 

[cheerio]

Just create a calendar table with every date in it. A whole century is under 40000 rows so it's no big deal.

Easiest way to populate such a table is to import from Excel.

 

[efinnen]

Golom

Thanks for the reply. Quite interesting code (I wouldn't have thought to do it that way). And it works perfectly for when the STEP in under a year in length. But you nailed the problem on the head with how to represent steps greater then a year.

Optimally, using your example from up above I would want the next STEP to start at the first MMDD after the STEPEND from the previous record so it would look something like

[Start Date] = 01/01/1995
[End Date] = 06/01/2006
[STARTSTEPDATE] = 01/01/1995
[STEPENDDATE] = 03/01/1996

This produces

StepStart StepEnd PK
01/01/1995 03/01/1996 1
01/01/1997 03/01/1998 2
01/01/1999 03/02/2000 3

etc etc etc

I would be fine if the END DATE fell between the last STARTSTEPDATE and STEPENDDATE

Thanks
-e

ps. Thanks for the help, more suggestion would definitely be helpful but there is no rush on it. I'll definitely be using something along these lines for when it gets deployed (couple of weeks).. Luckily the VB (its ugly but it works) is getting the job done for the demo purposes of the application right now.

 

[Golom]

Here are two approaches.

This one accepts four parameters (as in your original example)

PARAMETERS [Start Date] DateTime, [End Date] DateTime, [Step Start] DateTime, [Step End] DateTime;

SELECT 

DateSerial(Year([Start Date]) + I.N + (I.N *Int(DateDiff("d",[Step Start],[Step End])/365.25)), 
           Month([Start Date]),
           Day([Start Date])) AS StepStart, 

DateAdd("d", DateDiff("d",[Step Start],[Step End]), 
             DateSerial(Year([Start Date]) + I.N +  (I.N *Int(DateDiff("d",[Step Start],[Step End])/365.25)), 
             Month([Start Date]), 
             Day([Start Date]))) AS StepEnd, 

I.N + 1 AS PK

FROM [Select (100*I1.Num + 10*I2.Num + I3.Num)  As N
      From Integers I1, Integers I2, Integers I3]. AS I

WHERE DateSerial(Year([Start Date]) +  I.N + (I.N *Int( DateDiff("d",[Step Start],[Step End])/365.25)), 
      Month([Start Date]),
      Day([Start Date])) < [End Date]

ORDER BY 3;

This one accepts three parameters. It assumes that "Start Step Date" is the same as "Start Date" and specifies the number of days in the step rather than the date range.

PARAMETERS [Start Date] DateTime, [End Date] DateTime, [Step Size (Days)] Long;

SELECT 

DateSerial(Year([Start Date]) + I.N + (I.N *Int( [Step Size (Days)]/365.25)), 
           Month([Start Date]), 
           Day([Start Date])) AS StepStart, 

DateAdd("d",  [Step Size (Days)], 
    DateSerial(Year([Start Date]) + I.N +  (I.N *Int( [Step Size (Days)]/365.25)), 
               Month([Start Date]), 
               Day([Start Date]))) AS StepEnd, 

I.N + 1 AS PK

FROM [Select (100*I1.Num + 10*I2.Num + I3.Num)  As N
      From Integers I1, Integers I2, Integers I3]. AS I

WHERE DateSerial(Year([Start Date]) +  I.N + (I.N *Int( [Step Size (Days)]/365.25)), 
                 Month([Start Date]), 
                 Day([Start Date])) < [End Date]

ORDER BY 3;

Both seem to work as you stipulated.