продажи в работе

Вопрос

26 сентября 2014 13:32

Добрый день. Я уже как-то поднимала этот вопрос тут Тогда окончательный я так и не получила. Напомню, что задача была, задавая дату, получить на основе журнала изменений количество продаж в состоянии в работе на эту дату. Эта первоначальная задача решена запросом

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]

Остался открытым вопрос , как изменить этот запрос, чтобы получить ответ на вопрос "какой был потенциал этих продаж на требуемую дату".
У меня получается вытянуть только сегодняшний потенциал - sum ([tbl_Opportunity].[Cash]) , на все другие изменения запроса sql ругается - sum ([tbl_OpportunityLog].[Cash]), sum([OpportunityTable].[Cash]....
А варианты запросов сразу к журналу изменений приводят к дублированию продаж.

Нравится

7 комментариев

Тихенко Виктория

26 сентября 2014 17:25

Вот навернула тут 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]

Ответить

Гущин Николай

26 сентября 2014 21:05

Спасибо за Ваше обращение. Мы зарегистрировали его в Службе технической поддержки и назначили специалиста, который сможет Вас компетентно проконсультировать по данному вопросу

Ответить

Maxim Gritsenko

29 сентября 2014 08:25

where [tbl_OpportunityLog].[CreatedOn] = (select max([CreatedOn]) from [tbl_OpportunityLog])

Ответить

Тихенко Виктория

29 сентября 2014 11:40

Два раза подряд WHERE такого sql не понимает.
Скобки тоже не помогли.

Если вообще убрать WHERE и продолжить писать условия через and для выборки INNER JOIN, то получится пустое множество.

Ответить

Maxim Gritsenko

29 сентября 2014 12:52

Виктория, я демонстрировал конструкцию.
В вашем запросе она должна располагаться так:

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]

Ответить

Тихенко Виктория

29 сентября 2014 15:00

Так, как Вы написали , тоже же получается пустое множество.

Да и не нужна там эта строка.
После WHERE EXISTS очень правильный запрос уже написан. Он одновременно 1) проверяет существуют ли в журнале изменений записи, дата которых меньше, чем указанная, а состояния равны указанным. Таких записей может быть несколько 2) и выбирает с помощью top (1) CreatedOn и сортировки DESC максимальную из дат , которая удовлетворяет требованию.

Проблема в другом. Проблема в том, что никак не получается узнать какая же Сумма потенциала была на тот момент в этой продаже. Не получается обратиться к OpportunityTable, которая сформирована таким образом.
Чтобы это как-то исправить я добавила INNER JOIN. Те. тут я хочу повторить эту же самую выборку, но таким образом, чтобы я могла к ней обращаться. Поэтому 1) я повторила ограничение по дате, по статусу и по состоянию. 2) Но никак не могу выбрать именно максимальную по дате запись журнала изменений.

Ответить

Тихенко Виктория

29 сентября 2014 15:26

ПОЛУЧИЛОСЬ! надо было просто не бояться впихнуть еще один селект

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]

Ответить

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

Вопрос

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

Тихенко Виктория

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 если вам нужны все данные (или большая часть) оттуда

Ответить

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

Подписаться на продажи в работе