August 8, 2016 at 3:27 am
Hi Friends,
please some one help me how can i get my Expected output.?
Sample Input :
Start End Amount
14-Apr-1413-May-14 200
15-May-1416-Jun-14 320
Expected Output
Start End Amount
14-Apr-1430-Apr-14 100
01-May-1413-May-14 100
15-May-1431-May-14 160
01-Jun-1416-Jun-14 160
--Create Table
CREATE TABLE TestSplitData
(
Start DATETIME
,EndDt DATETIME
,Amount INT
)
GO
--Insert Data
INSERT INTO TestSplitData(Start,EndDt,Amount)
VALUES
('14-Apr-14','13-May-14',200),
('15-May-14','16-Jun-14',320)
August 8, 2016 at 7:53 am
Anandkumar-SQL_Developer (8/8/2016)
Hi Friends,please some one help me how can i get my Expected output.?
Sample Input :
Start End Amount
14-Apr-1413-May-14 200
15-May-1416-Jun-14 320
Expected Output
Start End Amount
14-Apr-1430-Apr-14 100
01-May-1413-May-14 100
15-May-1431-May-14 160
01-Jun-1416-Jun-14 160
--Create Table
CREATE TABLE TestSplitData
(
Start DATETIME
,EndDt DATETIME
,Amount INT
)
GO
--Insert Data
INSERT INTO TestSplitData(Start,EndDt,Amount)
VALUES
('14-Apr-14','13-May-14',200),
('15-May-14','16-Jun-14',320)
A simple Tally Table is all you need here. I created a function to do the work just pass in your Table and it should give you the results you expect.
Function
CREATE FUNCTION fnSplitDates(@StartDate DATETIME,@EndDate DATETIME)
RETURNS @tblResult TABLE
(
StartDate DATETIME,
modEndDate DATETIME,
timesSplit INT
)
AS
BEGIN
DECLARE @MonthDiff INT
IF @EndDate > @StartDate --Dont Do this if The END DATE IS GREATER THAN START DATE
BEGIN
SET @MonthDiff = DATEDIFF(MONTH, @StartDate, @EndDate)
;WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),
E2(N) AS (SELECT 1 FROM E1 a, E1 b),
E4(N) AS (SELECT 1 FROM E2 a, E2 b),
E5(N) AS (SELECT 1 FROM E4 a, E2 b),
cteTally(N) AS (
SELECT 0 UNION ALL
SELECT TOP (@MonthDiff) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E5
)
INSERT INTO @tblResult SELECT [FIRST DAY] = CASE WHEN DATEADD(MONTH, N, CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@StartDate)-1),@StartDate),101)) < @StartDate THEN @StartDate ELSE DATEADD(MONTH, N, CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@StartDate)-1),@StartDate),101)) END,
[LAST DAY] = CASE WHEN DATEADD(MONTH, N, CONVERT(VARCHAR(25),DATEADD(ms, -3, DATEADD(MONTH, 1, DATEADD(dd,-(DAY(@StartDate)-1),@StartDate))),101)) > @EndDate THEN @EndDate ELSE DATEADD(MONTH, N, CONVERT(VARCHAR(25),DATEADD(ms, -3, DATEADD(MONTH, 1, DATEADD(dd,-(DAY(@StartDate)-1),@StartDate))),101)) END
, (SELECT MAX(N) FROM cteTally)
FROM [cteTally]
END
RETURN
END
Now the call from your table to the function above...
SELECT (t2.StartDate),(t2.modEndDate),Amount/((t2.TimesSplit)+1) as SplitAmt FROM testsplitdata tsd
CROSS APPLY dbo.fnSplitDates(tsd.[start],tsd.Enddt) t2
As a verification test I also added the following record which spans 3 months...
INSERT INTO TestSplitData([Start],EndDT,Amount) VALUES ('07-01-2014','09-15-2014',1050)
Results in...
2014-04-14 00:00:00.0002014-04-30 00:00:00.000100
2014-05-01 00:00:00.0002014-05-13 00:00:00.000100
2014-05-15 00:00:00.0002014-05-31 00:00:00.000160
2014-06-01 00:00:00.0002014-06-16 00:00:00.000160
2014-07-01 00:00:00.0002014-07-31 00:00:00.000350
2014-08-01 00:00:00.0002014-08-31 00:00:00.000350
2014-09-01 00:00:00.0002014-09-15 00:00:00.000350
*EDIT* Removed the GROUP BY on Tally Table as I changed the split counter to (SELECT MAX(N)....)
August 8, 2016 at 7:55 am
And now you have 2 threads with the same question and very similar answers. You only divided the replies and confused people on what version you're using.
http://www.sqlservercentral.com/Forums/Topic1807873-3412-1.aspx
August 8, 2016 at 8:02 am
Luis Cazares (8/8/2016)
And now you have 2 threads with the same question and very similar answers. You only divided the replies and confused people on what version you're using.http://www.sqlservercentral.com/Forums/Topic1807873-3412-1.aspx
hrmm I just read the other thread and this will NOT work as you want the split (n line) begin date to be the original start date....I will defer to the other thread....
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply