August 12, 2013 at 8:25 am
Hello,
I need to convert a query in MS Access to a view in SQL Server 2005. The view will draw from two tables: Employee and Spaces. I want the view to be grouped by the space_number. Also, if there are two employees for one space_number, I need the two employees' names to be listed in the same row of the view.
Here is the Access code of the query, which uses the FIRST function to do what I need:
SELECT Spaces.Space_Number, First(Employee.First) AS First1, First(Employee.Last) AS Last1, Last(Employee.First) AS First2, Last(Employee.Last) AS Last2, First(Employee.CC) AS FirstOfCC, First(Employee.[Employee Type]) AS [FirstOfEmployee Type], Spaces.[Space Type]
FROM Spaces LEFT JOIN Employee ON Spaces.Space_ID = Employee.Space_Number
GROUP BY Spaces.Space_Number, Spaces.[Space Type];
Below is the SQL Code that I have so far... :
SELECT TOP (100) PERCENT dbo.Spaces.Space_Number, dbo.Spaces.Space_ID, dbo.Employee.First AS First1, dbo.Employee.Last AS Last1, dbo.Employee.First AS First2,
dbo.Employee.Last AS Last2, dbo.Employee.CC, dbo.Employee.[Employee Type], dbo.Spaces.[Space Type]
FROM dbo.Employee RIGHT OUTER JOIN
dbo.Spaces ON dbo.Employee.Space_Number = dbo.Spaces.Space_ID
ORDER BY dbo.Spaces.Space_Number
August 12, 2013 at 8:57 am
jamesheslin123 (8/12/2013)
Hello,I need to convert a query in MS Access to a view in SQL Server 2005. The view will draw from two tables: Employee and Spaces. I want the view to be grouped by the space_number. Also, if there are two employees for one space_number, I need the two employees' names to be listed in the same row of the view.
Here is the Access code of the query, which uses the FIRST function to do what I need:
SELECT Spaces.Space_Number, First(Employee.First) AS First1, First(Employee.Last) AS Last1, Last(Employee.First) AS First2, Last(Employee.Last) AS Last2, First(Employee.CC) AS FirstOfCC, First(Employee.[Employee Type]) AS [FirstOfEmployee Type], Spaces.[Space Type]
FROM Spaces LEFT JOIN Employee ON Spaces.Space_ID = Employee.Space_Number
GROUP BY Spaces.Space_Number, Spaces.[Space Type];
Below is the SQL Code that I have so far... :
SELECT TOP (100) PERCENT dbo.Spaces.Space_Number, dbo.Spaces.Space_ID, dbo.Employee.First AS First1, dbo.Employee.Last AS Last1, dbo.Employee.First AS First2,
dbo.Employee.Last AS Last2, dbo.Employee.CC, dbo.Employee.[Employee Type], dbo.Spaces.[Space Type]
FROM dbo.Employee RIGHT OUTER JOIN
dbo.Spaces ON dbo.Employee.Space_Number = dbo.Spaces.Space_ID
ORDER BY dbo.Spaces.Space_Number
You have a couple things going on here. The first challenge is the FIRST and LAST functions from Access. These functions make no sense in relation theory because tables by their nature have no concept of order. Access however decided to do this which is incredibly confusing for people and causes lots of issues. To get the "first" in sql server you must specify which one is first via an order by clause. The same concept holds true for LAST. This in itself wouldn't be so bad but you added one more little challenge to the puzzle.
Also, if there are two employees for one space_number, I need the two employees' names to be listed in the same row of the view.
What does that mean? Do you want all employee's listed in a comma seperated list in one column or do you want a dynamic number of columns based on the values found? Neither of these is trivial but are both certainly feasible.
In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 12, 2013 at 11:35 am
Okay, I am very new to SQL and I apologize in advance for incorrect etiquette or seemingly silly mistakes :unsure:. Here is what I have according to the link:
For the Employee Table:
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..dbo.Employee','U') IS NOT NULL
DROP TABLE dbo.Employee
--===== Create the test table with
CREATE TABLE dbo.Employee
(
Employee_ID INT, PRIMARY KEY, ---==This is the Primary Key in the dbo.Employee Table
CC FLOAT,
First NVARCHAR(50)
Last NVARCHAR(50),
Space_Number INT
[Employee Type] NVARCHAR(50)
)
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT dbo.Employee ON
--===== Insert the test data into the test table
INSERT INTO dbo.Employee
(Employee_ID, CC, First, Last, Space_Number, [Empoyee Type])
SELECT '20','9700','Joseph','McSmith','457''(F.T)' UNION ALL
SELECT '21','4800','Pat','Dunlovey','1456''(F.T)' UNION ALL
SELECT '24','9150','Scott','Chatting','638''(F.T)' UNION ALL
SELECT '25','9150','Brian','Hutchin','1270''(F.T)' UNION ALL
SELECT '779','9150','Peter','Galant','1270''(F.T)' UNION ALL
SELECT '26','8400','Bob','Pryt','629''(F.T)' UNION ALL
SELECT '27','7570','Karen','Home','620''(F.T)' UNION ALL
SELECT '28','7400','Helen','Mill','609''(F.T)' UNION ALL
SELECT '30','7400','Kimberly','Parth','608''(F.T)'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT dbo.Employee OFF
For the Spaces Table:
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..dbo.Spaces','U') IS NOT NULL
DROP TABLE dbo.Spaces
--===== Create the test table with
CREATE TABLE dbo.Spaces
(
Space_ID INT, PRIMARY KEY, ---==This is the Primary Key in the dbo.Spaces Table
[Building #] NVARCHAR(50),
Floor NVARCHAR(50)
Space_Number NVARCHAR(255),
[Space Type] NVARCHAR(50)
Area INT
)
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT dbo.Spaces ON
--===== Insert the test data into the test table
INSERT INTO dbo.Employee
(Space_ID, [Building #], Floor, Space_Number, [Space Type], Area)
SELECT '457','84','4S','84R4141','Office','194' UNION ALL
SELECT '608','84','2S','84R2121','Office','130' UNION ALL
SELECT '609','84','2S','84R2120','Office','126' UNION ALL
SELECT '620','84','1N','84R1576','Storage','1002' UNION ALL
SELECT '629','84','1N','84R1558','Office','109' UNION ALL
SELECT '638','84','1N','84R1544','Workspace','69' UNION ALL
SELECT '1270','158','1','158R1533','Office','139' UNION ALL
SELECT '1456','158','3','158R3111','Office','208'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT dbo.Spaces OFF
What I need to do is, for each Space_Number, have a unique row in the view. For example, above we have Brian Hutchin and Peter Galant sharing a Space_Number (Note, this is equivalent to the Space_ID in the dbo.Spaces table). The view I have now shows two separate rows for these two employees. I want to have their names displayed next to eachoter in the same row with the same space_number (space_id).
The necessary columns would then, in this new view, be: Space_ID, Space_Number, First1, Last1, First2, Last2, FirstofCC, FirstofEmployeeType, Building #, Key. Here, First1/Last1 would be the name of the first employee in the space_number, likewise for the second employee would be First2/Last2. The rest of the data would pertain to the first employee. It is not necessary to have a dynamically changing # of columns. If there is no second employee, I would simply like a null value to be shown.
Hope that this sheds more light on my problem. Please let me know any other way I can help you to allow you to help me. Thank you for your time, it is much appreciated.
August 12, 2013 at 12:48 pm
Before we even get into a query your code has some pretty serious issues. I know you are new and I am not trying to bust your chops but you have some major datatypes mismatches and naming challenges. Let's take a look at the column CC. I am guessing that abbreviation means something? In general it is best practice to name your columns with something that gives an indication of what it is. Then you have a datatype of float but all the values you posted are ints.
Space_Number. The datatype is int in the employee table. I think this is the column used to join to space? However in the space table that name has changed to space_id. Then there is a column in the space table called space_number, but it is not a number because there are characters in the column. You should find a column name and keep the same across all tables. Otherwise you are constantly going to be fighting figuring out what column is what.
Reserved words and spaces. It is generally accepted best practice to avoid using sql server reserved words as column names. Also, using spaces in column is generally not done (although it is allowed).
You might also want to read up on normalization. Things like SpaceType are ok but in this case it is denormalized.
OK back to the task for which you have actually come seeking help and guidance on...
You say you want First1, First 2, Last1, Last2 etc. Not a problem to have NULL when there isn't a second. But what about if there is a third? You just skip that one? How are we going to define first, second, etc...what is the ordering condition?
Thanks for posting the ddl and sample data. There were a lot of issues with what you put together so I am assuming you didn't test it because it wouldn't execute as posted. I cleaned this up so we have something work with.
IF OBJECT_ID('Employee','U') IS NOT NULL
DROP TABLE dbo.Employee
--===== Create the test table with
CREATE TABLE dbo.Employee
(
Employee_ID INT PRIMARY KEY,
CC FLOAT,
FirstName NVARCHAR(50),
LastName NVARCHAR(50),
Space_Number INT,
[Employee Type] NVARCHAR(50)
)
--===== Insert the test data into the test table
INSERT INTO dbo.Employee
(Employee_ID, CC, FirstName, LastName, Space_Number, [Employee Type])
SELECT 20, 9700, 'Joseph', 'McSmith', 457, '(F.T)' UNION ALL
SELECT 21, 4800, 'Pat', 'Dunlovey', 1456, '(F.T)' UNION ALL
SELECT 24, 9150, 'Scott', 'Chatting', 638, '(F.T)' UNION ALL
SELECT 25, 9150, 'Brian', 'Hutchin', 1270, '(F.T)' UNION ALL
SELECT 779, 9150,'Peter', 'Galant', 1270, '(F.T)' UNION ALL
SELECT 26, 8400, 'Bob', 'Pryt', 629, '(F.T)' UNION ALL
SELECT 27, 7570, 'Karen', 'Home', 620, '(F.T)' UNION ALL
SELECT 28, 7400, 'Helen', 'Mill', 609, '(F.T)' UNION ALL
SELECT 30, 7400, 'Kimberly', 'Parth', 608, '(F.T)'
--===== If the test table already exists, drop it
IF OBJECT_ID('Spaces','U') IS NOT NULL
DROP TABLE dbo.Spaces
--===== Create the test table with
CREATE TABLE dbo.Spaces
(
Space_ID INT PRIMARY KEY,
BuildingNum NVARCHAR(50),
FloorNum NVARCHAR(50),
Space_Number NVARCHAR(255),
SpaceType NVARCHAR(50),
Area INT
)
--===== Insert the test data into the test table
INSERT INTO dbo.Spaces
(Space_ID, BuildingNum, FloorNum, Space_Number, SpaceType, Area)
SELECT '457','84','4S','84R4141','Office','194' UNION ALL
SELECT '608','84','2S','84R2121','Office','130' UNION ALL
SELECT '609','84','2S','84R2120','Office','126' UNION ALL
SELECT '620','84','1N','84R1576','Storage','1002' UNION ALL
SELECT '629','84','1N','84R1558','Office','109' UNION ALL
SELECT '638','84','1N','84R1544','Workspace','69' UNION ALL
SELECT '1270','158','1','158R1533','Office','139' UNION ALL
SELECT '1456','158','3','158R3111','Office','208'
Now if I understand the result set you are looking it would start with something like this query?
select *
from employee e
join spaces s on e.space_Number = s.space_id
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 12, 2013 at 1:32 pm
Could it be as simple as this?
SELECT
s.Space_Number,
MIN(e.First) AS First1,
MIN(e.Last) AS Last1,
MAX(e.First) AS First2,
MAX(e.Last) AS Last2,
MIN(e.CC) AS FirstOfCC,
MIN(e.[Employee Type]) AS [FirstOfEmployee Type],
s.[Space Type]
FROM Spaces s
LEFT JOIN Employee e
ON s.Space_ID = e.Space_Number
GROUP BY s.Space_Number, s.[Space Type];
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
August 12, 2013 at 3:33 pm
Before we even get into a query your code has some pretty serious issues. I know you are new and I am not trying to bust your chops but you have some major datatypes mismatches and naming challenges
I realize this, I was not the one who created the database. Also, for other reasons not mentioned, they want the names of the fields to remain unchanged. I apologize for any frustration it may cause you, but that is a problem with management that I would have to tackle on another day.
You say you want First1, First 2, Last1, Last2 etc. Not a problem to have NULL when there isn't a second. But what about if there is a third? You just skip that one? How are we going to define first, second, etc...what is the ordering condition?
If you see ChrisM's post, that is the format of the result I need. I would actually prefer to have a null if there isn't a second employee. A third employee will be ignored completely.
As for defining first and second. If you were to order by the Space_Number or Space_ID (I understand this is confusing, but each space_number also has the same space_id, so ordering by either would work), the first employee to show up would be first, and the second would be second. Or they could be chosen by their employee_id.
Essentially, it does not matter which employee is the first or second, as long as they each appear in the same row (in the same space_number) with the correct first and last names. Neither employee is given precedent over another and their selection as first and second is arbitrary.
ChrisM has the correct idea here:
ChrisM@home (8/12/2013)
Could it be as simple as this?
SELECTs.Space_Number,
MIN(e.First) AS First1,
MIN(e.Last) AS Last1,
MAX(e.First) AS First2,
MAX(e.Last) AS Last2,
MIN(e.CC) AS FirstOfCC,
MIN(e.[Employee Type]) AS [FirstOfEmployee Type],
s.[Space Type]
FROM Spaces s
LEFT JOIN Employee e
ON s.Space_ID = e.Space_Number
GROUP BY s.Space_Number, s.[Space Type];
This is exactly the format that I need my database to be in. However, simply using MIN and MAX could separate the first and last names of different employees. For example, when I ran this code, for Space_Number 158R1533, it went across like: Brian, Galante, Peter, Hutchinson ... when we would want either Brian, Hutchinson, Peter, Galante or Peter, Galante, Brian, Hutchinson.
However you did interpret my problem correctly and I thank you for the effort! I almost thought it was actually that easy until I looked through the results =(
Also, how if you care to explain, how can you just write s.Space_Number or e.First without using the whole "prefix" if you will. Like I said... I am terribly new to SQL.
August 12, 2013 at 9:45 pm
I assumed some of the structures were not yours but the post that won't execute certainly was. At any rate I think that something like this will get you what you are looking for.
with SortedData as
(
select e.*, s.SpaceType, s.Space_Number as Space_SpaceNumber, ROW_NUMBER() over (partition by s.Space_Number order by (select null)) as RowNum
from employee e
join spaces s on e.space_Number = s.space_id
)
select CC, Space_Number, SpaceType, Space_SpaceNumber,
MAX(case when RowNum = 1 then FirstName end) as FirstName1,
MAX(case when RowNum = 1 then LastName end) as LastName1,
MAX(case when RowNum = 2 then FirstName end) as FirstName2,
MAX(case when RowNum = 2 then LastName end) as LastName2
from SortedData s
group by
CC, Space_Number, SpaceType, Space_SpaceNumber
This is using a cte (common table expression). You should probably read up on them.
Also, how if you care to explain, how can you just write s.Space_Number or e.First without using the whole "prefix" if you will. Like I said... I am terribly new to SQL.
Chris is using an alias. I did the same thing in the code I just posted. You might want to read up on aliases in sql server. They will make your life a LOT easier.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 13, 2013 at 9:12 am
First of all, thank you! You solved the problem I had and now the view is very close to what I am looking for. Despite the error message: The OVER SQL construct or statement is not supported.... it seems to work regardless, and, if it works, well that is what counts.
Do you think you could help me with one more minor formatting tweak?
Also,
I assumed some of the structures were not yours but the post that won't execute certainly was.
Yes, I take full blame for that. Just know that it was ignorance and not intentional laziness on my part.
------------
As for the formatting..
Is there a way to join the first and last names for each employee into a single cell? In other words, instead of four cells for two employees, it would only be two cells. Each employee name cell would countain Firstname Lastname. For example, instead of: John, Smith, Mary, Johnson. It would be: John Smith, Mary Johnson.
Once again, thank you so much!
August 13, 2013 at 9:28 am
Just for fun (and providing the Employee_ID is valid for determining First and Last), you could use Cross Apply to get the First and Last Record so that when the Access SQL has First(FirstName) you just convert it to First.FirstName, etc. keeping your select column output list very close to the original Access SQL.
Thanks Sean for your create/populate script!
SELECT employee.CC
, spaces.space_ID
, SpaceType
, spaces.Space_Number AS Spaces_SpaceNumber
, First.FirstName AS First1
, First.LastName AS Last1
, Last.FirstName AS First2
, Last.LastName AS Last2
FROM Spaces
LEFT JOIN Employee
ON Spaces.Space_ID = Employee.Space_Number
--use cross apply to get the First record in Employee_ID sequence
CROSS APPLY (SELECT *
FROM employee f
WHERE f.Space_Number = Spaces.Space_ID
AND f.Employee_ID = (SELECT MIN(Employee_ID) FROM employee WHERE space_number = Spaces.Space_ID)
) First
--use cross apply to get the Last record in Employee_ID sequence
CROSS APPLY (SELECT *
FROM employee f
WHERE f.Space_Number = Spaces.Space_ID
AND f.Employee_ID = (SELECT MAX(Employee_ID) FROM employee WHERE space_number = Spaces.Space_ID)
) Last
GROUP BY employee.CC
, Spaces.Space_ID
, SpaceType
, spaces.Space_Number
, First.FirstName
, First.LastName
, Last.FirstName
, Last.LastName
-- use the following cases in place of Last.FirstName and Last.LastName, if you need First2 and Last2 to be NULL if there is only one employee:
-- , CASE WHEN First.Employee_ID = Last.Employee_ID THEN NULL ELSE Last.FirstName END
-- , CASE WHEN First.Employee_ID = Last.Employee_ID THEN NULL ELSE Last.LastName END
August 13, 2013 at 9:43 am
jamesheslin123 (8/13/2013)
First of all, thank you! You solved the problem I had and now the view is very close to what I am looking for. Despite the error message: The OVER SQL construct or statement is not supported.... it seems to work regardless, and, if it works, well that is what counts.
Are you by chance doing this in SSRS? If so, you should turn your sql into a stored procedure instead of putting the sql in your report.
Yes, I take full blame for that. Just know that it was ignorance and not intentional laziness on my part.
No problem. Just trying to emphasize the importance of testing the scripts you post. 🙂
Is there a way to join the first and last names for each employee into a single cell? In other words, instead of four cells for two employees, it would only be two cells. Each employee name cell would countain Firstname Lastname. For example, instead of: John, Smith, Mary, Johnson. It would be: John Smith, Mary Johnson.
Sure this is quite simple. Just add the two columns together. A very minor change to the previous query can accomplish this.
with SortedData as
(
select e.*, s.SpaceType, s.Space_Number as Space_SpaceNumber, ROW_NUMBER() over (partition by s.Space_Number order by (select null)) as RowNum
from employee e
join spaces s on e.space_Number = s.space_id
)
select CC, Space_Number, SpaceType, Space_SpaceNumber,
MAX(case when RowNum = 1 then FirstName + ' ' + LastName end) as Name1,
MAX(case when RowNum = 2 then FirstName + ' ' + LastName end) as Name2
from SortedData s
group by
CC, Space_Number, SpaceType, Space_SpaceNumber
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 13, 2013 at 11:11 am
Are you by chance doing this in SSRS? If so, you should turn your sql into a stored procedure instead of putting the sql in your report.
I don't think so... I am doing this in a view in SQL Server Studio Management Express (SQL Server 2005). Maybe it is because it is the express version?
No problem. Just trying to emphasize the importance of testing the scripts you post.
Totally understand, I hontestly was pretty lost with that whole part, but I am getting the hang of this.
Thank you for the help with combining the names, it worked perfectly.
--------------------------
Now there is one last slightly more complicated piece to this puzzle that I have... and then I think I am actually 100% done with this project.
I think there are two ways to get what I need, the first (A) is preferred. I'll explain what I mean below.
(A)
For the space_type column, there are several different values which it can have (Workspace, Office, bathroom, etc.) If there are 0 employees in a space_type: Workspace, the name1 column would read 'Vacant Cubicle' instead of being NULL. If there are 0 employees in a space_type: Office, the name1 column would read 'Vacant Office' instead of being NULL. If there are 0 employees in any other space_type, nothing will happen, name 1 will be NULL.
(B)
Pretty much same as in (A) except that a new column entirely could be created (let's call it 'Vacancy'). This column will take on the value 'Vacant Cubicle' if there are 0 employees in the space and it is of the space_type: Workspace. This column will take on the value 'Vacant Office' if there are 0 employees in the space and it is of the space_type: Office.
I have been messing around with the CASE WHEN and I just can't seem to get the syntax correct and functional. Probably because I have such an unsure footing when it comes to the fundamentals of this language. Hopefully you can help! 🙂 Thank you in advance.
August 13, 2013 at 12:13 pm
jamesheslin123 (8/13/2013)
Now there is one last slightly more complicated piece to this puzzle that I have... and then I think I am actually 100% done with this project.
I think there are two ways to get what I need, the first (A) is preferred. I'll explain what I mean below.
(A)
For the space_type column, there are several different values which it can have (Workspace, Office, bathroom, etc.) If there are 0 employees in a space_type: Workspace, the name1 column would read 'Vacant Cubicle' instead of being NULL. If there are 0 employees in a space_type: Office, the name1 column would read 'Vacant Office' instead of being NULL. If there are 0 employees in any other space_type, nothing will happen, name 1 will be NULL.
I have been messing around with the CASE WHEN and I just can't seem to get the syntax correct and functional. Probably because I have such an unsure footing when it comes to the fundamentals of this language. Hopefully you can help! 🙂 Thank you in advance.
Let's skip option B as option A is not only feasible it would be the preferred method. All you need to do is wrap Name1 with an ISNULL check. The problem is that the query selects from employee first so there is no way you will get a space that doesn't already have an employee. You could switch the order of the tables and change the join to employees from an inner join to a left join. This would then return all spaces even if there is no match on employee. In the sample data you provided there are no spaces without employees.
Let's start by adding a new space with no employees.
insert Spaces
select 1234, 42, '8', 'Sean''s Space', 'Home', 654
Now we need to tweak up our code a bit so it will do what you are asking.
with SortedData as
(
select e.*, s.SpaceType, s.Space_Number as Space_SpaceNumber, ROW_NUMBER() over (partition by s.Space_Number order by (select null)) as RowNum
from spaces s
left join employee e on e.space_Number = s.space_id
)
select CC, Space_SpaceNumber, SpaceType, Space_SpaceNumber,
MAX(case when RowNum = 1 then isnull(FirstName + ' ' + LastName, 'Vacant Cubicle') end) as Name1,
MAX(case when RowNum = 2 then FirstName + ' ' + LastName end) as Name2
from SortedData s
group by
CC, Space_SpaceNumber, SpaceType, Space_SpaceNumber
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 13, 2013 at 1:01 pm
Hmm... the only problem with this is I don't think the code takes into account what the SpaceType is.
I need the Name1 column to say Vacant "SpaceType" only if the SpaceType is a workspace or an office and there is no employee allocated to that particular Space_Number. (Ideally when there is a vacant workspace, then Name1 will read Vacant Cubicle, instead of vacant workspace).
Otherwise, it should remain Null. So if the SpaceType is Restroom for example, I do not the Name1 to read Vacant Cubicle, nor do I want it to read Vacant Restroom.
August 13, 2013 at 1:18 pm
jamesheslin123 (8/13/2013)
Hmm... the only problem with this is I don't think the code takes into account what the SpaceType is.I need the Name1 column to say Vacant "SpaceType" only if the SpaceType is a workspace or an office and there is no employee allocated to that particular Space_Number. (Ideally when there is a vacant workspace, then Name1 will read Vacant Cubicle, instead of vacant workspace).
Otherwise, it should remain Null. So if the SpaceType is Restroom for example, I do not the Name1 to read Vacant Cubicle, nor do I want it to read Vacant Restroom.
You lost me a bit here. I think you can accomplish what you are after by simply tossing in an additional NULLIF or maybe a case. Can you post up some sample data with the requirements you are talking about and what the desired output should be from that data?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 13, 2013 at 3:29 pm
Okay first I will try to explain again verbally. Then I will give some data and what I would like the results to be.
1. If you haven't guessed already, this is a table/view of where employees are within a building. There are several different SpaceTypes in this building for different rooms or areas; these SpaceTypes include Office, Workspace (this is a cubicle), Storage, Utility, Conference Rooms, Restrooms, etc. Each distinct Space_Number is assigned exactly one of these SpaceTypes. For example, the Space_Number 84R4702 has a Storage SpaceType. Storage SpaceTypes are normally vacant, so we don't want extraneous data in our table/view with a bunch of cells reading "Vacant Storage" or something like that because a Space_Number with a Storage SpaceType will always be vacant.
The only time I want to be notified of vacancy is if the SpaceType is either Workspace or Office. We would like to know when there are no employees assigned to a Space_Number with the SpaceType Workspace or Office.
So, for the Name1 column there are three possibilities depending on what type of SpaceType we are dealing with and whether or not it is vacant:
(i) a Space_Number has a SpaceType of either Office or Workspace, but there is at least one employee in it. The Name1 column should show the name of the first employee in this Space_Number which has a SpaceType of either Office or Workspace.
(ii) a Space_Number has a SpaceType of either Office or Workspace, and there are no employees in it. The Name1 column should read "Vacant SpaceType" , in other words it will say "Vacant Workspace" or Vacant Office" in the Name1 column.
(iii) a Space_Number has a SpaceType that is anything other than Office or Workspace. ThisSpace_Number will not have an Employee assigned to them. In the Name1 column I simply want the SpaceType for that Space_Number.
I hope you understand this. I tried to be as clear as possible. Below I will give sample data and expected results to solidify what I just said.
=======================================
--=====DROP PRE-EXISTING TABLE dbo.Employee
IF OBJECT_ID('Employee','U') IS NOT NULL
DROP TABLE dbo.Employee
--===== CREATE TEST TABLE dbo.Employee
CREATE TABLE dbo.Employee
(
Employee_ID INT PRIMARY KEY,
CC FLOAT,
FirstName NVARCHAR(50),
LastName NVARCHAR(50),
Space_ID INT,
[Employee Type] NVARCHAR(50)
)
--===== TEST DATA FOR dbo.Employee
INSERT INTO dbo.Employee
(Employee_ID, CC, FirstName, LastName, Space_ID, [Employee Type])
SELECT 25, 9150, 'Brian', 'Hutchin', 1270, '(F.T)' UNION ALL
SELECT 779, 9150,'Peter', 'Galant', 1270, '(F.T)'
========================================
--===== DROP PRE-EXISTING TABLE dbo.Spaces
IF OBJECT_ID('Spaces','U') IS NOT NULL
DROP TABLE dbo.Spaces
--===== CREATE TEST TABLE dbo.Spaces
CREATE TABLE dbo.Spaces
(
Space_ID INT PRIMARY KEY,
BuildingNum NVARCHAR(50),
FloorNum NVARCHAR(50),
Space_Number NVARCHAR(255),
SpaceType NVARCHAR(50),
Area INT
)
--===== TEST DATA FOR dbo.Spaces
INSERT INTO dbo.Spaces
(Space_ID, BuildingNum, FloorNum, Space_Number, SpaceType, Area)
SELECT '1270','158','1','158R1533','Office','139' UNION ALL
SELECT '527','84','3S','84R3134','Office','123' UNION ALL
SELECT '305','84','2S','84W2313','Workspace','80' UNION ALL
SELECT '1353','158','2','158R2625','Storage','153'
=======================================
----=== EXPECTED RESULTS -----==
--===== DROP PRE-EXISTING TABLE dbo.ExpectedResults
IF OBJECT_ID('ExpectedResults','U') IS NOT NULL
DROP TABLE dbo.ExpectedResults
--===== EXPECTED RESULTS FOR QUERY (Well I am really using a view)
CREATE TABLE dbo.ExpectedResults
(
Space_ID INT PRIMARY KEY,
Space_Number NVARCHAR(50),
Name1 NVARCHAR(50),
Name2 NVCHAR(50),
)
--===== EXPECTED DATA FOR dbo.ExpectedResults
INSERT INTO dbo.ExpectedResults
(Space_ID, Space_Number, Name1, Name2)
SELECT '1270','158R1533','Brian Hutchin','Peter Galant' UNION ALL
SELECT '527','84R3134','Vacant Office','NULL' UNION ALL
SELECT '305','84W2313','Vacant Cubicle','NULL' UNION ALL
SELECT '1353','158R2625','Storage','NULL'
Hopefully that makes more sense, I have still been messing around with functions to try and figure this out on my own, but to no avail!
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply