SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Distribute amount
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rocknpop
Posting Yak Master

184 Posts

Posted - 03/24/2014 :  08:59:44  Show Profile  Reply with Quote
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='2014-01-08 00:00:00',@dateEnd DATETIME='2014-01-13 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

Posted - 03/25/2014 :  16:27:57  Show Profile  Reply with Quote
you need to use the running totals concept... follow this link to an article I wrote about running totals.. It should work fine..

http://sqlsaga.com/sql-server/how-to-calculate-running-totals-in-sql-server/

Visit www.sqlsaga.com for more t-sql snippets and BI related how to's.
Go to Top of Page

rocknpop
Posting Yak Master

184 Posts

Posted - 03/26/2014 :  00:13:24  Show Profile  Reply with Quote
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
Go to Top of Page

robvolk
Most Valuable Yak

USA
15663 Posts

Posted - 03/26/2014 :  09:46:49  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.
Go to Top of Page

rocknpop
Posting Yak Master

184 Posts

Posted - 03/26/2014 :  10:17:03  Show Profile  Reply with Quote
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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17608 Posts

Posted - 03/26/2014 :  10:51:29  Show Profile  Reply with Quote
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
Go to Top of Page

rocknpop
Posting Yak Master

184 Posts

Posted - 03/26/2014 :  11:46:54  Show Profile  Reply with Quote
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='2014-01-08 00:00:00',@dateEnd DATETIME='2014-01-11 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='2014-01-08 00:00:00',@dateEnd DATETIME='2014-01-13 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='2014-01-08 00:00:00',@dateEnd DATETIME='2014-01-13 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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17608 Posts

Posted - 03/26/2014 :  21:05:25  Show Profile  Reply with Quote
quote:

--INPUT 3 -> EXPECTED "Result 3" in the above link
DECLARE @dateStart DATETIME='2014-01-08 00:00:00',@dateEnd DATETIME='2014-01-13 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

Go to Top of Page

rocknpop
Posting Yak Master

184 Posts

Posted - 03/27/2014 :  00:03:37  Show Profile  Reply with Quote
Sorry about that, updated.

Thanks

--------------------
Rock n Roll with SQL
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17608 Posts

Posted - 03/28/2014 :  08:46:42  Show Profile  Reply with Quote
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 break-apart 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
Go to Top of Page

rocknpop
Posting Yak Master

184 Posts

Posted - 03/28/2014 :  11:45:08  Show Profile  Reply with Quote
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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17608 Posts

Posted - 03/28/2014 :  19:36:08  Show Profile  Reply with Quote
amt as
(
	select	*,
		breakAmount	= amount / nights,
		finalAmount	= sum(amount) over()
	from	rcte
)



KH
Time is always against us

Go to Top of Page

rocknpop
Posting Yak Master

184 Posts

Posted - 03/30/2014 :  06:38:02  Show Profile  Reply with Quote
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='2014-01-09 00:00:00'

Thanks

--------------------
Rock n Roll with SQL
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17608 Posts

Posted - 03/30/2014 :  09:30:36  Show Profile  Reply with Quote
so @datestart = '2014-01-09' , @dateEnd '2014-01-09' = 1 night ?
and @datestart = '2014-01-09' , @dateEnd '2014-01-10' also = 1 night ?



KH
Time is always against us


Edited by - khtan on 03/30/2014 09:30:52
Go to Top of Page

rocknpop
Posting Yak Master

184 Posts

Posted - 03/30/2014 :  09:47:44  Show Profile  Reply with Quote
Hi, @datestart = '2014-01-09' , @dateEnd '2014-01-10' = 1 night
and not
@datestart = '2014-01-09' , @dateEnd '2014-01-09'

Thanks


--------------------
Rock n Roll with SQL
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17608 Posts

Posted - 03/30/2014 :  10:45:32  Show Profile  Reply with Quote
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

Go to Top of Page

rocknpop
Posting Yak Master

184 Posts

Posted - 03/30/2014 :  12:36:42  Show Profile  Reply with Quote
Hi, it is showing up for 1 night but it's not considering the lowest amount.

Please check for this:
DECLARE @dateStart DATETIME='2014-01-08 00:00:00',@dateEnd DATETIME='2014-01-09 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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17608 Posts

Posted - 03/30/2014 :  22:22:56  Show Profile  Reply with Quote
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

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17608 Posts

Posted - 03/30/2014 :  23:51:33  Show Profile  Reply with Quote
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

Go to Top of Page

rocknpop
Posting Yak Master

184 Posts

Posted - 03/31/2014 :  01:11:35  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000