Complicating Query

In my Database I have a table called BEDTRANSFERS .It has following
fields in it -:
BEDID IPID FROMDATETIME TODATETIME OPERATORID
Initially when a patient is admitted, application inserts a row in it
(for example) -:
BEDID IPID FROMDATETIME TODATETIME OPERATORID
4 10 1/14/2005 1:43:19p.m null 116
Now when a patient is transferred from that BEDID (4) to another BEDID
(5).
Application inserts a row in that table with same IPID and with
different BEDID and
Always the FROMDATETIME of inserted row is same as that of TODATETIME.
For example table will look as below -:
BEDID IPID FROMDATETIME TODATETIME OPERATORID
4 10 1/14/2005 1:43:19p.m 1/15/2005 2:15:09p.m 116
5 10 1/15/2005 2:15:09p.m null 220
Now I need a SQL QUERY, which will help me to get a report of Patient
Transfers in
following format -:
IPID FROMBED TOBED OPERATORID DATE
10 4 5 220 1/15/2005 2:15:09p.m
Operator 220 has transferred him from 4 to 5 on 1/15/2005 2:15:09p.m.
NOTE -: IPID is a Patient Unique Id
Please I need a help to write this query.You didn't specify the primary key but here are my assumptions:
CREATE TABLE bed_transfers (bedid INTEGER NOT NULL, ipid INTEGER NOT NULL,
fromdatetime DATETIME NOT NULL, todatetime DATETIME NULL, operatorid INTEGER
NOT NULL, PRIMARY KEY (bedid,fromdatetime), UNIQUE(ipid,fromdatetime))
Try this:
SELECT B1.ipid, B1.bedid AS from_bed, B2.bedid AS to_bed,
B2.operatorid, B1.todatetime AS [date]
FROM bed_transfers AS B1
JOIN bed_transfers AS B2
ON B1.ipid = B2.ipid
AND B1.todatetime = B2.fromdatetime
--
David Portas
SQL Server MVP
--|||hi david,
thanks for the reply.but this query didnt work ,its retreving data
properly but all the records are not captured.
I cross verified(for one operator) whether their is any null values in
it.
But their was no null values in that.
But their is null value in my table bedtransfers(field called
todatetime).i have to retrieve that data also
any help will be of great ueful to me.
David Portas wrote:
> You didn't specify the primary key but here are my assumptions:
> CREATE TABLE bed_transfers (bedid INTEGER NOT NULL, ipid INTEGER NOT
NULL,
> fromdatetime DATETIME NOT NULL, todatetime DATETIME NULL, operatorid
INTEGER
> NOT NULL, PRIMARY KEY (bedid,fromdatetime),
UNIQUE(ipid,fromdatetime))
> Try this:
> SELECT B1.ipid, B1.bedid AS from_bed, B2.bedid AS to_bed,
> B2.operatorid, B1.todatetime AS [date]
> FROM bed_transfers AS B1
> JOIN bed_transfers AS B2
> ON B1.ipid = B2.ipid
> AND B1.todatetime = B2.fromdatetime
> --
> David Portas
> SQL Server MVP
> --