–Run this Quiery
——ELECTRIC BILL
DECLARE @a as table(sno int,start int,finish int,rate numeric(5,2))
–INSERT INTO @a VALUES(1,0,100,50),(2,101,200,100),(3,201,300,150),(4,301,400,200),(5,401,500,250),(6,501,600,350)
INSERT INTO @a VALUES(1,1,100,0.5),(2,101,200,0.10),(3,201,300,0.15),(4,301,400,0.20),(5,401,500,0.25),(6,501,600,0.30),(7,601,700,0.35),(8,701,NULL,0.40)

–BILL AMOUNT
–DECLARE @b as table(bill int)

–INSERT @b VALUES(1000)
DECLARE @AMT INT =5000

—FIND A RATE

SELECT *,CASE WHEN T.Reading>=T.SumFinish AND T.Finish IS NOT NULL THEN T.Finish ELSE T.Reading-(T.SumFinish-(ISNULL(T.Finish,0))) END AS bal FROM
(
SELECT B.sno,MAX(B.Finish) Finish,MAX(A.start) as start ,SUM(A.finish) SumFinish,MAX(B.rate) rate,MAX(b.AMT) Reading FROM
(SELECT sno,start,finish,rate,@AMT AMT FROM @A) A
CROSS JOIN
(SELECT sno,start,finish,rate,@AMT AMT FROM @A) B
WHERE A.start<=B.start
GROUP BY B.SNO
)
T
where T.Reading>=T.start

———
SELECT T2.Reading,SUM(T2.rate*t2.bal) AMT FROM
(
SELECT *,CASE WHEN T.Reading>=T.SumFinish AND T.Finish IS NOT NULL THEN T.Finish ELSE T.Reading-(T.SumFinish-(ISNULL(T.Finish,0))) END AS bal FROM
(
SELECT B.sno,MAX(B.Finish) Finish,MAX(A.start) as start ,SUM(A.finish) SumFinish,MAX(B.rate) rate,MAX(b.AMT) Reading FROM
(SELECT sno,start,finish,rate,@AMT AMT FROM @A) A
CROSS JOIN
(SELECT sno,start,finish,rate,@AMT AMT FROM @A) B
WHERE A.start<=B.start
GROUP BY B.SNO
)
T
where T.Reading>=T.start
)
T2
WHERE T2.bal>0
GROUP by T2.Reading
–SELECT * FROM @a
SELECT * FROM @a

Advertisements