SELECT WellID tmp WellID Index dl Index iOil SUM case when year tmp Da

 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
SELECT
[WellID] = tmp.WellID
,[Index] = dl.[Index]
,[iOil] = SUM(case when year(tmp.[Date])*12+month(tmp.[Date])-1 >= year(dl.BOP)*12+month(dl.BOP)-1 then tmp.[Oil] end)
,[iOilMech] = SUM(case when year(tmp.[Date])*12+month(tmp.[Date])-1 >= year(dl.BOP)*12+month(dl.BOP)-1 then tmp.[OilMech] end)
,[iWater] = SUM(case when year(tmp.[Date])*12+month(tmp.[Date])-1 >= year(dl.BOP)*12+month(dl.BOP)-1 then tmp.[Water] end)
,[iWaterMech] = SUM(case when year(tmp.[Date])*12+month(tmp.[Date])-1 >= year(dl.BOP)*12+month(dl.BOP)-1 then tmp.[WaterMech] end)
,[iWorkDays] = SUM(case when year(tmp.[Date])*12+month(tmp.[Date])-1 >= year(dl.BOP)*12+month(dl.BOP)-1 then tmp.[WorkDays] end)
,[iPumping] = SUM(case when year(tmp.[Date])*12+month(tmp.[Date])-1 >= year(dl.BOP)*12+month(dl.BOP)-1 then tmp.[Pumping] end)
,[iPWorkDays] = SUM(case when year(tmp.[Date])*12+month(tmp.[Date])-1 >= year(dl.BOP)*12+month(dl.BOP)-1 then tmp.[PWorkDays] end)
,[iFluid_m3p] = SUM(case when year(tmp.[Date])*12+month(tmp.[Date])-1 >= year(dl.BOP)*12+month(dl.BOP)-1 then tmp.[Fluid_m3p] end)
,[aOil] = SUM(tmp.[Oil])
,[aOil_Dif] = SUM(tmp.[Oil_Dif])
,[aWater] = SUM(tmp.[Water])
,[aWater_Dif] = SUM(tmp.[Water_Dif])
,[aPumping] = SUM(tmp.[Pumping])
,[aPumping_Dif] = SUM(tmp.[Pumping_Dif])
,[aFluid_m3p] = SUM(tmp.[Fluid_m3p])
,[aFluid_m3p_Dif] = SUM(tmp.[Fluid_m3p_Dif])
FROM (
SELECT
[WellID] = tmp.WellID
,[Date] = tmp.[Date]
,[Oil] = SUM(tmp.[Oil])
,[Oil_Dif] = SUM(tmp.[Oil_Dif])
,[OilMech] = SUM(tmp.[OilMech])
--,[OilMech_Dif] = SUM(tmp.[OilMech_Dif])
,[Water] = SUM(tmp.[Water])
,[Water_Dif] = SUM(tmp.[Water_Dif])
,[WaterMech] = SUM(tmp.[Water])
--,[WaterMech_Dif] = SUM(tmp.[Water_Dif])
,[WorkDays] = MAX(tmp.[WorkDays])
--,[WorkDays_Dif] = MAX(tmp.[WorkDays_Dif])
,[Pumping] = SUM(tmp.[Pumping])
,[Pumping_Dif] = SUM(tmp.[Pumping_Dif])
,[PWorkDays] = MAX(tmp.[PWorkDays])
--,[PWorkDays_Dif] = MAX(tmp.[PWorkDays_Dif])
,[Fluid_m3p] = SUM(tmp.[Fluid_m3p])
,[Fluid_m3p_Dif] = SUM(tmp.[Fluid_m3p_Dif])
FROM (
SELECT
[WellID] = wp.WellID
,[Date] = wp.[Date]
,[StratumID] = wp.StratumID
,[Oil] = SUM(case when (wp.WorkDirection = 1) then isnull(wp.OilValue,0) else 0 end)
,[Oil_Dif] = SUM(case when (wp.WorkDirection = 3) then isnull(wp.OilValue,0) else 0 end)
,[OilMech] = SUM(case when (wp.WorkDirection = 1)and(wp.ExploitModeID <> 1) then isnull(wp.OilValue,0) else 0 end)
--,[OilMech_Dif] = SUM(case when (wp.WorkDirection = 3)and(wp.ExploitModeID <> 1) then isnull(wp.OilValue,0) else 0 end)
,[Water] = SUM(case when (wp.WorkDirection = 1) then isnull(wp.WaterValue,0) else 0 end)
,[Water_Dif] = SUM(case when (wp.WorkDirection = 3) then isnull(wp.WaterValue,0) else 0 end)
,[WaterMech] = SUM(case when (wp.WorkDirection = 1)and(wp.ExploitModeID <> 1) then isnull(wp.WaterValue,0) else 0 end)
--,[WaterMech_Dif] = SUM(case when (wp.WorkDirection = 3)and(wp.ExploitModeID <> 1) then isnull(wp.WaterValue,0) else 0 end)
,[WorkDays] = SUM(case when (wp.WorkDirection = 1) then isnull(wp.AccumDays,0)+isnull(StreamDays,0) else 0 end)
--,[WorkDays_Dif] = SUM(case when (wp.WorkDirection = 3) then isnull(wp.AccumDays,0)+isnull(StreamDays,0) else 0 end)
,[Pumping] = SUM(case when (wp.WorkDirection = 0) then isnull(wp.WaterValue,0) else 0 end)
,[Pumping_Dif] = SUM(case when (wp.WorkDirection = 2) then isnull(wp.WaterValue,0) else 0 end)
,[PWorkDays] = SUM(case when (wp.WorkDirection = 0) then isnull(wp.AccumDays,0)+isnull(StreamDays,0) else 0 end)
--,[PWorkDays_Dif] = SUM(case when (wp.WorkDirection = 2) then isnull(wp.AccumDays,0)+isnull(StreamDays,0) else 0 end)
,[Fluid_m3p] = SUM(case when (wp.WorkDirection = 1) then isnull(wp.OilValue,0)*sl.pop+isnull(wp.WaterValue,0)/sl.pw else 0 end)
,[Fluid_m3p_Dif] = SUM(case when (wp.WorkDirection = 3) then isnull(wp.OilValue,0)*sl.pop+isnull(wp.WaterValue,0)/sl.pw else 0 end)
FROM WellProduction wp WITH (NOLOCK)
join #SG_WELLLIST wl on wp.WellID = wl.WellID
join #SG_STRATUMLIST sl on wp.StratumID = sl.StratumID
WHERE isnull(wp.VersionID,0) = 0--@ProductionVersion
--and wp.WellID in (SELECT WellID FROM #SG_WELLLIST)
--and wp.StratumID in (SELECT StratumID FROM #SG_STRATUMLIST)
GROUP BY wp.WellID, wp.[Date], wp.StratumID
) tmp
GROUP BY tmp.[WellID], tmp.[Date]
) tmp
join #SG_DATELIST dl on year(tmp.[Date])*12+month(tmp.[Date])-1 <= year(dl.EOP)*12+month(dl.EOP)-1
GROUP BY tmp.[WellID], dl.[Index]