Вопрос
SQL
запросы
продажи в работе
Фильтры
Разработка

Запрос по продажам в работе на любую дату

Тихенко Виктория
14 июня 2013 17:54

Помогите пожалуйста с запросом.
Задача такая: на любой момент времени (заданный пользователем) вывести сколько Продаж в состоянии "в работе" на Стадии 1, Стадии 2 или Стадии 3 было.
Таблица Продажи логируется. Соответственно, возникла мысль, что надо делать следующий по логике запрос:
1) выбрать все продажи,
2) выбрать связанные с этими продажами записи журнала изменений,
3) сделать фильтр - дата записи журнала изменений меньше, чем заданная пользователем дата
4) выбрать среди них запись с максимальной датой
5) проверить является ли поле состояние этой записи = "в работе", а также равняется ли поле Стадия одной из трех указанных стадий.

Делаю следующий запрос. В результате получаю неправильный результат: находятся не последние записи журнала изменений, а все, у которых состояние = в работе, а стадия = одной из 3х необходимых стадий.
Как исправить ошибку?

SELECT
[tbl_Opportunity].[Title] AS [Title],
MAX(CAST(CONVERT(VARCHAR(8), [tbl_OpportunityLog].[CreatedOn], 112) AS DATETIME)) AS [CreatedOn],
[tbl_OpportunityLog].[StatusName] AS [StatusName],
[tbl_OpportunityLog].[StageStage] AS [StageStage],
SUM([tbl_OpportunityLog].[Revenue]) AS [Revenue],
SUM([tbl_OpportunityLog].[Cash]) AS [Cash],
CAST(CONVERT(VARCHAR(8), [tbl_Opportunity].[CreatedOn], 112) AS DATETIME) AS [CreatedOnOpportunity]
FROM
[dbo].[tbl_Opportunity] AS [tbl_Opportunity]
LEFT OUTER JOIN
[dbo].[tbl_OpportunityLog] AS [tbl_OpportunityLog] ON [tbl_OpportunityLog].[RecordID] = [tbl_Opportunity].[ID]
WHERE([tbl_Opportunity].[ID] = [tbl_OpportunityLog].[RecordID] AND
[tbl_OpportunityLog].[CreatedOn] = :CreatedOn AND
[tbl_OpportunityLog].[StatusName] = :Status AND
([tbl_OpportunityLog].[StageStage] = :Stage1 OR
[tbl_OpportunityLog].[StageStage] = :Stage2 OR
[tbl_OpportunityLog].[StageStage] = :Stage3))
GROUP BY
[tbl_Opportunity].[Title],
[tbl_OpportunityLog].[StatusName],
[tbl_OpportunityLog].[StageStage],
CAST(CONVERT(VARCHAR(8), [tbl_Opportunity].[CreatedOn], 112) AS DATETIME)

Нравится

10 комментариев
Андросов Дмитрий
17 июня 2013 09:36

"Тихенко Виктория" написал:Задача такая: на любой момент времени (заданный пользователем) вывести сколько Продаж в состоянии "в работе" на Стадии 1, Стадии 2 или Стадии 3 было.

select count (ID) from tbl_Opportunity
where exist
(select ID from tbl_OpportunityLog where [tbl_OpportunityLog].[RecordID] = [tbl_Opportunity].[ID]
[tbl_OpportunityLog].[CreatedOn] <= :CreatedOn AND
[tbl_OpportunityLog].[StatusName] = :Status AND
([tbl_OpportunityLog].[StageStage] = :Stage1 OR
[tbl_OpportunityLog].[StageStage] = :Stage2 OR
[tbl_OpportunityLog].[StageStage] = :Stage3))
Тихенко Виктория
17 июня 2013 11:19

Увы, но так тоже получится выбрать продажи, у которых просто существует где-то во времени запись в журнале изменений "в работе" и одна из трех стадий.
Например, у меня есть Продажа для Клиента.
Журнал изменений:
дата изменений 01.04.2013, в работе, стадия 1.
15.04.2013, в работе, стадия 2.
30.04.2013, отменена, стадия 2.
10.05.2013, в работе, стадия 3.

Так вот, если делать подобный запрос на 01.05.2013 , то продажа попадет в результат запроса. Ведь существует запись журнала, дата изменения которой меньше 01.05.2013, состояние в работе, стадия 2.
Другое дело что существует и запись на 30.04.2013, которая этому требованию уже не удовлетворяет. И 01.05.2013 Продажа для клиента фактически не была в работе.
И надо как-то именно эту запись проверять на состояние и стадию.

Андросов Дмитрий
17 июня 2013 11:39

надо так попробовать

select count (ID) from tbl_Opportunity
where exist
(select top 1 ID from tbl_OpportunityLog where [tbl_OpportunityLog].[RecordID] = [tbl_Opportunity].[ID]
[tbl_OpportunityLog].[CreatedOn] <= :CreatedOn AND
[tbl_OpportunityLog].[StatusName] = :Status AND
([tbl_OpportunityLog].[StageStage] = :Stage1 OR
[tbl_OpportunityLog].[StageStage] = :Stage2 OR
[tbl_OpportunityLog].[StageStage] = :Stage3) order by [tbl_OpportunityLog].[CreatedOn] desc)
Тихенко Виктория
17 июня 2013 14:47

У меня sql ругается на exist , на второй SELECT , на запись WHERE [tbl_OpportunityLog].[RecordID] = [tbl_Opportunity].[ID].

Кажется, что все должно получится, если отсортировать, а потом выбрать первую запись, но не получается никак.

Андросов Дмитрий
17 июня 2013 14:57

потому что exists (буква s в конце), извините)

Тихенко Виктория
17 июня 2013 15:28

Все ближе к цели :smile:
Теперь по-крайней мере , если у меня для какой-то продажи существовало 3 записи в журнале изменений, считается в результат только 1 продажа.
Но все равно Продажа для Клиента из моего примера в результат попадает.
Даже не зависит от того, поставить в конце сортировку DESC или ASC.
Получается что выбирает все же не первую запись, а лишь бы соответствовала условию про состояния и стадии.

Андросов Дмитрий
17 июня 2013 15:41

будем извращаться дальше)

SELECT count (ID) FROM tbl_Opportunity
WHERE exist
(SELECT top 1 ID FROM 
(Select ID from tbl_OpportunityLog WHERE [tbl_OpportunityLog].[RecordID] = [tbl_Opportunity].[ID]
[tbl_OpportunityLog].[CreatedOn] <= :CreatedOn AND
[tbl_OpportunityLog].[StatusName] = :STATUS AND
([tbl_OpportunityLog].[StageStage] = :Stage1 OR
[tbl_OpportunityLog].[StageStage] = :Stage2 OR
[tbl_OpportunityLog].[StageStage] = :Stage3) ORDER BY [tbl_OpportunityLog].[CreatedOn] DESC)
)
Тихенко Виктория
17 июня 2013 19:06

А вот и правильный ответ!!

SELECT count (ID) FROM tbl_Opportunity 
WHERE exists
(SELECT ID from 
(Select top (1) CreatedOn,StatusName, StageStage, Title, ID, RecordID, Cash, Revenue  from tbl_OpportunityLog WHERE 
[tbl_OpportunityLog].[RecordID] = [tbl_Opportunity].[ID]and
[tbl_OpportunityLog].[CreatedOn] <= :CreatedOn
ORDER BY [tbl_OpportunityLog].[CreatedOn] Desc) As OpportunityTable
Where [OpportunityTable].[StatusName] = :STATUS AND
 ([OpportunityTable].[StageStage] =:Stage1 OR
 [OpportunityTable].[StageStage] = :Stage2 OR
 [OpportunityTable].[StageStage] = :Stage3))
GROUP BY
	[tbl_Opportunity].[Title]
Тихенко Виктория
18 июня 2013 12:32

Итак, в запросе получилось наконец-то правильное число продаж, или список этих продаж, если вместо count (ID) написать просто Title. Это хорошо.

Теперь возникает следующая задача - как изменить запрос, чтобы еще и старые данные по сумме потенциала продажи вытянуть?
Продолжая пример: на сегодня Продажа для Клиента имеет Потенциал дохода продажи (поле Cash) = 6000. А в в журнале изменений есть строка 15.04.2013, в работе, стадия 2, Cash =2500.

Те выполняя запрос на 20.04.2013 я хочу получить в списке мою продажу, состояние в работе, стадия 2, и поле Cash = 2500 , а не 6000.
(последний результат я получу, если в первом select написать sum([tbl_Opportunity].[Cash ]).

Пробовала изменить начало запроса так:

SELECT [tbl_Opportunity].[Title], 
sum([OpportunityTableLog].[Cash]) AS [CashLog],
[OpportunityTableLog].[StageStage] AS [StageStageLog] ,
[OpportunityTableLog].[StatusName] AS [StatusNameLog] ,
FROM tbl_Opportunity inner join
(select ID, CreatedOn,RecordID, Cash, StageStage, StatusName, Title from tbl_OpportunityLog) As OpportunityTableLog 
on [OpportunityTableLog].[RecordID] = [tbl_Opportunity].[ID]

В результате предсказуемо получила все подряд записи журнала изменений.
Пробовала также вставить в блок inner join все, что раньше было в блоке exist, но sql ругается :(
Замучил меня этот запрос.

Андросов Дмитрий
18 июня 2013 13:00

а почему вы вообще привязались к таблице tbl_Opportunity?))
делайте запрос с tbl_OpportunityLog если вам нужны все данные (или большая часть) оттуда

Показать все комментарии