August 8, 2016 at 4:38 am
Hi Friends,
please some one help me how can i get my Expected output.?
Sample Input :
Start End Amount
14-Apr-1413-May-14200
15-May-1416-Jun-14320
Expected Output
Start End Amount
14-Apr-1430-Apr-14100
01-May-1413-May-14100
15-May-1431-May-14160
01-Jun-1416-Jun-14160
--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 4:47 am
Do StartDate and EndDate always straddle a month boundary, and if so do they always straddle exactly one? Is the Amount to be divided evenly between the months regardless of what the in-month dates are (for example, would it still be 100 and 100 if StartDate were 28th April and EndDate were 27th May)? What have you tried so far?
John
August 8, 2016 at 4:59 am
Actually ,
if particular amount will collected between 2 months means .., i need to divide them and
start date is given date and end date is end day of the same month.
and second of end date is given date and start date month first day of the same month.
August 8, 2016 at 7:18 am
Here's a possibility using an inline table function for this.
CREATE TABLE TestSplitData
(
Start DATETIME
,EndDt DATETIME
,Amount INT
)
GO
--Insert Data
INSERT INTO TestSplitData(Start,EndDt,Amount)
VALUES
('20140414','20141013',200),
('15-May-14','16-Jun-14',320)
GO
CREATE FUNCTION SplitDatesInMonths(
@StartDt datetime = '20140414',
@EndDt datetime = '20141013'
) RETURNS TABLE WITH SCHEMABINDING AS
RETURN
WITH
E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
SELECT a.n FROM E a, E b
),
E4(n) AS(
SELECT a.n FROM E2 a, E2 b
),
cteTally(n) AS(
SELECT TOP(DATEDIFF(MM, @StartDt, @EndDt) + 1) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 n
FROM E4
)
SELECT
CASE WHEN n = 0 THEN @StartDt ELSE DATEADD(MM, DATEDIFF( MM, 0, @StartDt) + n, 0) END AS Start,
CASE WHEN DATEADD(MM, DATEDIFF( MM, 0, @StartDt) + n, 31) > @EndDt THEN @EndDt ELSE DATEADD(MM, DATEDIFF( MM, 0, @StartDt) + n, 30) END AS EndDt
FROM cteTally;
GO
SELECT sp.Start, sp.EndDt, t.Amount / (DATEDIFF(MM, t.Start, t.EndDt) + 1.) AS Amount
FROM TestSplitData t
CROSS APPLY dbo.SplitDatesInMonths( t.Start, t.EndDt) sp
GO
DROP TABLE TestSplitData;
August 8, 2016 at 10:32 pm
Thank u Luis Cazares ,
your code is working awesome.
August 9, 2016 at 7:32 am
Anandkumar-SQL_Developer (8/8/2016)
Thank u Luis Cazares ,your code is working awesome.
And do you understand how it works?
What will happen when you need to support it to make changes or fix an error?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply