?
Solved

sql server 2008 query

Posted on 2013-06-04
1
Medium Priority
?
295 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 2000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

800 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