Getting results based on condition from another table in SQL SERVER

Question

I have got two tables for a Microhydel Application, one consists the monthly billing record of Consumers from which monthly customer Bill is generated.

CREATE TABLE billing_history(
    [id] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    [reading_date] [date] NOT NULL,
    [reading_year] [smallint] NULL,
    [reading] [numeric](18, 0) NOT NULL,
    [consumer_id] [int] NOT NULL,
    [paid_amount] [numeric](18, 0) NULL)

I have another table which stores the different slab for per unit cost for both commercial and domestic users.

CREATE TABLE [rate_list](
    [flag] [varchar](50) NULL,
    [limit] [numeric](18, 0) NOT NULL,
    [price] [numeric](18, 0) NOT NULL,
    [service_charges] [numeric](18, 0) NOT NULL,
    [surcharge] [numeric](18, 0) NOT NULL
)

For e.g For a Domestic customer consuming 50 units or less electricity monthly will be charged differently then a Commercial customer consuming the same amount of electricity. Similarly consuming units over this slab will have another rate applied on them.

Thanks to @bluefeet i already have the query to generate the numbers of units consumed from the first table using the query

select c.consumer_id, 
  sum(c.reading - isnull(pre.reading, 0)) TotalReading
from
(
  select consumer_id,
    reading,
    month(getdate()) curMonth,
    year(getdate()) curYear,
    case when month(getdate()) = 1 then 12 else month(getdate()) -1 end preMonth,
    case when month(getdate()) = 1 then year(getdate())-1 else year(getdate()) end preYear
  from billing_history
  where month(reading_date) = month(getdate())
    and year(reading_date) = year(getdate())
) c
left join billing_history pre
  on c.consumer_id = pre.consumer_id
  and month(pre.reading_date) = c.preMonth
  and year(pre.reading_date) = c.preYear
group by c.consumer_id;

However I need to generate Monthly bill for each customer so that for e.g according to the rates in the rate_list table. The Key here is DOMESTIC/COMMERCIAL which has different slabs for the number of units consumed.

Any ideas

If you can show your data or create two tables with some data on fiddle that would be helpful. — Indi
– Indi, Commented Feb 5, 2013 at 17:17
What column identifies either domestic or commercial? Can you post some sample data for the rate_list and the billing_history table? — Taryn
– Taryn, Commented Feb 5, 2013 at 17:39
A customer main table consisting of the customer details has the field type_of_connection which can either be DOMESTIC OR COMMERCIAL. — user2018756
– user2018756, Commented Feb 5, 2013 at 17:55
@user2018756 Is the limit value in the price_list table the range values? So it is 0-50 will have a price of 4 and 51-100 has 10. If so, what is over 100? — Taryn
– Taryn, Commented Feb 5, 2013 at 18:44

Taryn · Accepted Answer · 2013-02-05 19:25:57Z

A few comments on my answer.

First, I wasn't not sure where the type_of_connection flag is present in the SQL Fiddle that you posted so I added it to Consumers.

Second, I think you need to alter your price_list2 table to include the limit start and end values for the prices. Otherwise it will be very difficult to determine the price for each consumer.

I used the following price_list2 table which will contain the start/end values for each limit:

CREATE TABLE [price_list2](
    [flag] [varchar](50) NULL,
    [limitStart] [numeric](18, 0) NOT NULL,
    [limitEnd] [numeric](18, 0) NOT NULL,
    [price] [numeric](18, 0) NOT NULL,
    [service_charges] [numeric](18, 0) NOT NULL,
    [surcharge] [numeric](18, 0) NOT NULL);

On to the query, using the tables and the original query that you posted you should be able to use something like this:

select c.consumer_id,
  r.totalreading * p.price TotalBill
from Consumers c
inner join
(
  select c.consumer_id, 
    sum(c.reading - isnull(pre.reading, 0)) TotalReading
  from
  (
    select consumer_id,
      reading,
      month(getdate()) curMonth,
      year(getdate()) curYear,
      case when month(getdate()) = 1 then 12 else month(getdate()) -1 end preMonth,
      case when month(getdate()) = 1 then year(getdate())-1 else year(getdate()) end preYear
    from billing_history
    where month(reading_date) = month(getdate())
      and year(reading_date) = year(getdate())
  ) c
  left join billing_history pre
    on c.consumer_id = pre.consumer_id
    and month(pre.reading_date) = c.preMonth
    and year(pre.reading_date) = c.preYear
  group by c.consumer_id
) r
  on c.consumer_id = r.consumer_id
inner join price_list2 p
  on c.type_of_connection = p.flag
  and r.totalreading between p.limitStart and p.limitEnd

See SQL Fiddle with Demo

As you can see when joining on the price_list2 table I am joining on the start/end range of the limits. This allows you to determine what price should be used for the bill.

Thanks yet again @bluefeet. It worked perfectly, though the application_type field in the customers table held the connection_type value. Only had to change that. Thanks a million.

Collectives™ on Stack Overflow

Getting results based on condition from another table in SQL SERVER

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related