Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 считаем сколько должны абоненты... )  [new]
Кифирчик
Member

Откуда: СПб
Сообщений: 909
Ломаю голову тут...
вопрос касается расчету "сколько должен абонент за услугу"...
в моём случае за "воду".
абоненты могут платить как по норме - определённое колличество кубов на каждого из проживающих, либо по счётчику. и всё это надо считать "по дням"

вот таблички с "тестовой моделью"
-- список счетов
CREATE TABLE [test].[score_list] (
  [score_id] int IDENTITY(1, 1) NOT NULL,
  [score_num] varchar(10) COLLATE Cyrillic_General_CI_AS NULL,
  [open_date] smalldatetime NULL,
  PRIMARY KEY CLUSTERED ([score_id])
)
ON [PRIMARY]
GO
-- отметки о колличестве услуг
CREATE TABLE [test].[score_history_list] (
  [h_id] int IDENTITY(1, 1) NOT NULL,
  [score_id] int NULL,
  [h_date] smalldatetime NULL, -- дата с которой потребляется h_value услуг
  [h_value] float NULL,
  [with_counter] bit NULL, -- если есть счётчик, то h_value - показатель счетчика на дату h_date, 
                           -- если счетчика нету, то начиная с h_date, 
                           -- h_value это колличество проживающих
  PRIMARY KEY CLUSTERED ([h_id])
)
ON [PRIMARY]
GO
-- таблица со списком счетчиков
CREATE TABLE [test].[counter_list] (
  [counter_id] int IDENTITY(1, 1) NOT NULL,
  [score_id] int NULL,
  [install_date] smalldatetime NULL,
  [install_value] float NULL, -- начальные показания
  PRIMARY KEY CLUSTERED ([counter_id])
)
ON [PRIMARY]
GO
-- таблица со списком "тарифов"
CREATE TABLE [test].[tarif_list] (
  [tarif_id] int IDENTITY(1, 1) NOT NULL,
  [tarif_date] smalldatetime NULL, -- дата начала действия тарифа
  [tarif_value] money NULL, -- сумма за единицу услуги
  [tarif_norma] float NULL, -- колличество услуги "по норме"
  PRIMARY KEY CLUSTERED ([tarif_id])
)
ON [PRIMARY]
GO
-- таблица в которой храним "сколько должны абоненты"
CREATE TABLE [test].[tr_list] (
  [tr_id] int IDENTITY(1, 1) NOT NULL,
  [score_id] int NULL,
  [tr_value] money NULL, -- сколько должны за услугу
  [tr_year] smallint NULL, -- год предоставления услуги
  [tr_month] tinyint NULL, -- месяц
  PRIMARY KEY CLUSTERED ([tr_id])
)
ON [PRIMARY]
GO
заливка данных, для счёта score_id=7 я самую извращенскую ситуацию придумал... и для него же привёл табличку, с результатом расчета который нужно получить...
DELETE FROM test.tarif_list;
DBCC CHECKIDENT ('test.tarif_list' , RESEED, 0);
INSERT INTO test.tarif_list (tarif_date, tarif_value,tarif_norma)
SELECT '01.01.2009', 12.50,3.8 UNION ALL
SELECT '01.04.2009', 13.60,3.8 UNION ALL
SELECT '01.09.2009', 14.70,3.8 ;

DELETE FROM test.score_list;
DBCC CHECKIDENT ('test.score_list' , RESEED, 0);
INSERT INTO test.score_list (score_num,open_date)
SELECT '001','01.01.2009' UNION ALL
SELECT '002','12.02.2009' UNION ALL
SELECT '003','04.03.2009' UNION ALL
SELECT '004','20.04.2009' UNION ALL
SELECT '005','25.06.2009' UNION ALL
SELECT '006','01.06.2009' UNION ALL
SELECT '007','03.09.2009'; -- открытие счёта

DELETE FROM test.score_history_list;
DBCC CHECKIDENT ('test.score_history_list' , RESEED, 0);
INSERT INTO test.score_history_list (score_id,h_date,h_value,with_counter)
SELECT 1,'01.01.2009', 5  ,0 UNION ALL
SELECT 1,'15.05.2009', 2  ,0 UNION ALL
SELECT 1,'15.06.2009', 1  ,1 UNION ALL
SELECT 1,'23.08.2009',25  ,1 UNION ALL
SELECT 2,'12.02.2009', 3  ,0 UNION ALL
SELECT 2,'01.07.2009', 1  ,0 UNION ALL
SELECT 3,'04.03.2009', 4  ,0 UNION ALL
SELECT 3,'01.08.2009', 2  ,0 UNION ALL
SELECT 3,'01.09.2009', 4  ,1 UNION ALL
SELECT 4,'20.04.2009', 1  ,0 UNION ALL
SELECT 4,'01.06.2009', 4  ,0 UNION ALL
SELECT 5,'25.06.2009', 3  ,0 UNION ALL
SELECT 5,'01.08.2009', 1  ,0 UNION ALL
SELECT 6,'01.06.2009', 3  ,0 UNION ALL
SELECT 7,'03.09.2009', 3  ,0 UNION ALL -- открытие счёта, проживает 3 чел, расчет по "норме"
SELECT 7,'10.09.2009', 2  ,1 UNION ALL -- абонент установил счётчик, начальные показания 2 куба
SELECT 7,'26.09.2009',12.3,1 UNION ALL -- снятие показаний счётчика, 3.1 куба
SELECT 7,'26.09.2009', 3  ,0 -- абонент снова захотел по норме платить, проживающих 3 чел
 ;

DELETE FROM test.counter_list;
DBCC CHECKIDENT ('test.counter_list' , RESEED, 0);
INSERT INTO test.counter_list (score_id,install_date,install_value)
SELECT 1,'15.06.2009',1.0 UNION ALL
SELECT 3,'01.09.2009',2.0 UNION ALL
SELECT 7,'10.09.2009',2.0 UNION ALL -- дата установки счётчика 10.09, начальные показания 2куба
SELECT 5,'01.08.2009',1.5;

в конце поста, скрин таблички, какой расчет хотелось бы получить...
функция, которая частично решает данный вопрос...
CREATE PROCEDURE test.procedure1
@usluga_year  int,
@usluga_month int
AS
BEGIN
    declare @d1  datetime;
    declare @cn  int;
    
    -- удаляем начисления на данный месяц
    delete from test.tr_list
    where tr_year  = @usluga_year
    and   tr_month = @usluga_month;
    
    -- формируем начальную дату месяца
    if @usluga_month < 10 
         set @d1 = convert(datetime, '01.0'+
                   convert(varchar, @usluga_month)+'.'+
                   convert(varchar, @usluga_year ) );
    if @usluga_month > 9 
         set @d1 = convert(datetime, '01.'+
                   convert(varchar, @usluga_month)+'.'+
                   convert(varchar, @usluga_year ) );

    -- временная табличка в которой будем по дням считать                  
    create table #calc_table(
      [score_id   ] int not null,
      [usluga_date] datetime ,
      [h_count    ] float default 0,
      [tarif_value] float default 0,
      [tarif_norma] float default 0,
      [usluga_sum ] float default 0 
    );
    
    set @cn = 0; -- счётчик колличества дней в месяце
    -- цикл по дням месяца
    while month(@d1)=@usluga_month 
    begin
      -- вставляем данные для текущего числа
      insert into #calc_table(score_id,usluga_date)
      select a.score_id, @d1
      from test.score_list a
      where a.score_id = 7;

      -- прибавляем к дате один день
      set @d1 = dateadd(day, 1, @d1);
      set @cn = @cn + 1;
    end;    

     -- рассчитываем по сколько начислять и по какому тарифу
    update #calc_table
     set h_count = (
              SELECT TOP 1 h_value
              FROM test.score_history_list c
              WHERE score_id = #calc_table.score_id
              AND dbo.fun_get_rusdate(c.h_date) <= #calc_table.usluga_date
              ORDER BY c.h_date DESC ),
         tarif_value = (
              SELECT TOP 1 b.tarif_value FROM test.tarif_list b
              WHERE  b.tarif_date <= #calc_table.usluga_date
              ORDER BY b.tarif_date DESC), 
         tarif_norma = (
              SELECT TOP 1 b.tarif_norma FROM test.tarif_list b
              WHERE  b.tarif_date <= #calc_table.usluga_date
              ORDER BY b.tarif_date DESC);                   

    -- так как считаем по дням, то за каждый день, делим на количество дней в месяце
    update #calc_table
     set tarif_norma = tarif_norma/@cn;

    -- считаем конечную сумму
    update #calc_table
     set usluga_sum = h_count*tarif_value*tarif_norma;
   
    -- удаляем дни, когда ещё не был заключен договор
    delete from #calc_table where h_count is null;
   
    -- вставляем в таблицу начислений
    insert into test.tr_list (score_id,tr_year,tr_month,tr_value)
    select score_id,@usluga_year,@usluga_month,sum(usluga_sum)
    from #calc_table
    group by score_id
    ;
  
    select * from #calc_table;
    
    drop table #calc_table;
END
здесь считается без счётчика. такой вариант применим сразу ко всему списку с абонентами... без подзапросов... считается относительно офигительно быстро... для 10 тыс абонентов, на ноуте процедура выполняется за 4..6 секунд. по сравнению с перебором списка, и "индивидуальным" расчетом это ОЧЕНЬ быстро.
не могу догнать как сюда счётчики прикрутить... очень не хочется делать функции с подзапросами...

гуру sql, подскажите, возможно ли сделать это 2..3 апдейтами #calc_table?
или для этого надо изменить модель?

ниже каринка с примером "нужного варианта расчета", где более или менее понятен алгоритм расчета

К сообщению приложен файл. Размер - 0Kb
28 сен 09, 09:47    [7713864]     Ответить | Цитировать Сообщить модератору
 Re: считаем сколько должны абоненты... )  [new]
vino
Member

Откуда:
Сообщений: 1191
Кифирчик, очередной велосипед? советую сначала изучить имеющиеся алгоритмы расчетов. Каждый новый разработчик сталкивается потом с проблемой округления при дроблении объемов услуг на подневный расчет...
28 сен 09, 11:55    [7714558]     Ответить | Цитировать Сообщить модератору
 Re: считаем сколько должны абоненты... )  [new]
vino
Member

Откуда:
Сообщений: 1191
Кифирчик, вот классический расчет: за 16 дней счетчика 14,7*10,3 = 151,41 (руб.) + расчет "по норме" 14,7*(3,8*3ч*14д)/30д = 78,204 (руб.) Итого 229,614 (руб.) ~ 229,614 (руб.)
А с подневным начислением 220,79 вы обделили компанию, предоставляющую услуги...

ИМХО:
1) когда расчет идет по счетчику, подневный расчет точно вреден, тем более, что сейчас временные льготы, для которых он был необходим, повсеместно отменены
2) имеет смысл уйти от использования float
3) дробить расчеты надо только по строгой необходимости и с утвержденной официально методикой
28 сен 09, 12:21    [7714736]     Ответить | Цитировать Сообщить модератору
 Re: считаем сколько должны абоненты... )  [new]
Кифирчик
Member

Откуда: СПб
Сообщений: 909
vino

Кифирчик, вот классический расчет: за 16 дней счетчика 14,7*10,3 = 151,41 (руб.) + расчет "по норме" 14,7*(3,8*3ч*14д)/30д = 78,204 (руб.) Итого 229,614 (руб.) ~ 229,614 (руб.)
А с подневным начислением 220,79 вы обделили компанию, предоставляющую услуги...
ИМХО:
1) когда расчет идет по счетчику, подневный расчет точно вреден, тем более, что сейчас временные льготы, для которых он был необходим, повсеместно отменены
2) имеет смысл уйти от использования float
3) дробить расчеты надо только по строгой необходимости и с утвержденной официально методикой

как раз с флоат получается нормальная сумма в итоге, никаких проблем с округлением
абоненты регулярно приносят справки и другие документы, что какое-то колличество дней никого небыло... и требуют пересчитать
также и с установкой счётчиков... не кайф абонентам чтоб округяли по пол месяца )

а вот на счёт "официально утверждённой"... надо им мозг попарить... такого дока у них точно нет...
пусть утверждают, и наверно надо чтоб вышестоящая организация утвердила

сейчас вот стрельнуло... а если человек даёт показания... после молчания в 4 месяца...
предыдущие - закрыты, все отчёты есть... там ничего пересчитывать нельзя... нужно начислять всю сумму на текущую дата, по разнице показаний...
мде, сам себе усложнил задачу )

пойду начать с "утверждённого документа"
28 сен 09, 13:22    [7715069]     Ответить | Цитировать Сообщить модератору
 Re: считаем сколько должны абоненты... )  [new]
vino
Member

Откуда:
Сообщений: 1191
Кифирчик
...пойду начать с "утверждённого документа"

и это правильно!!!
А по поводу существенной разницы между 229,61 и 220,79 что думаем?
Дробление расчетного периода желательно исключить и сделать его только как альтернативный расчет только там, где несколькими формулами не обойдешься. Попутно придется думать, в чью пользу относится погрешность такого расчета, так как даже float не спасет...
А сами данные желательно считать в money или т.п.

Любые пересчеты делаются сторнированием старого документа и расчетом нового за нужный период, который, кажется, не может быть более 3 лет.
28 сен 09, 14:27    [7715493]     Ответить | Цитировать Сообщить модератору
 Re: считаем сколько должны абоненты... )  [new]
Кифирчик
Member

Откуда: СПб
Сообщений: 909
vino
А по поводу существенной разницы между 229,61 и 220,79 что думаем?

я думаю разницы вообще никакой не будет... тож цифры были приведены округлёнными...
а в реальном расчете висит вся дробная часть
select sum(a.f1), 44.35
from (
       select 44.35/5 as f1 union all
       select 44.35/5 union all
       select 44.35/5 union all
       select 44.35/5 union all
       select 44.35/5 ) as a
в рассчетах - float, а там где храним итоговые суммы - money
vino
Любые пересчеты делаются сторнированием старого документа и расчетом нового за нужный период, который, кажется, не может быть более 3 лет.

с заказчиком это однажды обсуждал... пришли к тому, что даже если были ошибки в начислениях, в прошедших периодах ничего не меняем, так как поплывут все отчёты. на момент как была выявлена неточность - в ручную начисляется/списывается сумма, так сказать корректируется, и делается комметарий для операции, в котором фиксируют основание и причину сего события.
28 сен 09, 15:02    [7715754]     Ответить | Цитировать Сообщить модератору
 Re: считаем сколько должны абоненты... )  [new]
vino
Member

Откуда:
Сообщений: 1191
Кифирчик
... пришли к тому, что даже если были ошибки в начислениях, в прошедших периодах ничего не меняем, так как поплывут все отчёты. на момент как была выявлена неточность - в ручную начисляется/списывается сумма, так сказать корректируется, и делается комметарий для операции, в котором фиксируют основание и причину сего события.

Значит, вы в курсе классического понятия сторнирования - это оно обычно и используется для плановой корректировки показателей
28 сен 09, 18:17    [7717020]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить