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

Откуда:
Сообщений: 34
+
Declare
@SalerID int =9929, 
@BeginDate date = '29.11.2017',
@EndDate date = '29.11.2017',
@ChangeStartTime int = 1,
@StartTime time = '09:00';

Declare
@StartTime1 time = '09:00',--для мерчей-- Новгородская обл.,  Волгоградская обл.----//304--Пермский край,
@StartTime2 time = '09:00';

Use ANTHILL

select 
	DISTINCT  v.visitid, 
	ISNULL(cal.Date, CAST(v.VisitStartDate as date)) as Calendar
	,ROW_NUMBER() OVER(PARTITION BY ISNULL(v.SalerID,sl.SalerID),ISNULL(cal.Date, CAST(v.VisitStartDate as date)) ORDER BY CAST(v.VisitStartDate AS time) ASC)as num
	,d.[vlat]-----27.11.2017
	,d.[vLong]-----27.11.2017
	,ISNULL(v.SalerID, sl.SalerID) as SalerID
	,ISNULL(v.AddrID, sl.AddrID) as AddrID
	,v.AddrID as visAddr
	,sl.AddrID as routAddr
	,ISNULL(CONVERT(date, v.VisitStartDate),CONVERT(date, sl.RouteDate)) as VisitDate
	,convert(varchar(5),v.VisitStartDate, 108) as sTime
	,convert(varchar(5),v.VisitEndDate, 108) as eTime
	,sss.salername as Manager, ss.salername as SV
	,s.SalerName as SalerName
	,c.NameScreen ,c.NameScreen +' - '+ a.AddrName as AddrName 
	,a.Lat as AddrLat
	,a.Long as AddrLong
	,v.VisitStartDate as vsd
	,v.VisitEndDate as ved
	,DATEDIFF ( MINUTE , v.VisitStartDate , v.VisitEndDate) as dif_time
	,sl.RouteDate as datePlan
	,sf.FirmID   
	,f.FirmName
--------------------------------------------------------------------------------------------------------------
	,(Round(111100*Acos(Sin(AVG(d.[vLat]))*Sin(a.Lat) + Cos(AVG(d.[vLat]))*Cos(a.Lat) *Cos(a.Long-AVG(d.[vLong]))), 0)) as distance --показывает дистанцию 
	,case
		when (Round(111100*Acos(Sin(AVG(d.[vLat]))*Sin(a.Lat) + Cos(AVG(d.[vLat]))*Cos(a.Lat) *Cos(a.Long-AVG(d.[vLong]))), 0))>500 then (Str(v.AddrID) + '_' + CAST(CAST(v.VisitStartDate as date) as varchar))
		else null
	 end as big_dist -- просчет дистанции больше 500 метров
--------------------------------------------------------------------------------------------------------------
	,datename(weekday, ISNULL(cal.Date, CAST(v.VisitStartDate as date))) as WeekDN
	,count (ed.docid) as countDocs	
--------------------------------------------------------------------------------------------------------------
	,indicator_countDoc = --есть ли заказ
		Case 
			WHEN count(ed.docid) > 0 THEN 1
			else  0
		End
	,FactTO= --сколкьо кг продали
		Case
			when ed.DocID>0 and abs(ed.STS)>=3 and st.StsName like '%Проведен%' then sum(i.UnitWeight*edd.Quantity)
			else null
		End
	,indicator = 
		Case 
			WHEN v.VisitStartDate is NULL THEN 0
			else  2
		End
	,indicatormenpat = ----Нахождение в точке <5
		Case
			when DATEDIFF ( MINUTE , v.VisitStartDate , v.VisitEndDate) < 5 then 1 
			else 0
		end
	,case 
			when (  max(v.VisitEndDate) <=DateAdd("HOUR", 17,cast(cast(v.VisitStartDate as date) as datetime))) then max(v.VisitEndDate)
			else null
	 end as last_time -- время окончания раб дня
----------------------------Опоздания--------------------------------------------------------------------------------------------------------------
,opozdanie_time =--время опоздания
		Case 
			--when @ChangeStartTime = 1 then DATEDIFF("MI", DateAdd("MI",SUBSTRING(CONVERT(VARCHAR, @StartTime, 108),1,2) * 60 + SUBSTRING(CONVERT(VARCHAR, @StartTime, 108),4,2) + 5,CAST(CONVERT(date, min(v.VisitStartDate))as datetime)), min(v.VisitStartDate))
			--прибавляем 5 мин к StartTime и находим опоздания
			--when @ChangeStartTime = 1 then DATEDIFF("MI", DateAdd("MI",SUBSTRING(CONVERT(VARCHAR, @StartTime, 108),1,2) * 60 + SUBSTRING(CONVERT(VARCHAR, DATEAdd("MI", 5,@StartTime), 108),4,2),CAST(CONVERT(date, min(v.VisitStartDate))as datetime)), min(v.VisitStartDate))
			when  (sf.FirmID in (298,252) -- Новгородская обл.,  Волгоградская обл.----//304--Пермский край,
			    )			   
			   then DATEDIFF("MI", DateAdd("MI",SUBSTRING(CONVERT(VARCHAR, @StartTime1, 108),1,2) * 60 + SUBSTRING(CONVERT(VARCHAR, @StartTime1, 108),4,2),CAST(CONVERT(date, min(v.VisitStartDate))as datetime)), min(v.VisitStartDate))
			
			else DATEDIFF("MI", DateAdd("MI",SUBSTRING(CONVERT(VARCHAR, @StartTime2, 108),1,2) * 60 + SUBSTRING(CONVERT(VARCHAR, @StartTime2, 108),4,2),CAST(CONVERT(date, min(v.VisitStartDate))as datetime)), min(v.VisitStartDate))
		end
	,dif= --есть ли опоздание
		Case 
			when 

				(sf.FirmID in (298,252) -- Новгородская обл.,  Волгоградская обл.----//304--Пермский край,
					and
					DATEDIFF("MI", DateAdd("MI",SUBSTRING(CONVERT(VARCHAR, @StartTime1, 108),1,2) * 60 + SUBSTRING(CONVERT(VARCHAR, @StartTime1, 108),4,2),CAST(CONVERT(date, min(v.VisitStartDate))as datetime)), min(v.VisitStartDate)) <=0 or min(v.VisitStartDate) is null 
				)
				or 			   
				(sf.FirmID not in (298,252) -- Новгородская обл.,  Волгоградская обл.----//304--Пермский край,
					and
					--DATEDIFF("MI", DateAdd("MI",SUBSTRING(CONVERT(VARCHAR, @start_time, 108),1,2) * 60 + SUBSTRING(CONVERT(VARCHAR, @start_time, 108),4,2),CAST(CONVERT(date, min(v.VisitStartDate))as datetime)), min(v.VisitStartDate)) <=0 or min(v.VisitStartDate) is null then 0
					DATEDIFF("MI", DateAdd("MI",SUBSTRING(CONVERT(VARCHAR, @StartTime2, 108),1,2) * 60 + SUBSTRING(CONVERT(VARCHAR, @StartTime2, 108),4,2),CAST(CONVERT(date, min(v.VisitStartDate))as datetime)), min(v.VisitStartDate)) <=0 or min(v.VisitStartDate) is null
				) 
				then 0
			else 1
		end
	,novisit = 
		Case 
			WHEN CAST(v.VisitStartDate as date) is NULL THEN 0
			else  ROW_NUMBER() OVER(PARTITION BY v.SalerID,CAST(v.VisitStartDate as date) ORDER BY CAST(min(v.VisitStartDate) AS time) ASC)
		End
--------------------------------------------------------------------------------------------------------------		 
	,indicatornoVis=
		Case 
			When v.AddrID is NULL then 1
			Else 0
		end 
	,indicator2 = 
		Case 
			WHEN sl.RouteDate is NULL THEN 0
			else  1
		End
--------------------------------------------------------------------------------------------------------------
	,sl.sts as sl_sts
from Calendar as cal
left outer join SalerRoutes as sl on sl.RouteDate=cal.Date and (sl.SalerID in (@SalerID) or sl.SalerID is null)
FULL outer join Visits as v on sl.AddrID = v.AddrID and CAST(sl.RouteDate as date) = CAST(v.VisitStartDate as date) and cal.Date = CAST(v.VisitStartDate as date) 
and (v.SalerID = sl.SalerID or v.SalerID is null or sl.SalerID is null)
--------------------------------------------------------------------------------------------------------------
left  join Salers as s on s.SalerID = ISNULL(v.SalerID,sl.SalerID) --and s.SalerID = v.SalerID 
--------------------------------------------------------------------------------------------------------------
left  join ExportDocuments as ed on v.VisitID= ed.VisitID 
left  join ExportDocDetails as edd on edd.DocID=ed.DocID
left  join items as i on i.ItemID=edd.ItemID
--------------------------------------------------------------------------------------------------------------
left  join Addresses as a on  a.AddrID = ISNULL(v.AddrID,sl.AddrID)
left  join Clients as c on a.ClientID = c.ClientID
--------------------------------------------------------------------------------------------------------------
left  join SalerFirms as sf on sf.SalerID =  ISNULL(v.SalerID,sl.SalerID)
left  join Firms as f on f.FirmID = sf.FirmID
-------------------------------Добавлено 27.11.2017--------------------------------------------------------------------------------------------------------------
OUTER APPLY (
     SELECT
        [vLat] = AVG( d.[Lat] ),
        [vLong] = AVG( d.[Long] )
     FROM
        DeviceGpsLog as d
     WHERE
            v.[SalerID] = d.[SalerID]
        AND v.[VisitID] = d.[VisitID]
  ) d
--------------------------------------------------------------------------------------------------------------
inner  join salers as ss on s.parentid=ss.salerid
inner  join salers as sss on ss.parentid=sss.salerid
--------------------------------------------------------------------------------------------------------------
--outer apply
--(
--SELECT
--v.VisitStartDate
--from visits as v2
--where v.SalerID=v2.SalerID and CAST(v.VisitStartDate as date) = CAST(v2.VisitStartDate as date)
--) v2
--------------------------------------------------------------------------------------------------------------
left  join Statuses as st on st.StsID=ed.STS
--left  join visits as v2 on v.SalerID = v2.SalerID and CAST(v.VisitStartDate as date) = CAST(v2.VisitStartDate as date)
--------------------------------------------------------------------------------------------------------------
where  (cal.[Date] between @BeginDate and @EndDate or CAST(v.VisitStartDate as date) between @BeginDate and @EndDate) 
and ISNULL(v.SalerID, sl.SalerID) in (@SalerID)
and f.firmid=342
and (sl.sts = 1 or sl.sts is null) -----проверяем отключен ли маршрут
--------------------------------------------------------------------------------------------------------------
Group by  sss.salername , ss.salername , s.SalerName,  v.visitid, cal.Date, v.AddrID, sl.AddrID, v.SalerID, sl.SalerID,c.NameScreen, a.AddrName, v.VisitStartDate, v.VisitEndDate,
sl.RouteDate,a.Lat,a.Long,sf.FirmID,f.FirmName,cal.WeekDayName ,sl.sts, ed.DocTypeID ,d.[vLat] ,d.[vLong] ,st.StsName ,ed.STS ,ed.DocID 
order by  Calendar,num 

Код отчета.
Проблема в данной строке
left  join visits as v2 on v.SalerID = v2.SalerID and CAST(v.VisitStartDate as date) = CAST(v2.VisitStartDate as date)

данная строка выводит последнее время рабочего дня,но она суммирует FactTO на N-ое количество раз(потому как я понял она просчитывает каждый визит работника и все их суммирует)
Вопрос заключается в следующем через что можно вывести данную строку так чтоб не было суммирования.
P.S. Да это опять я,да для вас я тупой,многого не понимаю,но я прихожу за помощью сюда.
P.S.S. Пробовал через APPLY,не получилось .

Сообщение было отредактировано: 5 дек 17, 11:02
5 дек 17, 10:22    [21006308]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с запросом  [new]
Pasher
Member

Откуда:
Сообщений: 34
Прошу прощения,проблемная срока нужна для вывода last_time
,case 
			when (  max(v2.VisitEndDate) <=DateAdd("HOUR", 17,cast(cast(v.VisitStartDate as date) as datetime))) then max(v2.VisitEndDate)
			else null
	 end as last_time -- время окончания раб дня
5 дек 17, 10:24    [21006316]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с запросом  [new]
iap
Member

Откуда: Москва
Сообщений: 46953
Pasher,

не могли бы пару слов сказать по-русски?
Вчитываться в вашу простыню на своём рабочем месте как-то не очень получается.
В чём проблема, короче говоря?

P.S. "данная строка" ничего не суммирует.
5 дек 17, 10:31    [21006352]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с запросом  [new]
Pasher
Member

Откуда:
Сообщений: 34
iap,При добавлении
left  join visits as v2 on v.SalerID = v2.SalerID and CAST(v.VisitStartDate as date) = CAST(v2.VisitStartDate as date)
которая нужна для того чтоб вывести через
,case 
			when (  max(v2.VisitEndDate) <=DateAdd("HOUR", 17,cast(cast(v.VisitStartDate as date) as datetime))) then max(v2.VisitEndDate)
			else null
	 end as last_time
последнюю дату визита за день.
Но при этом перемножается FactTO на количество совершенных визитов сотрудников.
Суть моего вопроса чтоб примерно помогли вывести данную строку без перемножения на количество визитов
5 дек 17, 10:37    [21006381]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с запросом  [new]
Шыфл
Member

Откуда: Прага
Сообщений: 774
Pasher
iap,При добавлении
left  join visits as v2 on v.SalerID = v2.SalerID and CAST(v.VisitStartDate as date) = CAST(v2.VisitStartDate as date)
которая нужна для того чтоб вывести через
,case 
			when (  max(v2.VisitEndDate) <=DateAdd("HOUR", 17,cast(cast(v.VisitStartDate as date) as datetime))) then max(v2.VisitEndDate)
			else null
	 end as last_time
последнюю дату визита за день.
Но при этом перемножается FactTO на количество совершенных визитов сотрудников.
Суть моего вопроса чтоб примерно помогли вывести данную строку без перемножения на количество визитов


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

outer apply
(
SELECT top 1
v.VisitStartDate
--case when (  max(v2.VisitEndDate) <=DateAdd("HOUR", 17,cast(cast(v.VisitStartDate as date) as datetime))) then max(v2.VisitEndDate)
--		else null
--	 end
from visits as v2
where v.SalerID=v2.SalerID and CAST(v.VisitStartDate as date) = CAST(v2.VisitStartDate as date)
order by v2.VisitEndDate desc
) v2


Ну или как ты там пробовал - добавь сортировку и бери только последнюю (первую) дату
5 дек 17, 11:21    [21006579]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с запросом  [new]
iap
Member

Откуда: Москва
Сообщений: 46953
Шыфл
Ну или как ты там пробовал - добавь сортировку и бери только последнюю (первую) дату
Почему первую, а не третью? Так просто, от балды?
Вообще-то, надо правильно проектировать базу. И правильно обращаться к спроектированным таблицам.
Чтобы однозначно использовать одну нужную строку. Тогда и произведения не будет.
5 дек 17, 11:28    [21006614]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с запросом  [new]
Pasher
Member

Откуда:
Сообщений: 34
iap,
Базу проектировали не мы,работаем с чем можем, при условии без произведения, выводит последнее дата\время визита.
Обращение к таблице ,только такое выводит "правильный" результат.
5 дек 17, 11:35    [21006651]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с запросом  [new]
Шыфл
Member

Откуда: Прага
Сообщений: 774
Pasher,

Ну так посчитай правельный результать сбоку на бумажке в apply или cte, и джойни её, а не тащи всю простыню визитов второй раз.

Разбей задачу на куски, а то твой запрос в 2 экрана не влазит - плохой знак. В череп он тоже не помещается, отсюда и ошибки
5 дек 17, 11:39    [21006675]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить