JOINs problem

I work for a hospital network (three different hospitals involved), and
I need to generate a result set containing ever combination of
specialty and department applicable for each doctor. The base tables
are as follows...
CREATE TABLE Hospitals
(
HospitalID VARCHAR(10)
)
CREATE TABLE Specialties
(
DoctorID VARCHAR(10),
SpecialtyID VARCHAR(10),
)
CREATE TABLE Departments
(
DoctorID VARCHAR(10),
HospitalID, VARCHAR(10),
DepartmentID VARCHAR(10)
)
INSERT Hospitals VALUES ('HOSP1')
INSERT Hospitals VALUES ('HOSP2')
INSERT Hospitals VALUES ('HOSP3')
INSERT Specialties VALUES ('JONES1', 'CARDIO1')
INSERT Departments VALUES ('JONES1', 'HOSP1', 'CARVASSUR1')
INSERT Departments VALUES ('JONES1', 'HOSP3', 'VASSUR1')
Here's the kicker: specialties apply to all hospitals, while
departments are hospital-specific. Therefore, for Dr. Jones the
following should be produced...
DoctorID HospitalID Specialty DepartmentID
JONES1 HOSP1 CARDIO1 CARVASSUR1
JONES1 HOSP2 CARDIO1 null
JONES1 HOSP3 CARDIO1 VASSURG1
...but if no specialty record existed for Dr. Jones, only the two
relevant hospitals would be represented...
DoctorID HospitalID Specialty DepartmentID
JONES1 HOSP1 null CARVASSUR1
JONES1 HOSP3 null VASSURG1
What would be the simplest way to accomplish this result?how about:
SELECT *
FROM Hospitals as h
LEFT JOIN Departments as d on d.HospitalID = h.HospitalID
LEFT JOIN Specialties as s on s.DoctorID = d.DoctorID
WHERE NOT (s.DoctorID is null AND d.DoctorID is null)|||That would work *except* that where there is no specialty, I only want
a record for each hospital that has a related department record.|||so when you run my example, what output is it that's wrong, could you
give an example input, output, and desired output.|||Well, after discussing it with the client further, it appears that they
*would* like to see a record for each hospital, so your suggestion will
work for me after all. Thank you very much for your time.|||Glad it's all sorted, but I'm still pretty sure that the result will
actually show as you originally specified. You see if there's no
speciality, and no dept in the hospital, both these clauses will be
null, so it will be filtered by that where clause.
Anyway, as long as it's working, it's - just check that example
before you use it.
Cheers
Will

JOIN problem with time involved

I'm starting with:

dtTimeIn bStatus

1899-12-30 12:00:00 0
1899-12-30 12:00:01 0
1899-12-30 12:00:02 0
1899-12-30 12:00:03 1
1899-12-30 12:00:04 1
1899-12-30 12:00:05 0
1899-12-30 12:00:06 0
1899-12-30 12:00:07 1

I'm trying to get to:

dtTimeStart dtTimeFinish bStatus

1899-12-30 12:00:00 1899-12-30 12:00:02 0
1899-12-30 12:00:03 1899-12-30 12:00:04 1
1899-12-30 12:00:05 1899-12-30 12:00:06 0
1899-12-30 12:00:07 2005-09-09 22:00:00 1

Hi,

what is the logic with which you want to create the second table?|||

I'm trying to group the 0's and 1's but still maintain the sequence of 0's and 1's.

Ex: For the first three seconds it's off then, for two seconds it's on, then for....

|||The query below should get what you want. There are some assumptions in the query that you need to modify based on your data/requirements.

with tt_seq
as
(
select t1.dtTimeIn, t1.bStatus,
coalesce((select dateadd(second, -1, min(t2.dtTimeIn))
from tt as t2
where t2.dtTimeIn > t1.dtTimeIn
and t2.bStatus = case t1.bStatus
when 0 then 1
else 0
end), t1.dtTimeIn) as next_dtTimeIn
from tt as t1
)
select min(t.dtTimeIn) as dtTimeStart, max(t.dtTimeIn) as dtTimeEnd, t.bStatus
from tt_seq as t
group by t.next_dtTimeIn, t.bStatus
order by t.next_dtTimeIn;

|||

Thanks for the help! Took me awhile to figure what you did.

Nicely done.

|||Well done!
I hope i would act as you one day.|||I've tried looking up the syntax for "with <tablename> as" in BOL 2000. Can't find anything.

Does anyone know of a keyword I can use to find out more about this syntax?

Thx.|||WITH is CTE syntax, new in SQL Server 2005. You can use a derived table in SQL Server 2000 to do the same.

select min(t.dtTimeIn) as dtTimeStart, max(t.dtTimeIn) as dtTimeEnd, t.bStatus
from
(
select t1.dtTimeIn, t1.bStatus,
coalesce((select dateadd(second, -1, min(t2.dtTimeIn))
from tt as t2
where t2.dtTimeIn > t1.dtTimeIn
and t2.bStatus = case t1.bStatus
when 0 then 1
else 0
end), t1.dtTimeIn) as next_dtTimeIn
from tt as t1
) as t
group by t.next_dtTimeIn, t.bStatus
order by t.next_dtTimeIn;|||This is a SQL Server 2005 feature. See the following entry in SQL Server 2005 BOL for more info: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/27cfb819-3e8d-4274-8bbe-cbbe4d9c2e23.htm -- Adam MachanicSQL Server MVPhttp://www.datamanipulation.net-- <Bullfrog@.discussions.microsoft.com> wrote in message news:2a2f39de-068c-4a7b-b71f-38961a9b5b69@.discussions.microsoft.com...I've tried looking up the syntax for "with <tablename> as" in BOL 2000. Can't find anything. Does anyone know of a keyword I can use to find out more about this syntax?Thx.