Deriving records for insertion ...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Deriving records for insertion ...

Author

Topic

LaurieCox

158 Posts

Posted - 2010-08-16 : 15:10:00

oops ... I was too quick and didn't look at the results close enough. I didn't solve it (see fourth post)

I have come up with a solution on my own (see third post), but wouldn't mind some critique of my solution.

Hi,

(the ddl and sample data are at the end of the post).

I am trying to come up with a set based insert query that will insert rows into a table based on data from a couple of rows and determined by date order.

I am guaranteed that any given PATID/StartDate will have zero or one record marked as primary (PrimaryDx = 1) and I am further guaranteed the there will always be a PrimaryDx record for the first (minimum) StartDate for any given PATID.

I want every PATID/StartDate to have one (and only one) record that is marked as Primary. So, where such a row does not exist (for a given PATID/StartDate), I want to insert one. Using primary data (PrimaryDx and axis) from the most recent (but not later than) StartDate that has a primary dx (for the given PATID/StartDate).

There is one other twist the ProvId for the newly inserted records should be the same as the ProvId for StartDate that is being inserted. (I am also guaranteed that for any given PATID/StartDate there will only be one ProvId).

Now that I have completed confused everybody let me try to explain with a data example.

I have this data for PATID 202:


PATID   StartDate   ProvID      PrimaryDx   axis        code
------- ----------- ----------- ----------- ----------- ----------
202     2005-12-10  -1          1           2           317
202     2005-12-10  -1          0           1           V71.09
202     2005-12-10  -1          0           5           25
202     2005-12-10  -1          0           1           309.81
202     2005-12-10  -1          0           1           296.31

202     2008-04-15  2945        0           1           294.8a

202     2008-06-06  2945        0           1           312.34

202     2010-04-27  5454        0           1           298.8

There are four dates, but only the first date (2005-12-10) has a PrimaryDx. So I want to insert the following three records:


PATID   StartDate   ProvID      PrimaryDx   axis        code
------- ----------- ----------- ----------- ----------- ----------
202     2008-04-15  2945        1           2           317
202     2008-06-06  2945        1           2           317
202     2010-04-27  5454        1           2           317

Note I got the ProvId from the same record that I got the StartDate from for each inserted record.

Another example:


PATID   StartDate   ProvID      PrimaryDx   axis        code
------- ----------- ----------- ----------- ----------- ----------
33937   2008-09-25  4182        1           1           296.80
33937   2008-09-25  4182        0           1           296.23
33937   2008-09-25  4182        0           1           314.01a
33937   2008-09-25  4182        0           1           309.81
33937   2008-09-25  4182        0           1           300.00
33937   2008-09-25  4182        0           1           312.34

33937   2008-10-16  4182        0           1           313.89
33937   2008-10-16  4182        0           1           313.81

33937   2009-08-20  3187        1           1           333.33

33937   2009-12-08  3187        0           3           251.2

33937   2010-01-19  4444        0           1           995.53
33937   2010-01-19  4444        0           1           309.81

From these records I want to insert the following records:


PATID   StartDate   ProvID      PrimaryDx   axis        code
------- ----------- ----------- ----------- ----------- ----------
33937   2008-10-16  4182        1           1           296.80
33937   2009-12-08  3187        1           1           333.33
33937   2010-01-19  4444        1           1           333.33

The first inserted record takes the Primary data from 2008-09-25. The second two inserted records take the Primary data from 2009-08-20.

So I am going to try to work this out. So far I have gotten this far in my thinking:
I want to select the following fields (for insertion from two records already in the table):


Select a.PATID,
           a.StartDate,
           a.ProvID,
           p.PrimaryDx,
           p.Axis

The two records that I want to use are found somehow with some sort of query that meets these conditions:

where the a.* fields come from the records defined by PATID/StartDate where there is not already a record where PrimaryDx = 1

and the p.* fields come from the records where p.PrimaryDx = 1 and p.StartDate < a.StartDate

And it is all wrapped up in syntactically correct sql.

Thanks,

Laurie

Here's the table ddl and raw data from my examples above:


CREATE TABLE #TestData
	(PATID varchar(20) NULL,
	StartDate datetime NULL,
	ProvID int NULL,
	PrimaryDx int NULL,
	axis int NULL,
	code varchar(10) NULL)

Insert into #TestData
(PATID,StartDate,ProvId,primaryDx,axis,code)
SELECT '202','12/10/2005',-1,0,1,'309.81' UNION ALL
SELECT '202','12/10/2005',-1,0,1,'296.31' UNION ALL
SELECT '202','12/10/2005',-1,1,2,'317' UNION ALL
SELECT '202','12/10/2005',-1,0,1,'V71.09' UNION ALL
SELECT '202','12/10/2005',-1,0,5,'25' UNION ALL
SELECT '202','04/15/2008',2945,0,1,'294.8a' UNION ALL
SELECT '202','06/06/2008',2945,0,1,'312.34' UNION ALL
SELECT '202','04/27/2010',5454,0,1,'298.8' UNION ALL
SELECT '33937','08/20/2009',3187,1,1,'333.33' UNION ALL
SELECT '33937','12/08/2009',3187,0,3,'251.2' UNION ALL
SELECT '33937','01/19/2010',4444,0,1,'995.53' UNION ALL
SELECT '33937','01/19/2010',4444,0,1,'309.81' UNION ALL
SELECT '33937','09/25/2008',4182,0,1,'314.01a' UNION ALL
SELECT '33937','09/25/2008',4182,0,1,'309.81' UNION ALL
SELECT '33937','09/25/2008',4182,0,1,'300.00' UNION ALL
SELECT '33937','09/25/2008',4182,0,1,'312.34' UNION ALL
SELECT '33937','09/25/2008',4182,1,1,'296.80' UNION ALL
SELECT '33937','09/25/2008',4182,0,1,'296.23' UNION ALL
SELECT '33937','10/16/2008',4182,0,1,'313.89' UNION ALL
SELECT '33937','10/16/2008',4182,0,1,'313.81' 

select * from #TestData
order by PATID,StartDate,ProvId,PrimaryDx desc

LaurieCox

158 Posts

Posted - 2010-08-16 : 16:22:44

I have come up with the following code:


select a.patid,
       a.startdate,
       a.provid,
       p.PrimaryDx,
       p.axis,
       p.code
from (select distinct
             t1.Patid,
             t1.StartDate,
             t1.ProvId
        from #TestData t1
       where not exists (select t2.PATID, t2.StartDate, t2.ProvId
                           from #TestData t2
                          where t2.patid = t1.patid
                            and t2.StartDate = t1.StartDate 
                            and t2.ProvId = t1.ProvId
                            and PrimaryDx = 1)) a
     join
     (select t3.Patid,
             t3.StartDate,
             t3.ProvId,
             t3.PrimaryDx,
             t3.Axis,
             t3.code
        from #TestData t3
       where t3.PrimaryDx = 1) p on a.patid = p.patid

Which gives me this output:


patid       startdate   provid      PrimaryDx   axis        code
----------- ----------- ----------- ----------- ----------- ----------
202         2008-04-15  2945        1           2           317
202         2008-06-06  2945        1           2           317
202         2010-04-27  5454        1           2           317
33937       2008-10-16  4182        1           1           333.33
33937       2008-10-16  4182        1           1           296.80
33937       2009-12-08  3187        1           1           333.33
33937       2009-12-08  3187        1           1           296.80
33937       2010-01-19  4444        1           1           333.33
33937       2010-01-19  4444        1           1           296.80

So now I have to do some magic with the StartDate to eliminate the rows in red. And I am not sure how to do that.

Also is a good way of doing it or is there a better way?

Thanks,

Laurie

Edit: because my output didn't need two headers.
Edit2: because one of the red rows shouldn't have been red.

LaurieCox

158 Posts

Posted - 2010-08-17 : 12:49:57

So I have solved my problem in three steps.


select distinct
       t1.Patid,
       t1.StartDate,
       t1.ProvId
  into #NeedRow
  from #TestData t1
 where not exists (select t2.PATID, t2.StartDate, t2.ProvId
                    from #TestData t2
                   where t2.patid = t1.patid
                     and t2.StartDate = t1.StartDate 
                     and t2.ProvId = t1.ProvId
                     and PrimaryDx = 1)

select Patid,
       StartDate,
       ProvId,
       PrimaryDx,
       Axis,
       code
 into #PrimaryRecords
 from #TestData 
where PrimaryDx = 1

select n.patid,
       n.startdate,
       n.provid,
       p.PrimaryDx,
       p.axis,
       p.code
  from #NeedRow n join #PrimaryRecords p
                  on n.patid = p.patid
                 and p.startdate = (select max(startdate)
                                      from #PrimaryRecords t
                                     where t.patid = p.patid
                                       and p.startdate < n.startdate)

I am now going to work on combining these into one big query. But I wouldn't mind some critique of my solution. Is this the optimal way or is there a better way to get the results I want?

Thanks,

Laurie

LaurieCox

158 Posts

Posted - 2010-08-17 : 13:04:43

oops this doesn't work

It gives me one less row than I want. It gives me this:


patid    startdate   provid      PrimaryDx   axis        code
-------- ----------- ----------- ----------- ----------- ----------
33937    2009-12-08  3187        1           1           333.33
33937    2010-01-19  4444        1           1           333.33
202      2008-04-15  2945        1           2           317
202      2008-06-06  2945        1           2           317
202      2010-04-27  5454        1           2           317

Which is missing this row:


patid       startdate   provid      PrimaryDx   axis        code
----------- ----------- ----------- ----------- ----------- ----------
33937       2008-10-16  4182        1           1           296.80

So I still need to work on it. Any help will be appreciated .

Thanks,

Laurie

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-17 : 13:39:08

[code]
CREATE TABLE #TestData
(PATID varchar(20) NULL,
StartDate datetime NULL,
ProvID int NULL,
PrimaryDx int NULL,
axis int NULL,
code varchar(10) NULL)

Insert into #TestData
(PATID,StartDate,ProvId,primaryDx,axis,code)
SELECT '202','12/10/2005',-1,0,1,'309.81' UNION ALL
SELECT '202','12/10/2005',-1,0,1,'296.31' UNION ALL
SELECT '202','12/10/2005',-1,1,2,'317' UNION ALL
SELECT '202','12/10/2005',-1,0,1,'V71.09' UNION ALL
SELECT '202','12/10/2005',-1,0,5,'25' UNION ALL
SELECT '202','04/15/2008',2945,0,1,'294.8a' UNION ALL
SELECT '202','06/06/2008',2945,0,1,'312.34' UNION ALL
SELECT '202','04/27/2010',5454,0,1,'298.8' UNION ALL
SELECT '33937','08/20/2009',3187,1,1,'333.33' UNION ALL
SELECT '33937','12/08/2009',3187,0,3,'251.2' UNION ALL
SELECT '33937','01/19/2010',4444,0,1,'995.53' UNION ALL
SELECT '33937','01/19/2010',4444,0,1,'309.81' UNION ALL
SELECT '33937','09/25/2008',4182,0,1,'314.01a' UNION ALL
SELECT '33937','09/25/2008',4182,0,1,'309.81' UNION ALL
SELECT '33937','09/25/2008',4182,0,1,'300.00' UNION ALL
SELECT '33937','09/25/2008',4182,0,1,'312.34' UNION ALL
SELECT '33937','09/25/2008',4182,1,1,'296.80' UNION ALL
SELECT '33937','09/25/2008',4182,0,1,'296.23' UNION ALL
SELECT '33937','10/16/2008',4182,0,1,'313.89' UNION ALL
SELECT '33937','10/16/2008',4182,0,1,'313.81'

SELECT DISTINCT t.PATID,t.StartDate,t.ProvID,t2.*
FROM #TestData t
INNER JOIN (SELECT PATID,StartDate
FROM #TestData
GROUP BY PATID,StartDate
HAVING COUNT(CASE WHEN primaryDx=1 THEN 1 ELSE NULL END) =0)t1
ON t1.PATID = t.PATID
AND t1.StartDate = t.StartDate
CROSS APPLY (SELECT TOP 1 PrimaryDx,axis,code
FROM #TestData
WHERE PATID=t.PATID
AND PrimaryDx=1
AND StartDate < t.StartDate
ORDER BY StartDate DESC) t2

DROP TABLE #TestData

output
-------------------------------------------------------
PATID StartDate ProvID PrimaryDx axis code
202 2008-04-15 00:00:00.000 2945 1 2 317
202 2008-06-06 00:00:00.000 2945 1 2 317
202 2010-04-27 00:00:00.000 5454 1 2 317
33937 2008-10-16 00:00:00.000 4182 1 1 296.80
33937 2009-12-08 00:00:00.000 3187 1 1 333.33
33937 2010-01-19 00:00:00.000 4444 1 1 333.33

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

LaurieCox

158 Posts

Posted - 2010-08-18 : 08:43:00

Hi visakh16,

Thanks, that is beautiful. You not only solved my problem, but gave me something new to study.

Again thanks,

Laurie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources