Date series generation using SELECT - SQL?

[torturedmind]

Hello all,

Can a record containing a beginning covered date of 2015-06-01 and an ending covered date of 2015-06-05 be generated to become fifteen (15) separate records having individual date of the covered dates using SELECT - SQL only? If so, how?

TIA

kilroy
philippines

"Once a king, always a king. But being a knight is more than enough."

 

[Mike Lewis]

Hi Kilroy, and welcome back.

A couple of questions:

1. Assuming you want to generate one record per date, then you will only get five records, not 15. Perhaps the end date should be 2015-06-15 rather than 2015-06-05?

2. Are the dates you've given the specific dates that you want to generate? Or are you looking for a more general solution, with those dates being just an example?

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[vgulielmus]

From what I know, in VFP you can't create dynamic queries
Here is a solution for three days

CLOSE DATABASES ALL
CREATE CURSOR cc (id D,fd D)
INSERT INTO cc VALUES (DATE(2015,6,1),DATE(2015,6,3))

SELECT NVL(cc.id,NVL(cc1.id,cc2.id)) as id ;
	FROM cc ;
	FULL JOIN (SELECT id+1 as id FROM cc) cc1 ON .F. ;
	FULL JOIN (SELECT id+2 as id FROM cc) cc2 ON .F. ;
	ORDER BY 1

You can create a function that generates and returns the expression and the joins.

CLOSE DATABASES ALL
CREATE CURSOR cc (id D,fd D)
INSERT INTO cc VALUES (DATE(2015,6,1),DATE(2015,6,5))

LOCAL lcSelect,lcExpr

lcExpr = MySelect(cc.id,cc.fd,@lcSelect) 
?	lcExpr,lcSelect

SELECT &lcExpr ;
	FROM cc &lcSelect ;
	ORDER BY 1

FUNCTION MySelect
	LPARAMETERS ldStart,ldFin,lcSelect
	LOCAL lcVal
	lcVal = "NVL(cc.id"
	lcSelect = ""
	FOR lni = 1 TO m.ldFin - m.ldStart - 1
		lcVal = m.lcVal + ",NVL(cc"+ TRANSFORM(m.lni) +".id"
		lcSelect = m.lcSelect + "FULL JOIN (SELECT id+" + TRANSFORM(m.lni) + " as id FROM cc) cc" + TRANSFORM(m.lni) + " ON .F. "
	NEXT
	IF m.ldFin - m.ldStart > 0
		lcVal = m.lcVal + ",cc"+ TRANSFORM(m.ldFin - m.ldStart) +".id"
		lcSelect = m.lcSelect + "FULL JOIN (SELECT id+" + TRANSFORM(m.ldFin - m.ldStart) + " as id FROM cc) cc" + TRANSFORM(m.ldFin - m.ldStart) + " ON .F. "
	ENDIF
	FOR lni = 1 TO m.ldFin - m.ldStart 
		lcVal = m.lcVal + ")"
	NEXT
	RETURN m.lcVal
ENDIF

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[Olaf Doschke]

Hello Kilroy,

a rare guest, you're welcome.
The question I ad is why should a query do this?

Even if you'd do this with a simple loop, why would you need the individual dates? If you query something between startdate and enddate all you need to know are these two dates.

One assumption: You want to put in results into a calendar. But if your final query result has data per date you want to show up in a calendar, it would be sufficient to have the dates in the result, that have data, to navigate to the calender rows (or column) and skip other dates thereby.

That said you can use any table with lots of data to get a tally via RECNO()-1 as Offset - [tt]SELECT RECNO()-1 as offsets FROM <some large table> WHERE RECNO()<=N[/tt] for example, and then add that to the start date. That way it's easy to create such a series of offsets from 0 to N-1 and then full join "ON .T." this to get the number of records you'd want.

We've also had this as a puzzle in thread184-1749101.

Bye, Olaf.

 

[torturedmind]

Thanks for the replies, good sirs.

1. Assuming you want to generate one record per date, then you will only get five records, not 15. Perhaps the end date should be 2015-06-15 rather than 2015-06-05?

Sorry my bad. Your right, it should read 2015-06-15 and not 2015-06-05.

2. Are the dates you've given the specific dates that you want to generate? Or are you looking for a more general solution, with those dates being just an example?

The dates were just samples. I'd prefer it to be a more general solution.

One assumption: You want to put in results into a calendar...

Correct. The puzzle seems fun and similar to what Vilhelm-Ion posted above. But I can't help but view the spoilers.

@Vilhelm-Ion
I'll try to experiment on it and see if I can make it work on my end.

I had a different function way back that I copied from an Oracle DBA blog that I've forgotten who the original author was. It generated number series instead of date series in pure SELECT - SQL statement. But alas, I think I've lost it in the process of changing/upgrading my PC.

Thanks for sharing a piece of your time and your minds, good sirs. As always, you've been very helpful. I'll try to post back soon.


kilroy
philippines

"Once a king, always a king. But being a knight is more than enough."

 

[Olaf Doschke]

I didn't meant you to participate int the puzzle, the first part of it is also generating N records on the fly via SQL, and that technique can also be used here.
You know the number of records you need by the difference between end date and start date and you can simply add 0,1,2,3,... to the start date to get further dates up to enddate.

Bye, Olaf.

Edit: For example this way:

Create Cursor curDates (dStart D, dEnd D)
Insert into curDates Values ({^2015-06-01}, {^2015-06-15})

Select Top (curDates.dEnd-curDates.dStart+1) dd.dStart+days.iday as dDate ;
From curDates as dd ;
full join ;
(Select Recno()-1 as iday FROM (Sys(2005))) as days;
on .t.;
Order by 1

 

[Olaf Doschke]

Another idea:

Create Cursor curDates (dStart D, dEnd D)
Insert into curDates Values ({^2015-06-01}, {^2015-06-15})
Insert into curDates Values ({^2015-11-01}, {^2015-11-11})

Public gncount

Select dd.dStart+m.gncount as dDate;
From curDates dd;
left join (Sys(2005)) on IsNull(_vfp.DoCmd("gncount=gncount+1")) AND dd.dStart+m.gncount<=dd.dEnd;
where IsNull(_vfp.DoCmd("gncount=-1"));
Into Cursor curResult

It still uses sys(2005), which is foxuser.dbf and assumes the date spans are smaller than the number of records in foxuser.dbf. I'd also not really recommend this, as there are much easier to understand and maintain ways of generating N records with a series of dates. The nice part is, this resets the counter for each record in curDates, so you produce as many series as needed.

Bye, Olaf.

 

[Mike Lewis]

Having thought about this a bit more, I must admit that I would probably do it by brute force: Do a FOR / ENDFOR loop, and use INSERT to place a new record in your result cursor each time round the loop. I know it's not at all elegant; it's probably not particularly efficient; and it doesn't answer your original question, which specified using SQL - SELECT. I suppose it relfects my temperament: I would rather just get it done than trying to get my head round the optimum solution.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

I agree with ou Mike. I would also just do a insert loop. The need to do this with sql might come from needing this as a view or for an external language only capable to use odbc or oledb. There always is EXECSCRIPT to execute a script instead of just single commands.

Perhaps, kilroy, you also think there is a simple sql mechanism for generating series of records on the fly. If you tell more about the bigger picture in which you need this piece of the puzzle the most ideal solution might become something completely different.

For example my last idea to use _vfp.dcmd within a query is something I wouldn't recommend in any production code, it's too unpredictable what happens, if the query is extended, optimized in some ways by rushmore, that might skip the gncount increment for every record or make it happen in another order. It also doesn't translate into other sql dialects.

Brute force and straight forward code mostly is easy to understand and maintain and therefore preferable, you often enough need optimised code for performance reasons, ideally it's both more performant and eas to understand and maintain, not as short as possible.

Bye, Olaf.