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

Откуда: г. Екатеринбург
Сообщений: 670
Добрый день.

Суть такая, есть сумма наличных и безналичных средств, например налички: 1000, безнала: 650 т.е. общая сумма чека составляет 1650. За 1650 приобретают 3 услуги, например:

стоимость "услуги1" 360, "услуги2" 800 и "услуга3" 490 (что в сумме 1650).

Возможно ли составить запрос так, чтобы сумма наличных и безналичных средств автоматом распределилась по этим услугам? В голову приходит только "курсор"...

что имею:
-- входящие параметры
DECLARE @Cash MONEY = 1000
DECLARE @Cashless MONEY = 650

-- список 3 услуг, которые необходимо оплатить
SELECT Service.Name FROM Service WHERE [Key] IN (1, 2, 3)

-- Как написать запрос, чтоб было автоматическое распределение, какие услуги оплачены наличкой, какие безналом, а какие тем и тем???
20 окт 17, 17:56    [20886887]     Ответить | Цитировать Сообщить модератору
 Re: Мозговой штурм, помогите написать SQL запрос  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36691
Алгоритм распределения предлагается придумать за вас?
20 окт 17, 17:57    [20886892]     Ответить | Цитировать Сообщить модератору
 Re: Мозговой штурм, помогите написать SQL запрос  [new]
Игорь_UUS
Member

Откуда: г. Екатеринбург
Сообщений: 670
Гавриленко Сергей Алексеевич
Алгоритм распределения предлагается придумать за вас?


Собственно вопрос, можно ли это сделать без использования курсора?

если можно, то какая тут предполагается идея?
20 окт 17, 18:00    [20886903]     Ответить | Цитировать Сообщить модератору
 Re: Мозговой штурм, помогите написать SQL запрос  [new]
Игорь_UUS
Member

Откуда: г. Екатеринбург
Сообщений: 670
Гавриленко Сергей Алексеевич,

Наверное, курсор - это и есть алгоритм в Вашем понимании...))
20 окт 17, 18:01    [20886905]     Ответить | Цитировать Сообщить модератору
 Re: Мозговой штурм, помогите написать SQL запрос  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36691
Игорь_UUS
Гавриленко Сергей Алексеевич
Алгоритм распределения предлагается придумать за вас?
Собственно вопрос, можно ли это сделать без использования курсора?
если можно, то какая тут предполагается идея?
Вы значение слова "алгоритм" знаете вообще?
20 окт 17, 18:02    [20886907]     Ответить | Цитировать Сообщить модератору
 Re: Мозговой штурм, помогите написать SQL запрос  [new]
Игорь_UUS
Member

Откуда: г. Екатеринбург
Сообщений: 670
Гавриленко Сергей Алексеевич,

Что такое алгоритм я понимаю... Предположим, что необходимо в начале производить оплату наличными, далее когда их не хватает идут безналичные
20 окт 17, 18:04    [20886913]     Ответить | Цитировать Сообщить модератору
 Re: Мозговой штурм, помогите написать SQL запрос  [new]
aleks222
Guest
Вы гарантируете совпадение суммы оплаты и суммы цен?

Если гарантируете

1. Оплачиваем полностью первые (можно по цене упорядочить) N товаров налом (сколько хватит).
2. Оплачиваем последние M безналом (сколько хватит).
3. Остается не более ОДНОГО неоплаченного товара - его оплачиваем смесью.
20 окт 17, 18:12    [20886931]     Ответить | Цитировать Сообщить модератору
 Re: Мозговой штурм, помогите написать SQL запрос  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36691
Как-то так на коленке, если суммы будут бить:

-- входящие параметры
DECLARE @Cash MONEY = 1000
DECLARE @Cashless MONEY = 650

declare @service table (
    id      int             identity (1, 1)
    , name  varchar (50)
    , price money
)

insert @service (
    name
    , price
)
          select 'услуги1', 360
union all select 'услуги2', 800 
union all select 'услуга3', 490

; with a as (
    select
        a.*
        , p = sum ( a.price ) over ( order by a.id )
    from    @service    a
)
select
    *
    , cash = 
        case 
            when a.p < @Cash                    then a.price 
            when a.price - (a.p - @Cash) > 0    then a.price - (a.p - @Cash)
            else 0 
        end
    , cashless = 
        case
            when a.p < @Cash then 0
            when a.price - (a.p - @Cash) > 0    then (a.p - @Cash)
            else a.price
        end
from    a
20 окт 17, 18:22    [20886955]     Ответить | Цитировать Сообщить модератору
 Re: Мозговой штурм, помогите написать SQL запрос  [new]
Игорь_UUS
Member

Откуда: г. Екатеринбург
Сообщений: 670
Гавриленко Сергей Алексеевич
Как-то так на коленке, если суммы будут бить:

-- входящие параметры
DECLARE @Cash MONEY = 1000
DECLARE @Cashless MONEY = 650

declare @service table (
    id      int             identity (1, 1)
    , name  varchar (50)
    , price money
)

insert @service (
    name
    , price
)
          select 'услуги1', 360
union all select 'услуги2', 800 
union all select 'услуга3', 490

; with a as (
    select
        a.*
        , p = sum ( a.price ) over ( order by a.id )
    from    @service    a
)
select
    *
    , cash = 
        case 
            when a.p < @Cash                    then a.price 
            when a.price - (a.p - @Cash) > 0    then a.price - (a.p - @Cash)
            else 0 
        end
    , cashless = 
        case
            when a.p < @Cash then 0
            when a.price - (a.p - @Cash) > 0    then (a.p - @Cash)
            else a.price
        end
from    a


Сообщение 102, уровень 15, состояние 1, строка 21
Неправильный синтаксис около конструкции "order".
20 окт 17, 18:37    [20886996]     Ответить | Цитировать Сообщить модератору
 Re: Мозговой штурм, помогите написать SQL запрос  [new]
aleks222
Guest
-- входящие параметры
DECLARE @Cash MONEY = 1000
DECLARE @Cashless MONEY = 650
declare @p money = 0

declare @service table (
    id      int             identity (1, 1) primary key
    , name  varchar (50)
    , price money
	, type char(2) default ' '
)

insert @service (
    name
    , price
)
          select 'услуги1', 360
union all select 'услуги2', 800 
union all select 'услуга3', 490
order by 2;


update t set @p = @p + price, [type] = case when @p <= @Cash then 'c' when @p > @Cash and @p - price < @Cash then 'x' else 'l' end
   from @service as t;
 

select * from @service;
20 окт 17, 18:42    [20887004]     Ответить | Цитировать Сообщить модератору
 Re: Мозговой штурм, помогите написать SQL запрос  [new]
Игорь_UUS
Member

Откуда: г. Екатеринбург
Сообщений: 670
aleks222,

Хм... а как быть, если услуга должна быть оплачена и наличкой и безналом одновременно?
20 окт 17, 18:54    [20887023]     Ответить | Цитировать Сообщить модератору
 Re: Мозговой штурм, помогите написать SQL запрос  [new]
Игорь_UUS
Member

Откуда: г. Екатеринбург
Сообщений: 670
aleks222,

Идея вроде понятна, вот только что с этой идеей делать низнаю... нужно получить 3 поля: Наименование услуги, оплата наличкой и оплата безналом.

А тут получается, только флаг, какая будет оплата, наличкой, комбинированная или безналом... а сумм то нет((
20 окт 17, 18:58    [20887030]     Ответить | Цитировать Сообщить модератору
 Re: Мозговой штурм, помогите написать SQL запрос  [new]
aleks222
Guest
Игорь_UUS
aleks222,

Идея вроде понятна, вот только что с этой идеей делать низнаю... нужно получить 3 поля: Наименование услуги, оплата наличкой и оплата безналом.

А тут получается, только флаг, какая будет оплата, наличкой, комбинированная или безналом... а сумм то нет((


Вот оне - современные кнопкодавы.

DECLARE @Cash MONEY = 1000
DECLARE @Cashless MONEY = 650
declare @p money = 0

declare @service table (
    id      int             identity (1, 1) primary key
    , name  varchar (50)
    , price money
    , cash money, cashless money
)

insert @service (
    name
    , price
)
          select 'услуги1', 360
union all select 'услуги2', 800 
union all select 'услуга3', 490
order by 2;


update t set @p = @p + price
     , cash = case when @p <= @Cash then price when @p > @Cash and @p - price < @Cash then @Cash - (@p - price) else 0 end
     , cashless = case when @p <= @Cash then 0 when @p > @Cash and @p - price < @Cash then @p - @Cash else price end
   from @service as t;
 

select * from @service;
 
 
21 окт 17, 05:32    [20887855]     Ответить | Цитировать Сообщить модератору
 Re: Мозговой штурм, помогите написать SQL запрос  [new]
anvg
Member

Откуда: СПб
Сообщений: 101
Доброе время суток.
Коль разрешено использование переменных, то тогда почему бы не использовать пропорциональное распределение по price?
Declare @Cash MONEY = 1000
Declare @Cashless MONEY = 650
Declare @Mult Numeric(32, 31);

Declare @service table (id int identity (1, 1) primary key,
					    [name]  nvarchar (50), price money,
						cash money, cashless money
);
Insert Into @service (name, price)
	Values (N'Услуги1', 360), (N'Услуги2', 800), (N'Услуги3', 490);

Select @Mult = 1 / Cast(Sum(price) As numeric(32, 18)) From @service;
Update @service
Set 
	cash = @Mult * @Cash * price,
	cashless = @Mult * Cast(@Cashless As numeric(32, 18)) * Cast(price As numeric(32, 18));
Select sum(cash) As s1, sum(cashless) as s2 From @service;
24 окт 17, 16:47    [20896214]     Ответить | Цитировать Сообщить модератору
 Re: Мозговой штурм, помогите написать SQL запрос  [new]
doz.ace
Member

Откуда: Москва
Сообщений: 136
Может можно попробовать сделать такое с помощью cte и оконных функций LAG, LEAD, накопительный итог и т.п.

[url=]http://snakeproject.ru/rubric/article.php?art=tsql_windows_functions[/url]
27 окт 17, 14:05    [20906580]     Ответить | Цитировать Сообщить модератору
 Re: Мозговой штурм, помогите написать SQL запрос  [new]
Andy_OLAP
Member

Откуда: я знаю, что Хапоэль Беэр-Шева - чемпион
Сообщений: 3151
Игорь_UUS
Возможно ли составить запрос так, чтобы сумма наличных и безналичных средств автоматом распределилась по этим услугам?

А если у Вас в чеке 3 строки по 10 рублей в таблице строк чеков и 2 строки по 15 рублей (наличные и безналичные) в таблице строк оплат? Допустим, Вы запросом выдаете, что услуга1 оплатили наличными на 10 рублей, затем услуга2 наличными на 5 рублей и безналичными на 5 рублей, услуга3 только безналичкой.
Затем идет следующая порция чеков - и тут уже услуга1 получается исключительно безналичными, потому что кроме него в чеках ничего нет.
Далее идет следующая порция чеков - и тут уже в чеках только услуга3 и немного услуги4, и получается, что услуга3 стала вдруг "оплачиваться наличными".
А далее бизнес задает резонный вопрос - почему раньше за услугу1 платили наличными, а теперь безналичкой? И почему для услуги3 все стало наоборот?

То есть я бы смотрел глубже - распределить можно, но это будет абстракция, которую невозможно использовать в бизнес-анализе для решения проблем заказчика.
27 окт 17, 14:15    [20906624]     Ответить | Цитировать Сообщить модератору
 Re: Мозговой штурм, помогите написать SQL запрос  [new]
Andy_OLAP
Member

Откуда: я знаю, что Хапоэль Беэр-Шева - чемпион
Сообщений: 3151
Игорь_UUS,
"список 3 услуг, которые необходимо оплатить" - а как изначально поставлена задача. У Вас есть некие заказы на оплату и нужно свободные деньги распределить, чтобы оплатить? Или есть уже готовые чеки, что-то оплатили наличными, что-то по безналу - и нужно понять, что из услуг чаще всего оплачивается по безналу - если к примеру в чеке только одна такая услуга и больше услуг нет.
27 окт 17, 14:20    [20906660]     Ответить | Цитировать Сообщить модератору
 Re: Мозговой штурм, помогите написать SQL запрос  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30747
Andy_OLAP
А далее бизнес задает резонный вопрос - почему раньше за услугу1 платили наличными, а теперь безналичкой? И почему для услуги3 все стало наоборот?
Так это бизнесу и нужно, это такое задание. Есть поступление денег, бизнес хочет сначала получить за работу все наличные, выгрести всё, что принёс клиент (и уйти от налогов, и работать с чёрным налом, и платить в конвертах), а потом, когда нала не осталось, грустно получать безналичными. Даже если речь про одну услугу, неважно.
27 окт 17, 14:24    [20906694]     Ответить | Цитировать Сообщить модератору
 Re: Мозговой штурм, помогите написать SQL запрос  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30747
alexeyvg
Andy_OLAP
А далее бизнес задает резонный вопрос - почему раньше за услугу1 платили наличными, а теперь безналичкой? И почему для услуги3 все стало наоборот?
Так это бизнесу и нужно, это такое задание. Есть поступление денег, бизнес хочет сначала получить за работу все наличные, выгрести всё, что принёс клиент (и уйти от налогов, и работать с чёрным налом, и платить в конвертах), а потом, когда нала не осталось, грустно получать безналичными. Даже если речь про одну услугу, неважно.
Это вообще обсуждаемая здесь с возникновения форума задача распределения поступлений на оплаты. Поступило N платежей, нужно деньги с них распределить на оплаты. По приоритету. Обычно платежей N, приоритет в порядке времени поступления, ну а тут платежей (источников) 2, приоритет задан (сначала нал, потом безнал)
27 окт 17, 14:29    [20906725]     Ответить | Цитировать Сообщить модератору
 Re: Мозговой штурм, помогите написать SQL запрос  [new]
Andy_OLAP
Member

Откуда: я знаю, что Хапоэль Беэр-Шева - чемпион
Сообщений: 3151
alexeyvg
alexeyvg
пропущено...
Так это бизнесу и нужно, это такое задание. Есть поступление денег, бизнес хочет сначала получить за работу все наличные, выгрести всё, что принёс клиент (и уйти от налогов, и работать с чёрным налом, и платить в конвертах), а потом, когда нала не осталось, грустно получать безналичными. Даже если речь про одну услугу, неважно.
Это вообще обсуждаемая здесь с возникновения форума задача распределения поступлений на оплаты. Поступило N платежей, нужно деньги с них распределить на оплаты. По приоритету. Обычно платежей N, приоритет в порядке времени поступления, ну а тут платежей (источников) 2, приоритет задан (сначала нал, потом безнал)

Интересно, а почему для услуг не задан приоритетный источник - что пытаться оплатить наличными, что безналом, был в обсуждении вариант "Оплачиваем полностью первые (можно по цене упорядочить) N товаров налом (сколько хватит)" - может быть, задать для услуг таблицу привязок, почему предлагается по цене упорядочить, или в этой схеме неважно, как деньги раскидать.
27 окт 17, 14:37    [20906784]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить