August 4, 2014 at 2:39 pm
I am trying to write a query that will return X amount of records per Company using a top down priority approach. This is more complex than I thought and the various tries kept giving me errors. I am hoping someone can help me with this as this will save our staff hours of manual work picking and choosing.
For example: I would like 2 records per Company with top down priority on JobLevel (Priority below). So using the partial table below, select 2 records from "123 Inc", they have 2 Managers and 3 Staff contacts. I would like return results to choose the (2) managers and not any of the staff records and FirstName and LastName not duplicate. Then for "A Small Co.", they have 1 VP, 1 Director, 1 Manager and 3 Staff contacts. I would like the return results to choose the 1 VP and 1 Director and none of the managers or staff level people. And so on and so forth for the remainder of the companies.
Partial Table:
CoIDCompanyFirstNameLastNameJobLevel
A1123 IncRobertOrtizManager
A1123 IncRobertRathbunManager
A1123 IncRosaFisherManager
A1123 IncSanthoshMichaelsonStaff
A1123 IncSharyClarkStaff
A1123 IncSteveWhyersStaff
A1123 IncSteveFranklinManager
A1123 IncSteveGibsonStaff
A1123 IncSusanneMcKennaManager
B1A Small Co.MikeIrwinDirector
B1A Small Co.MikeLeechStaff
B1A Small Co.NicoleKowalczykStaff
B1A Small Co.PaulBelcherStaff
B1A Small Co.PurabChapinManager
B1A Small Co.RamonHalpernVP
C1AAA Inc.MartineWahlDirector
C1AAA Inc.MaureenHolmesDirector
D2ABC CompanyTaraViitasaloManager
D2ABC CompanyTinaLyndsManager
D2ABC CompanyToddJohnsonDirector
D2ABC CompanyToddAdolfsonStaff
CampServ.dbo.SampleFakeCustomerData
Columns
Company (nvarchar(255), null)
Address (nvarchar(255), null)
City (nvarchar(255), null)
State (nvarchar(255), null)
Zip (nvarchar(255), null)
Country (nvarchar(255), null)
FirstName (nvarchar(255), null)
LastName (nvarchar(255), null)
JobLevel (nvarchar(255), null)
JobLevel Priority:
1. VP
2. Director
3. Manager
4. Staff
Sample table attached.
August 4, 2014 at 4:15 pm
Hi Renee,
Welcome to the forums. You're quite new, so I'm explaining some best practices when posting. You explained your problem almost clearly enough but I'm not sure if your priorities are in a table or just defined as business rules. Second, you should post your sample data with the DDL (Create table) and Insert statements. I did this for you this time but you're expected to do it the next time. We can't work with Excel files, so they're almost useless as sample data.
For what is worth, you should really have adequate data types for your columns. Nvarchar uses twice the space, so it should be used only when using unicode values, for the rest, use varchar.
This is an example on how to do it, feel free to ask any questions that you have and don't forget to read the article linked in my signature.
CREATE TABLE SampleData (
CoID nvarchar(255),
Company nvarchar(255),
FirstName nvarchar(255),
LastName nvarchar(255),
JobLevel nvarchar(255))
INSERT INTO SampleData
VALUES
('A1', '123 Inc', 'Robert', 'Ortiz', 'Manager'),
('A1', '123 Inc', 'Robert', 'Rathbun', 'Manager'),
('A1', '123 Inc', 'Rosa', 'Fisher', 'Manager'),
('A1', '123 Inc', 'Santhosh', 'Michaelson', 'Staff'),
('A1', '123 Inc', 'Shary', 'Clark', 'Staff'),
('A1', '123 Inc', 'Steve', 'Whyers', 'Staff'),
('A1', '123 Inc', 'Steve', 'Franklin', 'Manager'),
('A1', '123 Inc', 'Steve', 'Gibson', 'Staff'),
('A1', '123 Inc', 'Susanne', 'McKenna', 'Manager'),
('B1', 'A Small Co.', 'Mike', 'Irwin', 'Director'),
('B1', 'A Small Co.', 'Mike', 'Leech', 'Staff'),
('B1', 'A Small Co.', 'Nicole', 'Kowalczyk', 'Staff'),
('B1', 'A Small Co.', 'Paul', 'Belcher', 'Staff'),
('B1', 'A Small Co.', 'Purab', 'Chapin', 'Manager'),
('B1', 'A Small Co.', 'Ramon', 'Halpern', 'VP'),
('C1', 'AAA Inc.', 'Martine', 'Wahl', 'Director'),
('C1', 'AAA Inc.', 'Maureen', 'Holmes', 'Director'),
('D2', 'ABC Company', 'Tara', 'Viitasalo', 'Manager'),
('D2', 'ABC Company', 'Tina', 'Lynds', 'Manager'),
('D2', 'ABC Company', 'Todd', 'Johnson', 'Director'),
('D2', 'ABC Company', 'Todd', 'Adolfson', 'Staff');
WITH Priorities(Priority, JobLevel) AS(
SELECT 1, 'VP' UNION ALL
SELECT 2, 'Director' UNION ALL
SELECT 3, 'Manager' UNION ALL
SELECT 4, 'Staff'
),
RowNums AS(
SELECT s.*,
ROW_NUMBER() OVER( PARTITION BY s.CoID ORDER BY p.Priority) rownum
FROM SampleData s
JOIN Priorities p ON s.JobLevel = p.JobLevel
)
SELECT *
FROM RowNums
WHERE rownum <= 2
August 4, 2014 at 4:22 pm
Thanks so much!! I will make sure I post correctly next time. This is exactly what I needed. You saved us a BUNCH of manual work!!!
Much Appreciated. Renee
August 5, 2014 at 8:22 am
I was trying to make some changes to the query but I keep getting errors. Can someone help me again? How do I get the same results but rather than creating a table having the query point to the database tables
Such as:
Select *
From CampServ.dbo.SampleFakeCustomerData
//This is where I am getting stuck - How do I add priorities?
WITH Priorities(Priority, JobLevel) AS(
SELECT 1, 'VP' UNION ALL
SELECT 2, 'Director' UNION ALL
SELECT 3, 'Manager' UNION ALL
SELECT 4, 'Staff'
),
RowNums AS(
SELECT s.*,
ROW_NUMBER() OVER( PARTITION BY s.CoID ORDER BY p.Priority) rownum
FROM CampServ.dbo.SampleFakeCustomerData s
JOIN Priorities p ON s.JobLevel = p.JobLevel
)
SELECT *
FROM RowNums
WHERE rownum <= 2
CampServ.dbo.SampleFakeCustomerData Table Columns
Company (nvarchar(255), null)
Address (nvarchar(255), null)
City (nvarchar(255), null)
State (nvarchar(255), null)
Zip (nvarchar(255), null)
Country (nvarchar(255), null)
FirstName (nvarchar(255), null)
LastName (nvarchar(255), null)
JobLevel (nvarchar(255), null)
Thanks so much. I am currently taking a SQL class but we are not at this point yet so this is beyond my experience and knowledge.
August 5, 2014 at 1:15 pm
put a ";" in front of the WITH. WITH is very finnicky about having a separation between it and any previous SQL statements
As in:
Select *
From CampServ.dbo.SampleFakeCustomerData
//This is where I am getting stuck - How do I add priorities?
;WITH Priorities(Priority, JobLevel) AS(--<<look here
SELECT 1, 'VP' UNION ALL
SELECT 2, 'Director' UNION ALL
SELECT 3, 'Manager' UNION ALL
SELECT 4, 'Staff'
),
RowNums AS(
SELECT s.*,
ROW_NUMBER() OVER( PARTITION BY s.CoID ORDER BY p.Priority) rownum
FROM CampServ.dbo.SampleFakeCustomerData s
JOIN Priorities p ON s.JobLevel = p.JobLevel
)
SELECT *
FROM RowNums
WHERE rownum <= 2
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 5, 2014 at 1:32 pm
Matt Miller (#4) (8/5/2014)
put a ";" in front of the WITH. WITH is very finnicky about having a separation between it and any previous SQL statementsAs in:
Select *
From CampServ.dbo.SampleFakeCustomerData
//This is where I am getting stuck - How do I add priorities?
;WITH Priorities(Priority, JobLevel) AS(--<<look here
SELECT 1, 'VP' UNION ALL
SELECT 2, 'Director' UNION ALL
SELECT 3, 'Manager' UNION ALL
SELECT 4, 'Staff'
),
RowNums AS(
SELECT s.*,
ROW_NUMBER() OVER( PARTITION BY s.CoID ORDER BY p.Priority) rownum
FROM CampServ.dbo.SampleFakeCustomerData s
JOIN Priorities p ON s.JobLevel = p.JobLevel
)
SELECT *
FROM RowNums
WHERE rownum <= 2
Actually the semicolon belongs at the end of the statement preceding the WITH starting the CTE. Semicolons are statement terminators not statement begininators.
This is what it should look like:
Select *
From CampServ.dbo.SampleFakeCustomerData;
//This is where I am getting stuck - How do I add priorities?
WITH Priorities(Priority, JobLevel) AS(--<<look here
SELECT 1, 'VP' UNION ALL
SELECT 2, 'Director' UNION ALL
SELECT 3, 'Manager' UNION ALL
SELECT 4, 'Staff'
),
RowNums AS(
SELECT s.*,
ROW_NUMBER() OVER( PARTITION BY s.CoID ORDER BY p.Priority) rownum
FROM CampServ.dbo.SampleFakeCustomerData s
JOIN Priorities p ON s.JobLevel = p.JobLevel
)
SELECT *
FROM RowNums
WHERE rownum <= 2
August 5, 2014 at 1:38 pm
Okay, I get it. I tried it and it works like a charm! Thanks everyone.. My team and I THANK YOU...
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply