Помогите пожалуйста с запросом.
Задача такая: на любой момент времени (заданный пользователем) вывести сколько Продаж в состоянии "в работе" на Стадии 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)
Нравится
"Тихенко Виктория" написал:Задача такая: на любой момент времени (заданный пользователем) вывести сколько Продаж в состоянии "в работе" на Стадии 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))
Увы, но так тоже получится выбрать продажи, у которых просто существует где-то во времени запись в журнале изменений "в работе" и одна из трех стадий.
Например, у меня есть Продажа для Клиента.
Журнал изменений:
дата изменений 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 Продажа для клиента фактически не была в работе.
И надо как-то именно эту запись проверять на состояние и стадию.
надо так попробовать
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)
У меня sql ругается на exist , на второй SELECT , на запись WHERE [tbl_OpportunityLog].[RecordID] = [tbl_Opportunity].[ID].
Кажется, что все должно получится, если отсортировать, а потом выбрать первую запись, но не получается никак.
Все ближе к цели :smile:
Теперь по-крайней мере , если у меня для какой-то продажи существовало 3 записи в журнале изменений, считается в результат только 1 продажа.
Но все равно Продажа для Клиента из моего примера в результат попадает.
Даже не зависит от того, поставить в конце сортировку DESC или ASC.
Получается что выбирает все же не первую запись, а лишь бы соответствовала условию про состояния и стадии.
будем извращаться дальше)
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) )
А вот и правильный ответ!!
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]
Итак, в запросе получилось наконец-то правильное число продаж, или список этих продаж, если вместо 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 ругается :(
Замучил меня этот запрос.
а почему вы вообще привязались к таблице tbl_Opportunity?))
делайте запрос с tbl_OpportunityLog если вам нужны все данные (или большая часть) оттуда