在T-sql中使用游标检查一年中每个月的值

Question

我需要一个光标的例子为我的仪表系统，其中系统读取仪表每月。

光标需要检查，以确保每个仪表都记录了本年度的读数。对于读数缺失的仪表，添加一个估计值，使日消耗等于上一周期的日消耗加15%。在之前的周期中，没有使用上述kWh值。

Answer 1

不如这样吧。( MonthSeed表可以成为数据库中的一个真正的表)

declare @MonthSeed table (MonthNumber int) 
insert into @MonthSeed values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)

-- assumes declared table "Reading" with fields ( Id int, [Date] datetime, MeterNo varchar(50), Consumption int )

select
    m.MeterNo,
    r.Date,
    calculatedConsumption = isnull(r.Consumption, -- read consumption
                                isnull((select max(r2.Consumption) Consumption from Reading r2 where datepart(month, r2.Date) = (m.MonthNumber - 1) and r2.MeterNo = m.MeterNo) * 1.15, -- previous consumption + 15%
                                    9999)) -- default consumption
from
    (select distinct
        MeterNo,
        MonthNumber
    from
        Reading, @MonthSeed) m
left join
    Reading r on r.MeterNo = m.MeterNo and datepart(month, r.Date) = m.monthNumber

编辑以下注释-添加缺失读数的示例

如上所述，需要在select insert into Reading (MeterNo, Date, Consumption)之前包含一个insert，并使用到reading表的左连接包括检查读取id是否为空，即缺少where r.Id is null。

我注意到，在插入到reading表中时，这会导致日期条目为空。因此，我在主子选择Date = dateadd(month, monthnumber, @seeddate)中包含了一个日期聚合；修改了主选择，以显示缺少条目的日期isnull(r.Date, m.Date),

一年前，我将@SeedDate计算为当月的1号，但您可能希望传入一个更早的日期。

declare @MonthSeed table (MonthNumber int) 
insert into @MonthSeed values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)

-- assumes declared table "Reading" with fields ( Id int, [Date] datetime, MeterNo varchar(50), Consumption int )

declare @SeedDate datetime = (select dateadd(month, datediff(month, 0, getdate())-12, 0)) -- this month, last year

insert into Reading (MeterNo, Date, Consumption)
select
    m.MeterNo,
    isnull(r.Date, m.Date),
    calculatedConsumption =
        isnull(r.Consumption, -- read consumption
        isnull(1.15 * (select max(r2.Consumption) Consumption 
                from Reading r2 
                where datepart(month, r2.Date) = (m.MonthNumber - 1) 
                and r2.MeterNo = m.MeterNo), -- previous consumption + 15%
        9999)) -- default consumption
from
    (select distinct
        MeterNo,
        MonthNumber,
        Date = dateadd(month, monthnumber, @seeddate)
    from
        Reading
    cross join
        @MonthSeed) m
left join
    Reading r on r.MeterNo = m.MeterNo and datepart(month, r.Date) = m.monthNumber
where
    r.Id is null

select * from Reading

Answer 2

(以下假设SQL Server 2005或更高版本。)

在这里找找看有没有什么值钱的东西：

declare @StartDate as Date = '2012-01-01'
declare @Now as Date = GetDate()
declare @DefaultConsumption as Int = 2000 -- KWh.

declare @MeterReadings as Table
  ( MeterReadingId Int Identity, ReadingDate Date, MeterNumber VarChar(10), Consumption Int )
insert into @MeterReadings ( ReadingDate, MeterNumber, Consumption ) values
  ( '2012-01-13', 'E154', 2710 ),
  ( '2012-01-19', 'BR549', 650 ),
  ( '2012-02-15', 'E154', 2970 ),
  ( '2012-02-19', 'BR549', 618 ),
  ( '2012-03-16', 'BR549', 758 ),
  ( '2012-04-11', 'E154', 2633 ),
  ( '2012-04-20', 'BR549', 691 )

; with Months ( Month ) as (
  select @StartDate as [Month]
  union all
  select DateAdd( mm, 1, Month )
    from Months
    where Month < @Now
  ),
MeterNumbers ( MeterNumber ) as (
  select distinct MeterNumber
    from @MeterReadings )
select M.Month, MN.MeterNumber,
  MR.MeterReadingId, MR.ReadingDate, MR.Consumption,
  Coalesce( MR.Consumption, @DefaultConsumption ) as [BillableConsumption],
  ( select Max( ReadingDate ) from @MeterReadings where MeterNumber = MN.MeterNumber and ReadingDate < M.Month ) as [PriorReadingDate],
  ( select Consumption from @MeterReadings where MeterNumber = MN.MeterNumber and ReadingDate =
  ( select Max( ReadingDate ) from @MeterReadings where MeterNumber = MN.MeterNumber and ReadingDate < M.Month ) ) as [PriorConsumption],
  ( select Consumption from @MeterReadings where MeterNumber = MN.MeterNumber and ReadingDate =
  ( select Max( ReadingDate ) from @MeterReadings where MeterNumber = MN.MeterNumber and ReadingDate < M.Month ) ) * 1.15 as [PriorConsumptionPlus15Percent]  
  from Months as M cross join
    MeterNumbers as MN left outer join
    @MeterReadings as MR on MR.MeterNumber = MN.MeterNumber and DateAdd( dd, 1 - DatePart( dd, MR.ReadingDate ), MR.ReadingDate ) = M.Month
  order by M.Month, MN.MeterNumber