SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


T-SQL Function that works like the FIRST funcion in MS Access?


T-SQL Function that works like the FIRST funcion in MS Access?

Author
Message
jamesheslin123
jamesheslin123
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 30
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.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59488 Visits: 17947
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
jamesheslin123
jamesheslin123
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 30
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.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59488 Visits: 17947
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
jamesheslin123
jamesheslin123
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 30
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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search