Stored Procedure with mulitple inserts into single table 1

[glthornton]

Hi everyone, (DB Info: SQL Server 2000). I'm currently working on a project for our hospital to participate in our states RHIO. What I need to do is create sql queries that will check to see if certain values (BP, Height, Weight, etc.) has been added to a patient record within the past 24 hours. There queries are all written, but what I need help with is to create a stored procedure that will store the patient's MRN and Visit ID in a single table. So for example, I have a Blood Pressure query that returns the MRN and VISITID. I want to store it in the following table (dbo.vtrhio_daily). So the BP data into this table will look like this:

[b][u]vtrhio_daily[/u][/b]
MRN     VISITID
1234    33123
2345    33234
3456    33345
4567    33456

Now I need to run say the Height query. I'm going to extract the same data fields as the BP query and I want to add my results into the same table where the MRN and VISITID do not already exist. So for example, if my Height query as a standalone returns this:

[b][u]HEIGHT[/u][/b]
MRN     VISITID
1234    33123
2345    33234
5678    33678
6789    33789

As you can see the first 2 rows already exist and I only need to add the last 2 rows. Which would now look like this:

[b][u]vtrhio_daily[/u][/b]
MRN     VISITID
1234    33123
2345    33234
3456    33345
4567    33456
5678    33678
6789    33789

How would I do this in a stored procedure? I thought I could use and WHERE NOT EXISTS command, but I'm not a DBA.

Kind regards, Glenn

 

[gmmastros]

Use UNION.

Ex:

Select MRN, VisitID
From   BloodPressureTable
Where  -- Whatever

UNION

Select MRN, VisitId
From   HeightTable
Where  -- Whatever

UNION

Select MRN, VisitId
From   WeightTable
Where  -- Whatever

To effectively use a union query, each query that participates in the union MUST return the same number of columns AND the columns must have the same data type.

There is also a UNION ALL statement. The difference between UNION and UNION ALL is that UNION will only return distinct records where UNION ALL will return duplicates (if they exist). For your query... you'll want to use UNION.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[glthornton]

Thank you George. I do understand the UNION and UNION ALL syntax, but let me provide you with some more examples. Here is my Weight query:

SELECT vst.patid, vst.vid
INTO dbo.vtrhio_daily
FROM
Visits vst INNER JOIN checktext ckt ON vst.VID = ckt.VID INNER JOIN Checkmark ckm INNER JOIN Checkdata  ckd ON ckm.CID = ckd.CID ON ckt.DataID = ckd.DataID INNER JOIN details dtl ON ckd.DID = dtl.DID
WHERE
ckm.Name Like 'Vitals%' AND dtl.name Like 'Weight (lbs):%' AND CONVERT(CHAR(10),vst.VisitCreatedOn,110) = CONVERT(CHAR(10),getDate()-1,110)

And here is my BMI query:

SELECT vst.patid, vst.vid 
INTO dbo.vtrhio_daily
FROM
Visits vst INNER JOIN checktext ckt ON vst.VID = ckt.VID INNER JOIN Checkmark ckm INNER JOIN Checkdata  ckd ON ckm.CID = ckd.CID ON ckt.DataID = ckd.DataID INNER JOIN details dtl ON ckd.DID = dtl.DID
WHERE
ckm.Name Like 'Vitals%' AND dtl.name Like 'Body Mass Index (BMI):%' AND CONVERT(CHAR(10),vst.VisitCreatedOn,110) = CONVERT(CHAR(10),getDate()-1,110)

But my question is should I use a DISTINCT clause to keep things more precise or just let the UNION clause handle it like this?:

SELECT vst.patid, vst.vid
INTO dbo.vtrhio_daily
FROM
Visits vst INNER JOIN checktext ckt ON vst.VID = ckt.VID INNER JOIN Checkmark ckm INNER JOIN Checkdata  ckd ON ckm.CID = ckd.CID ON ckt.DataID = ckd.DataID INNER JOIN details dtl ON ckd.DID = dtl.DID
WHERE
ckm.Name Like 'Vitals%' AND dtl.name Like 'Weight (lbs):%' AND CONVERT(CHAR(10),vst.VisitCreatedOn,110) = CONVERT(CHAR(10),getDate()-1,110)

UNION
SELECT vst.patid, vst.vid 
INTO dbo.vtrhio_daily
FROM
Visits vst INNER JOIN checktext ckt ON vst.VID = ckt.VID INNER JOIN Checkmark ckm INNER JOIN Checkdata  ckd ON ckm.CID = ckd.CID ON ckt.DataID = ckd.DataID INNER JOIN details dtl ON ckd.DID = dtl.DID
WHERE
ckm.Name Like 'Vitals%' AND dtl.name Like 'Body Mass Index (BMI):%' AND CONVERT(CHAR(10),vst.VisitCreatedOn,110) = CONVERT(CHAR(10),getDate()-1,110)

Kind regards, Glenn

 

[gmmastros]

If it were my query, I would just let the union handle that for you. In terms of performance, distinct is a relatively 'expensive' operation. The least often you do it, the better.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[glthornton]

This will work great!!

Thanks George!

 

[gmmastros]

Do you mind if I comment on other aspects of your query?

Specifically, the way you are filtering your dates can be inefficient. I suspect that you are getting the correct results, so that's not an issue. What I am referring to is performance.

There's a concept called 'sargable' that you should know about. Your queries are not sargable, which could mean poor performance. Now, you might be thinking that your performance is pretty good, so why worry. To that I would respond with... what's gonna happen to your performance when you add a lot more records?

The problem here is based on index usage.

[tt][blue]
AND CONVERT(CHAR(10),vst.VisitCreatedOn,110) = CONVERT(CHAR(10),getDate()-1,110)
[/blue][/tt]

When you have something like this in a where clause, your query will likely do an index scan, not a seek. Seeks are faster than scans. So, it is in your best interest to write queries that perform seeks.

In this case, in particular, I understand that you want to return all records for a particular day. There are many different ways to accomplish this. Obviously, the way you wrote your query is one of them. But, there is another way you can write this such that index seeks are possible.

[tt][blue]
AND vst.VisitCreatedOn >= DateAdd(day, DateDiff(Day, 0, GetDate()-1), 0)
And vst.VisitCreatedOn < DateAdd(Day, DateDiff(Day, 0, GetDate()), 0)
[/blue][/tt]

So, let's examine this a little further.

DateDiff returns the difference between 2 dates. The first argument identifies the 'units' for the results.

Open query analyzer and run this:

Select DateDiff(Day, 0, GetDate())

Today (January 17, 2008), it will return 39462. This is because there has been 39,462 days Since Jan 1, 1900. The second argument (0), specifies the 0 date which is Jan 1, 1900. Notice that GetDate() returns a date and a time, but DateDiff returns only an integer. Now, if we add that number of days to Jan 1, 1900, we will get today's date, but at midnight (the moment the day begins).

Run this:

Select DateAdd(Day, DateDiff(Day, 0, GetDate()), 0)

You will get '2008-01-17 00:00:00.000'.

Now, thinking about your query... you want all records from yesterday, so....

[tt][blue]
Where YourDate >= '2008-01-16'
And YourDate < '2008-01-17'
[/blue][/tt]

That will return the correct records for your query. The code I showed you earlier in this post does exactly what you want, but more importantly, allows indexes on the column to be used.

Does this make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ca8msm]

Nice explanation George.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[glthornton]

This is exactly the kind of information that I needed to know. I cannot tell you how much I really appreciate your assistance on this!!

This was great stuff!!

 

[ESquared]

This may surprise you. Look at the execution plan and run a profiler statement on this query to see if it's any better than the UNION with a distinct. For what it's worth, I would go with the distinct over selecting from the same table twice. But even that's not necessary. Use a semi-join, which won't multiply rows and can stop looking as soon as one matching row is found. Even though there's a subquery in the WHERE clause below, the execution plan will show that a JOIN is being performed.

This is NOT a correlated subquery in the classic sense of the word, because EXISTS is special syntax that indicates the desire for a semi-join or an anti semi-join.

SELECT vst.patid, vst.vid 
INTO dbo.vtrhio_daily
FROM
   Visits vst
WHERE
   EXISTS (
      SELECT *
      FROM
         checktext ckt
         INNER JOIN Checkdata ckd ON ckt.DataID = ckd.DataID
         INNER JOIN Checkmark ckm ON ckm.CID = ckd.CID
         INNER JOIN details dtl ON ckd.DID = dtl.DID
      WHERE
         vst.VID = ckt.VID
         AND ckm.Name Like 'Vitals%'
         AND (
            dtl.name Like 'Body Mass Index (BMI):%'
            OR dtl.name Like 'Weight (lbs):%'
         ) AND vst.VisitCreatedOn >= DateDiff(dd, 0, GetDate()) - 1
         AND vst.VisitCreatedOn < DateDiff(dd, 0, GetDate())
   )

I put all the tables in the subquery because 1, you don't want duplicates and I don't know if there are 1-to-many relationships in the joined tables and 2, the real difference of a semi-join is that you are not returning values but simply checking for row existence and this accentuates that. This makes plain to the eyeballs looking at the query what the optimizer can also see: no columns from the other tables are being returned. If you needed to return columns from some of those tables, then they would need to be moved up into the main query.