All i need is the right query for this....

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

All i need is the right query for this....

Author

Topic

tishri
Yak Posting Veteran

95 Posts

Posted - 2007-07-06 : 06:26:55

declare @tmp_table table(account_no int, balance smallmoney, last_charged smalldatetime, monthlycharge smallmoney)

insert into @tmp_table
select 5 as account_no, 1050 as balance, '07/07/2006' as last_charged , 350 as monthlycharge
union all select 8,370, '09/07/2006' ,350.50
union all select 10,2000, '07/15/2006',500
union all select 11,750.50, '07/12/2006',350.50

--if balance / monthlycharge has remainder then
-- set the remainder as the last_date_charged unpaid amount

select * , convert(int,(balance / monthlycharge))
+ case when (cast(balance as int) % cast(monthlycharge as int)) > 0 then 1 else 0 end as number_of_months
, round(((balance / monthlycharge) - convert(int, balance / monthlycharge) ) * monthlycharge,2) as last_charged_unpaid_amount
from @tmp_table t



/*
account_no	balance		last_charged		monthlyCharge	number_of_months	last_charged_unpaid_amount
----------	-------		-------------------	-------------	----------------	--------------------------
5		1050.0000	2006-07-07 00:00:00	350.0000	3			.0000	
8		370.0000	2006-07-07 00:00:00	350.5000	1			19.4900	
10		2000.0000	2006-07-15 00:00:00	500.0000	4			.0000	
11		750.5000	2006-07-12 00:00:00	350.5000	2			49.4900	


if last_charged_unpaid_amount not equal to zero then the first value for 
	date_charged will be equal to last_charged 
	and amount_charged will be equal to last_charged_unpaid_amount
else
	increment last_charged by 1 month
	and amount_charged will be equal to monthlycharge

to produce a result like this

account_no	balance		last_charged		monthlyCharge	number_of_months_covered	last_charged_unpaid_amount	date_charged		amount_charged
----------	-------		-------------------	-------------	------------------------	--------------------------	------------		--------------
5		1050.0000	2006-07-07 00:00:00	350.0000	3				.0000				2006-08-07 00:00:00	350.0000
5		1050.0000	2006-07-07 00:00:00	350.0000	3				.0000				2006-09-07 00:00:00	350.0000
5		1050.0000	2006-07-07 00:00:00	350.0000	3				.0000				2006-10-07 00:00:00	350.0000
8		370.0000	2006-09-07 00:00:00	350.5000	2				19.4900				2006-09-07 00:00:00	19.4900
8		370.0000	2006-09-07 00:00:00	350.5000	2				19.4900				2006-10-07 00:00:00	350.5000
10		2000.0000	2006-07-15 00:00:00	500.0000	4				.0000				2006-08-15 00:00:00	500.0000
10		2000.0000	2006-07-15 00:00:00	500.0000	4				.0000				2006-09-15 00:00:00	500.0000
10		2000.0000	2006-07-15 00:00:00	500.0000	4				.0000				2006-10-15 00:00:00	500.0000
10		2000.0000	2006-07-15 00:00:00	500.0000	4				.0000				2006-11-15 00:00:00	500.0000
11		750.5000	2006-07-12 00:00:00	350.5000	3				49.4900				2006-07-12 00:00:00	49.0000
11		750.5000	2006-07-12 00:00:00	350.5000	3				49.4900				2006-08-12 00:00:00	350.5000
11		750.5000	2006-07-12 00:00:00	350.5000	3				49.4900				2006-09-12 00:00:00	350.5000

and by getting only the necessary fields it would result like this.
The result below is the final result that i would like to gather. You can also suggest another approach so that
i can derive the result below based on the table values given above.


account_no	date_charged		amount_charged
----------	------------		--------------
5		2006-08-07 00:00:00	350.0000
5		2006-09-07 00:00:00	350.0000
5		2006-10-07 00:00:00	350.0000
8		2006-09-07 00:00:00	19.4900
8		2006-10-07 00:00:00	350.5000
10		2006-08-15 00:00:00	500.0000
10		2006-09-15 00:00:00	500.0000
10		2006-10-15 00:00:00	500.0000
10		2006-11-15 00:00:00	500.0000
11		2006-07-12 00:00:00	49.0000
11		2006-08-12 00:00:00	350.5000
11		2006-09-12 00:00:00	350.5000


*/

TCC

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-06 : 07:11:00

Try this. I can't test it right now as I don't have SQL Server 2005 nearby.

-- prepare sample data
declare @sample table(account_no int, balance smallmoney, last_charged smalldatetime, monthlycharge smallmoney)

insert	@sample (account_no, balance, last_charged, monthlycharge)
select	5, 1050, '07/07/2006', 350 union all
select	8, 370, '09/07/2006', 350.50 union all
select	10, 2000, '07/15/2006', 500 union all
select	11, 750.50, '07/12/2006', 350.50

--show the expected output
select		s.account_no,
		s.balance,
		dateadd(month, d.number, s.last_charged) as last_charged,
		s.monthlycharge
from		@sample as s
cross apply	(
			select	number
			from	master..spt_values
			where	number between 1 and floor(s.balance / s.monthlycharge)
				and type = 'p'
		) AS d
where		floor(s.balance / s.monthlycharge) > 0

union all

select		account_no,
		balance,
		last_charged,
		balance - monthlycharge * floor(balance / monthlycharge)
from		@sample
where		balance - monthlycharge * floor(balance / monthlycharge) > 0

order by	account_no,
		last_charged

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-06 : 07:19:25

Also see
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76197
and
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76143

Peter Larsson
Helsingborg, Sweden

Ifor
Aged Yak Warrior

700 Posts

Posted - 2007-07-06 : 07:41:29

SELECT account_no
    ,CASE
        WHEN D.last_charged_unpaid_amount <> 0.0
        THEN DATEADD(month, N.N - 1, D.last_charged)
        ELSE DATEADD(month, N.N, D.last_charged)
    END AS date_charged
    ,CASE
        WHEN D.last_charged_unpaid_amount <> 0.0
        THEN CASE N.N
            WHEN 1
            THEN D.last_charged_unpaid_amount
            ELSE D.monthlycharge
            END
        ELSE D.monthlycharge
    END
FROM (
        SELECT T.*
            ,CAST(T.balance/T.monthlycharge AS int) +
                CASE
                    WHEN CAST(T.balance AS int) % cast(T.monthlycharge AS int) > 0
                    THEN 1
                    ELSE 0
                END AS number_of_months
            , ROUND((T.balance/T.monthlycharge - CAST(T.balance/T.monthlycharge AS int)) * T.monthlycharge, 2)
                AS last_charged_unpaid_amount
        FROM @tmp_table T
    ) D
    JOIN (
            SELECT 1 UNION ALL
            SELECT 2 UNION ALL
            SELECT 3 UNION ALL
            SELECT 4
            -- etc
        ) N (N) -- or use Number/Tally table
        ON  N.N <= D.number_of_months

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-06 : 07:57:43

quote:

Originally posted by Ifor

    JOIN (
            SELECT 1 as n UNION ALL
            SELECT 2 UNION ALL
            SELECT 3 UNION ALL
            SELECT 4
            -- etc
        ) N (N) -- or use Number/Tally table
        ON  N.N <= D.number_of_months

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-06 : 08:17:00

Ifor, try this test data. it will not function correctly.

select	99, 100, '07/15/2006', 34 union all

Do you have SQL Server 2005? Try my suggestion with this test data too, please.

Peter Larsson
Helsingborg, Sweden

Ifor
Aged Yak Warrior

700 Posts

Posted - 2007-07-06 : 09:24:44

Peso,

1. I do not seem to need to put 'as n' in the derived table as long as I put '(N)' after the alias.

2. Thanks for providing data that points out my logic error.
When I replace all occurances of CAST(T.balance/T.monthlycharge AS int) with
FLOOR(T.balance/T.monthlycharge) my query produces the desired results.

3. When I tried the queries in SQL2005, on a desktop machine, the execution plan produced the following querty costs relative to the batch:
a) Create Test Data - 17%
b) My altered query - 6%
c) Your query - 78%

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-06 : 10:13:03

Slightly different if you also put an order by to your query

Still in your favor though!

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-06 : 10:16:07

And try this test data

select	99, 7, '07/15/2006', 3.50 union all

Peter Larsson
Helsingborg, Sweden

Ifor
Aged Yak Warrior

700 Posts

Posted - 2007-07-06 : 10:45:31

Peso,

A good point about the ORDER BY.

quote:
select 99, 7, '07/15/2006', 3.50 union all

OK! The WHEN CAST(T.balance AS int) % cast(T.monthlycharge AS int) > 0
should be WHEN T.balance % T.monthlycharge > 0.0.
(A lesson in not mindlessly copying someone else's logic!

)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-06 : 10:55:19

And replacing your hardcoded tally table with the use of master..spt_values (or changing my query to hardcoded tally table) changed the scene.
Now both are 50% each!

--show the expected output with improved algorithm for SQL Server 2000
select		s.account_no,
		dateadd(month, d.number, s.last_charged) as last_charged,
		case
			when d.number = 0 and s.balance > s.monthlycharge * floor(s.balance / s.monthlycharge) then s.balance - s.monthlycharge * floor(s.balance / s.monthlycharge) 
			when d.number > 0 then s.monthlycharge
		end as monthlycharge
from		@sample as s
inner join	(
			select	number
			from	master..spt_values
			where	number between 0 and 4
				and type = 'p'
		) AS d ON d.number <= floor(s.balance / s.monthlycharge)
where		balance > monthlycharge * floor(balance / monthlycharge)
		or d.number > 0
order by	1,
		2

--show the expected output with improved algorithm for SQL Server 2005
select		s.account_no,
		dateadd(month, d.number, s.last_charged) as last_charged,
		case
			when d.number = 0 and s.balance > s.monthlycharge * floor(s.balance / s.monthlycharge) then s.balance - s.monthlycharge * floor(s.balance / s.monthlycharge) 
			when d.number > 0 then s.monthlycharge
		end as monthlycharge
from		@sample as s
cross apply	(
			select	number
			from	master..spt_values
			where	number between 0 and floor(s.balance / s.monthlycharge)
				and type = 'p'
		) AS d
where		balance > monthlycharge * floor(balance / monthlycharge)
		or d.number > 0
order by	1,
		2

I could not test my SQL Server 2005 query.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-06 : 10:57:04

quote:
Originally posted by Ifor

The WHEN CAST(T.balance AS int) % cast(T.monthlycharge AS int) > 0
should be WHEN T.balance % T.monthlycharge > 0.0.

This does not work for smallmoney datatype.

Peter Larsson
Helsingborg, Sweden

tishri
Yak Posting Veteran

95 Posts

Posted - 2007-07-06 : 21:08:36

yeah perfect guys!!

TCC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources