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.
|||Hi,
I am thinking through this same process, and I could be wrong, but I don't think that this code is going to be accurate for determining weekdays. The reason is that if the first day you're counting is a Sunday (in this case, your DateintoSD), then you will have one more weekday than SQL is going to count. It will count the first week interval 6 days later, between Saturday and Sunday. You're multiplying your week DateDiff by 2, so you'll get that saturday and sunday subtracted from your total days of the month, but that first Sunday never gets subtracted. Am I wrong?
There is a solution I found elsewhere that involves building a calendar table in SQL, and if you Google that it will come up in your results. That's a bit more involved, however.
Andy
|||A "calander" table should be added to any database as standard practice, loaded with weekday/end flags, public holidays and various date formats (these can be very useful when dealing with system interfaces)
