Запрос по продажам в работе на любую дату (продолжение)
Добрый день. Я уже как-то поднимала этот вопрос тут Тогда окончательный я так и не получила. Напомню, что задача была, задавая дату, получить на основе журнала изменений количество продаж в состоянии в работе на эту дату. Эта первоначальная задача решена запросом
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]
Остался открытым вопрос , как изменить этот запрос, чтобы получить ответ на вопрос "какой был потенциал этих продаж на требуемую дату".
У меня получается вытянуть только сегодняшний потенциал - sum ([tbl_Opportunity].[Cash]) , на все другие изменения запроса sql ругается - sum ([tbl_OpportunityLog].[Cash]), sum([OpportunityTable].[Cash]....
А варианты запросов сразу к журналу изменений приводят к дублированию продаж.
Нравится
Вот навернула тут inner join, чтобы было можно выбирать данные из журнала изменений.
Остался вопрос (написала его в тексте запроса, чтобы было понятнее)
SELECT [tbl_Opportunity].[Title], CONVERT(DATE, [tbl_Opportunity].[CreatedOn], 102) As CreatedOn, sum([tbl_Opportunity].[Cash])/count([tbl_OpportunityLog].[id]) as CashToday, sum([tbl_OpportunityLog].[Cash])/count([tbl_OpportunityLog].[id]) AS [AvgCashLog] FROM tbl_Opportunity inner join [tbl_OpportunityLog] on [tbl_OpportunityLog].[RecordID] = [tbl_Opportunity].[ID] and [tbl_OpportunityLog].[CreatedOn] <= CONVERT(DATETIME, '2014-03-31 00:00:00', 102) and [tbl_OpportunityLog].[StatusName] = :STATUS AND ([tbl_OpportunityLog].[StageStage] =:Stage1 OR [tbl_OpportunityLog].[StageStage] = :Stage2 OR [tbl_OpportunityLog].[StageStage] = :Stage3) // в ЭТОМ МЕСТЕ НАДО еще добавить условие на выбор записи журнала изменений с МАКСИМАЛЬНОЙ датой CreatedOn. КАК? WHERE exists (SELECT ID,StatusName, StageStage, Title, RecordID, Cash, Revenue 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] <= CONVERT(DATETIME, '2014-03-31 00:00:00', 102) 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], [tbl_Opportunity].[CreatedOn]
Спасибо за Ваше обращение. Мы зарегистрировали его в Службе технической поддержки и назначили специалиста, который сможет Вас компетентно проконсультировать по данному вопросу
where [tbl_OpportunityLog].[CreatedOn] = (select max([CreatedOn]) from [tbl_OpportunityLog])
Два раза подряд WHERE такого sql не понимает.
Скобки тоже не помогли.
Если вообще убрать WHERE и продолжить писать условия через and для выборки INNER JOIN, то получится пустое множество.
Виктория, я демонстрировал конструкцию.
В вашем запросе она должна располагаться так:
SELECT [tbl_Opportunity].[Title], CONVERT(DATE, [tbl_Opportunity].[CreatedOn], 102) AS CreatedOn, sum([tbl_Opportunity].[Cash])/count([tbl_OpportunityLog].[id]) AS CashToday, sum([tbl_OpportunityLog].[Cash])/count([tbl_OpportunityLog].[id]) AS [AvgCashLog] FROM tbl_Opportunity INNER JOIN [tbl_OpportunityLog] ON [tbl_OpportunityLog].[RecordID] = [tbl_Opportunity].[ID] AND [tbl_OpportunityLog].[CreatedOn] <= CONVERT(DATETIME, '2014-03-31 00:00:00', 102) AND [tbl_OpportunityLog].[StatusName] = :STATUS AND ([tbl_OpportunityLog].[StageStage] =:Stage1 OR [tbl_OpportunityLog].[StageStage] = :Stage2 OR [tbl_OpportunityLog].[StageStage] = :Stage3) WHERE EXISTS (SELECT ID,StatusName, StageStage, Title, RecordID, Cash, Revenue 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] <= CONVERT(DATETIME, '2014-03-31 00:00:00', 102) ORDER BY [tbl_OpportunityLog].[CreatedOn] DESC) AS OpportunityTable WHERE [OpportunityTable].[StatusName] = :STATUS AND ([OpportunityTable].[StageStage] =:Stage1 OR [OpportunityTable].[StageStage] = :Stage2 OR [OpportunityTable].[StageStage] = :Stage3)) AND [tbl_OpportunityLog].[CreatedOn] = (SELECT max([CreatedOn]) FROM [tbl_OpportunityLog]) GROUP BY [tbl_Opportunity].[Title], [tbl_Opportunity].[CreatedOn]
Так, как Вы написали , тоже же получается пустое множество.
Да и не нужна там эта строка.
После WHERE EXISTS очень правильный запрос уже написан. Он одновременно 1) проверяет существуют ли в журнале изменений записи, дата которых меньше, чем указанная, а состояния равны указанным. Таких записей может быть несколько 2) и выбирает с помощью top (1) CreatedOn и сортировки DESC максимальную из дат , которая удовлетворяет требованию.
Проблема в другом. Проблема в том, что никак не получается узнать какая же Сумма потенциала была на тот момент в этой продаже. Не получается обратиться к OpportunityTable, которая сформирована таким образом.
Чтобы это как-то исправить я добавила INNER JOIN. Те. тут я хочу повторить эту же самую выборку, но таким образом, чтобы я могла к ней обращаться. Поэтому 1) я повторила ограничение по дате, по статусу и по состоянию. 2) Но никак не могу выбрать именно максимальную по дате запись журнала изменений.
ПОЛУЧИЛОСЬ! надо было просто не бояться впихнуть еще один селект
SELECT [tbl_Opportunity].[Title], CONVERT(DATE, [tbl_Opportunity].[CreatedOn], 102) As CreatedOn, sum([tbl_Opportunity].[Cash])/count([tbl_OpportunityLog].[id]) as CashToday, sum([tbl_OpportunityLog].[Cash])/count([tbl_OpportunityLog].[id]) AS [CashLogOnDate] FROM tbl_Opportunity inner join [tbl_OpportunityLog] on [tbl_OpportunityLog].[RecordID] = [tbl_Opportunity].[ID] and [tbl_OpportunityLog].[CreatedOn] <= CONVERT(DATETIME, '2014-03-31 00:00:00', 102) and [tbl_OpportunityLog].[StatusName] =:STATUS AND ([tbl_OpportunityLog].[StageStage] =:Stage1 OR [tbl_OpportunityLog].[StageStage] = :Stage2 OR [tbl_OpportunityLog].[StageStage] = :Stage3)and [tbl_OpportunityLog].[CreatedOn] = (SELECT max([tbl_OpportunityLog1].[CreatedOn]) from [tbl_OpportunityLog] as [tbl_OpportunityLog1] where [tbl_OpportunityLog1].[RecordID] = [tbl_Opportunity].[ID] and [tbl_OpportunityLog1].[CreatedOn] <= CONVERT(DATETIME, '2014-03-31 00:00:00', 102) and [tbl_OpportunityLog1].[StatusName] = :STATUS AND ([tbl_OpportunityLog1].[StageStage] =:Stage1 OR [tbl_OpportunityLog1].[StageStage] = :Stage2 OR [tbl_OpportunityLog1].[StageStage] = :Stage3) ) WHERE exists (SELECT ID,StatusName, StageStage, Title, RecordID, Cash, Revenue 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] <= CONVERT(DATETIME, '2014-03-31 00:00:00', 102) 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], [tbl_Opportunity].[CreatedOn]