Solved

sql server 2008 query

Posted on 2013-06-04
1
293 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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.
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.

691 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