Getting 1 record even a day includes 2 different record

Question

There is a table which shows employee's daily program.

SELECT COUNT(*) AS TotalDay FROM [User]
    INNER JOIN [x] ON [x].UserID = [User].ID  
WHERE       
        StartTime BETWEEN '20120611' AND '20120618' AND UserID = 20
GROUP BY [User].ID, [User].Name
ORDER BY Name

it return 7 records. because in one day, one user ( UserID) can go two different places.

For example, This user went A place from 20120611 08:30:00 to 20120611 13:30:00 and went B place from 20120611 14:00:00 to 20120611 19:00:00

and this return 2 records when I use below query.

SELECT COUNT(*) AS TotalDay FROM [User]
     INNER JOIN [x] ON [x].UserID = [User].ID  
WHERE       
        StartTime = '20120611' AND UserID = 20
GROUP BY [User].ID, [User].Name
ORDER BY Name

But I want to get one record because that operations were in one day.

So how can I get it?

I use MSSQL. StartTime is datetime in sql.

As per your query they are counting total operation .. if you are looking for distinct day count then you can use `count(distinct starttime)` in place of count(*) in your second query.. please correct me if i am getting you wrong.. — pratik garg, Jul 17 '12 at 06:23
Are you keeping date and time portions in two columns? I'm asking because your second query would return 0 if time is part of StartTime. Could you please clarify the schema a bit? — Nikola Markovinović, Jul 17 '12 at 07:41

score 0 · Answer 1 · answered Jul 17 '12 at 06:32

0

you need to group by datepart?

SELECT COUNT(*) AS TotalDay FROM [User]
 INNER JOIN [x] ON [x].UserID = [User].ID  
WHERE       
    StartTime = '20120611' AND UserID = 20
GROUP BY [User].ID, [User].Name, cast(floor(cast(starttime as float)) as datetime)
ORDER BY Name

answered Jul 17 '12 at 06:32

shome.zd

1

Casting will ignore all index applied and may make the query slow. – danish Jul 17 '12 at 06:33
`CAST` to `FLOAT` is not the fastest way to do it, with SQL-Server 2008 the best way to remove the time part of the query is `CAST(StartTime AS DATE)`. Datetime is stored as 2 unsigned integers, not as a float as many believe, so casting to date avoids casting between types. The `DATEADD/DATEDIFF` method @danish has used has the same benefits. – GarethD Jul 17 '12 at 07:16

score 0 · Accepted Answer · answered Jul 17 '12 at 06:35

Try following in your where clause:

DATEADD(DD, DATEDIFF(DD, 0, STARTTIME), 0)  BETWEEN DATEADD(DD, DATEDIFF(DD, 0, <DATE_TIME_PARAMETER>), 0) AND DATEADD(DD, DATEDIFF(DD, 0, <DATE_TIME_PARAMETER>), 0)

You will also need to group the results by above mentioned date part.

Getting 1 record even a day includes 2 different record

2 Answers2