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

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

• santech79

  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.

  
  

• The Philosiphiser

  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 ')

  )

  
  
  

• Ceefour

  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

  
  

• Johnson.R

  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.
  

• baskardurai

  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)

  
  
  

• Atem

  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'

  
  
  

• A. Nagy

  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.

   

  
  

• oz_michaelw

  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

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