 7 replies

Last post Mar 30, 2017 10:23 PM by limno

• Create a view from this information

Hello everyone, I have one problem. Before anything, here is my table called Limiter_Table:

invoice_id              limiter             rate

1                            20000                 5

1                            60000                 2

1                              null                   1

5                             10000                2

5                             20000                1

So here is what I want to do. For invoice 1, the sum amount is \$70000. So here how the rate would work out:

For 0 - 20000 it's 5% so it's 20000*5/100 = 1000

For 20000 - 60000 it's 2% so it's (60000-20000)*2/100 = 800

For 60000 upward, it's 1% so it's (70000-60000)*1/100 = 100

Now invoice 5, the sum amount is \$9000, so here how the rates work out:

For 0 - 10000 it's 2%, so it's 9000*2/100=180

So in my view I want row one to be:

invoice_id            invoice_amount      limiter_amount

1                            70000                         1900

5                              9000                         180

3                            10000                            0

• Re: Create a view from this information

asplearning

Is that hard to post T-SQL question with table DDL and sample data inserts script?

Thanks.

Jingyang Li
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm
• Re: Create a view from this information

Thank you Limno. No it's not hard to create that. I will do that. But which part of my question is not clear to you. I am more than happy to explain.

Thanks.

• Re: Create a view from this information

Hello everyone here is my sql script:

```DECLARE  @tbl TABLE
(
invoice_id int
,limiter decimal(18,2)
,rate decimal(18,2)
,invoice_amount decimal(18,2)

)

insert into @tbl values(1, 20000, 5, 70000)
insert into @tbl values(1, 60000, 2, 70000)
insert into @tbl values(1, null, 1, 70000)
insert into @tbl values(5, 10000, 2, 9000)
insert into @tbl values(5, 20000, 1, 9000)

select * from @tbl
```

The invoice_id is actually a foreign key of the invoice table, so the invoice amount is actually calculated from another query. For simplicity I just set that 70000 and 9000 respectively.

• Re: Create a view from this information

```DECLARE  @tbl TABLE
(
invoice_id int
,limiter decimal(18,2)
,rate decimal(18,2)
,invoice_amount decimal(18,2)

)

insert into @tbl values(1, 20000, 5, 70000)
insert into @tbl values(1, 60000, 2, 70000)
insert into @tbl values(1, null, 1, 70000)
insert into @tbl values(5, 10000, 2, 9000)
insert into @tbl values(5, 20000, 1, 9000)

;with mycte as (
select t.invoice_id, t.limiter, t.invoice_amount, t.rate
,  lag(t.limiter) Over(Partition by t.invoice_id Order by ISNULL(t.limiter,99999999))  pre_limiter
from @tbl t)

select DISTINCT invoice_id, invoice_amount,
SUM( Case when ISNULL(pre_limiter,0)>=0 then
case when limiter>=invoice_amount  then
Case when  invoice_amount-isnull(pre_limiter,0)>0 then invoice_amount else null end
else isnull(limiter,invoice_amount) - isnull(pre_limiter  ,0)  end
else null end *rate/100
)
OVER(Partition by  invoice_id ) limiter_amount

from mycte
```

Jingyang Li
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm
• Re: Create a view from this information

Thank you very much Limno. After some modification, this is working for me now. There is one problem in the query. Here is the sample data that produced the problem:

```DECLARE  @tbl TABLE
(
invoice_id int
,limiter decimal(18,2)
,rate decimal(18,2)
,invoice_amount decimal(18,2)

)

insert into @tbl values(1, 1000, 5, 1100)
insert into @tbl values(1, 1500, 2, 1100)
insert into @tbl values(1, null, 1, 1100)
insert into @tbl values(5, 20, 5, 100)
insert into @tbl values(5, 50, 4, 100)
insert into @tbl values(5, 70, 2, 100)
insert into @tbl values(5, NULL, 1, 100)

;with mycte as (
select t.invoice_id, t.limiter, t.invoice_amount, t.rate
,  lag(t.limiter) Over(Partition by t.invoice_id Order by ISNULL(t.limiter,99999999))  pre_limiter
from @tbl t)

--SELECT * FROM mycte

SELECT DISTINCT invoice_id, invoice_amount,
SUM(
Case WHEN ISNULL(pre_limiter,0)>=0 THEN
CASE
WHEN limiter>=invoice_amount  THEN
CASE
WHEN  invoice_amount-isnull(pre_limiter,0)>= 0 THEN invoice_amount-isnull(pre_limiter,0)
ELSE null
END
--WHEN pre_limiter > invoice_amount THEN 0 --I added this and now everything is working
ELSE isnull(limiter,invoice_amount) - isnull(pre_limiter,0)
END
ELSE null END *rate/100
)
OVER(PARTITION BY  invoice_id ) limiter_amount

from mycte ```

Without this line:

`--WHEN pre_limiter > invoice_amount THEN 0 --I added this and now everything is working`

The  calculation is wrong for the invoice 1.

Thank you for helping me out.