1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
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
Код отчета.
Проблема в данной строке 1.
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,не получилось . |
