Solved

sql server 2008 query

Posted on 2013-06-04
1
292 Views
Last Modified: 2013-06-04
I'm using sql server 2008 and SSMS.

I have a table called TestEmployees that looks like this:
TestEmployees table
Here is the script to create this table:

CREATE TABLE [dbo].[TestEmployees](
	[EmployeeID] [int] NOT NULL,
	[PositionID] [varchar](10) NULL,
	[PositionDescription] [varchar](50) NULL,
	[TimeStatus] [decimal](10, 6) NULL,
	[LastName] [varchar](50) NULL,
	[FirstName] [varchar](50) NULL,
	[HireDate] [smalldatetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[TestEmployees] ([EmployeeID], [PositionID], [PositionDescription], [TimeStatus], [LastName], [FirstName], [HireDate]) VALUES (1, N'D1001', N'Pizza delivery driver', CAST(0.500000 AS Decimal(10, 6)), N'Simpson', N'Bart', CAST(0x9E050000 AS SmallDateTime))
INSERT [dbo].[TestEmployees] ([EmployeeID], [PositionID], [PositionDescription], [TimeStatus], [LastName], [FirstName], [HireDate]) VALUES (1, N'D1005', N'Waiter', CAST(0.500000 AS Decimal(10, 6)), N'Simpson', N'Bart', CAST(0x9F940000 AS SmallDateTime))
INSERT [dbo].[TestEmployees] ([EmployeeID], [PositionID], [PositionDescription], [TimeStatus], [LastName], [FirstName], [HireDate]) VALUES (2, N'D1007', N'Waitress', CAST(1.000000 AS Decimal(10, 6)), N'Shue', N'Elizabeth', CAST(0x9A550000 AS SmallDateTime))
INSERT [dbo].[TestEmployees] ([EmployeeID], [PositionID], [PositionDescription], [TimeStatus], [LastName], [FirstName], [HireDate]) VALUES (3, N'D1002', N'Pizza delivery driver', CAST(0.600000 AS Decimal(10, 6)), N'Depp', N'Johnny', CAST(0x96AA0000 AS SmallDateTime))
INSERT [dbo].[TestEmployees] ([EmployeeID], [PositionID], [PositionDescription], [TimeStatus], [LastName], [FirstName], [HireDate]) VALUES (3, N'D1006', N'Waiter', CAST(0.400000 AS Decimal(10, 6)), N'Depp', N'Johnny', CAST(0x983C0000 AS SmallDateTime))
INSERT [dbo].[TestEmployees] ([EmployeeID], [PositionID], [PositionDescription], [TimeStatus], [LastName], [FirstName], [HireDate]) VALUES (4, N'D1008', N'Waitress', CAST(1.000000 AS Decimal(10, 6)), N'Swift', N'Taylor', CAST(0xA1D30000 AS SmallDateTime))

Open in new window


If you look at the table Bart was first hired as a delivery driver and then later he became a waiter. The TimeStatus column shows .5 for each position meaning he worked half of his time as a driver and the other as a waiter.


How would I write a query to return the following.

I have 2 conditions to consider in my query:

Condition 1

if you notice Johnny depp has one position with TimeStatus = 0.6 and another with TimeStatus = 0.4
When 2 positions for an employee have an unequal value in the TimeStatus column
then I want to only show the position with the bigger value.
So in Johnny's case, I only want to show the position with TimeStatus = 0.6 which would be delivery driver.

Condition 2

if you notice Bart Simpson has one position with TimeStatus = 0.5 and another with TimeStatus = 0.5
When the TimeStatus on 2 positions for a person is equal then, return the position with the older Hire Date.
So in Bart's case that would be the delivery driver since the hire date is older.


So then my query should return this:

desired result
Can anyone help?
0
Comment
Question by:maqskywalker
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 500 total points
ID: 39220055
Why do I feel like I'm doing someone's homework?

WITH cteOrderedEmployee (RowNumber, EmployeeID, PositionID, PositionDescription, TimeStatus,
      LastName, FirstName, HireDate)
AS
      (
            SELECT ROW_NUMBER() OVER(ORDER BY EmployeeID, TimeStatus DESC, HireDate) AS RowNumber,
            EmployeeID, PositionID, PositionDescription, TimeStatus, LastName, FirstName, HireDate
            FROM TestEmployees
      )
SELECT EmployeeID, PositionID, PositionDescription, TimeStatus, LastName, FirstName, HireDate
FROM cteOrderedEmployee
WHERE RowNumber IN (SELECT MIN(RowNumber) FROM cteOrderedEmployee AS cte2 GROUP BY EmployeeID)
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question