Tags
Complex SQL Challenges, Complex TSQL Challenge, Interview Qs.SQL SERVER Questions, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Learn complex SQL, Learn SQL, Learn T-SQL, PL/SQL Challenges, Puzzles, Queries for SQL Interview, SQL, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Challenge, SQL Challenges, SQL pl/sql puzzles, SQL Puzzles, SQL SERVER Interview questions, SQL SERVER2005/2008, SQL Sudoku, SQLSERVER, T SQL Puzzles, T-SQL Challenge, Tough SQL Challenges, Tough SQL Puzzles, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Queries
T-SQL Query | [ Normalize (Divide) Amount between Months ] – In this puzzle we have Normalize (Divide) Amount between Months. Please check out the sample input and expected output for details.
Sample Input
| Start | End | Amount |
| 14-Apr-14 | 13-May-14 | 200 |
| 15-May-14 | 16-Jun-14 | 320 |
Expected Output
| Start | End | Amount |
| 14-Apr-14 | 30-Apr-14 | 100 |
| 01-May-14 | 13-May-14 | 100 |
| 15-May-14 | 31-May-14 | 160 |
| 01-Jun-14 | 16-Jun-14 | 160 |
Rules/Restrictions
- The solution should be should use “SELECT” statement or “CTE”.
- Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com
Script
Use the below script to generate the source table and fill them up with the sample data.
--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)
--Verify Data
SELECT Start,EndDt,Amount FROM TestSplitData
|
Please leave a comment if you need solution to the above puzzle
Keep Learning
Improving my SQL BI Skills 
Solution for this problem is
WITH CTE_1
AS
(
SELECT
[Start]
,CASE
WHEN
Start=CAST(( DATEADD(month,DATEDIFF(month,0,EndDt),0))AS DATE)
THEN Start
ELSE
CAST(( DATEADD(month,DATEDIFF(month,0,EndDt),0))AS DATE)
END AS ActualStart,
EndDT,
CASE
WHEN EndDt=EOMONTH(Start)
THEN EndDt
ELSE
EOMONTH(Start)
END
AS ActualEndDATE
,Amount
FROM TestSplitData
)
SELECT CONVERT(varchar,START,106) AS START
,CONVERT(varchar,ActualEndDATE,106) As[END],(Amount/2) AS Amount
FROM CTE_1
UNION
SELECT CONVERT(varchar,ActualStart,106) AS Start,
CONVERT(varchar,ENdDT,106) As [End],(Amount/2) AS Amount
FROM CTE_1
ORDER BY START
LikeLike
Wah
LikeLike
;with cte
as
(
select Start,EndDt,amount/2 as amt
,DATEADD(MONTH,datediff(MONTH,’20010131′,Start),’20010131′) as enddateofmonth
,DATEADD(MONTH,datediff(MONTH,’20010101′,EndDt),’20010101′) as startofmonth
from #TestSplitData
)
select start,enddateofmonth as enddt,amt as amount
from cte
union all
select startofmonth,EndDt,amt
from cte
order by amt
LikeLiked by 1 person
select StartDate as [Start],
eomonth(StartDate) as [End],
Amt / (datediff(mm, StartDate, EndDate) + 1) as [Amount]
from tblSplitData
union
select dateadd(dd, 1, eomonth(StartDate)) as [Start],
EndDate as [End],
Amt / (datediff(mm, StartDate, EndDate) + 1) as [Amount]
from tblSplitData
order by [Start]
LikeLiked by 1 person
Nice..
LikeLike
;with cte as
(
select Start,Dateadd(MM,datediff(MM,0,Start),30)[end],Amount/2 Amount,Row_number()over(partition by amount order by Start)rno from
(
SELECT Start,Amount FROM #TestSplitData
union all
select EndDt,Amount FROM #TestSplitData
)t
)
select Case when rno=2 then dateadd(mm,datediff(MM,0,[end]),0) else Start End Start,
Case when rno=2 then Start else [end] end [end],Amount
from cte
LikeLike