masoode
سه شنبه 23 بهمن 1397, 11:11 صبح
سلام
من برای طراحی داشبورد نرم افزارم صفحه ای دارم که در آن چندین گراف وجود دارد. من برای هر گراف یک TADOQUERY روی فرم گذاشته ام. آیا این امکان وجود دارد که همه SELECT را در یک کوئری بنویسم (مثل SQL Server که با دستور GO از هم جدا میکنیم)
SELECT FullName,COUNT(*)CNT
FROM tblSF03Log A INNER JOIN tblUsersLogin B ON A.OperatorID=B.ID
WHERE DATEPART(YEAR,DT)=DATEPART(YEAR,SYSDATETIME())
GROUP BY FullName
go
Declare @tmp real
SET @tmp=(100.0/(SELECT COUNT(*) FROM vwSF03Log WHERE (CostFilled=1 OR QCManOk=1)and DATEPART(YEAR,DT)=DATEPART(YEAR,SYSDATETIME())))
SELECT B.Name,(SUM(ValidWaste)*@tmp)ValidWasteCNT,(SUM(In validWaste)*@tmp)InvalidWasteCNT
FROM vwSF03Log A
INNER JOIN tblFactors B ON A.WasteFactor=B.Id
WHERE CostFilled=1 and DATEPART(YEAR,DT)=DATEPART(YEAR,SYSDATETIME())
GROUP BY B.Name
go
SELECT B.FullName,(A.ValidWasteCNT*100.0/C.CNT)ValidWasteCNT,(A.InvalidWasteCNT*100.0/C.CNT)InvalidWasteCNT
FROM(
SELECT OperatorID,SUM(ValidWaste)ValidWasteCNT,SUM(Invali dWaste) InvalidWasteCNT
FROM vwSF03Log
WHERE WasteFactor is not null and Cost is not null and DATEPART(YEAR,DT)=DATEPART(YEAR,SYSDATETIME())
GROUP BY OperatorID
)A
INNER JOIN tblUsersLogin B ON A.OperatorID=B.Id
INNER JOIN (
SELECT OperatorID,COUNT(*) CNT
FROM vwSF03Log
WHERE ((WasteFactor is not null and Cost is not null)or(QCManOk=1))and DATEPART(YEAR,DT)=DATEPART(YEAR,SYSDATETIME())
GROUP BY OperatorID) C ON A.OperatorID=C.OperatorID
go
SELECT B.Name as Model,(A.ValidWasteCNT*100.0/C.CNT)ValidWasteCNT,(A.InvalidWasteCNT*100.0/C.CNT)InvalidWasteCNT
FROM(
SELECT ModelIranCode,SUM(ValidWaste)ValidWasteCNT,SUM(Inv alidWaste) InvalidWasteCNT
FROM vwSF03Log
WHERE CostFilled=1 and DATEPART(YEAR,DT)=DATEPART(YEAR,SYSDATETIME())
GROUP BY ModelIranCode
)A
INNER JOIN tblSubProductTypes B ON A.ModelIranCode=B.Id
INNER JOIN (
SELECT ModelIranCode,COUNT(*) CNT
FROM vwSF03Log
WHERE ((WasteFactor is not null and Cost is not null)or(QCManOk=1))and DATEPART(YEAR,DT)=DATEPART(YEAR,SYSDATETIME())
GROUP BY ModelIranCode
) C ON A.ModelIranCode=C.ModelIranCode
go
SELECT B.Name as Model,COUNT(*)CNT
FROM vwSF03Log A
INNER JOIN tblSubProductTypes B ON A.ModelIranCode=B.Id
WHERE DATEPART(YEAR,DT)=DATEPART(YEAR,SYSDATETIME())
GROUP BY B.Name
go
SELECT B.Name as Organ,COUNT(*)CNT
FROM vwSF03Log A
INNER JOIN tblFactors B ON A.WasteFactor=B.Id
WHERE ((CostFilled=1)or(QCManOk=1))and DATEPART(YEAR,DT)=DATEPART(YEAR,SYSDATETIME())
GROUP BY B.Name
من برای طراحی داشبورد نرم افزارم صفحه ای دارم که در آن چندین گراف وجود دارد. من برای هر گراف یک TADOQUERY روی فرم گذاشته ام. آیا این امکان وجود دارد که همه SELECT را در یک کوئری بنویسم (مثل SQL Server که با دستور GO از هم جدا میکنیم)
SELECT FullName,COUNT(*)CNT
FROM tblSF03Log A INNER JOIN tblUsersLogin B ON A.OperatorID=B.ID
WHERE DATEPART(YEAR,DT)=DATEPART(YEAR,SYSDATETIME())
GROUP BY FullName
go
Declare @tmp real
SET @tmp=(100.0/(SELECT COUNT(*) FROM vwSF03Log WHERE (CostFilled=1 OR QCManOk=1)and DATEPART(YEAR,DT)=DATEPART(YEAR,SYSDATETIME())))
SELECT B.Name,(SUM(ValidWaste)*@tmp)ValidWasteCNT,(SUM(In validWaste)*@tmp)InvalidWasteCNT
FROM vwSF03Log A
INNER JOIN tblFactors B ON A.WasteFactor=B.Id
WHERE CostFilled=1 and DATEPART(YEAR,DT)=DATEPART(YEAR,SYSDATETIME())
GROUP BY B.Name
go
SELECT B.FullName,(A.ValidWasteCNT*100.0/C.CNT)ValidWasteCNT,(A.InvalidWasteCNT*100.0/C.CNT)InvalidWasteCNT
FROM(
SELECT OperatorID,SUM(ValidWaste)ValidWasteCNT,SUM(Invali dWaste) InvalidWasteCNT
FROM vwSF03Log
WHERE WasteFactor is not null and Cost is not null and DATEPART(YEAR,DT)=DATEPART(YEAR,SYSDATETIME())
GROUP BY OperatorID
)A
INNER JOIN tblUsersLogin B ON A.OperatorID=B.Id
INNER JOIN (
SELECT OperatorID,COUNT(*) CNT
FROM vwSF03Log
WHERE ((WasteFactor is not null and Cost is not null)or(QCManOk=1))and DATEPART(YEAR,DT)=DATEPART(YEAR,SYSDATETIME())
GROUP BY OperatorID) C ON A.OperatorID=C.OperatorID
go
SELECT B.Name as Model,(A.ValidWasteCNT*100.0/C.CNT)ValidWasteCNT,(A.InvalidWasteCNT*100.0/C.CNT)InvalidWasteCNT
FROM(
SELECT ModelIranCode,SUM(ValidWaste)ValidWasteCNT,SUM(Inv alidWaste) InvalidWasteCNT
FROM vwSF03Log
WHERE CostFilled=1 and DATEPART(YEAR,DT)=DATEPART(YEAR,SYSDATETIME())
GROUP BY ModelIranCode
)A
INNER JOIN tblSubProductTypes B ON A.ModelIranCode=B.Id
INNER JOIN (
SELECT ModelIranCode,COUNT(*) CNT
FROM vwSF03Log
WHERE ((WasteFactor is not null and Cost is not null)or(QCManOk=1))and DATEPART(YEAR,DT)=DATEPART(YEAR,SYSDATETIME())
GROUP BY ModelIranCode
) C ON A.ModelIranCode=C.ModelIranCode
go
SELECT B.Name as Model,COUNT(*)CNT
FROM vwSF03Log A
INNER JOIN tblSubProductTypes B ON A.ModelIranCode=B.Id
WHERE DATEPART(YEAR,DT)=DATEPART(YEAR,SYSDATETIME())
GROUP BY B.Name
go
SELECT B.Name as Organ,COUNT(*)CNT
FROM vwSF03Log A
INNER JOIN tblFactors B ON A.WasteFactor=B.Id
WHERE ((CostFilled=1)or(QCManOk=1))and DATEPART(YEAR,DT)=DATEPART(YEAR,SYSDATETIME())
GROUP BY B.Name