Help with SELECT please

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

CREATE TABLE [dbo].& #91;tblResidentRotat
ions] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[SSN] [varchar] (9) COLLATE  SQL_Latin1_General_C
P1_CI_AS NOT NULL ,
& #91;ResidentProgram]
 [varchar] (50) COLLATE  SQL_Latin1_General_C
P1_CI_A
S
NULL ,
[RotationID] [int] NULL ,
[MonthName] [varchar] (15) COLLATE  SQL_Latin1_General_C
P1_CI_AS NULL
 ,
& #91;RotationLocation
ID] [int] NULL ,
[CallLocationID] [int] NULL ,
[IMClinicDay] [varchar] (15) COLLATE  SQL_Latin1_General_C
P1_CI_AS NU
LL
,
& #91;IMClinicDateLast
] [datetime] NULL ,
& #91;IMClinicDateFirs
t] [datetime] NULL ,
[PedsClinicDay] [varchar] (15) COLLATE  SQL_Latin1_General_C
P1_CI_AS
NULL ,
[Comments] [varchar] (50) COLLATE  SQL_Latin1_General_C
P1_CI_AS NULL 
,
& #91;ClinicScheduleCo
mments] [varchar] (50) COLLATE
 SQL_Latin1_General_C
P1_CI_AS NULL ,
& #91;LastFirstComment
s] [varchar] (50) COLLATE
 SQL_Latin1_General_C
P1_CI_AS NULL ,
[PGYLevel] [varchar] (1) COLLATE  SQL_Latin1_General_C
P1_CI_AS NULL ,
[AcademicYear] [varchar] (20) COLLATE  SQL_Latin1_General_C
P1_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.

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_n
br),
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_n
br),
PRIMARY KEY (ssn,  rotation_start_time)
);
 

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.

(manning_news@hotmai
l.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, esquel@sommarskog.se

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

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:
>  (manning_news@hotmai
l.com) writes: 
>
> 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.
> 
>
> If I understand this correctly, you should have a look at the datename()
> function in Books Online.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

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_n
br),
>  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_n
br),
>  PRIMARY KEY (ssn,  rotation_start_time)
);
> 
>
> 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.

(manning_news@hotmai
l.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, esquel@sommarskog.se

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