May 17, 2011 at 6:12 am
Hi
I have two tables tTimetable and tStaffTimeTableInfo.
tTimeTable has the following fields
DayID
StaffID
PeriodID
tStaffTimeTableInfo has the following fields
StaffID
StaffInitials
I need to be able to select DayID = 1 and select all the PeriodID's but show the staff initials of those StaffID's that are not in tTimetable from tStaffTimeTableInfo in the form of ghc, trb, tec, etc
I cant even seem how to work out how to get the missing StaffID's and include the PeriodID
Below is my attempt
SELECT PeriodID
FROM dbo.tTimeTable
WHERE (NOT EXISTS
(SELECT staffID
FROM tStaffTimeTableInfo
WHERE tTimetable.staffID = tStaffTimeTableInfo.staffID))
Thanks for any advice
May 17, 2011 at 7:26 am
Can you please give some output example.It will be easier to answer your question.
Thanks
Vineet Bhargava
Thanks And Regards
Vineet Bhargava
vineetbhargav@gmail.com
May 17, 2011 at 7:31 am
I think this will work but there must be a better way, just cannot think of it at the moment.
WITH p (DayId,PeriodID)
AS (SELECT DISTINCT t.DayId,t.PeriodID
FROM tTimeTable t
WHERE t.DayID = 1)
SELECT p.PeriodID,s.StaffInitials
FROM p
CROSS JOIN tStaffTimeTableInfo s
WHERE NOT EXISTS(SELECT * FROM tTimeTable t WHERE t.DayID=p.DayID AND t.PeriodID=p.PeriodID AND t.StaffID=s.StaffID)
Far away is close at hand in the images of elsewhere.
Anon.
May 17, 2011 at 7:41 am
Hi Hope thie helps
tTimeTable
DayID PeriodID StaffID
1 1 2
1 1 7
1 1 8
1 1 9
1 2 6
1 2 7
1 2 8
1 2 9
1 3 1
1 3 2
1 3 3
1 3 4
1 3 5
1 3 6
1 3 7
tStaffTimeTableInfo
StaffID StaffInitials
1 ABC
2 RWT
3 YUJ
4 OPP
5 QWE
6 TFV
7 ASA
8 MNB
9 PLM
Output should be
PeriodID StaffInitilas
1 ABC, YUJ, OPP, QWE, TFV
2 ABC, RWT, YUJ, OPP, QWE
3 MNB, PLM
Hop that meks some sense
May 17, 2011 at 9:00 am
Working DDL for the adventurers:
create table tTimetable (DayID int, PeriodID int, StaffID int);
insert into tTimetable
values
(1,1,2),
(1,1,7),
(1,1,8),
(1,1,9),
(1,2,6),
(1,2,7),
(1,2,8),
(1,2,9),
(1,3,1),
(1,3,2),
(1,3,3),
(1,3,4),
(1,3,5),
(1,3,6),
(1,3,7)
create table tStaffTimeTableInfo (StaffID int, StaffInitials varchar(10));
insert into tStaffTimeTableInfo
values
(1,'ABC'),
(2,'RWT'),
(3,'YUJ'),
(4,'OPP'),
(5,'QWE'),
(6,'TFV'),
(7,'ASA'),
(8,'MNB'),
(9,'PLM')
drop table tTimetable;
drop table tStaffTimeTableInfo;
EDIT: he switched the column order on me :w00t:
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
May 17, 2011 at 9:29 am
Jay B-317395 (5/17/2011)
Hi Hope thie helpstTimeTable
DayID PeriodID StaffID
1 1 2
1 1 7
1 1 8
1 1 9
1 2 6
1 2 7
1 2 8
1 2 9
1 3 1
1 3 2
1 3 3
1 3 4
1 3 5
1 3 6
1 3 7
tStaffTimeTableInfo
StaffID StaffInitials
1 ABC
2 RWT
3 YUJ
4 OPP
5 QWE
6 TFV
7 ASA
8 MNB
9 PLM
Output should be
PeriodID StaffInitilas
1 ABC, YUJ, OPP, QWE, TFV
2 ABC, RWT, YUJ, OPP, QWE
3 MNB, PLM
Hop that meks some sense
I am lost on what you are trying to do. It sounds like you want a cross tab query for each period for staff members that do not have a entry in ttimetable. Your desired output does not match that. Period 1 has only 1 record of a staff and your output has only 4 (should that be 8?). I need a bit more clear explanation of what you are trying to get out of these two tables.
Extra kudos to Todd for creating the ddl and sample data. Nice Work!!!
_______________________________________________________________
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/
May 17, 2011 at 9:46 am
This should do it, I think
create table #tTimetable (DayID int, PeriodID int, StaffID int);
insert into #tTimetable(DayID, PeriodID, StaffID)
values
(1,1,2),
(1,1,7),
(1,1,8),
(1,1,9),
(1,2,6),
(1,2,7),
(1,2,8),
(1,2,9),
(1,3,1),
(1,3,2),
(1,3,3),
(1,3,4),
(1,3,5),
(1,3,6),
(1,3,7)
create table #tStaffTimeTableInfo (StaffID int, StaffInitials varchar(10));
insert into #tStaffTimeTableInfo(StaffID, StaffInitials)
values
(1,'ABC'),
(2,'RWT'),
(3,'YUJ'),
(4,'OPP'),
(5,'QWE'),
(6,'TFV'),
(7,'ASA'),
(8,'MNB'),
(9,'PLM')
CREATE TABLE #Results(PeriodID int, StaffInitials varchar(10)) -- You could also just use a subquery
INSERT INTO #Results(PeriodID, StaffInitials)
SELECT TT.PeriodID, ST.StaffInitials
FROM #tTimeTable TT
INNER JOIN #tStaffTimeTableInfo ST
ON TT.StaffID = ST.StaffID
SELECT PeriodID, STUFF((
SELECT ', ' + StaffInitials
FROM #Results R
WHERE R.PeriodID = #Results.PeriodID
ORDER BY R.StaffInitials
FOR XML PATH('')), 1, 1, '') as Initials
FROM #Results
GROUP BY PeriodID
drop table #tTimetable;
drop table #tStaffTimeTableInfo;
I got the technique for using FOR XML PATH here
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
May 17, 2011 at 9:52 am
Stefan, your solution lists the staff members for that particular periodID. It seems to me he wants the staff that are not matched.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
May 17, 2011 at 9:59 am
toddasd (5/17/2011)
Stefan, your solution lists the staff members for that particular periodID. It seems to me he wants the staff that are not matched.
Oops, sorry about that. I'll go fix it in a minute. This is what I get for trying to answer a question as a break from formulating one of my own. : -)
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
May 17, 2011 at 10:05 am
WITH cte (PeriodID,StaffInitials)
AS (
SELECT p.PeriodID,s.StaffInitials
FROM (SELECT DISTINCT t.DayId,t.PeriodID FROM tTimeTable t WHERE t.DayID = 1) p
CROSS JOIN tStaffTimeTableInfo s
WHERE NOT EXISTS(SELECT * FROM tTimeTable t WHERE t.DayID=p.DayID AND t.PeriodID=p.PeriodID AND t.StaffID=s.StaffID)
)
SELECT a.PeriodID,
STUFF((SELECT ',' + cte.StaffInitials FROM cte WHERE cte.PeriodID = a.PeriodID ORDER BY cte.StaffInitials FOR XML PATH('')), 1, 1, '') AS [StaffInitials]
FROM cte a
GROUP BY a.PeriodID
Far away is close at hand in the images of elsewhere.
Anon.
May 17, 2011 at 10:07 am
OK, this should get all the initials that aren't in a given period
create table #tTimetable (DayID int, PeriodID int, StaffID int);
insert into #tTimetable(DayID, PeriodID, StaffID)
values
(1,1,2),
(1,1,7),
(1,1,8),
(1,1,9),
(1,2,6),
(1,2,7),
(1,2,8),
(1,2,9),
(1,3,1),
(1,3,2),
(1,3,3),
(1,3,4),
(1,3,5),
(1,3,6),
(1,3,7)
create table #tStaffTimeTableInfo (StaffID int, StaffInitials varchar(10));
insert into #tStaffTimeTableInfo(StaffID, StaffInitials)
values
(1,'ABC'),
(2,'RWT'),
(3,'YUJ'),
(4,'OPP'),
(5,'QWE'),
(6,'TFV'),
(7,'ASA'),
(8,'MNB'),
(9,'PLM')
CREATE TABLE #Results(PeriodID int, StaffInitials varchar(10)) -- You could also just use a subquery
INSERT INTO #Results(PeriodID, StaffInitials)
SELECT XS.PeriodID, XS.StaffInitials
FROM
(SELECT X.PeriodID, S.StaffID, S.StaffInitials
FROM
(SELECT DISTINCT PeriodID
FROM #tTimetable) X
CROSS JOIN #tStaffTimeTableInfo S) XS
LEFT JOIN
(SELECT TT.PeriodID, ST.StaffID
FROM #tTimeTable TT
INNER JOIN #tStaffTimeTableInfo ST
ON TT.StaffID = ST.StaffID) STT
ON XS.StaffID = STT.StaffID
AND XS.PeriodID = STT.PeriodID
WHERE STT.StaffID IS NULL
SELECT PeriodID, STUFF((
SELECT ', ' + StaffInitials
FROM #Results R
WHERE R.PeriodID = #Results.PeriodID
ORDER BY R.StaffInitials
FOR XML PATH('')), 1, 1, '') as Initials
FROM #Results
GROUP BY PeriodID
drop table #tTimetable;
drop table #tStaffTimeTableInfo;
drop table #results;
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
May 17, 2011 at 11:20 am
CELKO (5/17/2011)
Please post real DDL. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. People cannot read your mind, so post your code and clear specs if you really want help.>> I have two tables tTimetable and Staff. <<
You are already wrong. we never put data types or structure affixes in a data element name. That was BASIC not SQL. Take of that silly “t-” And you have a table with information in it! How many other tables have no information.
Please learn why field and columns are totally different things.
Think about how silly “day_id” is; just use a date!
CREATE TABLE TimeTable
(event_date DATE NOT NULL,
period_nbr INTEGER NOT NULL
CHECK(period_nbr BETWEEN 1 AND 6),
PRIMARY KEY (event_date, period_nbr),
staff_id CHAR(10) NOT NULL
REFERENCES Staff(staff_id)
);
CREATE TABLE Staff
(staff_id CHAR(10) NOT NULL PRIMARY KEY,
staff_initials CHAR(3) NOT NULL);
I need to be able to select day_id = 1 [sic] and select all the period_nbr's but show the staff initials of those staff_id's that are not in Timetable from Staff in the form of GHC, TRB, TEC, etc
SELECT Staff.staff_initials
FROM (SELECT staff_id FROM Staff
EXCEPT
SELECT staff_id FROM TimeTable)
AS UnusedStaff(staff_id)
INNER JOIN
Staff
ON Staff.staff_id = UnusedStaff.staff_id;
I wanted to see how that compared to the solution I posted, so I copied the original data, put it in the tables you posted and ran the query. It returns no rows.
Also, why would you declare the period_nbr column as int when you're limiting it to a maximum value of 6? tinyint would be the appropriate choice in that case. Staff_id as char(10) when it is clearly an int? And char instead of varchar when the id is clearly of variable length? And then limiting the initials to 3 characters (again as a char!) when the original spec called for a varchar(10).
And throwing on a composite primary key for no apparent reason. Yeesh.
Sloppy.
Also, what's with this "And you have a table with information in it! How many other tables have no information."? Total non-sequiter.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
May 17, 2011 at 11:23 am
CELKO (5/17/2011)
Please post real DDL. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. People cannot read your mind, so post your code and clear specs if you really want help.>> I have two tables tTimetable and Staff. <<
You are already wrong. we never put data types or structure affixes in a data element name. That was BASIC not SQL. Take of that silly “t-” And you have a table with information in it! How many other tables have no information.
Please learn why field and columns are totally different things.
Think about how silly “day_id” is; just use a date!
CREATE TABLE TimeTable
(event_date DATE NOT NULL,
period_nbr INTEGER NOT NULL
CHECK(period_nbr BETWEEN 1 AND 6),
PRIMARY KEY (event_date, period_nbr),
staff_id CHAR(10) NOT NULL
REFERENCES Staff(staff_id)
);
CREATE TABLE Staff
(staff_id CHAR(10) NOT NULL PRIMARY KEY,
staff_initials CHAR(3) NOT NULL);
I need to be able to select day_id = 1 [sic] and select all the period_nbr's but show the staff initials of those staff_id's that are not in Timetable from Staff in the form of GHC, TRB, TEC, etc
SELECT Staff.staff_initials
FROM (SELECT staff_id FROM Staff
EXCEPT
SELECT staff_id FROM TimeTable)
AS UnusedStaff(staff_id)
INNER JOIN
Staff
ON Staff.staff_id = UnusedStaff.staff_id;
Oh, and "just use a date"?
What if the "day" he's trying to track goes from 10 PM to 10PM? What if it changes based on the region or department? It is entirely possible that Day_id links to another table not shown here. Too many unfounded assumptions on your part Joe.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
May 17, 2011 at 11:25 am
Stefan, I dont think CELKO was having a go at you more the Original Poster.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
May 17, 2011 at 11:31 am
Jason-299789 (5/17/2011)
Stefan, I dont think CELKO was having a go at you more the Original Poster.
I know that, he's still wrong and rude in the process.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply