Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sqlnoob35
Starting Member
10 Posts |
 Posted - 2011-01-13 : 11:55:08
|
| I am trying to create a query that will show me employees who had service between 2010-07-01 and 2010-09-01. The twist is I only need employees who started work between these days.If I just use a simple BETWEEN command, it will show all employees since everyone had service in this range.I know MAX and MIN functions are the key here, but I can't remember the syntax. I want the query to bemin(date) >= '2010-07-01' and max(date) <= '2010-09'01.I wrote this query:select distinct mbr_ssn_nbr from historygroup by mbr_ssn_nbrhaving min(MBR_HIST_SVC_CR_DT) >= '2010-07-01'and exists (select distinct mbr_ssn_nbr from historygroup by mbr_ssn_nbrhaving max(MBR_HIST_SVC_CR_DT) <= '2010-09-01')Unfortunately, it is showing people with credit dates occuring after 09-01-2010 so the logic fails for some reason. Can anyone help?? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-01-13 : 12:04:30
|
| is table having single record per employee?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
sqlnoob35
Starting Member
10 Posts |
Posted - 2011-01-13 : 12:20:30
|
| No, there are multiple records for each employee |
|
|
|
JimL
SQL Slinging Yak Ranger
1537 Posts |
Posted - 2011-01-13 : 12:26:46
|
| Select Min(activitydate) as Startdate, EmployeeIDinto #SDfrom yourtableGroup by EmployeeIDSelect EmployeeIDfrom dbo.#SDwhere Startdate between YourStartDate and YourEndDateJimUsers <> Logic |
|
|
|
ahmeds08
Aged Yak Warrior
737 Posts |
Posted - 2011-01-14 : 00:45:16
|
| do you have any column like 'date of joining' |
|
|
|
|
|
|
Need Help with Date Range in Query