Help with SELECT please

[manning_news]

I need to select 2 rows from a table for every SSN. DDL for the table:

CREATE TABLE [dbo].[tblResidentRotations] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[SSN] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ResidentProgram] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[RotationID] [int] NULL ,
[MonthName] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RotationLocationID] [int] NULL ,
[CallLocationID] [int] NULL ,
[IMClinicDay] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[IMClinicDateLast] [datetime] NULL ,
[IMClinicDateFirst] [datetime] NULL ,
[PedsClinicDay] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Comments] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ClinicScheduleComments] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[LastFirstComments] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PGYLevel] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AcademicYear] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY]

For a given MonthName, I pull SSN, IMClinicDay, IMClinicDateLast, and
IMClinicDateFirst. IMClinicDay contains data like "Monday AM",
"Tuesday PM". IMClinicDateLast and IMClinicDateFirst contains date
data in mm/dd/yy format. I need to pull IMClinicDay for the date given
in IMClinicDateFirst. For example, if my data looks like this:

SSN: 999999999
MonthName: July
IMClinicDay: Monday PM
IMClinicDateLast: 07/01/05
IMClinicDateFirst: 09/01/05

then I need to pull what the IMClinicDay would be for this SSN in
September.

Thanks for any help and advice.

[--CELKO--]

Stop using that silly redundant "tbl-" prefix; If nobody told you
yet, SQL only has one data structure. Then you might want to read a
basic book on data modeling - you always name a data element for what
it is, not for how it is stored, where it is used, etc.

Next, we need keys to have proper tables. An IDENTITY is **never** a
key by definition. SSN is never VARCHAR() but it is fixed length, so
all you did was invite a loss of data integrity. Ditto when you pulled
oversize numbers out of the air for the other column sizes. Since there
are no non-NOT NULL columns, you cannot ever have a key!! Think about
VARCHAR(1) and what it means.

Do you know of a month name that is CHAR(15)? Why are you using
strings for temporal data in SQL? Why are you using vague strings like
"Monday PM" for temporal data? Why did you violate ISO-8601 formats
for the bad dates?

What does the resident's academic year have to do with rotations?
The whole mess looks denormalized. Just based on a few decades of
prior experience, I would guess this ought o reduce down to something
like this:

CREATE TABLE ResidentRotations
(ssn VARCHAR(9) NOT NULL
REFERENCES Residents(ssn),
resident_program VARCHAR(20) NOT NULL,
rotation_loc INTEGER NOT NULL
REFERENCES LocationsCodes(loc_nbr),
rotation_start_time DATETIME NOT NULL,
rotation_end_time DATETIME NOT NULL,
CHECK (rotation_start_time < rotation_end_time),
call_loc INTEGER NOT NULL
REFERENCES LocationsCodes(loc_nbr),
PRIMARY KEY (ssn, rotation_start_time));

I need to pull what the IMClinicDay would be for this SSN in September. <<

You need to use a Calendar table and insert the scheduled shifts in
advance for the known duration. You can take care of holidays,
re-scheduling, etc. with this approach.

You might want to read Rick Snodgrass' s book on Temporal queries in
SQL after you get thru a basic data modeling book and a few ISO
standards. Pretty much everything you did was fundamentally wrong.

[Erland Sommarskog]

(ma**********@hotmail.com) writes:

For a given MonthName, I pull SSN, IMClinicDay, IMClinicDateLast, and
IMClinicDateFirst. IMClinicDay contains data like "Monday AM",
"Tuesday PM". IMClinicDateLast and IMClinicDateFirst contains date
data in mm/dd/yy format.
No, they are declared as datetime, which means that they are in a
binary format. If you say

SELECT * FROM tbl WHERE datecol = '07/01/05'

You could get rows from from 2007-01-05, 2005-01-07 or any other
of the six possible permutations, depending on the current settings.
On the other hand:

SELECT * FROM tbl WHERE datecol = '20070105'

will always give the same set of data.

OK, so that is not what you asked about, but since you had an apparent
misunderstanding about datetime, I figured I should point it out.
I need to pull IMClinicDay for the date given
in IMClinicDateFirst. For example, if my data looks like this:

SSN: 999999999
MonthName: July
IMClinicDay: Monday PM
IMClinicDateLast: 07/01/05
IMClinicDateFirst: 09/01/05

then I need to pull what the IMClinicDay would be for this SSN in
September.

If I understand this correctly, you should have a look at the datename()
function in Books Online.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[manning_news]

Here's a sample of the data I have:

SSN: 999999999 (first row)
MonthName: July
IMClinicDay: Monday PM
IMClinicDateLast: 07/01/05
IMClinicDateFirst: 09/01/05

SSN: 999999999 (nth row)
MonthName: September
IMClinicDay: Wednesday PM
IMClinicDateLast: 09/01/05
IMClinicDateFirst: 10/01/05

With a SELECT statement, I want to return all of the first row and only
the IMClinicDay (Wednesday PM) of the nth row using IMClinicDateFirst
in the first row to get this data.

Thanks for any help.


Erland Sommarskog wrote:

(ma**********@hotmail.com) writes:

For a given MonthName, I pull SSN, IMClinicDay, IMClinicDateLast, and
IMClinicDateFirst. IMClinicDay contains data like "Monday AM",
"Tuesday PM". IMClinicDateLast and IMClinicDateFirst contains date
data in mm/dd/yy format.

No, they are declared as datetime, which means that they are in a
binary format. If you say

SELECT * FROM tbl WHERE datecol = '07/01/05'

You could get rows from from 2007-01-05, 2005-01-07 or any other
of the six possible permutations, depending on the current settings.
On the other hand:

SELECT * FROM tbl WHERE datecol = '20070105'

will always give the same set of data.

OK, so that is not what you asked about, but since you had an apparent
misunderstanding about datetime, I figured I should point it out.

I need to pull IMClinicDay for the date given
in IMClinicDateFirst. For example, if my data looks like this:

SSN: 999999999
MonthName: July
IMClinicDay: Monday PM
IMClinicDateLast: 07/01/05
IMClinicDateFirst: 09/01/05

then I need to pull what the IMClinicDay would be for this SSN in
September.

If I understand this correctly, you should have a look at the datename()
function in Books Online.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[manning_news]

I just want to know if I can get to the row I want. If it's impossible
with the structure I have then just say so.

--CELKO-- wrote:

Stop using that silly redundant "tbl-" prefix; If nobody told you
yet, SQL only has one data structure. Then you might want to read a
basic book on data modeling - you always name a data element for what
it is, not for how it is stored, where it is used, etc.

Next, we need keys to have proper tables. An IDENTITY is **never** a
key by definition. SSN is never VARCHAR() but it is fixed length, so
all you did was invite a loss of data integrity. Ditto when you pulled
oversize numbers out of the air for the other column sizes. Since there
are no non-NOT NULL columns, you cannot ever have a key!! Think about
VARCHAR(1) and what it means.

Do you know of a month name that is CHAR(15)? Why are you using
strings for temporal data in SQL? Why are you using vague strings like
"Monday PM" for temporal data? Why did you violate ISO-8601 formats
for the bad dates?

What does the resident's academic year have to do with rotations?
The whole mess looks denormalized. Just based on a few decades of
prior experience, I would guess this ought o reduce down to something
like this:

CREATE TABLE ResidentRotations
(ssn VARCHAR(9) NOT NULL
REFERENCES Residents(ssn),
resident_program VARCHAR(20) NOT NULL,
rotation_loc INTEGER NOT NULL
REFERENCES LocationsCodes(loc_nbr),
rotation_start_time DATETIME NOT NULL,
rotation_end_time DATETIME NOT NULL,
CHECK (rotation_start_time < rotation_end_time),
call_loc INTEGER NOT NULL
REFERENCES LocationsCodes(loc_nbr),
PRIMARY KEY (ssn, rotation_start_time));

I need to pull what the IMClinicDay would be for this SSN in September. <<

You need to use a Calendar table and insert the scheduled shifts in
advance for the known duration. You can take care of holidays,
re-scheduling, etc. with this approach.

You might want to read Rick Snodgrass' s book on Temporal queries in
SQL after you get thru a basic data modeling book and a few ISO
standards. Pretty much everything you did was fundamentally wrong.

[Erland Sommarskog]

(ma**********@hotmail.com) writes:

Here's a sample of the data I have:

SSN: 999999999 (first row)
MonthName: July
IMClinicDay: Monday PM
IMClinicDateLast: 07/01/05
IMClinicDateFirst: 09/01/05

SSN: 999999999 (nth row)
MonthName: September
IMClinicDay: Wednesday PM
IMClinicDateLast: 09/01/05
IMClinicDateFirst: 10/01/05

With a SELECT statement, I want to return all of the first row and only
the IMClinicDay (Wednesday PM) of the nth row using IMClinicDateFirst
in the first row to get this data.

Since your table definition did not include any information about keys,
I cannot be sure that this query works:

SELECT a.SSN, a.ResidentProgram, ..., b.IMClinicDay
FROM tblResidentRotations a
LEFT JOIN b tblResidentRotations
ON a.SSN = b.SSN
AND a.IMClinicDateLast = b.IMClinicDateFirst
WHERE a.IMClinicDateFirst >= @yearmonth + '01' AND
a.IMClincDateFirst < dateadd(MONTH, 1, @yearmonth + '01')

I assume that @yearmonth holds the month you are looking for on the
form YYYYMM.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp