• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 255
  • 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 JacksonSoftware EngineerCommented:
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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