Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

T-SQL Function that works like the FIRST funcion in MS Access? Expand / Collapse
Author
Message
Posted Tuesday, August 13, 2013 11:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 12:39 PM
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.
Post #1483867
Posted Tuesday, August 13, 2013 12:13 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:48 PM
Points: 13,427, Visits: 12,292
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 Moden's 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)
Post #1483891
Posted Tuesday, August 13, 2013 1:01 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 12:39 PM
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.
Post #1483916
Posted Tuesday, August 13, 2013 1:18 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:48 PM
Points: 13,427, Visits: 12,292
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 Moden's 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)
Post #1483924
Posted Tuesday, August 13, 2013 3:29 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 12:39 PM
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!
Post #1484007
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse