I try to calculate the days of a range of dates, I have something of this style, however I get the amount of the range, my goal is to get the number of the day corresponding to the month.
What it does
Goal
SELECT NOMBRE,RFC,CURP,
DATEDIFF(DAY, CAST(LEFT(FECHA,8) AS DATE),
CAST(RIGHT(FECHA,8) AS DATE))DIAS FROM TABLA
The date is of this form '2018010120180131' for that reason I extract.
Ideally, as Gbianchi has already told you, these problems should be resolved in the view layer and not in the business layer, however, I understand that sometimes this is not possible.
It would be very helpful if you had a date table for each day, so a simple LEFT JOIN to it would solve your problem. As I understand that you do not have it, the alternative is to generate it dynamically. Here the proof of concept:
CREATE TABLE Ejemplo (
Nombre VARCHAR(255),
Fecha VARCHAR(16)
)
INSERT INTO Ejemplo (Nombre, Fecha) VALUES ('LAURA', '2018010120180131')
;WITH CTE AS (
SELECT 1 AS N UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION
SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 0
)
SELECT E.*,
DATEADD(DAY, N.RN-1 , CONVERT(DATETIME,LEFT(E.Fecha,8)))
FROM Ejemplo E
CROSS JOIN (SELECT ROW_NUMBER() OVER(ORDER BY T1.N) AS RN
FROM CTE T1
CROSS JOIN CTE T2
CROSS JOIN CTE T3
) N
WHERE DATEADD(DAY, N.RN-1, CONVERT(DATETIME,LEFT(E.Fecha,8))) <= CONVERT(DATETIME,RIGHT(E.Fecha,8))
DROP TABLE Ejemplo
COMMON TABLE EXPRESSION so that by CROSS JOINS we can generate a% co_of% number of rows, in this case to be 10 rows for three recursions enables us to generate about 1000 rows, adding more X multiply the values, but obviously we should always work with a limit CROSS JOINs we generate the numerator from 1 to 1000 ROW_NUMBER() OVER(ORDER BY T1.N) and we generate each date simply adding the numerator to the initial date and verifying not to exceed the limit