Solved

sql server 2008 query

Posted on 2013-06-04
1
288 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
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 Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
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.

759 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now