找出同一表中两个字段之间的差异

I have a table which stores monthly billing information.

我有一个可以存储每月账单信息的表格。

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

The consumer_id is a foreign key referencing the consumer details table.

consumer_id是引用consumer details表的外键。

What i want is to subtract every customer current reading from the reading of the previous month. This would generate the current bill. Any ideas.

我想要的是减去前一个月每个客户的当前读数。这将产生当前的账单。任何想法。

1 个解决方案

#1

You could use something similar to this, where you would replace the values of the month/year that you want to return:

您可以使用类似的方法，将您想要返回的月/年的值替换为:

select b1.consumer_id,
  sum(b1.reading - isnull(b2.reading, 0)) Total
from billing_history b1
left join billing_history b2
  on b1.consumer_id = b2.consumer_id
  and month(b2.reading_date) =12
  and year(b2.reading_date) = 2012
where month(b1.reading_date) = 1
  and year(b1.reading_date) = 2013
group by b1.consumer_id;

See SQL Fiddle with Demo.

参见SQL小提琴演示。

If you don't want to pass in the values of the month and year to search and you only want the current/previous month, then you could use something similar to this using a CTE:

如果您不想传递月份和年份的值来搜索，而只想要当前/前一个月，那么您可以使用CTE:

;with cur as
(
  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())
)
select c.consumer_id, 
  sum(c.reading - isnull(pre.reading, 0)) TotalReading
from cur 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

See SQL Fiddle with Demo

参见SQL小提琴演示

This version gets both the current/previous month and year values to be used. If you are not familiar with CTE syntax, this can also be written as:

这个版本得到了当前/前一个月和年值。如果您不熟悉CTE语法，也可以这样写:

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;

See SQL Fiddle with Demo.

参见SQL小提琴演示。

As you can see in my queries I used the aggregate function SUM() and a GROUP BY on the consumer_id. I did this in the event you had more than one reading per customer. If you know you will only have one reading per month, then you could remove the aggregate.

正如您在查询中看到的，我在consumer_id上使用了聚合函数SUM()和一个组。我这样做的时候，每个客户有超过一个阅读量。如果你知道你每个月只有一次阅读，那么你就可以删除聚合。