get records in today RSS

• • Reply
  

  nazlin

  Member

  135 Points

  173 Posts

  get records in today

  Apr 18, 2012 09:29 AM|LINK

  hi dear,

  I want get many count from some of table for today

  my script is :

  select
   e.name,
  (select  COUNT(*) from  T_ReportWork  r where r.Engineer=e.id AND YEAR(r.DateInsert) = YEAR(GETDATE()) AND MONTH(R.dateInsert)=MONTH(GETDATE()) AND DAY(R.dateInsert)=DAY(GETDATE()) )as CompleteWork ,
  
   from T_Engineer e where e.Access='Engineer'

  but i dont have any result,complete work is zero

  are you have any siggesstion for me?

  I think for I am
• • Reply
  

  ignatandrei

  All-Star

  134960 Points

  21632 Posts

  Moderator

  MVP

  Re: get records in today

  Apr 18, 2012 09:33 AM|LINK

  nazlin

  dont have any result

  maybe because does not have any result in the database ?

  My .NET blog : http://msprogrammer.serviciipeweb.ro/ |2012 Programmer tools
  • Edited by Chen Yu - MSFT on Apr 27, 2012 10:04 AM
  • Edited by Chen Yu - MSFT on Apr 27, 2012 10:04 AM
• • Reply
  

  Ashutosh Pat...

  Contributor

  5737 Points

  1105 Posts

  Re: get records in today

  Apr 18, 2012 09:35 AM|LINK

  try below code:

  select  e.name,COUNT(e.*) as CompleteWork
  from  T_ReportWork  r JOIN T_Engineer e
  ON r.Engineer=e.id
  AND YEAR(r.DateInsert) = YEAR(GETDATE())
  AND MONTH(R.dateInsert)=MONTH(GETDATE())
  AND DAY(R.dateInsert)=DAY(GETDATE())
  and e.Access='Engineer'
  group by e.name

  Ashutosh Pathak
  Blog: http://catchcode.blogspot.com
  Please mark it as answer if it helps, as clicking on the button can save time of others :)
  MCP,MCAD,MCSD,MCTS
  • Edited by ashutosh pathak on Apr 18, 2012 09:35 AM
  • Marked as answer by Chen Yu - MSFT on Apr 27, 2012 10:04 AM
• • Reply
  

  subalakshmi....

  Member

  597 Points

  130 Posts

  Re: get records in today

  Apr 18, 2012 09:37 AM|LINK

  Hi,

  It seems, the tables does not contains today data. Have you check whether it contains today data for Access='Engineer' ??

   

  Subalakshmi
  Cognizant
• • Reply
  

  devender_dev...

  Member

  12 Points

  7 Posts

  Re: get records in today

  Apr 18, 2012 12:26 PM|LINK

  i cant understand ur problems .please exaplain full in details...

• • Reply
  

  limno

  All-Star

  117336 Points

  8003 Posts

  Moderator

  MVP

  Re: get records in today

  Apr 18, 2012 01:09 PM|LINK

  SELECT e.name, COUNT(*) AS CompleteWork
  FROM   T_Engineer e INNER JOIN T_ReportWork r ON r.Engineer = e.id
  WHERE  e.Access = 'Engineer'
  AND (r.DateInsert >=DATEADD(day, DATEDIFF(day,0,GETDATE()),0)
  AND r.DateInsert < DATEADD(day, DATEDIFF(day,0,GETDATE())+1,0)
  )
  GROUP BY e.name

  Limno
  
  Format your SQL query with instant sql formatter:
  http://www.dpriver.com/pp/sqlformat.htm
• • Reply
  

  sandeepmitta...

  Contributor

  6789 Points

  1058 Posts

  Re: get records in today

  Apr 18, 2012 01:16 PM|LINK

  SELECT e.name, COUNT(1) as CompleteWork
  FROM T_Engineer e
  INNER JOIN T_ReportWork r ON r.Engineer=e.id
  WHERE e.Access='Engineer'
  AND CAST(CONVERT(VARCHAR, r.DateInsert, 101) AS DATETIME) = CAST(CONVERT(VARCHAR, GETDATE(), 101) AS DATETIME)
  GROUP BY e.name

  if using sql server 2008 onwards, then you can replace the condition

  AND CAST(CONVERT(VARCHAR, r.DateInsert, 101) AS DATETIME) = CAST(CONVERT(VARCHAR, GETDATE(), 101) AS DATETIME)

  with

  AND CAST(r.DateInsert AS DATEADD) = CAST(GETDATE() AS DATEADD)

  
  Sandeep Mittal | My Blog - IT Developer Zone
  
  
  • Edited by sandeepmittal11 on Apr 18, 2012 01:16 PM
• • Reply
  

  sivaganesh12...

  Member

  227 Points

  308 Posts

  Re: get records in today

  Apr 19, 2012 11:10 AM|LINK

  try this

  SELECT e.name, COUNT(1) as CompleteWork
  FROM T_Engineer e
  INNER JOIN T_ReportWork r ON r.Engineer=e.id
  WHERE e.Access='Engineer'
  AND datediff(dd,r.DateInsert,GETDATE()) = 0 GROUP BY e.name

  • Edited by sivaganesh1234 on Apr 19, 2012 11:12 AM
• • Reply
  

  Chen Yu - MS...

  All-Star

  21584 Points

  2493 Posts

  Microsoft

  Re: get records in today

  Apr 23, 2012 09:10 AM|LINK

  nazlin

  hi dear,

  I want get many count from some of table for today

  my script is :

  select
   e.name,
  (select  COUNT(*) from  T_ReportWork  r where r.Engineer=e.id AND YEAR(r.DateInsert) = YEAR(GETDATE()) AND MONTH(R.dateInsert)=MONTH(GETDATE()) AND DAY(R.dateInsert)=DAY(GETDATE()) )as CompleteWork ,
  
   from T_Engineer e where e.Access='Engineer'

  but i dont have any result,complete work is zero

  are you have any siggesstion for me?

  Hi,

  Did the T_Engineer table has data when e.Access = 'Engineer'? Please check your table data with below query to make sure that table has required data.

  select * from T_Engineer e where e.Access='Engineer'
  
  --check your T_Engineer table data.
  
  
  select  COUNT(*) from  T_ReportWork  r
  where r.Engineer=e.id AND
  YEAR(r.DateInsert) = YEAR(GETDATE()) AND
  MONTH(R.dateInsert)=MONTH(GETDATE()) AND
  DAY(R.dateInsert)=DAY(GETDATE())
  
  ---check your CompleteWork column

  Thanks.

  Please mark the replies as answers if they help or unmark if not.
  Feedback to us
  
  Develop and promote your apps in Windows Store
  
  
  • Marked as answer by Chen Yu - MSFT on Apr 27, 2012 10:03 AM