query help

Question

Post reply

query help

RockingAdmin

SSCrazy

Points: 2828
More actions
September 22, 2011 at 8:23 am

#243617

Hi All
i want the count of employees which are having records greater than or equal to 2 on different dates in createddate column
and job as db.
emp table:
empid createddate job
1 2011-01-01 db
1 2011-01-01 db
1 2011-02-01 db
2 2011-01-01 db
3 2011-01-01 db
select count(distinct empid) from emp
where job='db'
having count(empid)>=2
I am able to get records but not on different dates
can one help me on this

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323482 More actions · Answer 1

does this work for you?

based ont he sample data you posted, this works:

;With emp (empid,createddate,job)

AS

(

SELECT '1','2011-01-01','db' UNION ALL

SELECT '1','2011-02-01','db' UNION ALL

SELECT '2','2011-01-01','db' UNION ALL

SELECT '3','2011-01-01','db')

select empid

from emp

where job='db'

group by empid

having count(createddate)>=2

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

RockingAdmin SSCrazy Points: 2828 More actions · Answer 2

Hi Lowell,

Thanks for the support.

sorry for not informing two things earlier, one is the version i am using is sql2000 and second one is the datetime datatype which i am using for the column createddate.

empid createddate job

1 2011-01-01 01:32:54 db

1 2011-01-01 02:32:54 db

1 2011-02-01 13:36:43 db

2 2011-01-01 18:52:02 db

3 2011-01-01 19:00:00 db

my query is like, i want the total employee count who are having records more than or equal to 2 on different dates with in his job i.e.db . CTE will not work on sql 2000.

any sugessitions.

Jared SSC Guru Points: 61793 More actions · Answer 3

You say more than 2 on different dates, but do you mean within the same day? As I understand it, you want to group on your date column, but cannot because of the timestamp. In which case you can group on convert(varchar(10),createddate, 101)

Thanks,

Jared

Jared
CE - Microsoft

Lowell SSC Guru Points: 323482 More actions · Answer 4

a CTE is just a subquery to get sample data out so people can see and test it themselves(since you did not provide the CREATE TABLE...INSERT INTO we need)

it's easy to move it into SQL2000 syntax; but you posted in a 2008 forum, so i assumed that was fair game to use 2008 features.

going where Jared was stating, and grouping by the date, this would work; i would leave the dates as dates, isntead of converting to varchar, but either way it would work.

select empid

from (

SELECT '1' AS empid,'2011-01-01' AS createddate,'db' AS job UNION ALL

SELECT '1','2011-01-01','db' UNION ALL

SELECT '1','2011-02-01','db' UNION ALL

SELECT '2','2011-01-01','db' UNION ALL

SELECT '3','2011-01-01','db'

)emp

where job='db'

group by empid,DATEADD(dd, DATEDIFF(dd,0,createddate), 0)

having count(createddate)>=2

and the query you'd use agaisnt your real data, assuming the table and column names are real:

select empid

from emp

where job='db'

group by empid,DATEADD(dd, DATEDIFF(dd,0,createddate), 0)

having count(createddate)>=2

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!