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

Откуда:
Сообщений: 265
Подскажите как мне сделать так что бы к Summa + Price Считалась?

select distinct H.Estimate, C.ID AS [CONTRACT], C.FirmName + ' ' + L.LawForm AS NAME,
(select sum(summa) FROM dbo.ContractSumTable S WHERE S.EstimateID = 18705000 AND C.ID = S.ContractID) as summa, S.Author, S.Status
INTO #Temp
FROM ContractFull C 
INNER JOIN _ApplicHeads A ON C.ID = A.Acceptor
INNER JOIN _EstimateHeads H ON H.Estimate = A.EstimateID 
INNER JOIN ContractFullLawForm L ON C.lawForm = L.LawID
LEFT JOIN  dbo.ContractSumTable S ON S.ContractID = C.ID AND S.EstimateID = H.Estimate AND S.Status > 0
WHERE H.Estimate = 18705000 AND C.[Status] > 0 AND A.[Status] > 0 AND H.[Status] > 0 

SELECT Estimate, [CONTRACT], NAME, summa, Author, Status FROM #Temp WHERE [CONTRACT] <> 12019000

UNION ALL

SELECT 18705000, -1, 'Промперсонал', (select sum(summa) FROM dbo.ContractSumTable WHERE EstimateID = 18705000 AND ContractID = -1), 1, 1

UNION  

SELECT S.EstimateID, S.ContractID, C.FirmName + ' ' + L.LawForm as Name, sum(T.Summa) as Summa, S.[Status], S.Author
FROM dbo.ContractSum S
INNER JOIN ContractFull C ON C.ID = S.ContractID
LEFT JOIN ContractFullLawForm L ON L.LawID = C.lawForm
LEFT JOIN dbo.ContractSumTable T ON T.EstimateID = S.EstimateID AND T.ContractID = C.ID
WHERE S.EstimateID = 18705000 AND C.[Status] > 0 AND S.[Status] > 0
GROUP BY S.EstimateID, S.ContractID, C.ID, C.FirmName + ' ' + L.LawForm, S.[Status], S.Author 




SELECT A.EstimateID, A.Acceptor AS ContractID, 'TEST' AS Name, /* sum( */B.Price/* ) AS Summa */, A.Author, A.[Status]
FROM _ApplicHeads A
INNER JOIN _ApplicBodies B ON A.Applic = B.ApplicID
INNER JOIN ContractSumTable T ON T.EstimateID = A.EstimateID AND T.ContractID = A.Acceptor
WHERE A.EstimateID = 18705000 AND A.Published <> 4 --AND A.Acceptor = 12334000 
GROUP BY A.EstimateID, A.Acceptor, B.Price, A.Author, A.[Status]
21 окт 16, 12:59    [19808766]     Ответить | Цитировать Сообщить модератору
 Re: Логика  [new]
Pabl0
Member

Откуда:
Сообщений: 265
Так вроде работает :)

select distinct H.Estimate, C.ID AS [CONTRACT], C.FirmName + ' ' + L.LawForm AS NAME,
(select sum(summa) FROM dbo.ContractSumTable S WHERE S.EstimateID = 18705000 AND C.ID = S.ContractID) as summa, S.Author, S.Status
INTO #Temp
FROM ContractFull C 
INNER JOIN _ApplicHeads A ON C.ID = A.Acceptor
INNER JOIN _EstimateHeads H ON H.Estimate = A.EstimateID 
INNER JOIN ContractFullLawForm L ON C.lawForm = L.LawID
LEFT JOIN  dbo.ContractSumTable S ON S.ContractID = C.ID AND S.EstimateID = H.Estimate AND S.Status > 0
WHERE H.Estimate = 18705000 AND C.[Status] > 0 AND A.[Status] > 0 AND H.[Status] > 0 

SELECT Estimate, [CONTRACT], NAME, summa, Author, Status FROM #Temp WHERE [CONTRACT] <> 12019000

UNION ALL

SELECT 18705000, -1, 'Промперсонал', (select sum(summa) FROM dbo.ContractSumTable WHERE EstimateID = 18705000 AND ContractID = -1), 1, 1

UNION  

SELECT S.EstimateID, S.ContractID, C.FirmName + ' ' + L.LawForm as Name, (sum(T.Summa) + (SELECT B.Price FROM _ApplicHeads A INNER JOIN _ApplicBodies B ON A.Applic = B.ApplicID
WHERE A.EstimateID = 18705000 AND A.Published <> 4 AND A.EstimateID = S.EstimateID AND C.ID = A.Acceptor)) as Summa, S.[Status], S.Author

FROM dbo.ContractSum S
INNER JOIN ContractFull C ON C.ID = S.ContractID
LEFT JOIN ContractFullLawForm L ON L.LawID = C.lawForm
LEFT JOIN dbo.ContractSumTable T ON T.EstimateID = S.EstimateID AND T.ContractID = C.ID
WHERE S.EstimateID = 18705000 AND C.[Status] > 0 AND S.[Status] > 0
GROUP BY S.EstimateID, S.ContractID, C.ID, C.FirmName + ' ' + L.LawForm, S.[Status], S.Author 




SELECT A.EstimateID, A.Acceptor AS ContractID, 'TEST' AS Name, /* sum( */B.Price/* ) AS Summa */, A.Author, A.[Status]
FROM _ApplicHeads A
INNER JOIN _ApplicBodies B ON A.Applic = B.ApplicID
INNER JOIN ContractSumTable T ON T.EstimateID = A.EstimateID AND T.ContractID = A.Acceptor
WHERE A.EstimateID = 18705000 AND A.Published <> 4 --AND A.Acceptor = 12334000 
GROUP BY A.EstimateID, A.Acceptor, B.Price, A.Author, A.[Status]



DROP TABLE #Temp
21 окт 16, 13:10    [19808826]     Ответить | Цитировать Сообщить модератору
 Re: Логика  [new]
Pabl0
Member

Откуда:
Сообщений: 265
Нет, не верно :(
21 окт 16, 13:32    [19808995]     Ответить | Цитировать Сообщить модератору
 Re: Логика  [new]
Pabl0
Member

Откуда:
Сообщений: 265
Больше похоже на правду :)

select distinct H.Estimate, C.ID AS [CONTRACT], C.FirmName + ' ' + L.LawForm AS NAME,
((select sum(summa) FROM dbo.ContractSumTable S1 WHERE S1.EstimateID = 18705000 AND C.ID = S1.ContractID) + (SELECT B.Price FROM _ApplicHeads A INNER JOIN _ApplicBodies B ON A.Applic = B.ApplicID
WHERE A.EstimateID = 18705000 AND A.Published <> 4 AND A.EstimateID = S.EstimateID AND C.ID = A.Acceptor)) as summa, S.Author, S.Status
INTO #Temp
FROM ContractFull C 
INNER JOIN _ApplicHeads A ON C.ID = A.Acceptor
INNER JOIN _EstimateHeads H ON H.Estimate = A.EstimateID 
INNER JOIN ContractFullLawForm L ON C.lawForm = L.LawID
LEFT JOIN  dbo.ContractSumTable S ON S.ContractID = C.ID AND S.EstimateID = H.Estimate AND S.Status > 0
WHERE H.Estimate = 18705000 AND C.[Status] > 0 AND A.[Status] > 0 AND H.[Status] > 0 

SELECT Estimate, [CONTRACT], NAME, summa, Author, Status FROM #Temp WHERE [CONTRACT] <> 12019000

UNION ALL

SELECT 18705000, -1, 'Промперсонал', (select sum(summa) FROM dbo.ContractSumTable WHERE EstimateID = 18705000 AND ContractID = -1), 1, 1

UNION 

SELECT S.EstimateID, S.ContractID, C.FirmName + ' ' + L.LawForm as Name, (sum(T.Summa) + (SELECT B.Price FROM _ApplicHeads A INNER JOIN _ApplicBodies B ON A.Applic = B.ApplicID
WHERE A.EstimateID = 18705000 AND A.Published <> 4 AND A.EstimateID = S.EstimateID AND C.ID = A.Acceptor)) as Summa, S.[Status], S.Author

FROM dbo.ContractSum S
INNER JOIN ContractFull C ON C.ID = S.ContractID
LEFT JOIN ContractFullLawForm L ON L.LawID = C.lawForm
LEFT JOIN dbo.ContractSumTable T ON T.EstimateID = S.EstimateID AND T.ContractID = C.ID
WHERE S.EstimateID = 18705000 AND C.[Status] > 0 AND S.[Status] > 0
GROUP BY S.EstimateID, S.ContractID, C.ID, C.FirmName + ' ' + L.LawForm, S.[Status], S.Author 




/* SELECT A.EstimateID, A.Acceptor AS ContractID, 'TEST' AS Name, /* sum( */B.Price/* ) AS Summa */, A.Author, A.[Status]
FROM _ApplicHeads A
INNER JOIN _ApplicBodies B ON A.Applic = B.ApplicID
INNER JOIN ContractSumTable T ON T.EstimateID = A.EstimateID AND T.ContractID = A.Acceptor
WHERE A.EstimateID = 18705000 AND A.Published <> 4 --AND A.Acceptor = 12334000 
GROUP BY A.EstimateID, A.Acceptor, B.Price, A.Author, A.[Status] */



DROP TABLE #Temp
21 окт 16, 13:36    [19809024]     Ответить | Цитировать Сообщить модератору
 Re: Логика  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
эксгибиционизм какой-то
21 окт 16, 13:37    [19809032]     Ответить | Цитировать Сообщить модератору
 Re: Логика  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
Много букав, мало данных.
21 окт 16, 13:49    [19809108]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить