Cacher is the code snippet organizer for pro developers

-------------- TOP, --WHERE

SELECT top (5) *
 FROM [dbo].[TopTenEnrolledSummary]
 where Year = 2019 and Month = 9

---------- ON

select distinct HedisCode from MemberMiscellaneousInformationTemps JOIN MeasureCodeOverview
ON MEAS_ID = HedisCode
 
 -------------------- LEFT JOIN
 
SELECT distinct
   b.MEAS_ID,
   c.HedisCode AS Hedis_Code
FROM MemberMiscellaneousInformationTemps b
left JOIN MeasureCodeOverview c ON b.MEAS_ID = c.HedisCode

----------------- NOT IN --WHERE

select distinct MEAS_ID from MemberMiscellaneousInformationTemps
where MEAS_ID not in(select HedisCode from MeasureCodeOverview)
 
SELECT  distinct [Contract ID]
FROM    dbo.CPSC_Contract_Info_2019_09
WHERE   [Contract ID] NOT IN (SELECT Enrollment_V1.dbo.CPSC_Contract_Info.ContractID FROM Enrollment_V1.dbo.CPSC_Contract_Info )
 
SELECT  distinct [Contract ID]
FROM   CMS_DB.dbo.CPSC_Contract_Info_2019_09
WHERE   [Contract ID] NOT IN (SELECT ContractID FROM Enrollment_V1.dbo.CPSC_Contract_Info )
 
SELECT  distinct [Plan ID]
FROM   CMS_DB.dbo.CPSC_Contract_Info_2019_09
WHERE   [Plan ID] NOT IN (SELECT PlanID FROM Enrollment_V1.dbo.CPSC_Contract_Info )

select c.[Contract ID],c.[Plan ID],c.[Organization Name],main.ContractID,main.PlanID,main.OrganizationName from CMS_DB.dbo.CPSC_Contract_Info_2019_09 c inner join Enrollment_V1.dbo.CPSC_Contract_Info main on c.[Contract ID]=main.ContractID and c.[Plan ID]=main.PlanID
and c.[Organization Name]!=main.OrganizationName
select c.[Contract ID],c.[Plan ID],c.[Parent Organization],main.ContractID,main.PlanID,main.ParentOrganization from CMS_DB.dbo.CPSC_Contract_Info_2019_09 c inner join Enrollment_V1.dbo.CPSC_Contract_Info main on c.[Contract ID]=main.ContractID and c.[Plan ID]=main.PlanID
and c.[Parent Organization]!=main.ParentOrganization

------------ INNER JOIN / JOIN --ON --AND

select new.[Contract ID],new.[Plan ID],new.[Organization Name], main.ContractID,main.PlanID,main.OrganizationName
from CMS_DB.dbo.CPSC_Contract_Info_2019_09 new
inner join Enrollment_V1.dbo.CPSC_Contract_Info main
on new.[Contract ID]=main.ContractID
and new.[Plan ID]=main.PlanID
and new.[Plan Type]=main.PlanType
and new.[Organization Name]!=main.OrganizationName


select new.[Contract ID],new.[Plan ID],new.[Parent Organization], main.ContractID,main.PlanID,main.ParentOrganization
from CMS_DB.dbo.CPSC_Contract_Info_2019_09 new
inner join Enrollment_V1.dbo.CPSC_Contract_Info main
on new.[Contract ID]=main.ContractID
and new.[Plan ID]=main.PlanID
and new.[Parent Organization]!=main.ParentOrganization

---------- SUM, CAST, AS, JOIN, ON, WHERE, GROUP BY --DESC

SELECT
SUM (CAST(Enrollment AS BIGINT)) as TotalEnrollment
FROM CMS_DB.dbo.CPSC_Enrollment_Info_2019_09 e
inner join Enrollment_V1.dbo.CPSC_Contract_Info c
on e.[Contract Number]=c.ContractID and e.[Plan ID] = c.[PlanID]
--and c.[Plan Name] = c.[Plan Name]
where c.[PlanType] in ('Employer/Union Only Direct Contract PDP','Medicare Prescription Drug Plan')
--GROUP BY e.[Plan ID], c.[Plan Name]


SELECT e.State,
SUM (CAST(Enrollment AS BIGINT)) as TotalEnrollment
FROM CMS_DB.dbo.CPSC_Enrollment_Info_2019_09 e
inner join Enrollment_V1.dbo.CPSC_Contract_Info c
on e.[Contract Number]=c.ContractID and e.[Plan ID] = c.[PlanID]
--and c.[Plan Name] = c.[Plan Name]
where c.[PlanType] in ('Employer/Union Only Direct Contract PDP','Medicare Prescription Drug Plan') and e.state != ''
GROUP BY e.State order by SUM (CAST(Enrollment AS BIGINT)) desc


SELECT e.State,
SUM (CAST(Enrollment AS BIGINT)) as TotalEnrollment
FROM CMS_DB.dbo.CPSC_Enrollment_Info_2019_09 e
inner join Enrollment_V1.dbo.CPSC_Contract_Info c
on e.[Contract Number]=c.ContractID and e.[Plan ID] = c.[PlanID]
--and c.[Plan Name] = c.[Plan Name]
where c.[PlanType] in ('Employer/Union Only Direct Contract PDP','Medicare Prescription Drug Plan') and e.state != ''
GROUP BY e.State order by TotalEnrollment desc


SELECT
    MemberTestKitTemp.[MBR_PERS_GEN_KEY]
     ,[ORDER_DATE]
     ,[DATE_SHIPPED]
     ,[KIT_TYPE]
      ,[IDCARD_MBR_ID]
      ,[KIT_TRACKING_NUM]
 FROM MemberTestKitTemp
 JOIN MemberMiscellaneousInformationTemps
   on MemberTestKitTemp.MBR_PERS_GEN_KEY = MemberMiscellaneousInformationTemps.MBR_PERS_GEN_KEY
    GROUP BY MemberTestKitTemp.[MBR_PERS_GEN_KEY]
      ,[ORDER_DATE]
     ,[DATE_SHIPPED]
     ,[KIT_TYPE]
      ,[IDCARD_MBR_ID]
      ,[KIT_TRACKING_NUM]

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