SQLTeam.com | Weblogs | Forums

Creating a flat file with Board Cert and Date


#1

Our Client would like to see their file as a flat file. I have done this in the past and it has worked. I don't know how to create this last request.

The field that I am using is called pcsb_what. This field has all of our Board Cert names in it. There could be 5 board certs for one doctor. I need to have them go across the page. I did that, but now they want to add in the date. Some of the board certs have different dates. I need to pull the Max date for that specific board. Here is an example of what I see.

pcs_id1 pcsb_id2 pcsb_what pcsb_trm
12542 1 Family Practice 12/31/2003
12542 2 Family Practice 12/31/2013
12542 3 Emergency Med 12/31/1998

Above technically has 2 boards for the firs two I need the max date. then for the other one, I just need them to pull on my flat file like this below.

PCS_id1 Board Cert 1 Effective BC 1 Board Cert 2 Effective BC 2
12542 Family Practice 12/31/2013 Emergency Med 12/31/1998

What is the best way for me to get these results? Below is what I attempted to use until I had to put the Date in.

/-----=========================================================
BoarCert - 1
-----=========================================================
/

Left Join (select pcsb_id1, [Board Cert1],[Experation Date-BC1]
from
(
select pcsb_id1, val_desc as [Board Cert1],pcsb_trm1 as [Experation Date-BC1],
row_number() over (partition by pcsb_id1 order by pcsb_trm1 desc) As r
from impact.dbo.pcsb
Left JOIN impact.dbo.vw_VALID_471 BCERT with (nolock)
ON pcsb_what = BCERT.val_code
)t
where r= 1)B1 on t8.pcsb_id1 = B1.pcsb_id1

/-----=========================================================
BoarCert - 2
-----=========================================================
/

Left Join (select pcsb_id1, [Board Cert2],[Experation Date-BC2]
from
(
select pcsb_id1, val_desc as [Board Cert2],pcsb_trm1 as [Experation Date-BC2],
row_number() over (partition by pcsb_id1 order by pcsb_trm1 desc) As r
from impact.dbo.pcsb
Left JOIN impact.dbo.vw_VALID_471 BCERT with (nolock)
ON pcsb_what = BCERT.val_code
)t
where r= 2)B2 on t8.pcsb_id1 = B2.pcsb_id1


#2

Try this:

with cte
  as (select pcsb_id1
            ,val_desc
            ,max(pcsb_trm1) as pcsb_trm1
            ,row_number() over (partition by pcsb_id1
                                            ,val_desc
                                order by pcsb_trm1 desc
                               )
             as rn
        from impact.dbo.pcsb as a
             left join impact.dbo.vw_VALID_471 as b
                    on b.val_code=a.pcsb_what
     )
select a.pcsb_id1 as PCS_id1
      ,a.val_desc as [Board Cert 1]
      ,a.pcsb_trm1 as [Effective BC 1]
      ,b.val_desc as [Board Cert 2]
      ,b.pcsb_trm1 as [Effective BC 2]
      ,c.val_desc as [Board Cert 3]
      ,c.pcsb_trm1 as [Effective BC 3]
      ,d.val_desc as [Board Cert 4]
      ,d.pcsb_trm1 as [Effective BC 4]
      ,e.val_desc as [Board Cert 5]
      ,e.pcsb_trm1 as [Effective BC 5]
  from cte as a
       left outer join cte as b
                    on b.pcsb_id1=a.pcsb_id1
                   and b.rn=2
       left outer join cte as c
                    on c.pcsb_id1=a.pcsb_id1
                   and c.rn=3
       left outer join cte as d
                    on d.pcsb_id1=a.pcsb_id1
                   and d.rn=4
       left outer join cte as e
                    on e.pcsb_id1=a.pcsb_id1
                   and e.rn=5
 where a.rn=1
;

#3

Thanks I believe this will work but I am getting an error and I am not sure how to fix this.

Msg 156, Level 15, State 1, Line 301
Incorrect syntax near the keyword 'Left'.
Msg 319, Level 15, State 1, Line 301
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 102, Level 15, State 1, Line 305
Incorrect syntax near 'c'.
Msg 102, Level 15, State 1, Line 320
Incorrect syntax near 'P1'.
Msg 102, Level 15, State 1, Line 335
Incorrect syntax near 'P2'.
Msg 102, Level 15, State 1, Line 348
Incorrect syntax near 'P3'.
Msg 102, Level 15, State 1, Line 362
Incorrect syntax near 'P4'.
Msg 102, Level 15, State 1, Line 375
Incorrect syntax near 'P5'.
Msg 102, Level 15, State 1, Line 381
Incorrect syntax near 'A'.

Here is the joins below.......

FROM impact.dbo.tmpHumanaEffectiveDates t1
LEFT OUTER JOIN impact.dbo.pcst t2
ON t1.pcsp_id1 = t2.pcst_id1

Right OUTER JOIN------- took out pcsp_trm is null 5-26-11 now other records are showing.
impact.dbo.pcs t3 ON t1.pcsp_id1 = t3.pcs_id1 and t3.pcs_ctl <> 'X' --t1.pcsp_trm is null and

LEFT OUTER JOIN impact.dbo.pcsa t7
ON t7.pcsa_id1 = t3.pcs_id1

Left OUTER JOIN impact.dbo.pcsb t8
on pcsb_id1 = t3.pcs_id1

left join impact.dbo.pro
on t7.pcsa_pro = pro_id1

inner join impact.dbo.prop
on t7.pcsa_pro = prop_id1 and prop_trm is null

left join impact.dbo.pcst t5
ON t1.pcsp_id1 = t5.pcst_id1
and t5.PCST_WHAT ='RECRED' and t5.pcst_act2 is NULL ----- Future Date

LEFT JOIN impact.dbo.valid s1 with (nolock)
ON t3.pcs_spec1 = s1.val_code and s1.val_type = '302'--Spec1

LEFT JOIN impact.dbo.valid s2 with (nolock)
ON t3.pcs_spec2 = s2.val_code and s2.val_type = '302' --Spec2

Left JOIN impact.dbo.valid IPA1 with (nolock)
ON Prop_SYS = IPA1.val_code and IPA1.val_type = '367' ---IPA

;WITH cte AS (select pcsb_id1
,val_desc
,max(PCSB_TRM1) as PCSB_TRM1
,row_number() over (partition by pcsb_id1
,val_desc
order by PCSB_TRM1 desc
) as rn
from impact.dbo.PCSB as a
left join impact.dbo.vw_VALID_471 as b
on b.val_code=a.pcsb_what
group by pcsb_id1,val_desc,pcsb_trm1
)
select a.pcsb_id1 as PCS_id1
,a.val_desc as [Board Cert1]
,a.pcsb_trm1 as [Experation Date-BC1]
,b.val_desc as [Board Cert2]
,b.pcsb_trm1 as [Experation Date-BC2]
,c.val_desc as [Board Cert3]
,c.pcsb_trm1 as [Experation Date-BC3]
,d.val_desc as [Board Cert4]
,d.pcsb_trm1 as [Experation Date-BC4]
,e.val_desc as [Board Cert5]
,e.pcsb_trm1 as [Experation Date-BC5]
from cte as a
left outer join cte as b
on b.pcsb_id1=a.pcsb_id1
and b.rn=2
left outer join cte as c
on c.pcsb_id1=a.pcsb_id1
and c.rn=3
left outer join cte as d
on d.pcsb_id1=a.pcsb_id1
and d.rn=4
left outer join cte as e
on e.pcsb_id1=a.pcsb_id1
and e.rn=5
Where a.rn=1
;

----Left JOIN impact.dbo.vw_VALID_471 BCERT with (nolock)
-----ON pcsb_what = BCERT.val_code --Board Cert

Left JOIN impact.dbo.vw_VALID_316 Protyp with (nolock)
ON t3.pcs_xtyp = protyp.val_code -----ProviderType

Left join (Select Distinct pcsa_id1 as correspondanceID, pcsa_addr1 as correspondanceAddress, pcsa_addr2 as correspondanceSuite,pcsa_city as correspondanceCity, pcsa_state as correspondanceState, pcsa_zip as correspondanceZIP,pcsa_phone as CorrespondancePhone, pcsa_fax as CorrespondanceFax , pcsa_tax1 as CorrespondanceTAX From impact.dbo.pcsa where pcsa_trst = 'CORRES' and (pcsa_trm is null or pcsa_trm = '' or pcsa_trm = NULL)) c on t7.pcsa_id1 = c.correspondanceID and t7.pcsa_tax1 = c.CorrespondanceTAX

/-----=========================================================
MAX Board Decision Made Date - Current updated(7/18/2013)
-----=========================================================
/

Left Join (select pcst_id1,MaxboardDecisionMade, [Cred Type - Last Board Decision Made Date]
from
(
select pcst_id1, pcst_dat2 as MaxboardDecisionMade, pcst_what as [Cred Type - Last Board Decision Made Date],
row_number() over (partition by pcst_id1 order by pcst_dat2 desc) As r
from impact.dbo.pcst
)t
where r= 1)P1 on t2.pcst_id1 = P1.pcst_id1

/-----=========================================================
MAX Previous Board Decision Made Date - Current updated(7/18/2013)
-----=========================================================
/

Left Join (select pcst_id1,MaxPreviousboardDecisionMade
from
(
select pcst_id1, pcst_dat2 as MaxPreviousboardDecisionMade,
row_number() over (partition by pcst_id1 order by pcst_dat2 desc) As r
from impact.dbo.pcst
)t
where r= 2)P2 on t2.pcst_id1 = P2.pcst_id1

/-----=========================================================
MAX Board Decision Made Date Other 3 - Current updated(7/18/2013)
-----=========================================================
/

Left Join (select pcst_id1,MaxBoardDecisionMadeOther3
from
(
select pcst_id1, pcst_dat2 as MaxBoardDecisionMadeOther3,
row_number() over (partition by pcst_id1 order by pcst_dat2 desc) As r
from impact.dbo.pcst
)t
where r= 3)P3 on t2.pcst_id1 = P3.pcst_id1

/-----=========================================================
MAX Board Decision Made Date Other 4 - Current updated(7/18/2013)
-----=========================================================
/

Left Join (select pcst_id1,MaxBoardDecisionMadeOther4
from
(
select pcst_id1, pcst_dat2 as MaxBoardDecisionMadeOther4,
row_number() over (partition by pcst_id1 order by pcst_dat2 desc) As r
from impact.dbo.pcst
)t
where r= 4)P4 on t2.pcst_id1 = P4.pcst_id1

/-----=========================================================
MAX Board Decision Made Date Other 5 - Current updated(7/18/2013)
-----=========================================================
/

Left Join (select pcst_id1,MaxBoardDecisionMadeOther5
from
(
select pcst_id1, pcst_dat2 as MaxBoardDecisionMadeOther5,
row_number() over (partition by pcst_id1 order by pcst_dat2 desc) As r
from impact.dbo.pcst
)t
where r= 5)P5 on t2.pcst_id1 = P5.pcst_id1

/-----=========================================================
[First_Board_Decision_Made_Date]
-----=========================================================
/

Left Join (select pcst_id1, MIN(pcst_dat2) as [First_Board_Decision_Made_Date] from impact.dbo.pcst group by pcst_id1) A
on t2.pcst_id1 = A.pcst_id1

WHERE
t3.pcs_ctl <> 'X' and
---t7.pcsa_ctl <> 'X' and
prop_prd is not null and prop_prd = 'DGH' and ------IN('DGH','ECN','ESN','IMA','IMW','MVA','WCP') and -----and 8/27/2014 took out because Felicia wants all Products to appear.
t7.pcsa_state IN('PA','DE')and
pcsa_trst <> 'INPROC' and
s1.val_desc NOT IN('PART-TIME PRECEPTOR','LOCUM TENEN')


#4

This will not work as written. Did you leave out something after the and?


#5

@wsilage,

Assuming that this problem is actually important for you to solve, you should help your self get the best answer possible by posting your data as readily consumable data and post enough of it for people to validate the code they come up with.

Here's one example of how you should have posted your sample data.

Shifting gears to the problem (hopefully, this is what you want because your requirements contain an "Expiration Date" but your sample data does not), this can easily be solved using a simple pivot problem except PIVOT is slower than the ancient method known as a CROSSTAB.

--==================================================================================
--      Setup a test table and populate it.
--      THIS IS NOT A PART OF THE SOLUTION! It's just a test table.
--==================================================================================
--===== If the test table exists, drop it to make reruns easier
     IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL
        DROP TABLE #TestTable
;
--===== Create the test table and populate it on-the-fly
 SELECT d.pcs_id1, d.pcsb_id2, d.pcsb_what, pcsb_trm = CAST(d.pcsb_trm AS DATETIME)
   INTO #TestTable
   FROM (
         SELECT 12542,1,'Family Practice','12/31/2003' UNION ALL
         SELECT 12542,2,'Family Practice','12/31/2013' UNION ALL
         SELECT 12542,3,'Emergency Med'  ,'12/31/1998' UNION ALL

         SELECT 12500,4,'Emergency Med'  ,'12/31/1998' UNION ALL
         SELECT 12500,5,'Family Practice','12/31/2003' UNION ALL
         SELECT 12500,6,'Family Practice','12/31/2013' UNION ALL
         SELECT 12500,7,'VODOO'          ,'12/31/2013' UNION ALL
         SELECT 12500,8,'Witchcraft'     ,'12/31/2013' UNION ALL
         SELECT 12500,9,'Alchemy'        ,'12/31/2013'
        ) d (pcs_id1, pcsb_id2, pcsb_what, pcsb_trm)
;

The hard part for most people is that they try to solve for things, like the max date for each pcs_id1/pcsb_what combination at the same time that they try to format the data. That violates the basic rules of keeping the data and presentation layers separate and, as you've found out, that makes for long, difficult to read, inefficient code.

With that idea in mind, the CTE in the following code does the heavy lifting on the aggregations and enumerations and the outer SELECT does the formatting. And, no... the two GROUP BYs aren't going to make this slow. It's a heck of a lot faster than all of the joins and inequality comparisons done, so far.

The following code works with the test table that was created above. Change the table name and/or column names to suit you. With the right indexes, it'll run so fast that you won't think it ran. :wink:

--==================================================================================
--      Solve the problem.
--==================================================================================
   WITH ctePreAgg AS
(--==== Preaggregate for the max date for each pcs_id1/pcsb_what, simplify the
     -- data, and enumerate (RN) each instance of pcsb_what in alphabetic order
     -- to control to display later.
 SELECT  RN = ROW_NUMBER() OVER (PARTITION BY pcs_id1 ORDER BY pcsb_what)
        ,pcs_id1 
        ,pcsb_what 
        ,pcsb_trm = CONVERT(CHAR(10),MAX(pcsb_trm),101)
   FROM #TestTable
  GROUP BY pcs_id1 ,pcsb_what
)--==== Display the data using a traditional, high performance CROSSTAB.
 SELECT  pcs_id1
        ,[Board Cert 1]     = MAX(CASE WHEN RN = 1 THEN pcsb_what ELSE '' END)
        ,[Effective BC 1]   = MAX(CASE WHEN RN = 1 THEN pcsb_trm  ELSE '' END)
        ,[Board Cert 2]     = MAX(CASE WHEN RN = 2 THEN pcsb_what ELSE '' END)
        ,[Effective BC 2]   = MAX(CASE WHEN RN = 2 THEN pcsb_trm  ELSE '' END)
        ,[Board Cert 3]     = MAX(CASE WHEN RN = 3 THEN pcsb_what ELSE '' END)
        ,[Effective BC 3]   = MAX(CASE WHEN RN = 3 THEN pcsb_trm  ELSE '' END)
        ,[Board Cert 4]     = MAX(CASE WHEN RN = 4 THEN pcsb_what ELSE '' END)
        ,[Effective BC 4]   = MAX(CASE WHEN RN = 4 THEN pcsb_trm  ELSE '' END)
        ,[Board Cert 5]     = MAX(CASE WHEN RN = 5 THEN pcsb_what ELSE '' END)
        ,[Effective BC 5]   = MAX(CASE WHEN RN = 5 THEN pcsb_trm  ELSE '' END)
   FROM ctePreAgg 
  GROUP BY pcs_id1
  ORDER BY pcs_id1
;

Heh... AND STOP USING WITH (NOLOCK)! It's a programming crutch that can raise hell with your data and cause "Data Moved" errors even on not-so-busy systems. :wink:


#6

Thank you JeffModen. This pulls exactly what I need. I added my table and I got the results! :slight_smile:

I have never done anything like this before. How do I inter the data results into another table? I probably am not doing this correct, but this is what I put.

WITH ctePreAgg AS
(SELECT RN = ROW_NUMBER() OVER (PARTITION BY pcsb_id1 ORDER BY val_desc)
,pcsb_id1
,val_desc
,pcsb_trm = CONVERT(CHAR(10),MAX(pcsb_trm1),101)
FROM impact.dbo.pcsb
Left JOIN impact.dbo.vw_VALID_471 BCERT with (nolock)
ON pcsb_what = BCERT.val_code
GROUP BY pcsb_id1 ,val_desc)--==== Display the data using a traditional, high performance CROSSTAB.

SELECT pcsb_id1
,[Board Cert1] = MAX(CASE WHEN RN = 1 THEN val_desc ELSE '' END)
,[Experation Date-BC1] = MAX(CASE WHEN RN = 1 THEN pcsb_trm ELSE '' END)
,[Board Cert2] = MAX(CASE WHEN RN = 2 THEN val_desc ELSE '' END)
,[Experation Date-BC2] = MAX(CASE WHEN RN = 2 THEN pcsb_trm ELSE '' END)
,[Board Cert3] = MAX(CASE WHEN RN = 3 THEN val_desc ELSE '' END)
,[Experation Date-BC3] = MAX(CASE WHEN RN = 3 THEN pcsb_trm ELSE '' END)
,[Board Cert4] = MAX(CASE WHEN RN = 4 THEN val_desc ELSE '' END)
,[Experation Date-BC4] = MAX(CASE WHEN RN = 4 THEN pcsb_trm ELSE '' END)
,[Board Cert5] = MAX(CASE WHEN RN = 5 THEN val_desc ELSE '' END)
,[Experation Date-BC5] = MAX(CASE WHEN RN = 5 THEN pcsb_trm ELSE '' END)
FROM ctePreAgg
GROUP BY pcsb_id1
ORDER BY pcsb_id1

INSERT INTO [Board Certs]
Select * from ctePreAgg

The error I am getting is this....

Msg 208, Level 16, State 1, Line 26
Invalid object name 'ctePreAgg'.

Please help. I am not familiar with this. Thanks.


#7

EDIT: Ignore that, that isn't the problem ... sorry about that! See below ...

Please mark your code with appropriate markup - otherwise this forum will gobble some of it up as formatting.

    ```sql
        Your code here
    ```

#8

You can't do that, as the ctePreAgg is only defined, and only in scope for, the previous SELECT query.

You'd need to either repeat the ctePreAgg definition or, my preference, do the INSERT first (to a #temporary table if you prefer) and then SELECT from that (and, if your INSERT to a #Temp table then also INSERT to the [Board Certs] table)


#9

Tangential thought:

Why insert into a Physical Table? You will have to do / re-do that whenever anything changes in the underlying table(s).

When we "flatten" tables we create a VIEW - which will query the underlying data in real time.

Do not use NOLOCK, it has really REALLY bad side effects. If you have Readers-blocking-writers then consider setting the database to read committed snapshot - no code changes required (except to remove all the NOLOCK statements !!) but you should test it as there are a few, rare, edge cases that perform differently.


#10

Like this... (the INSERT becomes a part of the outer SELECT)...

WITH ctePreAgg AS
(
 SELECT  RN = ROW_NUMBER() OVER (PARTITION BY pcsb_id1 ORDER BY val_desc)
        ,pcsb_id1 
        ,val_desc
        ,pcsb_trm = CONVERT(CHAR(10),MAX(pcsb_trm1),101)
   FROM impact.dbo.pcsb
   LEFT JOIN impact.dbo.vw_VALID_471 BCERT
     ON pcsb_what = BCERT.val_code
  GROUP BY pcsb_id1 ,val_desc
)--==== INSERT the data using a traditional, high performance CROSSTAB.
 INSERT INTO dbo,[Board Certs]
        (--===== You should almost always include the column list for INSERT.
         pcsb_id1
        ,[Board Cert1],[Experation Date-BC1]
        ,[Board Cert2],[Experation Date-BC2]
        ,[Board Cert3],[Experation Date-BC3]
        ,[Board Cert4],[Experation Date-BC4]
        ,[Board Cert5],[Experation Date-BC5]
        )
 SELECT  pcsb_id1
        ,[Board Cert1]          = MAX(CASE WHEN RN = 1 THEN val_desc ELSE '' END)
        ,[Experation Date-BC1]  = MAX(CASE WHEN RN = 1 THEN pcsb_trm ELSE '' END)
        ,[Board Cert2]          = MAX(CASE WHEN RN = 2 THEN val_desc ELSE '' END)
        ,[Experation Date-BC2]  = MAX(CASE WHEN RN = 2 THEN pcsb_trm ELSE '' END)
        ,[Board Cert3]          = MAX(CASE WHEN RN = 3 THEN val_desc ELSE '' END)
        ,[Experation Date-BC3]  = MAX(CASE WHEN RN = 3 THEN pcsb_trm ELSE '' END)
        ,[Board Cert4]          = MAX(CASE WHEN RN = 4 THEN val_desc ELSE '' END)
        ,[Experation Date-BC4]  = MAX(CASE WHEN RN = 4 THEN pcsb_trm ELSE '' END)
        ,[Board Cert5]          = MAX(CASE WHEN RN = 5 THEN val_desc ELSE '' END)
        ,[Experation Date-BC5]  = MAX(CASE WHEN RN = 5 THEN pcsb_trm ELSE '' END)
   FROM ctePreAgg 
  GROUP BY pcsb_id1
--ORDER BY pcsb_id1 --You don't need ORDER BY when inserting into a table.
                    --It's just extra work that the indexes will undo.
;

Keep in mind that since you're populating a table, it will someday go out of date and you'll need to update it. If it doesn't go out of date often, then having this table as a kind of "materialized view" will certainly save on CPU cost. If it changes a lot, then it might be better to change the code to a view, like Kristen suggested. It IS nasty fast code especially if you have the right indexes.

For more information on the ancient but still very effective method of CROSSTABs (they usually beat the PIVOT function by a fair bit), please see the following articles.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs


#11

p.s. BTW... to save on possible embarrassment for you and the company, "Experation" is misspelled in your column names. It should be "Expiration".


#12

See http://www.urbandictionary.com/define.php?term=experate :grin:


#13

Two weeks and 2 days and not a peep out of the OP. Hope it worked out.