combining 2 select with count and datediff into 1 select. need help.

I have created two select clauses for counting weekdays. Is there a way to combine the two select together? I would like 1 table with two columns:

Jobs Complete Jobs completed within 5 days

10 5

-

SELECT COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS 'Jobs Completed within 5 days'
FROM dbo.Project
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ') AND
(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2 <= 5)

Select COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS 'Total Jobs Completed'
From Project
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ')

select each one as a sub select - this will only work if each one only returns 1 column and 1 row

select

(

SELECT COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS 'Jobs Completed within 5 days'
FROM dbo.Project
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ') AND
(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2 <= 5)

)

(

Select COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS 'Total Jobs Completed'
From Project
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ')

)

|||thanks for the response.. However, this will not work. I'm using Visual Web Developer 2005 express to test your script and it returns 0. I believe its because you it select (....) <-nothing.|||

I missed the comma between the two... I really should check my syntax better!

select

(

SELECT COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2)
FROM dbo.Project
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ') AND
(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2 <= 5)

) AS 'Jobs Completed within 5 days',

(

Select COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2)
From Project
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ')

)AS 'Total Jobs Completed'

|||

thanks for the update. It runs and returned:

Total Jobs Completed Jobs Completed within 5 days

0 0

It should return 6 and 3. So, the subset is not returning the right values.

|||

I have also ran the query just this:

SELECT (SELECT COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS Expr1
FROM dbo.Project
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ') AND
(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2 <= 5)) AS 'Jobs Completed within 5 days'

-

it return:

Jobs Completed within 5 days

0

|||

with some messing around.. I found that this code below works properly.

-

SELECT (SELECT COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS Expr1
FROM dbo.Project
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request '))
AS 'Total Jobs Completed',
(SELECT COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS Expr1
FROM dbo.Project AS Project_1
WHERE (SDCompleted > @.SDCompleted) AND (SDCompleted < @.SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ') AND
(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2 <= 5)) AS 'Jobs Completed within 5 days'

-

Many thanks for you help.