How to get data beetween 2 dates

Question

0.00/5 (No votes)

See more:

leave request table created in database

example data inserted like this

EMPNAME  leaveType     fromdate	    to_date

Nisanam leaveApproved  12/08/2020	14/08/2020

What I have tried:

employee taken 3 days leave so we need data like this. how we get

EMPNAME  leaveType   leave_date

Nisanam leaveApproved 12/08/2020
Nisanam leaveApproved 13/08/2020
Nisanam leaveApproved 14/08/2020

Posted 18-Nov-20 20:17pm

chilukuri sumanth reddy

Updated 18-Nov-20 22:45pm

v2

Add a Solution

Comments

OriginalGriff 19-Nov-20 2:21am

This is not a good question - we cannot work out from that little what you are trying to do.
Remember that we can't see your screen, access your HDD, or read your mind - we only get exactly what you type to work with.
Use the "Improve question" widget to edit your question and provide better information.

chilukuri sumanth reddy 19-Nov-20 2:45am

now updated question

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Answer 1 · 2020-11-18T22:45:00

Start with a tally table[^] containing enough numbers to cover the maximum length of leave. Combine that with DateAdd to expand the leave days, and filter out any days beyond the end date of the request.

SQL

WITH E1(N) AS -- 5 rows
(
    SELECT 1 
    UNION ALL SELECT 1 
    UNION ALL SELECT 1 
    UNION ALL SELECT 1 
    UNION ALL SELECT 1 
),
E2(N) AS -- 25 rows
(
    SELECT 1 
    FROM E1 a CROSS JOIN E1 b
),
E4(N) AS -- 625 rows
(
    SELECT 1 
    FROM E2 a CROSS JOIN E2 b
),
E(N) As
(
    SELECT ROW_NUMBER() OVER (ORDER BY N) - 1
    FROM E4
)
SELECT
    LeaveRequests.EMPNAME,
    LeaveRequests.LeaveType,
    DateAdd(day, E.N, LeaveRequests.FromDate) As LeaveDate
FROM
    LeaveRequests
    CROSS JOIN E
WHERE
    DateAdd(day, E.N, LeaveRequests.FromDate) <= LeaveRequests.ToDate
;