• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 248
  • Last Modified:

What is this SQL Code doing?

Can someone tell me what "umc" is referencing, or how it is being populated in this query.  
I have a basic understanding of the #temp for temp tables but I am a bit puzzled with what is going on with "umc".
umc.txt
0
morinia
Asked:
morinia
  • 3
  • 2
3 Solutions
 
OCDanCommented:
Basically they have just called the larger query below UMC. It is just an alias for the query it is the equivalent of OH in the below query:
select * from orderheader as OH

Your query:
(
--QUERY1
SELECT
  mc.member_id, mc.enrollment_date , mc.exit_date
, mc.primary_secondary , mc.active_code , mc.benefit_plan
, mc.benefit_product, mc.benefit_group, 'CURR' as ONFILE
from llm.llmdba.member_coverage mc (nolock)
   , #mbrLkup m (nolock)
where
   mc.enrollment_date < '01/01/2012'
  and (mc.exit_date >= '01/01/2011' or mc.exit_date is null)
  and (convert(varchar(10),mc.enrollment_date,120) <> convert(varchar(10),mc.exit_date,120) or mc.exit_date is null)
  and mc.member_id = m.member_id
union
--QUERY2
SELECT
  mc.member_id, mc.enrollment_date, mc.exit_date, mc.primary_secondary
, mc.active_code, mc.benefit_plan, mc.benefit_product, mc.benefit_group, 'HIST' as ONFILE
from llm.llmdba.member_coverage_history mc (nolock), #mbrLkup m (nolock)
where
      mc.enrollment_date < '01/01/2012'  and (mc.exit_date > '01/01/2011' or mc.exit_date is null)
  and (convert(varchar(10),mc.enrollment_date,120) <> convert(varchar(10),mc.exit_date,120) or mc.exit_date is null)
  and mc.member_id = m.member_id
) umc

Hope that helps.
0
 
Paul JacksonCommented:
umc is referencing the results of the sub-select that starts on line 40 and finishes on line 86:

into #mbr_covg0
from
(     <------    starts here
SELECT
  mc.member_id
...
...
) umc    <-- finishes here
0
 
moriniaAdvanced Analytics AnalystAuthor Commented:
SELECT umc.*
, dense_rank()
       over (partition by umc.member_id
             order by umc.enrollment_date desc
                    , umc.exit_date)
       as nRank

What is the first six lines of the code doing?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
OCDanCommented:
select umc.*
selects all the fields from UMC subquery as said above

Dense_rank():
Ranks the results per each umc.member_id with the most recent enrollment date and the earliest exit date being the highest ranked.

Have a read of this link on
Ranking Functions it should help you understand the dense_rank function a bit better.
0
 
moriniaAdvanced Analytics AnalystAuthor Commented:
The use of temporary tables and partitions is this regular SQL or Transact-Sql?  Since you are able to access temproary tables as well as the tables on the server what differentiates this code from regular  MSSql code?
0
 
OCDanCommented:
Yes they get used quite a lot in TSQL soryr mate not sure what you mean by regular SQL.

This is valid MSSQL(TSQL) code, copying it into Sql Server Management Studio and performing a syntax check runs fine, so it is likely valid.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now