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

Откуда:
Сообщений: 265
Подскажите можно этот код подредактировать так что бы работал через вьюжку?


SELECT t1.EstimateID, SUM( 
				t1.[CountTimeV] * t1.[OneTime]+ t1.CQuant * t1.Price+
				(
					t1.[CountTimeV] * t1.[OneTime] + t1.CQuant * t1.Price
				 )/100*[AgencyRate]
				+(
					t1.[CountTimeV] * t1.[OneTime] + t1.CQuant * t1.Price
				
				)/100*TaxRateKl +
					((
						t1.[CountTimeV] * t1.[OneTime] + t1.CQuant * t1.Price
					)/100*[AgencyRate])/100*18
			) as [SumTotal]
	INTO #Price
	FROM 
 	(
		SELECT  B.EstimateID, B.[CountTimeV], B.[OneTime], 0 AS CQuant, 0 AS Price, NULL AS [Sum], agencyRate, TaxRateKL
		   from _EstimateBodies2 B WHERE B.[Status] > 0
		union all
		SELECT E.EstimateID, 0,0 , E.CQuant, E.Price, NULL,agencyRate,TaxRateKL
		  from _ExpensesBodies E WHERE E.[Status] > 0
		
	) AS t1
  	GROUP BY t1.EstimateID


SELECT D.EstimateID, D.SectionID, SUM(D.CQuant * D.Price) AS summ, SUM((D.CQuant * D.Price) * (D.AgencyRate / 100)) AS SumAgency,
	   P.SumTotal AS SumTotal,
	   SUM((D.CQuant * D.Price) * (1 + D.AgencyRate / 100)) AS SumTotalWithoutTax, SUM(D.Quant * D.Seb) AS Seb, SUM((D.Quant * D.Seb) / (1 + ISNULL(D.TaxRate, 18) / 100)) 
                      AS SebWithoutTax
FROM  dbo._ExpensesBodies AS D LEFT OUTER JOIN
      dbo._EstimateHeads AS d3 ON d3.Estimate = D.EstimateID
      INNER JOIN #Price P ON P.EstimateID = D3.Estimate
WHERE     (D.Status > 0) AND D.EstimateID IN (19792000,20007000) AND D3.EstimateTypeNew = 1
GROUP BY D.EstimateID, D.SectionID, P.SumTotal

DROP TABLE #Price
15 мар 17, 15:32    [20298465]     Ответить | Цитировать Сообщить модератору
 Re: Перепись процедуры во вьюжку  [new]
Pabl0
Member

Откуда:
Сообщений: 265
Просто INTO использовать же там нельзя
15 мар 17, 15:32    [20298469]     Ответить | Цитировать Сообщить модератору
 Re: Перепись процедуры во вьюжку  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
create view X as
with price as (
SELECT t1.EstimateID, SUM( 
				t1.[CountTimeV] * t1.[OneTime]+ t1.CQuant * t1.Price+
				(
					t1.[CountTimeV] * t1.[OneTime] + t1.CQuant * t1.Price
				 )/100*[AgencyRate]
				+(
					t1.[CountTimeV] * t1.[OneTime] + t1.CQuant * t1.Price
				
				)/100*TaxRateKl +
					((
						t1.[CountTimeV] * t1.[OneTime] + t1.CQuant * t1.Price
					)/100*[AgencyRate])/100*18
			) as [SumTotal]
	FROM 
 	(
		SELECT  B.EstimateID, B.[CountTimeV], B.[OneTime], 0 AS CQuant, 0 AS Price, NULL AS [Sum], agencyRate, TaxRateKL
		   from _EstimateBodies2 B WHERE B.[Status] > 0
		union all
		SELECT E.EstimateID, 0,0 , E.CQuant, E.Price, NULL,agencyRate,TaxRateKL
		  from _ExpensesBodies E WHERE E.[Status] > 0
		
	) AS t1
  	GROUP BY t1.EstimateID
)

SELECT D.EstimateID, D.SectionID, SUM(D.CQuant * D.Price) AS summ, SUM((D.CQuant * D.Price) * (D.AgencyRate / 100)) AS SumAgency,
	   P.SumTotal AS SumTotal,
	   SUM((D.CQuant * D.Price) * (1 + D.AgencyRate / 100)) AS SumTotalWithoutTax, SUM(D.Quant * D.Seb) AS Seb, SUM((D.Quant * D.Seb) / (1 + ISNULL(D.TaxRate, 18) / 100)) 
                      AS SebWithoutTax
FROM  dbo._ExpensesBodies AS D LEFT OUTER JOIN
      dbo._EstimateHeads AS d3 ON d3.Estimate = D.EstimateID
      INNER JOIN Price P ON P.EstimateID = D3.Estimate
WHERE     (D.Status > 0) AND D.EstimateID IN (19792000,20007000) AND D3.EstimateTypeNew = 1
GROUP BY D.EstimateID, D.SectionID, P.SumTotal
15 мар 17, 15:35    [20298491]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить