database - Getting results based on condition from another table in SQL SERVER -

database - Getting results based on condition from another table in SQL SERVER -

i have got 2 tables microhydel application, 1 consists monthly billing record of consumers monthly client bill 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 table stores different slab per unit cost both commercial , 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 domestic client consuming 50 units or less electricity monthly charged differently commercial client consuming same amount of electricity. consuming units on slab have rate applied on them.

thanks @bluefeet have query generate numbers of units consumed first table using query

select c.consumer_id, sum(c.reading - isnull(pre.reading, 0)) totalreading ( select consumer_id, reading, month(getdate()) curmonth, year(getdate()) curyear, case when month(getdate()) = 1 12 else month(getdate()) -1 end premonth, case when month(getdate()) = 1 year(getdate())-1 else year(getdate()) end preyear billing_history month(reading_date) = month(getdate()) , year(reading_date) = year(getdate()) ) c left bring together billing_history pre on c.consumer_id = pre.consumer_id , month(pre.reading_date) = c.premonth , year(pre.reading_date) = c.preyear grouping c.consumer_id;

however need generate monthly bill each client e.g according rates in rate_list table. key here domestic/commercial has different slabs number of units consumed.

any ideas

a few comments on answer.

first, wasn't not sure type_of_connection flag nowadays in sql fiddle posted added consumers.

second, think need alter price_list2 table include limit start , end values prices. otherwise hard determine cost each consumer.

i used next price_list2 table contain start/end values 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 query, using tables , original query posted should able utilize this:

select c.consumer_id, r.totalreading * p.price totalbill consumers c inner bring together ( select c.consumer_id, sum(c.reading - isnull(pre.reading, 0)) totalreading ( select consumer_id, reading, month(getdate()) curmonth, year(getdate()) curyear, case when month(getdate()) = 1 12 else month(getdate()) -1 end premonth, case when month(getdate()) = 1 year(getdate())-1 else year(getdate()) end preyear billing_history month(reading_date) = month(getdate()) , year(reading_date) = year(getdate()) ) c left bring together billing_history pre on c.consumer_id = pre.consumer_id , month(pre.reading_date) = c.premonth , year(pre.reading_date) = c.preyear grouping c.consumer_id ) r on c.consumer_id = r.consumer_id inner bring together price_list2 p on c.type_of_connection = p.flag , r.totalreading between p.limitstart , p.limitend

see sql fiddle demo

as can see when joining on price_list2 table joining on start/end range of limits. allows determine cost should used bill.

sql-server database