Tricks in MSSQL

Common Issues in SQL

Scenario when we need to filter specific select cases with joins

SELECT DISTINCT ProgramID
,STUFF((
SELECT '|' + t1.TrancID
FROM @TBL_ReportColumns T1
WHERE T1.ProgramID = T2.ProgramID
FOR XML PATH('')
), 1, 1, '') AS TransactionCode
--,trancName,trancDesc
INTO #originalTransSlct
FROM @TBL_ReportColumns T2
ORDER BY ProgramID

Scenario when we need to convert rows from vertical to horizontal

DECLARE @col_list VARCHAR(max) = ‘’
DECLARE @a INT
SET @a = 0
SELECT @col_list = Stuff((
SELECT ‘,sn = max(case when sn = ‘ + Cast(sn AS VARCHAR(50)) + ‘ then sn end), DateRestrictionSettingName = max(case when sn = ‘ + Cast(sn AS VARCHAR(50)) + ‘ then DateRestrictionSettingName end), DateRestrictionSettingValue = max(case when sn = ‘ + Cast(sn AS VARCHAR(50)) + ‘ then DateRestrictionSettingValue end)’
FROM #Table1
ORDER BY sn
FOR XML path(‘’)
), 1, 1, ‘’)
SET @col_list =
‘sn = max(case when sn = 1 then sn end), DateRestrictionSettingName1 = max(case when sn = 1 then DateRestrictionSettingName end), DateRestrictionSettingValue1 = max(case when sn = 1 then DateRestrictionSettingValue end),sn = max(case when sn = 2 then sn end), DateRestrictionSettingName2 = max(case when sn = 2 then DateRestrictionSettingName end), DateRestrictionSettingValue2 = max(case when sn = 2 then DateRestrictionSettingValue end),sn = max(case when sn = 3 then sn end), DateRestrictionSettingName3 = max(case when sn = 3 then DateRestrictionSettingName end), DateRestrictionSettingValue3 = max(case when sn = 3 then DateRestrictionSettingValue end),sn = max(case when sn = 4 then sn end), DateRestrictionSettingName4 = max(case when sn = 4 then DateRestrictionSettingName end), DateRestrictionSettingValue4 = max(case when sn = 4 then DateRestrictionSettingValue end),sn = max(case when sn = 5 then sn end), DateRestrictionSettingName5 = max(case when sn = 5 then DateRestrictionSettingName end), DateRestrictionSettingValue5 = max(case when sn = 5 then DateRestrictionSettingValue end),sn = max(case when sn = 6 then sn end), DateRestrictionSettingName6 = max(case when sn = 6 then DateRestrictionSettingName end), DateRestrictionSettingValue6 = max(case when sn = 6 then DateRestrictionSettingValue end),CONVERT(BIT, 1) AS editablerow,CONVERT(BIT, 0) AS deletablerow,CONVERT(BIT, 0) AS DateRestrictionSettingName6_editablecol,CONVERT(BIT, 0) AS DateRestrictionSettingValue6_editablecol’
EXEC (‘select ‘ + @col_list + ‘ from #Table1’)

Scenario when Date needs to be compared

SELECT DISTINCT SLCSemester.ID
,’ — — — ->’ AS Conflict
,ToCompare.StartDate
,ToCompare.EndDate
,CASE
WHEN ToCompare.StartDate >= SLCSemester.StartDate
AND ToCompare.StartDate <= SLCSemester.EndDate
AND ToCompare.EndDate >= SLCSemester.StartDate
AND ToCompare.EndDate <= SLCSemester.EndDate
THEN ‘Engulfed’
WHEN ToCompare.StartDate >= SLCSemester.StartDate
AND ToCompare.StartDate <= SLCSemester.EndDate
THEN ‘SD’
WHEN ToCompare.EndDate >= SLCSemester.StartDate
AND ToCompare.EndDate <= SLCSemester.EndDate
THEN ‘ED’
ELSE ‘Something Wrong’
END AS Issue
FROM SLCSemester
JOIN SLCSemester AS ToCompare ON ToCompare.ID = SLCSemester.ID
 — every row overlaps itself
AND ToCompare.ID <> SLCSemester.ID
OR ( ( ToCompare.StartDate >= SLCSemester.StartDate
AND ToCompare.StartDate <= SLCSemester.EndDate) )
OR (
ToCompare.EndDate >= SLCSemester.StartDate
AND ToCompare.EndDate <= SLCSemester.EndDate
)
WHERE SLCSemester.SLCAcademicYearID = @SLCAcademicYearID
)