Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 объединение по неравенству  [new]
ejgdynf
Member

Откуда:
Сообщений: 18
Друзья, нужен ваш совет.

Первая таблица ("смены") kassirchange:
id_kassir - идентификатор пользователя (кассира)
dt_begin - дата время начала смены
dt_end - дата время окончания смены

Вторая таблица ("продажи") sales:
id_kassir - идентификатор пользователя (кассира)
dt_sale - дата время продажи
sum_sale - сумма продажи

Нужно запросом определить общую сумму продаж за каждую смену по каждому кассиру.
Делаю так:

select id_kassir, sum(sum_sale)
from sales 
   left outer join kassirchange on     sales.id_kassir=kassirchange.id_kassir 
                                   and sales.dt_sale>=kassirchange.dt_begin
                                   and sales.dt_sale<=kassirchange.dt_end
group by id_kassir

Все бы хорошо, но оборудование, заносящее записи в базу данных, округляет время до минуты (секунды всегда нулевые) и бывает, что закрытие предыдущей смены, открытие новой смены и собственно продажа происходит в течение одной минуты. Тогда эта продажа попадает в общую сумму и первой и второй смен.
Как бы побороть этот эффект?
Спасибо.
25 авг 09, 10:12    [7575776]     Ответить | Цитировать Сообщить модератору
 Re: объединение по неравенству  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
ejgdynf
Как бы побороть этот эффект?
Написать по-русски принцип объединения, который Вы хотите чтобы был реализован в запросе
Перевести на SQL поможем

Да, и желательно скрипты создания таблиц и наполнения их тестовыми данными,
а также результат, который Вы хотите получить на этих тестовых данных
25 авг 09, 10:16    [7575796]     Ответить | Цитировать Сообщить модератору
 кстати  [new]
ejgdynf
Member

Откуда:
Сообщений: 18
Запрос на самом деле более наворочен и трудноват для понимания сути проблемы - может в приведенном варианте он не совсем верное построен - но тут важна суть описанной в конце поста проблемы
25 авг 09, 10:18    [7575805]     Ответить | Цитировать Сообщить модератору
 Re: объединение по неравенству  [new]
aleks2
Guest
ejgdynf,

Если записи НЕРАЗЛИЧИМЫ - их неразличить.
25 авг 09, 10:19    [7575813]     Ответить | Цитировать Сообщить модератору
 Re: кстати  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
ejgdynf
важна суть описанной в конце поста проблемы
повторяю вопрос:
Объясните, пожалуйста, на русском языке - как Вы хотите чтобы сервер таблицы объединял
25 авг 09, 10:21    [7575829]     Ответить | Цитировать Сообщить модератору
 Re: объединение по неравенству  [new]
ejgdynf
Member

Откуда:
Сообщений: 18
Паганель
ejgdynf
Как бы побороть этот эффект?
Написать по-русски принцип объединения, который Вы хотите чтобы был реализован в запросе
Перевести на SQL поможем

Да, и желательно скрипты создания таблиц и наполнения их тестовыми данными,
а также результат, который Вы хотите получить на этих тестовых данных


По-русски:

Иванова открыла смену в 12:15, осуществила пару продаж на сумму 700 руб. и закрыла ее в 13:02.
Тут же та же Иванова открыла новую смену в те же 13:02, продала пару носков за 50 руб. опять же в 13:02. Потом продала еще на 300 руб. и закрыла смену в 13:50.

Таким образом:
за первую смену сумма продаж 700 руб,
за вторую смену сумма продаж 350 руб.

Запрос утверждает, что:
за первую смену сумма продаж 750 руб,
за вторую смену сумма продаж 350 руб.
(носки попали и в первую и во вторую смену)
25 авг 09, 10:24    [7575837]     Ответить | Цитировать Сообщить модератору
 Re: кстати  [new]
ejgdynf
Member

Откуда:
Сообщений: 18
Паганель
ejgdynf
важна суть описанной в конце поста проблемы
повторяю вопрос:
Объясните, пожалуйста, на русском языке - как Вы хотите чтобы сервер таблицы объединял

А хотелось бы, чтобы эта продажа попадала в любую смену, но только в одну. Т.е. каждой записи таблицы sales ставилась в соответствие только одна запись таблицы kassirchange
25 авг 09, 10:29    [7575867]     Ответить | Цитировать Сообщить модератору
 Re: кстати  [new]
Glory
Member

Откуда:
Сообщений: 104760
ejgdynf
Паганель
ejgdynf
важна суть описанной в конце поста проблемы
повторяю вопрос:
Объясните, пожалуйста, на русском языке - как Вы хотите чтобы сервер таблицы объединял

А хотелось бы, чтобы эта продажа попадала в любую смену, но только в одну. Т.е. каждой записи таблицы sales ставилась в соответствие только одна запись таблицы kassirchange

И по какому признаку вы предлагаете определить попадание товара в смену, если вы сами не можете оличить эти смены друг от друга ?
25 авг 09, 10:42    [7575960]     Ответить | Цитировать Сообщить модератору
 Re: объединение по неравенству  [new]
baracs
Member

Откуда: Москва
Сообщений: 7198
ejgdynf
Иванова открыла смену в 12:15, осуществила пару продаж на сумму 700 руб. и закрыла ее в 13:02.
Тут же та же Иванова открыла новую смену в те же 13:02, продала пару носков за 50 руб. опять же в 13:02. Потом продала еще на 300 руб. и закрыла смену в 13:50.

Чет, бред какой-то... Кассиры произвольно закрывают/открвают смены в течение минуты... Ну ладно.

А коды проданных товаров в системе есть? Или только суммы?
25 авг 09, 10:42    [7575964]     Ответить | Цитировать Сообщить модератору
 Re: объединение по неравенству  [new]
Glory
Member

Откуда:
Сообщений: 104760
Имхо. Смены, которые идут непрерывно, т.е. где dt_begin одной смены совпадает с dt_end другой смены, должны быть объединены в одну смену
25 авг 09, 10:45    [7575985]     Ответить | Цитировать Сообщить модератору
 Re: объединение по неравенству  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
ejgdynf
А хотелось бы, чтобы эта продажа попадала в любую смену, но только в одну
ejgdynf
Нужно запросом определить общую сумму продаж за каждую смену по каждому кассиру.
declare @kassirchange table(id_change int, id_kassir int, dt_begin smalldatetime, dt_end smalldatetime)
insert into @kassirchange(id_change, id_kassir, dt_begin, dt_end)
select 1, 1, '20090825 12:15', '20090825 13:02' union all
select 2, 1, '20090825 13:02', '20090825 13:50'

declare @sales table(id_kassir int, dt_sale smalldatetime, sum_sale int)
insert into @sales(id_kassir, dt_sale, sum_sale)
select 1, '20090825 12:15', 200 union all
select 1, '20090825 12:20', 500 union all
select 1, '20090825 13:02',  50 union all
select 1, '20090825 13:03', 300

select s.id_kassir, k.id_change, sum(sum_sale) as sum_sale
  from @sales as s
 cross apply (select top 1 id_change
                from @kassirchange as k
               where s.dt_sale >= k.dt_begin
                 and s.dt_sale <= k.dt_end) as k
 group by s.id_kassir, k.id_change


id_kassir   id_change   sum_sale
----------- ----------- -----------
1           1           750
1           2           300

(2 row(s) affected)
25 авг 09, 10:47    [7576002]     Ответить | Цитировать Сообщить модератору
 Re: объединение по неравенству  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
...and s.id_kassir = k.id_kassir...
Совсем забыл
25 авг 09, 10:52    [7576037]     Ответить | Цитировать Сообщить модератору
 Re: объединение по неравенству  [new]
DENIS_CHEL
Member

Откуда:
Сообщений: 23097
Вариант Паганель хорош (единственное несколько первых покупок следующий смены могут фигурировать в предыдущей, а не в своей, но БД все равно не достаточно данных, что бы это устранить)…
25 авг 09, 10:53    [7576041]     Ответить | Цитировать Сообщить модератору
 Re: объединение по неравенству  [new]
baracs
Member

Откуда: Москва
Сообщений: 7198
baracs
А коды проданных товаров в системе есть?

Погорячился :-)
Коды товаров ни к чему, но номера чеков-то (продаж) точно должны быть. По ним можно отследить задвоения...
25 авг 09, 10:57    [7576066]     Ответить | Цитировать Сообщить модератору
 Re: объединение по неравенству  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
DENIS_CHEL
Вариант Паганель хорош (единственное несколько первых покупок следующий смены могут фигурировать в предыдущей, а не в своей, но БД все равно не достаточно данных, что бы это устранить)…

а вы единственный можете различить смены между собой?
25 авг 09, 11:03    [7576100]     Ответить | Цитировать Сообщить модератору
 Re: объединение по неравенству  [new]
ejgdynf
Member

Откуда:
Сообщений: 18
Паганель
ejgdynf
А хотелось бы, чтобы эта продажа попадала в любую смену, но только в одну
ejgdynf
Нужно запросом определить общую сумму продаж за каждую смену по каждому кассиру.
declare @kassirchange table(id_change int, id_kassir int, dt_begin smalldatetime, dt_end smalldatetime)
insert into @kassirchange(id_change, id_kassir, dt_begin, dt_end)
select 1, 1, '20090825 12:15', '20090825 13:02' union all
select 2, 1, '20090825 13:02', '20090825 13:50'

declare @sales table(id_kassir int, dt_sale smalldatetime, sum_sale int)
insert into @sales(id_kassir, dt_sale, sum_sale)
select 1, '20090825 12:15', 200 union all
select 1, '20090825 12:20', 500 union all
select 1, '20090825 13:02',  50 union all
select 1, '20090825 13:03', 300

select s.id_kassir, k.id_change, sum(sum_sale) as sum_sale
  from @sales as s
 cross apply (select top 1 id_change
                from @kassirchange as k
               where s.dt_sale >= k.dt_begin
                 and s.dt_sale <= k.dt_end) as k
 group by s.id_kassir, k.id_change


id_kassir   id_change   sum_sale
----------- ----------- -----------
1           1           750
1           2           300

(2 row(s) affected)


Здорово! То, что надо. А если будет продажа, по времени не попадающая ни под одну смену, она ведь уже вообще не попадет в результат запроса? А чтобы попала, как в left outer join по sales?
25 авг 09, 11:06    [7576115]     Ответить | Цитировать Сообщить модератору
 Re: объединение по неравенству  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
outer apply
25 авг 09, 11:08    [7576125]     Ответить | Цитировать Сообщить модератору
 Re: объединение по неравенству  [new]
Glory
Member

Откуда:
Сообщений: 104760
ejgdynf
А если будет продажа, по времени не попадающая ни под одну смену,

Это тоже особенность оборудования, заносящго записи в базу данных?
25 авг 09, 11:08    [7576127]     Ответить | Цитировать Сообщить модератору
 Re: объединение по неравенству  [new]
DENIS_CHEL
Member

Откуда:
Сообщений: 23097
Алексей2003 я как раз и говорю, что продажи на границе смен не различимы, поэтому-то Паганель и относит продажу к более ранней смене…

baracs мое ИМХО в БД надо табличку схема и продажи должны выглядеть так: declare @sales table(id_kassir int, [id_смены] int, sum_sale int)

а вот нужно ли хранить номера чеков-то (продаж), зависит от бизнес логики предприятия (может они им по каким-то сказочным причинам и не нужны)…
25 авг 09, 11:08    [7576129]     Ответить | Цитировать Сообщить модератору
 Re: кстати  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
DENIS_CHEL
поэтому-то Паганель и относит продажу к более ранней смене…
нет
я делаю так, как просил автор
ejgdynf
чтобы эта продажа попадала в любую смену, но только в одну
25 авг 09, 11:10    [7576139]     Ответить | Цитировать Сообщить модератору
 Re: объединение по неравенству  [new]
DENIS_CHEL
Member

Откуда:
Сообщений: 23097
Паганель мне очень понравилось ваше решение, а мое замечания камешек в огород “архитектора” этой БД, а не ваш)))

PS а как продажа может быть без смены? Кассир работает сверх нормы в свое личное время?
25 авг 09, 11:16    [7576164]     Ответить | Цитировать Сообщить модератору
 Re: объединение по неравенству  [new]
ejgdynf
Member

Откуда:
Сообщений: 18
Паганель, просто не знаю как благодарить. Спасибо большое. Сошлось все до рубля. Просто страшно представить что бы я наворотил с подзапросами, если бы не твое (ваше) участие.
25 авг 09, 11:19    [7576188]     Ответить | Цитировать Сообщить модератору
 Re: объединение по неравенству  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
ну вот так если без доп колонок. допущение, что когда заканчивается смена, она главнее, чем начало.
declare @kassirchange table(id_kassir int, dt_begin smalldatetime, dt_end smalldatetime)
insert into @kassirchange(id_kassir, dt_begin, dt_end)
select 1, '20090825 12:15', '20090825 13:02' union all
select 1, '20090825 13:02', '20090825 13:50'

declare @sales table(id_kassir int, dt_sale smalldatetime, sum_sale int)
insert into @sales(id_kassir, dt_sale, sum_sale)
select 1, '20090825 12:15', 200 union all
select 1, '20090825 12:20', 500 union all
select 1, '20090825 13:02',  50 union all
select 1, '20090825 13:03', 300

select s.id_kassir, k.dt_begin, sum(s.sum_sale) sum_sale

 from @sales s
  join (
	select k1.id_kassir, k1.dt_end,
	  case when k2.id_kassir is null then k1.dt_begin else dateadd(mi, 1, k1.dt_begin) end dt_beginrasch, k1.dt_begin
	 from @kassirchange k1
	  left join @kassirchange k2 on k1.id_kassir = k2.id_kassir and k1.dt_begin = k2.dt_end
    ) k
   on s.id_kassir=k.id_kassir 
	and s.dt_sale>=k.dt_beginrasch
	and s.dt_sale<=k.dt_end
 group by s.id_kassir, k.dt_begin

для спящего время бодрствования равносильно сну
25 авг 09, 11:25    [7576225]     Ответить | Цитировать Сообщить модератору
 Re: объединение по неравенству  [new]
ejgdynf
Member

Откуда:
Сообщений: 18
Glory, DENIS_CHEL продажа вне смены как ни странно попадаются. Это точно не особенность оборудования - скорее какие-то недоработки софта.
Поясню подробнее ситуацию: представим кассира, продающего билеты на сеанс в кинотеатре с несколькими залами. Кассовый аппарат у нее переносной, мобильный. В 13-02 начинается фильм (заканчивается продажа билетов) в одном зале и начинается продажа билетов в другой зал. Предварительной продажи нет. Смена в таком случае - это отрезок времени, за который кассир обилетит желающих попасть на сеанс. Кассир вполне успевает за минуту закрыть смену у одной двери, подойти к другой, открыть новую смену и продать билет на сеанс. Кассиров несколько. Периодически они подключают кассовые аппараты к компьютеру и некая программа "сливает" данные в одну базу данных. В этот момент и появляются продажи "вне смены" - как-то они связаны с программой слива данных.
Собственно, подобный запрос - один из способов выявить эти продажи, определить их природу ну и все такое.
25 авг 09, 11:34    [7576290]     Ответить | Цитировать Сообщить модератору
 Re: объединение по неравенству  [new]
baracs
Member

Откуда: Москва
Сообщений: 7198
DENIS_CHEL
а вот нужно ли хранить номера чеков-то (продаж), зависит от бизнес логики предприятия (может они им по каким-то сказочным причинам и не нужны)…

Есть еще логика налоговой службы...
25 авг 09, 11:42    [7576354]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить