Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL
date            userid
2012-12-30          21
2012-12-30          21
2012-12-31          21
2012-12-30          22
I would like the result to look like
userid          totaldays
21                 2
22                 1

There are two same date because on same date they will have two entry ie AM and PM
 
I'm trying like this:
SELECT
CONVERT(date,TOF.compoffdate,101),
count(compoffdate) as TotalDays
FROM tblcompoff TOF
WHERE TOF.userid=21
group by tof.compoffdate
How to proceed?
Posted 4-Jan-13 1:47am
Edited 4-Jan-13 2:46am
v3
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 5

First you need to make sure you have a dataset on which you want to count the rows.
In your case you only want to count the unique days for an userid, so the first SQL query to achieve this would be
SELECT DISTINCT CONVERT( date, compoffdate, 101 ) AS date, userid
FROM tblcompoff
This query results in the following rows:
date            userid
2012-12-30          21
2012-12-31          21
2012-12-30          22
Now based on this data set we can count the number off days for each user id by using the following query:
SELECT TOF.userid, COUNT( TOF.date ) AS totaldays
FROM (SELECT DISTINCT CONVERT( date, compoffdate, 101 ) AS date, userid
      FROM tblcompoff)
GROUP BY TOF.userid
The query performs the count over the first query we made to limit/specify our data.
The result is:
userid          totaldays
21                 2
22                 1
I hope this make the process off how to achieve your result clear.
  Permalink  
v3
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

select count(compoffdate),userid from tblcompoff  group by userid
 
Simple one i think its useful
  Permalink  
Comments
Arun1990 at 4-Jan-13 8:22am
   
i thing you not see the result of your query,
is,
userid totaldays
21 3
22 1
 
so your solution is wrong
kankeyan at 4-Jan-13 8:31am
   
I worked on this query it will work fine.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

Use this
 
SELECT userid , COUNT(DISTINCT DATEADD(dd, 0, DATEDIFF(dd, 0, compoffdate))) AS totaldays FROM tblcompoff
GROUP BY userid
  Permalink  
v2
Comments
Tharaka MTR at 4-Jan-13 10:27am
   
Just check this and see. this works definitely
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

select cap.userID, Count(cap.Date) as date
from
(
    SELECT ROW_NUMBER() over (partition by convert(varchar, table.Date, 108), useriD Order by userID, convert(varchar, table.Date, 108)) as rownum, userID, convert(varchar, table.Date, 108) from table 
) as cap
where cap.RowNum=1
group by cap.userID
  Permalink  
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 6

Hi
 
You can use from bellow query(it's short way):
 
Select	UserID, Count(*)
From	(Select	UserID, Date
		From	[YourTableName]
		Group By	UserID, Date) As Temp
	Group by UserID
 
I hope it's helpful.
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web02 | 2.8.141015.1 | Last Updated 4 Jan 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100