How to get missing dates between two dates.

[Niki_S]

I have to get missing dates from sql server and my sql server table is as below,

dDate
2022-01-01
2022-01-04
2022-01-05
2022-01-06

Now I want as below,
when I enter 2022-01-03 I want to get 2022-01-02 and 2022-01-03 as my final result. How can I do this in vfp9?

Thank you

 

[mjcmkrsr]

Hi Niki

you may have a look at the code below

LOCAL ARRAY laAllDates[1]

CREATE CURSOR csrDates (dPDate D)

INSERT INTO csrDates VALUES ({^2022-08-20})
INSERT INTO csrDates VALUES ({^2022-08-22})
INSERT INTO csrDates VALUES ({^2022-08-24})
INSERT INTO csrDates VALUES ({^2022-08-25})
INSERT INTO csrDates VALUES ({^2022-08-30})

SELECT MAX(dPDate), MIN(dPDate) FROM csrDates INTO ARRAY laAllDates

CREATE cursor csrAllDates (dDate D)

FOR i = 0 TO laAllDates[1] - laAllDates[2]
	INSERT INTO csrAllDates VALUES (laAllDates[2] + i)

ENDFOR

SELECT * FROM csrAllDates WHERE dDate NOT in (Select * FROM csrDates) INTO CURSOR csrMissingDates

BROWSE 

RETURN

hth

MarK

 

[Niki_S]

Thank you mark. This is working bust I changed this like this. it says operator type mismatch.

LOCAL i

CREATE cursor csrAllDates (dDate D)

[highlight #FCE94F]FOR i = 0 TO dDate - deDate[/highlight]
	INSERT INTO csrAllDates VALUES (deDate + i)

ENDFOR

SELECT * FROM csrAllDates WHERE dDate NOT in (Select * FROM csrDates) INTO CURSOR csrMissingDates

The error is in the highlighted row.I changed laAllDates[1] into dDate and laAllDates[2] into deDate because I'm getting data from cursors as below.

SQLExec(HrisHandle,"SELECT max(dDate) as dDate FROM HRIS_TEMP_FactAttendance WHERE dDate <?ThisForm.txtDate.value And nEmpNo =?Absent_New.nempno ",'_LastDay')
	
	dDate=Thisform.txtDate.Value
	deDate=_LastDay.dDate

How can I fix this issue?

Thank you

 

[mjcmkrsr]

Hi,

In your code dDate is empty. You'll have to "feed" it before using it for processing.

CREATE cursor csrAllDates (dDate D)

FOR i = 0 TO dDate - deDate [highlight #FCE94F]&& dDate is empty![/highlight]
	INSERT INTO csrAllDates VALUES (deDate + i)

ENDFOR

hth

MarK

 

[Niki_S]

Can't I take it with a value? Because I want to take this between two dates.

Thank you

 

[mjcmkrsr]

Hi,

Yes, you may, but you'll have to make sure that your variable dDate is within scope of your procedure.

Btw, you may want not to give the field (dDate) and the variable the same names (dDate) - better "ldDate" for the variable

hth

MarK

 

[Niki_S]

Thank you Mark, I did it as below.

	LOCAL ARRAY laAllDates[1]

	CREATE CURSOR csrDates (dPDate D null)

	INSERT INTO csrDates (dPDate) VALUES (_LastDay.dDate)
	INSERT INTO csrDates (dPDate) VALUES (dDate)
	

	SELECT MAX(dPDate), MIN(dPDate) FROM csrDates INTO ARRAY laAllDates

	CREATE cursor csrAllDates (dDate D)

	FOR i = 0 TO laAllDates[1] - laAllDates[2]
		INSERT INTO csrAllDates VALUES (laAllDates[2] + i)

	ENDFOR

	SELECT * FROM csrAllDates WHERE dDate NOT in (Select * FROM csrDates) INTO CURSOR csrMissingDates

Thank you

 

[mjcmkrsr]

Hi Niki,

Great to read that it worked

MarK

 

[Chris Miller]

While you solved your problem, you could also generate the missing dates in SQL Server already. Watch this:

https://www.youtube.com/watch?v=bVbOo8N-Rzc

Watch to the end, he also mentions the gaps problem and suggests to search for it in sqlservercentral.com
There you go:

https://www.sqlservercentral.com/categories/gaps

And finally

https://www.red-gate.com/simple-tal...ming-sql-server/gaps-islands-sql-server-data/

I bet you have open questions when you read all that, but it's worth your time doing so and then we're here to assist in problems.

Chriss