We help IT Professionals succeed at work.
Get Started

connect 2 sql statment

260 Views
Last Modified: 2021-04-21
Hi experts, i have these two sql statment and i am trying to connect them both in one sql statement, and they are from diffrent database, i tried to connect them but the records i get are wrong, they are as follow
---------------------------------
1- empDataBase
SELECT     dbo.vw_GetDuration.AssignDate, dbo.vw_GetDuration.FinishDate, dbo.vw_GetDuration.Duration,  (dbo.vw_GetDuration.Duration - OFFDAYS) AS ActualDuration, dbo.vw_GetDuration.EngSec, dbo.vw_GetDuration.DirCode, dbo.vw_GetDuration.KPI,
dbo.vw_GetDuration.ServiceName, dbo.vw_GetDuration.ServiceNo, dbo.vw_GetDuration.SecCode
FROM        dbo.vw_GetDuration
GROUP BY dbo.vw_GetDuration.AssignDate, dbo.vw_GetDuration.FinishDate, dbo.vw_GetDuration.Duration, dbo.vw_GetDuration.EngSec,                dbo.vw_GetDuration.DirCode, dbo.vw_GetDuration.KPI, dbo.vw_GetDuration.ServiceName, dbo.vw_GetDuration.ServiceNo, dbo.vw_GetDuration.SecCode

2-WorkDataBase
SELECT COUNT(*) as OFFDAYS FROM workData.tblCalendarinv WHERE(workData.Caldate >= dbo.vw_GetDuration.AssignDate ) AND (workData.Caldate <= dbo.vw_GetDuration.FinishDate) AND (workData.holiday = '1')
----------------------------------

where OFFDAYS in the first sql statment is a result from the second sql statement.
i tried to connect them as fllow

----------------------------------
SELECT     COUNT(*) AS OFFDAYS, dbo.vw_GetDuration.AssignDate, dbo.vw_GetDuration.FinishDate, dbo.vw_GetDuration.Duration,
dbo.vw_GetDuration.Duration - COUNT(*) AS ActualDuration, dbo.vw_GetDuration.EngSec, dbo.vw_GetDuration.DirCode, dbo.vw_GetDuration.KPI,
dbo.vw_GetDuration.ServiceName, dbo.vw_GetDuration.ServiceNo, dbo.vw_GetDuration.SecCode
FROM         dbo.vw_GetDuration LEFT OUTER JOIN
workData.tblCalendarinv ON workData.tblCalendarinv.Caldate >= CAST(CONVERT(varchar, dbo.vw_GetDuration.AssignDate, 101) AS datetime) AND
workData.tblCalendarinv.Caldate <= CAST(CONVERT(varchar, dbo.vw_GetDuration.FinishDate, 101) AS datetime) AND pr1.dbo.tblCalendarinv.Holiday = '1'
GROUP BY dbo.vw_GetDuration.AssignDate, dbo.vw_GetDuration.FinishDate, dbo.vw_GetDuration.Duration, dbo.vw_GetDuration.EngSec,
dbo.vw_GetDuration.DirCode, dbo.vw_GetDuration.KPI, dbo.vw_GetDuration.ServiceName, dbo.vw_GetDuration.ServiceNo,  dbo.vw_GetDuration.SecCode

but the OFFDAYS is wrong
please help me
Comment
Watch Question
Commented:
This problem has been solved!
Unlock 1 Answer and 5 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE