'how to get no of dates appear between Fromdt to Todt and fetching thouse dates in column

![enter image description here][1]

I have table called tbl_monthly_leave

in below table i am trying to take dates between fromdt to Todate like (Fromdt , Todate) as number of leaves date applied for, collect all dates in one single column leavedapplieddates. i am trying to match Emp_no to range of dates. For example If one employee apply leaves for 5 days He will select fromdt as 20-07-2020 and Todt has 25-07-2020, number of level applied days is 5, now I am stuck to iterate dates, required output is 20-07-2020 21-07-2020 22-07-2020 23-07-2020 24-07-2020.

I am applying one more logic for ex if Sunday and Saturday come need to filter , after filtering number dates will become 3, applied leave count will be 3 dates

sql-serverdaterecursive-query


Solution 1:[1]

One option uses a recursive query.

Assuming that you are running SQL Sever (because you tagged your question sql and server, which is quite common pattern of SO newcomers), that would be:

with leaves as (
    select emp_no, leave_appl_dt, from_dt, to_dt 
    from tbl_monthly_leaves
    union all
    select emp_no, leave_appl_dt, dateadd(day, 1, from_dt), to_dt
    from leaves
    where from_dt < to_dt
)
select from_dt leave_dt, emp_no, leave_appl_dt from leaves order by emp_no, leave_dt

I changed a little the names of your columns, which are not consistent between your sample data and results.

If any of your leaves spreads over more than 100 days, you need to add option (maxrecusion 0) at the very end of the query.

Demo on DB Fiddle:

Sample data:

slno | Emp_no | leave_appl_dt | From_dt    | To_dt      | no_of_days
---: | -----: | :------------ | :--------- | :--------- | ---------:
   1 |   1001 | 2020-01-01    | 2020-01-01 | 2020-01-12 |         12
   2 |   1002 | 2020-01-10    | 2020-01-15 | 2020-01-25 |         10

Results:

leave_dt   | emp_no | leave_appl_dt
:--------- | -----: | :------------
2020-01-01 |   1001 | 2020-01-01   
2020-01-02 |   1001 | 2020-01-01   
2020-01-03 |   1001 | 2020-01-01   
2020-01-04 |   1001 | 2020-01-01   
2020-01-05 |   1001 | 2020-01-01   
2020-01-06 |   1001 | 2020-01-01   
2020-01-07 |   1001 | 2020-01-01   
2020-01-08 |   1001 | 2020-01-01   
2020-01-09 |   1001 | 2020-01-01   
2020-01-10 |   1001 | 2020-01-01   
2020-01-11 |   1001 | 2020-01-01   
2020-01-12 |   1001 | 2020-01-01   
2020-01-15 |   1002 | 2020-01-10   
2020-01-16 |   1002 | 2020-01-10   
2020-01-17 |   1002 | 2020-01-10   
2020-01-18 |   1002 | 2020-01-10   
2020-01-19 |   1002 | 2020-01-10   
2020-01-20 |   1002 | 2020-01-10   
2020-01-21 |   1002 | 2020-01-10   
2020-01-22 |   1002 | 2020-01-10   
2020-01-23 |   1002 | 2020-01-10   
2020-01-24 |   1002 | 2020-01-10   
2020-01-25 |   1002 | 2020-01-10

Solution 2:[2]

If you don't have a calendar table or tally/numbers table (highly recommended), you can use an ad-hoc tally table in concert with a CROSS APPLY

Example

Declare @YourTable Table ([slno] int,[Emp_no] int,[leave_appl_dt] date,[Fromdt] date,[Todate] date,[no_of_days] int)
Insert Into @YourTable Values 
 (1,1001,'2020-01-01','2020-01-01','2020-01-12',12)
,(2,1002,'2020-01-10','2020-01-15','2020-01-25',10)

Select leavedt = b.D 
      ,A.Emp_no
      ,A.leave_appl_dt
 From @YourTable A
 Cross Apply (
               Select Top (DateDiff(DAY,[Fromdt],[Todate])+1) D=DateAdd(DAY,-1+Row_Number() Over (Order By (Select Null)),[Fromdt]) 
                From  master..spt_values n1,master..spt_values n2
             ) B

Returns

leavedt     Emp_no  leave_appl_dt
2020-01-01  1001    2020-01-01
2020-01-02  1001    2020-01-01
2020-01-03  1001    2020-01-01
2020-01-04  1001    2020-01-01
2020-01-05  1001    2020-01-01
2020-01-06  1001    2020-01-01
2020-01-07  1001    2020-01-01
2020-01-08  1001    2020-01-01
2020-01-09  1001    2020-01-01
2020-01-10  1001    2020-01-01
2020-01-11  1001    2020-01-01
2020-01-12  1001    2020-01-01
2020-01-15  1002    2020-01-10
2020-01-16  1002    2020-01-10
2020-01-17  1002    2020-01-10
2020-01-18  1002    2020-01-10
2020-01-19  1002    2020-01-10
2020-01-20  1002    2020-01-10
2020-01-21  1002    2020-01-10
2020-01-22  1002    2020-01-10
2020-01-23  1002    2020-01-10
2020-01-24  1002    2020-01-10
2020-01-25  1002    2020-01-10

Solution 3:[3]

You can use a numbers table :

DECLARE @Date1 DATE, @Date2 DATE
    SET @Date1 = '20150528'
    SET @Date2 = '20150531'

SELECT 
      DATEADD(DAY,number+1,@Date1) [Date]
FROM 
      master..spt_values
WHERE 
      type = 'P' AND
      DATEADD(DAY,number+1,@Date1) < @Date2

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 John Cappelletti
Solution 3 Amin Golmahalle

Related Questions