Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

sql server 2008 query

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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

596 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