I have have three tables that I need to pull information from, and
return selected rows. Sorry for the psudeo-code for the tables, but it
should give you an idea of their (simplified) structure:
Inspections (
InspectionID int (PK),
LocationID int (FK),
InspectorID int (FK),
InspectionDate datetime
)
Inspectors (
InspectorID int (PK)
InspectorName varchar(50)
)
Location (
LocationID int (PK)
LocationName varchar(50)
)
Here's the data I need displayed as it would be shown in a "flat"
return:
SELECT
p.InspectorName,
l.LocationName,
i.InspectionDate
FROM
Inspectors p
LEFT OUTER JOIN Inspections i ON p.InspectorID = i.InspectorID
LEFT OUTER JOIN Location l ON i.LocationID = l.LocationID
WHERE CONVERT(CHAR(10), i.InspectionDate, 101) = CONVERT(CHAR(10),
'02/09/2006', 101)
ORDER BY p.InspectorName, LastUpdate
would return results:
Joe Inspector DEARBORN 2006-02-09 10:00:07
Joe Inspector DEARBORN 2006-02-09 10:10:04
Joe Inspector DEARBORN 2006-02-09 10:19:19
John Smith ANN ARBOR 2006-02-09 14:20:35
John Smith DEXTER 2006-02-09 14:21:38
Jane Doe CLINTON 2006-02-09 11:40:49
Jane Doe MOUNT CLEMENS 2006-02-09 11:54:07
Now, this is what I actually need: I need ONLY the first row (ie,
earliest time of inspection) for EACH inspector, regardless of
location. So my result set would look like:
Joe Inspector DEARBORN 2006-02-09 10:00:07
John Smith ANN ARBOR 2006-02-09 14:20:35
Jane Doe CLINTON 2006-02-09 11:40:49
Another Guy <NULL> <NULL>
Also, if the inspector has no inspections for that day, I would still
like to see the inspector name (note last row)
I'll be passing in the date (not the time) as a parameter.
I have tried using a nested SQL statement with an aggreagate MIN on the
InspectionTime, but I can't seem to get just the FIRST row for each
inspector to display - it will just give me every inspection for that
inspector.
Any help or insight will be greatly appreciated.
Christiani think this will work...
SELECT
p.InspectorName,
l.LocationName,
i.InspectionDate
FROM
Inspectors p
LEFT OUTER JOIN Inspections i ON p.InspectorID = i.InspectorID
LEFT OUTER JOIN Location l ON i.LocationID = l.LocationID
where inspectiondate = (select min(inspectiondate) from inspections
where CONVERT(CHAR(10), i.InspectionDate, 101) = CONVERT(CHAR(10),
'02/09/2006', 101) group by inspectorid)
ORDER BY p.InspectorName, LastUpdate
post DDL and insert statement for more clear solutions|||SELECT p.InspectorName, ISNULL(l.LocationName, 'No Inspection Today'),
ISNULL(CONVERT(char(20), t1."EarliestInspection", 108), 'No Inspection Today
')
FROM Inspectors P
LEFT JOIN
(
SELECT InspectorId, LocationID, MIN(InspectionDate) AS "EarliestInspection"
FROM Inspections
WHERE DATEDIFF(dd, InspectionDate, '20060209') = 0
GROUP BY InspectorName, LocationID
) t1
ON p.InspectorId = t1.InspectorId
INNER JOIN Location l ON t1.LocationId = l.LocationID
ORDER BY p.InspectorName, t1.EaliestInspection
"kaczmar2@.hotmail.com" wrote:
> I have have three tables that I need to pull information from, and
> return selected rows. Sorry for the psudeo-code for the tables, but it
> should give you an idea of their (simplified) structure:
> Inspections (
> InspectionID int (PK),
> LocationID int (FK),
> InspectorID int (FK),
> InspectionDate datetime
> )
> Inspectors (
> InspectorID int (PK)
> InspectorName varchar(50)
> )
> Location (
> LocationID int (PK)
> LocationName varchar(50)
> )
> Here's the data I need displayed as it would be shown in a "flat"
> return:
> SELECT
> p.InspectorName,
> l.LocationName,
> i.InspectionDate
> FROM
> Inspectors p
> LEFT OUTER JOIN Inspections i ON p.InspectorID = i.InspectorID
> LEFT OUTER JOIN Location l ON i.LocationID = l.LocationID
> WHERE CONVERT(CHAR(10), i.InspectionDate, 101) = CONVERT(CHAR(10),
> '02/09/2006', 101)
> ORDER BY p.InspectorName, LastUpdate
> would return results:
> Joe Inspector DEARBORN 2006-02-09 10:00:07
> Joe Inspector DEARBORN 2006-02-09 10:10:04
> Joe Inspector DEARBORN 2006-02-09 10:19:19
> John Smith ANN ARBOR 2006-02-09 14:20:35
> John Smith DEXTER 2006-02-09 14:21:38
> Jane Doe CLINTON 2006-02-09 11:40:49
> Jane Doe MOUNT CLEMENS 2006-02-09 11:54:07
> Now, this is what I actually need: I need ONLY the first row (ie,
> earliest time of inspection) for EACH inspector, regardless of
> location. So my result set would look like:
> Joe Inspector DEARBORN 2006-02-09 10:00:07
> John Smith ANN ARBOR 2006-02-09 14:20:35
> Jane Doe CLINTON 2006-02-09 11:40:49
> Another Guy <NULL> <NULL>
> Also, if the inspector has no inspections for that day, I would still
> like to see the inspector name (note last row)
> I'll be passing in the date (not the time) as a parameter.
> I have tried using a nested SQL statement with an aggreagate MIN on the
> InspectionTime, but I can't seem to get just the FIRST row for each
> inspector to display - it will just give me every inspection for that
> inspector.
> Any help or insight will be greatly appreciated.
> Christian
>|||Sorry, that last JOIN should be LEFT, not INNER.
--
"Mark Williams" wrote:
> SELECT p.InspectorName, ISNULL(l.LocationName, 'No Inspection Today'),
> ISNULL(CONVERT(char(20), t1."EarliestInspection", 108), 'No Inspection Tod
ay')
> FROM Inspectors P
> LEFT JOIN
> (
> SELECT InspectorId, LocationID, MIN(InspectionDate) AS "EarliestInspection
"
> FROM Inspections
> WHERE DATEDIFF(dd, InspectionDate, '20060209') = 0
> GROUP BY InspectorName, LocationID
> ) t1
> ON p.InspectorId = t1.InspectorId
> INNER JOIN Location l ON t1.LocationId = l.LocationID
> ORDER BY p.InspectorName, t1.EaliestInspection
> --
> "kaczmar2@.hotmail.com" wrote:
>|||Thank you very much for your feedback. This gets me what I want except
for one thing: It shows the earliest time for each location. I want
to show the earliest time for each inspector regardless of location,
but I do want to see the lcoation in the result set. So I can't group
by location. This is your result set:
Tony Inspector ANN ARBOR 16:13:40
Tony Inspector YPSILANTI 17:19:08
Joe Schmoe PLAINWELL 13:12:39
Jane Doe GRAND RAPIDS 11:42:27
Jane Doe GRANDVILLE 12:48:00
Any ideas on how to get the earliest time regardless of location?
Thank you for your continued help.
Mark Williams wrote:
> Sorry, that last JOIN should be LEFT, not INNER.
> --
>
> "Mark Williams" wrote:
>|||Terribly sorry,
SELECT p.InspectorName, ISNULL(t3.LocationName, 'No Inspection Today'),
ISNULL(CONVERT(char(20), t3."EarliestInspection", 108), 'No Inspection Today
')
FROM Inspectors P
LEFT JOIN
(
SELECT t1.InspectorId, t1.EarliestInspection, t2.LocationName
FROM
(
SELECT InspectorId, MIN(InspectionDate) AS "EarliestInspection"
FROM Inspections
WHERE DATEDIFF(dd, InspectionDate, '20060209') = 0
GROUP BY InspectorName
) t1
INNER JOIN
(SELECT i.LocationId, l.LocationName FROM Inspections i
INNER JOIN Locations l ON i.LocationId = l.LocationId) t2
ON t1.InspectorId = t2.InspectorId AND t1.EarliestInspection =
t2.InspectionDate
) t3
ON t3.InspectorId = p.InspectorId
"kaczmar2@.hotmail.com" wrote:
> Thank you very much for your feedback. This gets me what I want except
> for one thing: It shows the earliest time for each location. I want
> to show the earliest time for each inspector regardless of location,
> but I do want to see the lcoation in the result set. So I can't group
> by location. This is your result set:
> Tony Inspector ANN ARBOR 16:13:40
> Tony Inspector YPSILANTI 17:19:08
> Joe Schmoe PLAINWELL 13:12:39
> Jane Doe GRAND RAPIDS 11:42:27
> Jane Doe GRANDVILLE 12:48:00
> Any ideas on how to get the earliest time regardless of location?
> Thank you for your continued help.
>
> Mark Williams wrote:
>|||Thank you for the reply. I mad to modify the query since your subquery
returns more than one value. "where inspectiondate = (.." was changed
to "where inspectiondate IN (.."
This looks to give me coreect results except for those inspectors that
did not work that day. I would like to show them in the results with
NULL data.
I have attached the DDL and INSERT statements as requested:
CREATE TABLE [dbo].[Inspections] (
[InspectionID] [int] NOT NULL ,
[LocationID] [int] NOT NULL ,
[InspectorID] [int] NOT NULL ,
[InspectionDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Inspectors] (
[InspectorID] [int] NOT NULL ,
[InspectorName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Location] (
[LocationID] [int] NOT NULL ,
[LocationName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Inspections] ADD
CONSTRAINT [PK_Inspections] PRIMARY KEY CLUSTERED
(
[InspectionID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Inspectors] ADD
CONSTRAINT [PK_Inspectors] PRIMARY KEY CLUSTERED
(
[InspectorID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Location] ADD
CONSTRAINT [PK_Location] PRIMARY KEY CLUSTERED
(
[LocationID]
) ON [PRIMARY]
GO
INSERT INTO Inspectors(InspectorID,InspectorName)
VALUES(1,'Joe Inspector')
INSERT INTO Inspectors(InspectorID,InspectorName)
VALUES(2,'Jane Doe')
INSERT INTO Inspectors(InspectorID,InspectorName)
VALUES(3,'John Smith')
INSERT INTO Inspectors(InspectorID,InspectorName)
VALUES(4,'New Guy')
INSERT INTO Location(LocationID,LocationName)
VALUES(1,'Detroit')
INSERT INTO Location(LocationID,LocationName)
VALUES(2,'Ann Arbor')
INSERT INTO Location(LocationID,LocationName)
VALUES(3,'Royal Oak')
INSERT INTO Location(LocationID,LocationName)
VALUES(4,'Monroe')
INSERT INTO Location(LocationID,LocationName)
VALUES(5,'Dearborn')
INSERT INTO
Inspections(InspectionID,LocationID,Insp
ectorID,InspectionDate)
VALUES(1,1,1,'2/9/2006 9:10 AM')
INSERT INTO
Inspections(InspectionID,LocationID,Insp
ectorID,InspectionDate)
VALUES(2,1,1,'2/9/2006 10:15 AM')
INSERT INTO
Inspections(InspectionID,LocationID,Insp
ectorID,InspectionDate)
VALUES(3,2,2,'2/9/2006 7:30 AM')
INSERT INTO
Inspections(InspectionID,LocationID,Insp
ectorID,InspectionDate)
VALUES(4,3,2,'2/9/2006 11:00 AM')
INSERT INTO
Inspections(InspectionID,LocationID,Insp
ectorID,InspectionDate)
VALUES(5,4,3,'2/9/2006 2:00 PM')
INSERT INTO
Inspections(InspectionID,LocationID,Insp
ectorID,InspectionDate)
VALUES(6,5,3,'2/9/2006 1:00 PM')
Thank you very much for your help.|||If you have the proper constraints on your table, you will almost NEVER
use CONVERT()) in a query. Why keep mopping the floor and losing the
ability to use indexes when a simple CHECK() can trim off the time part
of a DATETIME? Fix the leak!
You might also want to learn about ISO-8601 and the SQL Standards for
temporal data, just in case you need to use ISO Standards some day :))
After all, you are one of the few posters lately who actually followed
the ISO-11179 naming conventions!
Your origianl query looks useful in itself, so you might put it in a
VIEW, but this will give you what you asked for
SELECT inspector_name, location_name, MIN(inspection_date)
-- , MAX(inspection_date) could be useful, too!
FROM (SELECT P.inspector_name, L.location_name, I.inspection_date
FROM Inspectors AS P
LEFT OUTER JOIN
Inspections AS I
ON P.inspector_id = I.inspector_id
LEFT OUTER JOIN
Locations AS L
ON I.location_id = L.Location_id
WHERE I.inspection_date '2006-02-09')
AS X(inspector_name, location_name, inspection_date)
GROUP BY X.inspector_name, X.location_name;|||Mark-
Thank you very much, this worked! I just had to tweak one of the
joins. Here is the cleaned up, final query:
SELECT
p.InspectorName,
ISNULL(t3.LocationName, 'No Inspection Today'),
ISNULL(CONVERT(char(20), t3."EarliestInspection", 108), 'No Inspection
Today')
FROM Inspectors p
LEFT JOIN
(
SELECT t1.InspectorId, t1.EarliestInspection, t2.LocationName
FROM
(
SELECT i.InspectorId, MIN(i.InspectionDate) AS "EarliestInspection"
FROM Inspections i
WHERE DATEDIFF(dd, i.InspectionDate, '20060209') = 0
GROUP BY i.InspectorId /* name */
) t1
INNER JOIN
(
SELECT i.LocationId, l.LocationName, i.InspectionDate, i.InspectorID
-- added
FROM Inspections i
INNER JOIN Location l ON i.LocationId = l.LocationId
) t2
ON t1.InspectorId = t2.InspectorId AND t1.EarliestInspection =
t2.InspectionDate
) t3
ON t3.InspectorId = p.InspectorId
Thanks again for your help!!!|||CELKO-
Thank you for your input. I probably did not explain my complete
intentions when you saw the CONVERT function used to strip of the time.
I do need the time information, but I need to group by the day for
this query, that is why I was using convert. And yes, I probably
should be aware of my naming conventions/syntax. Note that I posted
abriged tables/data/queries to simplify the example to the group.sql
Showing posts with label pull. Show all posts
Showing posts with label pull. Show all posts
Wednesday, March 21, 2012
Nested SQL or IF END LOGIC ?
Below is a sample data set. Each episode consists of several unique chart_component_key. I need to be able to pull the MAX status_date for the episode, but only if all deficiency_status is equal to 'C', which signifies that the entire episode has been completed. I thought either nested select statements or if/end logic might work but I am stuck.
Can someone help me here?
Chuck,
You problem and sample data are a bit ambiguous.
It appears that all of this data belongs to one Episode -since there is but one Episode_key value.
Every Chart_Component-key appears to be unique.
So what is the expected result from this set of data?
|||This should do it for you
Code Snippet
DELETED - read edit
Edit : I re-read the question and realized you said "all deficiency_status is equal to 'C'" not just that one of the records in the episdoe is set to 'C'.
|||
This one makes sure that ALL the definciency code fields in the recordset of one episode are 'C'
Code Snippet
CREATE Table episode_table (
episode_key INT,
chart_component_key INT,
deficiency_type INT,
deficiency_status CHAR,
status_date DATETIME
)
INSERT INTO episode_table VALUES (13789881, 173398, 408, 'C' ,'8/4/2007')
INSERT INTO episode_table VALUES (13789881, 173488, 409, 'S', '8/4/2007' )
INSERT INTO episode_table VALUES (13789881, 173703, 409, 'S', '8/6/2007' )
INSERT INTO episode_table VALUES (13789881, 174568, 1028, 'S', '8/7/2007' )
INSERT INTO episode_table VALUES (13789881, 176213, 421, 'S', '8/9/2007' )
INSERT INTO episode_table VALUES (13789881, 176214, 421, 'S', '8/9/2007' )
INSERT INTO episode_table VALUES (13789881, 176215, 421, 'S', '8/9/2007' )
INSERT INTO episode_table VALUES (13789881, 176216, 421, 'S', '8/9/2007' )
INSERT INTO episode_table VALUES (13789881, 176218, 421, 'S', '8/9/2007' )
INSERT INTO episode_table VALUES (13789881, 176219, 406, 'D', '8/9/2007' )
INSERT INTO episode_table VALUES (13789882, 173398, 408, 'C' ,'8/4/2007')
INSERT INTO episode_table VALUES (13789882, 173488, 409, 'S', '8/4/2007' )
INSERT INTO episode_table VALUES (13789882, 173703, 409, 'S', '8/6/2007' )
INSERT INTO episode_table VALUES (13789882, 174568, 1028, 'S', '8/7/2007' )
INSERT INTO episode_table VALUES (13789882, 176213, 421, 'S', '8/9/2007' )
INSERT INTO episode_table VALUES (13789882, 176214, 421, 'S', '8/9/2007' )
INSERT INTO episode_table VALUES (13789882, 176215, 421, 'S', '8/9/2007' )
INSERT INTO episode_table VALUES (13789882, 176216, 421, 'S', '8/9/2007' )
INSERT INTO episode_table VALUES (13789882, 176218, 421, 'S', '8/9/2007' )
INSERT INTO episode_table VALUES (13789882, 176219, 406, 'D', '8/12/2007' )
INSERT INTO episode_table VALUES (13789883, 173398, 408, 'C' ,'8/4/2007')
INSERT INTO episode_table VALUES (13789883, 173488, 409, 'C', '8/4/2007' )
INSERT INTO episode_table VALUES (13789883, 176219, 406, 'C', '8/12/2007' )
SELECT et1.episode_key, MAX(et1.status_date)
FROM episode_table et1 LEFT OUTER JOIN episode_table et2 ON
-- You only have to include the unique primary key fields here, i didn't want to assume
-- that you had one though
(et1.episode_key = et2.episode_key AND et1.chart_component_key = et2.chart_component_key AND
et1.deficiency_type = et2.deficiency_type AND et1.deficiency_status = et2.deficiency_status
AND et1.status_date = et2.status_date AND et2.deficiency_status = 'C')
GROUP by et1.episode_key
HAVING COUNT(et1.episode_key) = COUNT(et2.episode_key)
I joined the tables on all fields to be sure, but you only need to include the fields in the primary key if there is one, and the deficiency status clause.|||Each patient episode may involve several physicians involved in their case and making notes in the chart. Each chart_component_key should signify a different physician making notes in the chart. A "completed" chart is defined as when all physicians have completed their charting thus reflecting a "C" in the deficiency_status fields.
I need to return a "Completed Chart Date" which would mean that all chart_component_keys would be "C" and I would then take the MAX(status_date) as the completed chart date.
|||Thanks Shawn. I was able to modify this and make it work. This piece of code was to pull the final field in a report I am working on. I am trying to plug it into my main SQL code for the big report, but having difficulties. I'll start a new thread for this issue.
Thanks!
|||
You can use the query below:
Code Snippet
select t.episode_key, max(t.status_date) as completed_chart_datefrom <your_table> as t
group by t.episode_key
having count(*) = sum(case t.deficiency_status when 'C' then 1 end); |||
Very nice Umachandar!
I have to remember that approach, very efficient.
Subscribe to:
Posts (Atom)