Changing query to point to DB table but has errors

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Changing query to point to DB table but has errors

Author

Topic

renee705
Starting Member

3 Posts

Posted - 2014-08-05 : 11:39:37

CREATE TABLE SampleData (
CoID nvarchar(255),
Company varchar(255),
FirstName varchar(255),
LastName varchar(255),
JobLevel varchar(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

I am trying to make changes to the above query. This query will select 2 records per Company with top down priority on JobLevel. For example: This query will select 2 records from "123 Inc", where they have 2 Managers and 3 Staff contacts. The return results selects (2) managers and no staff records and FirstName and LastName are not duplicate. Then for "A Small Co.", they have 1 VP, 1 Director, 1 Manager and 3 Staff contacts. the return results chooses 1 VP and 1 Director and no managers or staff level people. And so on and so forth for the remainder of the companies.
What I was trying to accomplish is having this same query point to the database tables instead creating a table each and every time. I have this data residing in our database. I tried to make the change so it would point to the database tables but it kept giving me errors. This is way more complex than I thought. Any help would be greatly appreciated as it will save our team hours of manual work.

My failed attempt:

Select *
From CampServ.dbo.SampleFakeCustomerData
//This is where I am getting stuck
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
COID (nvarchar(255), null)
Company (varchar(255), null)
FirstName (varchar(255), null)
LastName (varchar(255), null)
JobLevel (varchar(255), null)

RS

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-08-05 : 14:15:18

Maybe something like:

select *
  from (select s.*
              ,p.priority
              ,row_number() over (partition by s.coid order by p.priority) as rn
          from campserv.dbo.samplefakecustomerdata as c
               inner join (          select 1 as priority,'VP' as joblevel
                           union all select 2            ,'Director'
                           union all select 3            ,'Manager'
                           union all select 4            ,'Staff'
                          ) as p
                       on p.joblevel=c.joblevel
       ) as a
 where a.rn<=2

renee705
Starting Member

3 Posts

Posted - 2014-08-05 : 14:51:54

Just made a few changes and Voila! It worked perfectly! Thank you for your help!! Much appreciated..

select *
from (select c.*
,p.priority
,row_number() over (partition by c.coid order by p.priority) as rn
from campserv.dbo.samplefakecustomerdata as c
inner join ( select 1 as priority,'VP' as joblevel
union all select 2 ,'Director'
union all select 3 ,'Manager'
union all select 4 ,'Staff'
) as p
on p.joblevel=c.joblevel
) as a
where a.rn<=2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources