CODEWITHSUNDEEP
sql-server
Mohammad Sultan
Mohammad Sultan

Reputation: 15

How to make a sql server query that converts one row with From Date To Date columns to multiple rows for each day or each month

How to make a sql server query that converts one row with From Date To Date columns to multiple rows for each day or each month? I have the row shown in the attached image, what I want is to view each loan installment in a separate row according to its monthTable Row

Upvotes: 0

Views: 63

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93724

You need a calendar table 

;WITH calendar
     AS (SELECT dates = CONVERT(DATETIME, '2012-04-22') -- pass the minimum date range
         UNION ALL
         SELECT dates = Dateadd(Day, 1, dates)
         FROM   calendar
         WHERE  dates < '2017-02-27') -- pass the maximum date range
SELECT *
FROM   calendar c
       JOIN yourtable y
         ON c.dates BETWEEN cast(y.StartDate as date) AND cast(y.EndDate as date)

Since you care about minimum of day level I have removed time part from StartDate and EndDate in Join condition.

If you want the result in monthly level the replace Day with Month in DATEADD function Dateadd(Month, 1, dates)

I have used Recursive CTE to generate dates, but it is always good to have calendar table created in our database.

Upvotes: 0

Tyron78
Tyron78

Reputation: 4187

This can be done by recursive cte, but without additional joinig as well:

DECLARE @t TABLE (Test varchar(5), DateFrom date, DateTo date);

INSERT INTO @t VALUES ('a', '2016-01-15', '2016-01-31'), ('b', '2016-12-01', '2016-12-11');

WITH cte AS(
  SELECT Test, DateFrom, DateTo
    FROM @t
  UNION ALL
  SELECT Test, Dateadd(d, 1, DateFrom), DateTo
    FROM cte
    WHERE Dateadd(d, 1, DateFrom) <= DateTo
)
SELECT Test, DateFrom AS YourDate
  FROM cte
  ORDER BY 1, 2

Upvotes: 1

Related Questions