February 4, 2011 at 11:06 am
Hi,
Great forum and wonderful topics. I am new to T-SQL, but have experience with Access code. I am trying to get a list of people with special days. Some have one day, others more than one. I am is using CASE as follows:
- If a person has only one day as a special day, then list it
- If a person has more than one day, then list them with a comma and a space
This is the code I've written so far:
USE Students
SELECT
CASE WHEN ISNULL(MondayRequired, '') = '' THEN '' ELSE 'Monday, ' END) +
CASE WHEN ISNULL(TuesdayRequired, '') = '' THEN '' ELSE 'Tuesday, ' END) +
CASE WHEN ISNULL(WednesdayRequired, '') = '' THEN '' ELSE 'Wednesday, ' END) +
CASE WHEN ISNULL(ThursdayRequired, '') = '' THEN '' ELSE 'Thursday, ' END) +
CASE WHEN ISNULL(FridayRequired, '') = '' THEN '' ELSE 'Friday ' END) AS [Special Days]
From StudentSpecialNeeds
WHERE
((CASE WHEN ISNULL(MondayRequired, '') = '' THEN '' ELSE 'Monday, ' END) +
(CASE WHEN ISNULL(TuesdayRequired,'') = '' THEN '' ELSE 'Tuesday, ' END) +
(CASE WHEN ISNULL(WednesdayRequired, '') = '' THEN '' ELSE 'Wednesday, ' END) +
(CASE WHEN ISNULL(ThursdayRequired, '') = '' THEN '' ELSE 'Thursday, ' END) +
(CASE WHEN ISNULL(FridayRequired, '') = '' THEN '' ELSE 'Friday ' END) > '')
This is part of the result:
Tuesday, Wednesday,
Thursday,
Wednesday,
Monday, Wednesday, Friday
So, the problem I have is that I don't know how to eliminate the comma and the single space after a day in cases where there's one special day or two.
Thanks for any help you can provide me with.
jfmonterroso
February 4, 2011 at 11:18 am
Change your string to start with the comma.
So instead of: 'Tuesday, Wednesday, '
It would be: ', Tuesday, Wednesday'
And then you're able to always cutoff the 1st two characters.
DECLARE @STR VARCHAR(50)
SET @STR = ', Tuesday, Wednesday'
SELECT RIGHT(@str, LEN(@str)-2)
_____________________________________________________________________
- Nate
February 4, 2011 at 11:22 am
Thanks Nate!
I'll give it a try and let you know of the outcome.
jfmonteroso
February 4, 2011 at 12:11 pm
RP_DBA (2/4/2011)
Change your string to start with the comma.So instead of: 'Tuesday, Wednesday, '
It would be: ', Tuesday, Wednesday'
And then you're able to always cutoff the 1st two characters.
DECLARE @STR VARCHAR(50)
SET @STR = ', Tuesday, Wednesday'
SELECT RIGHT(@str, LEN(@str)-2)
Hi Nate,
I am still getting some instances with commas and spaces. In the sample I gave I am using 5 existing fields, either null or not null. Is there any way I can use/declare the field "Special Days" in the SELECT statement and say >'' and then use that with RIGHT AND LEN?
Thanks!
jfmonterroso
February 5, 2011 at 3:13 pm
All that you want in one short answer:
Prepare the data (you should have posted that!):
select MondayRequired='x', TuesdayRequired='x', WednesdayRequired = 'x', ThursdayRequired='x', FridayRequired='x'
into #WeekSchedules
union all select 'x', '', null, '', 'x'
union all select '', '', 'x', '', ''
union all select null, null, null, 'x', null
select * from #WeekSchedules
And the solution:
select ScheduleCSV = stuff( sch.ScheduleCSV, 1, 2, ''),
TotalLength = len( sch.ScheduleCSV )
from
(select ScheduleCSV =
CASE WHEN ws.MondayRequired <> '' THEN ', Monday' ELSE '' END
+ CASE WHEN ws.TuesdayRequired <> '' THEN ', Tuesday' ELSE '' END
+ CASE WHEN ws.WednesdayRequired <> '' THEN ', Wednesday' ELSE '' END
+ CASE WHEN ws.ThursdayRequired <> '' THEN ', Thursday' ELSE '' END
+ CASE WHEN ws.FridayRequired <> '' THEN ', Friday' ELSE '' END
from #WeekSchedules ws
) sch
Result:
ScheduleCSV TotalLength
----------------------------------------------------
Monday, Tuesday, Wednesday, Thursday, Friday46
Monday, Friday16
Wednesday11
Thursday10
Cheers! :smooooth:
Vedran Kesegic
February 7, 2011 at 7:25 am
Hi,
Programming is not and has never been my forte. I am not looking to create a database from scratch following the best parameters possible. I am working with data that already exists as it was migrated from Access to SQL Server 2008. Thanks for your suggestion of looking into 1NF and RDBMS. For the moment I need kludges to understand basic functions to get what I need.
CELKO (2/4/2011)
You are going to have an un-learning curve. SQL is a database language, period. The front end does all of the display formatting, never the database. Only single user, desktop products combine both functions. It is like COBOL used to be, if you are that old.What you want to do violated First Normal Form (1NF). This is the foundation of RDBMS. Have you read any books on RDBMS?
There are kludges in T-SQL to do what you asked, but try to be a better programmer than that.
February 7, 2011 at 7:29 am
Hi Vedran,
Interesting suggestion. Sometimes I do use tallies, however, at the moment I simply need to list instances of people who have special days in their schedules. I need to see what those days are and eliminate commas and spaces after the special day if there is only one value in the field.
Thanks!
February 7, 2011 at 7:52 am
jfm3 (2/7/2011)
Sometimes I do use tallies, however, at the moment I simply need to list instances of people who have special days in their schedules. I need to see what those days are and eliminate commas and spaces after the special day if there is only one value in the field.
Where do you see the tally in the script? It's not there.
Did you try to run the script ?
It gives you just what you have been described. You can add the names of the students or remove len() if you don't need it.
When you ask for a solution, It is always a good practice to provide complete script to create test table(s) and data, and the result you expect (not description, but exact data you expect as result).
February 7, 2011 at 8:07 am
Hi,
I meant to say the number of days.
My original code is like the following:
use Students
SELECT StudentID As ID, StudentLFM As [Student Name],
(CASE WHEN ISNULL(MondayRequired, '') = '' THEN '' ELSE 'Monday, ' END) +
(CASE WHEN ISNULL(TuesdayRequired, '') = '' THEN '' ELSE 'Tuesday, ' END) +
(CASE WHEN ISNULL(WednesdayRequired, '') = '' THEN '' ELSE 'Wednesday, ' END) +
(CASE WHEN ISNULL(ThursdayRequired, '') = '' THEN '' ELSE 'Thursday, ' END) +
(CASE WHEN ISNULL(FridayRequired, '') = '' THEN '' ELSE 'Friday ' END) AS [Special Days]
FROM StudentName
inner join NAMEPERSONNAME
on StudentName.StudentID = PERSONNAME.PersonID_Number
WHERE ((CASE WHEN ISNULL(MondayRequired, '') = '' THEN '' ELSE 'Monday, ' END) +
(CASE WHEN ISNULL(TuesdayRequired,'') = '' THEN '' ELSE 'Tuesday, ' END) +
(CASE WHEN ISNULL(WednesdayRequired, '') = '' THEN '' ELSE 'Wednesday, ' END) +
(CASE WHEN ISNULL(ThursdayRequired, '') = '' THEN '' ELSE 'Thursday, ' END) +
(CASE WHEN ISNULL(FridayRequired, '') = '' THEN '' ELSE 'Friday ' END) > '')
order by StudentLFM
I did try the script you provided and it is pretty neat. It is the closest one to get what I need.
Thanks!
February 7, 2011 at 1:55 pm
Hey Vedran,
Your code really worked. I'm grateful for your help. Can you please explain some of your sample a little bit further? I have a few textbooks I am using as I work my way around some problems, but I could not find enough information related to your syntax. For example, what exactly is "ws" used with the STUFF function? This is also after the closing parentheses at the bottom. As per "ws" I found out to be White Space, but I don't understand why it must go after the table name that it is being used. And last but not least, I would like to join another table to add more information to my query. Where should I place my joins within this code?
Thanks and cheers!
February 7, 2011 at 3:51 pm
You're welcome! 😉
jfm3
what exactly is "ws" used with the STUFF function?
"ws" is alias I choosed for #WeekSchedules table, ommiting optional "AS" keyword (I could write "from #WeekSchedules AS ws" or "from #WeekSchedules AS WkSched").
It's good practice to use aliases instead of full table names to shorten typing.
It is also recommended to prefix each column with alias of the table that column belongs to. You will avoid some misunderstandings and errors if you do so, even if there is just one table with no joins.
The same is with "sch" after parenthesis. Result of SELECT within parenthesis is in the form of table, and I'm also giving that derived table (it's actually called "inline view") an alias.
jfm3
Where should I place my joins within this code?
It would be very helpful to you to read some good tutorial on SQL basics, joins, etc.
You could do something like this:
select s.LastName, s.FirstName, s.SpecialDays
FROM
(
select SpecialDays =
CASE WHEN sn.MondayRequired <> '' THEN ', Monday' ELSE '' END
+ CASE WHEN sn.TuesdayRequired <> '' THEN ', Tuesday' ELSE '' END
+ CASE WHEN sn.WednesdayRequired <> '' THEN ', Wednesday' ELSE '' END
+ CASE WHEN sn.ThursdayRequired <> '' THEN ', Thursday' ELSE '' END
+ CASE WHEN sn.FridayRequired <> '' THEN ', Friday' ELSE '' END,
FirstName = pn.Name,
LastName = pn.LastName
from StudentName sn
join PersonName pn ON sn.StudentID = pn.ID
) s
WHERE s.SpecialDays <> ''
February 7, 2011 at 4:08 pm
CELKO (2/4/2011)
You are going to have an un-learning curve. SQL is a database language, period. The front end does all of the display formatting, never the database. Only single user, desktop products combine both functions. It is like COBOL used to be, if you are that old.What you want to do violated First Normal Form (1NF). This is the foundation of RDBMS. Have you read any books on RDBMS?
There are kludges in T-SQL to do what you asked, but try to be a better programmer than that.
BWAA-HAAAA!!!! Never say "Never", Joe. There are such things as databases with no GUI interface. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
February 8, 2011 at 9:47 am
Dude, I feel like a little kid with a new toy!
This really helped. Thanks for your kind advise and suggestion on books. I have the following:
- Beginning T-SQL With Microsoft SQL Server 2005 and 2008
- Professional Microsoft SQL Server 2008 Reporting Services
- Beginning SQL Server 2008 for Developers
- Microsoft Server 2008 Reporting Services
They are good, but they lack the explaining of some basic steps. Do you recommend any titles? I'll browse through the Books section of this great forum.
Cheers!
Vedran Kesegic (2/7/2011)
You're welcome! 😉jfm3
what exactly is "ws" used with the STUFF function?"ws" is alias I choosed for #WeekSchedules table, ommiting optional "AS" keyword (I could write "from #WeekSchedules AS ws" or "from #WeekSchedules AS WkSched").
It's good practice to use aliases instead of full table names to shorten typing.
It is also recommended to prefix each column with alias of the table that column belongs to. You will avoid some misunderstandings and errors if you do so, even if there is just one table with no joins.
The same is with "sch" after parenthesis. Result of SELECT within parenthesis is in the form of table, and I'm also giving that derived table (it's actually called "inline view") an alias.
jfm3
Where should I place my joins within this code?It would be very helpful to you to read some good tutorial on SQL basics, joins, etc.
You could do something like this:
select s.LastName, s.FirstName, s.SpecialDaysFROM
(
select SpecialDays =
CASE WHEN sn.MondayRequired <> '' THEN ', Monday' ELSE '' END
+ CASE WHEN sn.TuesdayRequired <> '' THEN ', Tuesday' ELSE '' END
+ CASE WHEN sn.WednesdayRequired <> '' THEN ', Wednesday' ELSE '' END
+ CASE WHEN sn.ThursdayRequired <> '' THEN ', Thursday' ELSE '' END
+ CASE WHEN sn.FridayRequired <> '' THEN ', Friday' ELSE '' END,
FirstName = pn.Name,
LastName = pn.LastName
from StudentName sn
join PersonName pn ON sn.StudentID = pn.ID
) s
WHERE s.SpecialDays <> ''
February 8, 2011 at 9:49 am
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply