N
Another problem I have is that I can't use the EOMONTH because the version of sql server és AntiguoThat's just a concept, because old, it's only up to whoever looks at it.I'm going to propose you a possible solution, but since I currently have no sql before 2008, or at least the level of database compatibility I can't put it unless, I don't make sure it works.To get an appropriate result, and to know which database you have, it is important to run SELECT @@VERSION, where you get the necessary information.SELECT S.COMPATIBILITY_LEVEL
FROM SYS.DATABASES S
WHERE NAME LIKE 'LAB1'
/*
compatibility_level
100
(1 fila afectada)
*/
GO
CREATE TABLE dbo.CLIENTES (CLIENTE INT, MEDIADIARIA DECIMAL(10,2), FECHAINICIO DATE, FECHAFINAL DATE)
GO
INSERT INTO dbo.CLIENTES (CLIENTE, MEDIADIARIA, FECHAINICIO, FECHAFINAL)
VALUES
(3,578.67,'20191002','20191016'),
(3,86.94, '20191017','20191217'),
(3,95.97, '20191218','20200213');
GO
As you can see, I have put my database, with a 2008 compatibility levelThe first part of the game is that we need that for a difference of months, we can get as many rows, as there are months. There are many ways to do this, but I have been delighted to use a magnificent feature that has made Itzik Ben-Gan available on the web.----------------------------------------------------------------------
-- © Itzik Ben-Gan, SolidQ
-- For more, see 5-day Advanced T-SQL Course:
-- http://tsql.solidq.com/t-sql-courses/
IF OBJECT_ID(N'dbo.GetNums', N'IF') IS NOT NULL DROP FUNCTION dbo.GetNums;
GO
CREATE FUNCTION dbo.GetNums(@low AS BIGINT, @high AS BIGINT) RETURNS TABLE
AS
RETURN
WITH
L0 AS (SELECT c FROM (SELECT 1 UNION ALL SELECT 1) AS D(c)),
L1 AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
L2 AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
L3 AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
L4 AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
L5 AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum
FROM L5)
SELECT TOP(@high - @low + 1) @low + rownum - 1 AS n
FROM Nums
ORDER BY rownum;
GO
If you call this function you return the numbers between the interval you pass (lower, greater).WITH CTE
AS (
--este conjunto no es necesario, pero por si el origen de tus datos fuera otra consulta
SELECT CLIENTES.CLIENTE
, CLIENTES.MEDIADIARIA
, CLIENTES.FECHAINICIO
, CLIENTES.FECHAFINAL
FROM dbo.CLIENTES),
ES_FIN_MES
AS (SELECT CTE.CLIENTE
, CTE.MEDIADIARIA
, CTE.FECHAINICIO
, CTE.FECHAFINAL
, DATEDIFF(MONTH, CTE.FECHAINICIO, CTE.FECHAFINAL) + 1 AS MESES
-- AHORA SABEMOS SI HAY VARIOS MESES
FROM CTE),
NUM_ROWS
AS (SELECT E.CLIENTE
, E.MEDIADIARIA
, E.FECHAINICIO
, E.FECHAFINAL
, E.MESES
, FN.n
FROM ES_FIN_MES AS E
CROSS APPLY dbo.GetNums
( 1, E.MESES ) AS FN
-- UTILIZANDO GETNUMS Tenemos tantas filas como necesitamos (1 por cada mes)
),
CAMBIAR_FECHAS
AS (SELECT N.CLIENTE
, N.MEDIADIARIA
, N.FECHAINICIO
, DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, n - 1, N.FECHAINICIO)), 0) AS PRIMER_DIA_MES
, N.FECHAFINAL
, DATEADD(ms, -3, DATEADD(mm, 1, DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, n - 3, N.FECHAFINAL)), 0))) AS ULTIMO_DIA_MES
, N.MESES
, N.n
FROM NUM_ROWS AS N
-- tenemos el primer y ultimo día del més utilizando el desplazamiento.
)
select C.CLIENTE
, MEDIADIARIA
, CONVERT(VARCHAR(10),
CASE
WHEN C.N = 1 THEN C.FECHAINICIO
ELSE CAST(PRIMER_DIA_MES AS DATE)
END, 103) AS FECHAINICIO
, CONVERT(VARCHAR(10),
CASE
WHEN C.MESES = 1 THEN C.FECHAFINAL
ELSE CASE
WHEN C.MESES = C.N THEN C.FECHAFINAL
ELSE CAST(ULTIMO_DIA_MES AS DATE)
END
END, 103) AS FECHAFINAL
from CAMBIAR_FECHAS AS C;
The first cte is useless, and it could have been omitted. I've exposed it, in case your data comes from another query, you'd only have to embed it in the first cte.In what you have put as a expected exit, there is a small bug, because October should have 31/10/2019 and November 30/11/2019 https://javifer2.wordpress.com/2019/12/15/funciones-de-fecha-para-datetime-utiles/ https://javifer2.wordpress.com/2018/12/18/with-cte-tablas-de-expresion-comun-2-correlativos/ https://tsql.solidq.com/SourceCodes/GetNums.txt
