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
| SELECT Kaa.LineNumber, DATEPART(week, dbo.Calculate_EndDate(Kaa.StartDate, (Kaa.DayNumber+1), '9900a5b9-14b5-4afc-abec-d6b3c2bc2f42')) AS Week,
SUM(Kaa.PriceMat) AS PriceMat, SUM(Kaa.PricePers) AS PricePers
FROM (
SELECT Act.LineNumber, T.StartDate, TM.DayNumber, SUM(TM.Quantity*RMeP.Rate) AS PricePers, SUM(TM.Quantity*RMeM.Rate) AS PriceMat
FROM dbo.TaskMean TM
LEFT OUTER JOIN (
(SELECT Code, Id
FROM dbo.Mean
WHERE SUBSTRING (Mean.Code, 1, 2 ) = 'ML') MeP
JOIN (
SELECT IdMean, SUM(Rate) AS Rate
FROM dbo.RateMean
GROUP BY IdMean) RMeP
ON RMeP.IdMean = MeP.Id)
ON TM.IdMean = MeP.Id
LEFT OUTER JOIN (
(SELECT Code, Id
FROM dbo.Mean
WHERE SUBSTRING (Mean.Code, 1, 2 ) = 'ME') MeM
JOIN (
SELECT IdMean, SUM(Rate) AS Rate
FROM dbo.RateMean
GROUP BY IdMean) RMeM
ON RMeM.IdMean = MeM.Id)
ON TM.IdMean = MeM.Id
JOIN dbo.Task T
ON TM.IdTask = T.Id
RIGHT OUTER JOIN dbo.Activity Act
ON T.IdActivity = Act.Id
JOIN dbo.Schedule Sch
ON Act.IdSchedule = Sch.Id
JOIN dbo.Site S
ON S.Id = Sch.IdSite
WHERE Sch.IdSite='9900a5b9-14b5-4afc-abec-d6b3c2bc2f42' AND Sch.ScheduleType=0
GROUP BY Act.LineNumber, TM.DayNumber, T.StartDate) Kaa
GROUP BY Kaa.LineNumber, DATEPART(week, dbo.Calculate_EndDate(Kaa.StartDate, (Kaa.DayNumber+1), '9900a5b9-14b5-4afc-abec-d6b3c2bc2f42'))
ORDER BY LineNumber ASC, Week ASC |
Partager