get records in today

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?

Re: get records in today

nazlin
dont have any result

maybe because does not have any result in the database ?

Re: get records in today

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

Re: get records in today

Hi,

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

Re: get records in today

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

Re: get records in today

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

Re: get records in today

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)

Re: get records in today

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

Re: get records in today

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.