Important SQL logic

-->> Logic to get date in AM/PM format and to get only HH:MM with AM/PM part from your date   

SELECT convert(varchar(40), GetDate(), 9)

SELECT substring(convert(varchar(20),  GetDate(), 9), 13, 5) + ' ' + substring(convert(varchar(30), GetDate(), 9), 25, 2)

---To get multiple values from row into one row based on grouping in comma separated format

Select Distinct FacilityGroupName--,TB1.FacilityName

,stuff((select ',' + TB2.FacilityCode from BINextGen..tbfacility TB2 where TB2.FacilityGroupName = TB1.FacilityGroupName

for xml path('')) ,1,1,'') as 'FacilityCode'

from  BINextGen..tbfacility TB1

 

----------------------------For partition or remove duplicate ------

,Dense_Rank() OVER(PARTITION BY Claim .RegistrationID,Claim.FacilityCode ORDER BY Claim.AdjudicationID,Claim .RegistrationID) as RowNumber

 

-------------For generate row number---------

SELECT ROW_NUMBER() OVER(ORDER BY FacilityCode) AS 'row number', * FROM TBFacility

 

-------------Window function for adding values----------

Select FacilityCode,SUM(TotalCharges) Over(PARTITION BY FacilityCode) as Total From ORRegistrationsload where facilityCode IN ('BHTN','BOMC')