Author 
Topic 

rocknpop
Posting Yak Master
184 Posts 
Posted  03/24/2014 : 08:59:44

Hi,
I need to split the amounts by dates. Check the below sample data.
The logic should generate dates from start date to end date (have written a date CTE below) and split the figure in the "amount" column dividing it by the "interval". Next step is to generate remaining dates and remaining amount from the "nightsLeft" column, the amount/interval to consider would be winAmount/winInterval columns. Please check the below pic for the final output. If we can have a generic way of producing the final result.
Here is the final output that is required, need a generic query to produce such a result: http://s2.postimg.org/iq73ww2vt/pic.png
Sample Data:
DECLARE @TBL TABLE (interval TINYINT,amount MONEY,multiplier TINYINT,nightsleft INT,total MONEY, winInterval INT, winAmount MONEY,remainingAmount MONEY,finalAmount MONEY)
this is what we need to split INSERT INTO @TBL VALUES(3,300,1,2,200,2,150,150,350)
SELECT interval, amount, multiplier, nightsLeft, total, winInterval, winAmount, remainingAmount, finalAmount FROM @TBL
TO SPLIT ABOVE AMOUNTS BETWEEN START/END DATE DECLARE @dateStart DATETIME='20140108 00:00:00',@dateEnd DATETIME='20140113 00:00:00'
;WITH dateCTE AS Generate Dates Between Start Date and End Date ( SELECT @dateStart AS startDate, 1 AS NUM UNION ALL SELECT DATEADD(DAY,1,startDate),NUM+1 FROM dateCTE WHERE startDate < @dateEnd  1 )
SELECT * FROM CTE
 Rock n Roll with SQL 

sqlsaga
Yak Posting Veteran
USA
93 Posts 

rocknpop
Posting Yak Master
184 Posts 
Posted  03/26/2014 : 00:13:24

Hi, thanks for the reply. But how would running totals solve the problem? And my apologies, I posted incorrect value in the amounts to split. This is what it looks like:
this is what we need to split INSERT INTO @TBL (interval,amount,multiplier,nightsLeft,total,winInterval,winAmount,remainingAmount,finalAmount)VALUES (3,300,1,2,300,2,300,300,600)
total=amount * multiplier finalAmount=total+remainingAmount. I have provided this column only for reference
We need to split the "total" figure and the "remainingAmount" figure based on interval, winInterval columns respectively. Please check the attached sample pic. So, total=300 and interval=3 thus 300/3 is split into 3 days, next remainingAmount=300 too but winInterval=2 and we have 2 nights Left (As give in the nightsLeft column), thus 300/2 will be split over the rest of the 2 days.
We need a generic query as these intervals and the no. of nights can be any number. Please let me know if you have any questions.
Edit: Ok I understand what you mean when you say use Running totals logic. We can do a cross join with date CTE and then update the breakup column. But we would need something like a quirky update.
Thanks
 Rock n Roll with SQL 
Edited by  rocknpop on 03/26/2014 00:23:12 


robvolk
Most Valuable Yak
USA
15681 Posts 
Posted  03/26/2014 : 09:46:49

I'm not clear about the remaining amount vs. the winAmount, are these related? Can one be calculated from the other? From your first post it sounds like they are, but you don't reference winAmount in the second post. 


rocknpop
Posting Yak Master
184 Posts 
Posted  03/26/2014 : 10:17:03

Hi sorry, it should be winAmount and not remainingAmount that needs to be split.
So we need to split total and winAmount based on interval, winInterval respectively. For the latter calculation, the column nightsLeft also comes into the picture. Here interval/winInterval is the no. of nights, so for example: if nightsLeft=3 and winInterval=4 (4 nights rate), then amount for each of the 3 nights= winAmount/nightsLeft but if winInerval=1 (single night rate), then nightly amount=winAmount.
Similarly the split needs to be done for the total figure which is based on interval. Thus total figure will be split across nights based on the interval column. For example: interval=2 and total=100 then for each night amount=100/2=50, provided we are applying this across 2 nights; for a single night it would only be 100; for 3 nights it would be 50,50,100 and so on.
Also, the winInterval/winAmount columns can be null. But interval/total will always have a value. So we need to keep this in mind while building the logic.
Please let me know if you have any questions.
Thanks
 Rock n Roll with SQL 
Edited by  rocknpop on 03/26/2014 10:19:40 


khtan
In (Som, Ni, Yak)
Singapore
17681 Posts 
Posted  03/26/2014 : 10:51:29

it seems like you have several scenario there, can you post sample data for each and the expected result for each of them ?
also, any relationship between the no of days between @dateStart/@dateEnd and the interval + winInterval ?
KH Time is always against us

Edited by  khtan on 03/26/2014 10:52:59 


rocknpop
Posting Yak Master
184 Posts 
Posted  03/26/2014 : 11:46:54

Hi, Ok I will start from scratch. Here are the inputs and the link which has the expected result corresponding to each input:
http://s23.postimg.org/ezq2gvmjf/result.png
INPUT 1 > EXPECTED "Result 1" in the above link DECLARE @dateStart DATETIME='20140108 00:00:00',@dateEnd DATETIME='20140111 00:00:00' DECLARE @TBL TABLE(ID INT IDENTITY(1,1), interval INT, amount MONEY) INSERT INTO @TBL VALUES (2,100),(3,125),(2,150)
INPUT 2 > EXPECTED "Result 2" in the above link DECLARE @dateStart DATETIME='20140108 00:00:00',@dateEnd DATETIME='20140113 00:00:00' DECLARE @TBL TABLE(ID INT IDENTITY(1,1), interval INT, amount MONEY) INSERT INTO @TBL VALUES (4,300),(2,55)
INPUT 3 > EXPECTED "Result 3" in the above link DECLARE @dateStart DATETIME='20140108 00:00:00',@dateEnd DATETIME='20140113 00:00:00' DECLARE @TBL TABLE(ID INT IDENTITY(1,1), interval INT, amount MONEY) INSERT INTO @TBL VALUES (4,300),(2,55),(1,45)
Explanation: The concept is the same; interval=1=amount per night; ex. if interval=4 then it is an amount for 4 nights and so on and it is split up across the nights. Thus rate would be amount/(no. of nights it is applied on)
We need to calculate the lowest final combination amount and apply it across each night depending upon the search dates.
The first input has 3 amounts with interval of 2,3,2; i.e. amounts for 2 nights, 3 nights, 2 nights respectively. Search is for 3 nights; so we make a combination of all the 3 amounts based on 3 nights and pick the lowest total. For first input (2,100),(3,125),(2,150) for 3 nights , the combinations can be: 100(for 2 nights) + 100 (for 1 night)=200, 100(for 2 nights) + 125(for 1 night)=225, 100(for 2 nights) + 150(for 1 night)=250 150(for 2 nights) + 125 (for 1 night)=275, 125(for 3 nights)=125, 150(for 2 nights) + 150(for 1 night)=300
We can see from the above the lowest figure is 125; once we get this figure we divide this by the nights 125/3 to get daily rate for 3 nights. Now if we look at input3, it found the lowest total across two amounts 55 and 45, so it split accordingly, used amount 55 twice (to span 4 nights) and amount 45 once (for the remaining single night) and split them as per the final result3.
Please let me know if you have any questions.
Thanks
 Rock n Roll with SQL 
Edited by  rocknpop on 03/27/2014 00:04:06 


khtan
In (Som, Ni, Yak)
Singapore
17681 Posts 
Posted  03/26/2014 : 21:05:25

quote:
INPUT 3 > EXPECTED "Result 3" in the above link DECLARE @dateStart DATETIME='20140108 00:00:00',@dateEnd DATETIME='20140113 00:00:00' DECLARE @TBL TABLE(ID INT IDENTITY(1,1), interval INT, amount MONEY) INSERT INTO @TBL VALUES (4,300,0,0),(2,55,0,0),(1,45,0,0)
what is the missing 2 column for @TBL ? please update your last post
KH Time is always against us



rocknpop
Posting Yak Master
184 Posts 
Posted  03/27/2014 : 00:03:37

Sorry about that, updated.
Thanks
 Rock n Roll with SQL 


khtan
In (Som, Ni, Yak)
Singapore
17681 Posts 
Posted  03/28/2014 : 08:46:42

There are actually 2 main issue here 1. to find the combination with lowest amount 2. to apportion the amount by dates
declare @nights int
select @nights = datediff(day, @dateStart, @dateEnd)
; with
numbers as  RCTE numbers / tally table
(
select n = 1
union all
select n = n + 1
from numbers
where n < 10
),
rcte as  to find the combination with lowest amount
(
select top 1
seq = 1,
ID, interval, amount, nights = interval, cumm_nights = interval
from @TBL
where interval <= @nights
order by amount / interval
union all
select seq, ID, interval, amount, nights, cumm_nights
from
(
select seq = r.seq + 1,
t.ID, t.interval, t.amount,
nights = case when t.interval <= @nights  r.cumm_nights
then t.interval
else @nights  r.cumm_nights
end,
cumm_nights = r.cumm_nights
+ case when t.interval <= @nights  r.cumm_nights
then t.interval
else @nights  r.cumm_nights
end,
rn = row_number() over (order by case when (@nights  r.cumm_nights) >= t.interval then t.amount / t.interval else t.amount end)
from @TBL t
cross join rcte r
where r.cumm_nights < @nights
) r
where rn = 1
and cumm_nights <= @nights
),
amt as  to apportion the amount
(
select *,
breakAmount = amount / interval nights,
finalAmount = sum(amount) over()
from rcte
)
 to breakapart by dates
select *, [date] = dateadd(day, row_number() over(order by seq)  1, @dateStart)
from amt a
cross join numbers n
where n.n <= a.nights
order by seq
KH Time is always against us

Edited by  khtan on 03/28/2014 21:28:10 


rocknpop
Posting Yak Master
184 Posts 
Posted  03/28/2014 : 11:45:08

Hi KH, thanks a lot for this.
There seems to be one minor hiccup with the 2nd output. Though the finalAmount is correct as 165 but the amount in the last day 12th should be 55 whereas this is showing up as 27.50. Since the amount needs to be applied only to one night so it is 55.
Can you please look into this?
Thanks
 Rock n Roll with SQL 


khtan
In (Som, Ni, Yak)
Singapore
17681 Posts 
Posted  03/28/2014 : 19:36:08

amt as
(
select *,
breakAmount = amount / nights,
finalAmount = sum(amount) over()
from rcte
)
KH Time is always against us



rocknpop
Posting Yak Master
184 Posts 
Posted  03/30/2014 : 06:38:02

Hi KH, working fine now but another minor issue. If we run it only for a single night it's not producing any output. Please check by making @dateEnd DATETIME='20140109 00:00:00'
Thanks
 Rock n Roll with SQL 


khtan
In (Som, Ni, Yak)
Singapore
17681 Posts 
Posted  03/30/2014 : 09:30:36

so @datestart = '20140109' , @dateEnd '20140109' = 1 night ? and @datestart = '20140109' , @dateEnd '20140110' also = 1 night ?
KH Time is always against us

Edited by  khtan on 03/30/2014 09:30:52 


rocknpop
Posting Yak Master
184 Posts 
Posted  03/30/2014 : 09:47:44

Hi, @datestart = '20140109' , @dateEnd '20140110' = 1 night and not @datestart = '20140109' , @dateEnd '20140109'
Thanks
 Rock n Roll with SQL 


khtan
In (Som, Ni, Yak)
Singapore
17681 Posts 
Posted  03/30/2014 : 10:45:32

change the anchor port of the rcte
rcte as
(
select top 1
seq = 1,
ID, interval, amount,
nights = case when interval < @nights then interval else @nights end,
cumm_nights = case when interval < @nights then interval else @nights end
from @TBL
order by amount / interval
union all
KH Time is always against us



rocknpop
Posting Yak Master
184 Posts 
Posted  03/30/2014 : 12:36:42

Hi, it is showing up for 1 night but it's not considering the lowest amount.
Please check for this: DECLARE @dateStart DATETIME='20140108 00:00:00',@dateEnd DATETIME='20140109 00:00:00' DECLARE @TBL TABLE(ID INT IDENTITY(1,1), interval INT, amount MONEY) INSERT INTO @TBL VALUES (4,300),(2,55),(1,45)
It's showing up 55 whereas it should be 45.
Thanks
 Rock n Roll with SQL 


khtan
In (Som, Ni, Yak)
Singapore
17681 Posts 
Posted  03/30/2014 : 22:22:56

try
rcte as
(
select top 1
seq = 1,
ID, interval, amount,
nights = case when interval < @nights then interval else @nights end,
cumm_nights = case when interval < @nights then interval else @nights end
from @TBL
order by amount / interval (case when interval < @nights then interval else @nights end)
union all
KH Time is always against us



khtan
In (Som, Ni, Yak)
Singapore
17681 Posts 
Posted  03/30/2014 : 23:51:33

Added comments in green
declare @nights int
select @nights = datediff(day, @dateStart, @dateEnd)
; with
numbers as  recursive CTE number / tally tables, if you have one, you don't need this
(
select n = 1
union all
select n = n + 1
from numbers
where n < 10  max n is 10. Change accordingly to requirement
),
rcte as  using recursive CTE to determine the lowest amount combinition
(
 Anchor Member (get the record with lowest amount)
select top 1
seq = 1,  Anchor Member, seq = 1. Used for ordering final result
ID, interval, amount,
nights = case when interval < @nights then interval else @nights end,  actual no of nighs
cumm_nights = case when interval < @nights then interval else @nights end  cummulative nights
from @TBL
order by amount / (case when interval < @nights then interval else @nights end)  amount / actual no of nights = lowest amount
union all
 Recursive Member (continue getting lowest amount based on balance nights)
select seq, ID, interval, amount, nights, cumm_nights
from
(
select seq = r.seq + 1,  Increment the seq number
t.ID, t.interval, t.amount,
nights = case when t.interval <= @nights  r.cumm_nights  if interval is less than the balance nights
then t.interval  actual no of nights = interval
else @nights  r.cumm_nights  if interval is more than the balance nights, actual = balance
end,
cumm_nights = r.cumm_nights
+ case when t.interval <= @nights  r.cumm_nights
then t.interval
else @nights  r.cumm_nights
end,
rn = row_number() over (order by case when (@nights  r.cumm_nights) >= t.interval
then t.amount / t.interval
else t.amount
end)
from @TBL t
cross join rcte r
where r.cumm_nights < @nights
) r
where rn = 1  get the lowest amount
and cumm_nights <= @nights
),
amt as  calculate the break amount
(
select *,
breakAmount = amount / nights,
finalAmount = sum(amount) over()
from rcte
)
 final result that break up the dates
select *, [date] = dateadd(day, row_number() over(order by seq)  1, @dateStart)
from amt a
cross join numbers n
where n.n <= a.nights
order by seq
KH Time is always against us



rocknpop
Posting Yak Master
184 Posts 
Posted  03/31/2014 : 01:11:35

Hi, it's working perfectly and thanks for the comments. I will let you know in case there is any scenario I may have missed and not able to solve the same.
Thanks for your help.
 Rock n Roll with SQL 



Topic 


