Share via

UNION a Sub-Total when the result set is empty

Bobby P 231 Reputation points
2024-03-08T16:20:27.9066667+00:00

I have a series of SUM and COUNTS for Claims. Sometimes there are no "Rejected" Claims. We tried a IF NOT EXISTS and hard-coding the values if the "Rejected" Query is Empty with 0s with a subsequent UNION to run all the Claim Statuses but T-SQL is not liking the UNION after the IF NOT EXISTS.

Is there any easy way around this?

I hope the question is clear and Thanks in advance for your review and am hopeful for a solid reply.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,787 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,637 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Bobby P 231 Reputation points
    2024-03-08T18:45:54.5+00:00

    I need a Row 6...even hard-coding 0s if it results in an empty result set...something...some way I can use within the UNION

    SELECT		1																		AS [RowNumber],

			[Temp_Table_Claim].[REF_FormSubtype_ShortDescpt]						AS [REF_FormSubtype_ShortDesc],

			'Inst'																	AS [ClaimType],

			'Clean'																	AS [Ref_ClaimStatus_ShortDescpt],

			COUNT (*)															AS [ClaimStatusCount],

			SUM ([Temp_Table_Claim_Line_Total].[ClaimLine_BilledCurrencyAmount]) AS [ClaimLine_BilledCurrencyAmt]

FROM		[#Temp_Table_Claim]				AS [Temp_Table_Claim]

INNER JOIN	[#Temp_Table_Claim_Line_Total]	AS [Temp_Table_Claim_Line_Total]

	ON		[Temp_Table_Claim_Line_Total].[ClaimID] = [Temp_Table_Claim].[EDW_Claim_ClaimID]

WHERE		[Temp_Table_Claim].[REF_FormSubtype_ShortDescpt]	= 'UB-04'

AND

			(

				[Temp_Table_Claim].[EDW_Claim_ReceivedDate]				>= @StartDate_In

		AND		[Temp_Table_Claim].[EDW_Claim_ReceivedDate]				< @EndDate_In

			)

AND			[Temp_Table_Claim].[REF_ClaimStatus_Code] IN

				(

					SELECT	[Code]

					FROM	@Meditrac_Clean

				)

GROUP BY [Temp_Table_Claim].[REF_FormSubtype_ShortDescpt]

UNION

SELECT		2																		AS [RowNumber],

			[Temp_Table_Claim].[REF_FormSubtype_ShortDescpt]						AS [REF_FormSubtype_ShortDesc],

			'Inst'																	AS [ClaimType],

			'Accepted'																AS [EDW_Claim_ClaimStatusID],

			COUNT (*)															AS [ClaimStatusCount],

			SUM ([Temp_Table_Claim_Line_Total].[ClaimLine_BilledCurrencyAmount]) AS [ClaimLine_BilledCurrencyAmt]

FROM		[#Temp_Table_Claim]				AS [Temp_Table_Claim]

INNER JOIN	[#Temp_Table_Claim_Line_Total]	AS [Temp_Table_Claim_Line_Total]

	ON		[Temp_Table_Claim_Line_Total].[ClaimID] = [Temp_Table_Claim].[EDW_Claim_ClaimID]

WHERE		[Temp_Table_Claim].[REF_FormSubtype_ShortDescpt]	= 'UB-04'

AND			[Temp_Table_Claim].[REF_ClaimStatus_Code]					<> 'PPD'

AND

			(

					[Temp_Table_Claim].[EDW_Claim_ReceivedDate]				>= @StartDate_In

		AND			[Temp_Table_Claim].[EDW_Claim_ReceivedDate]				< @EndDate_In

			)

GROUP BY [Temp_Table_Claim].[REF_FormSubtype_ShortDescpt]

UNION

SELECT		3																		AS [RowNumber],

			[Temp_Table_Claim].[REF_FormSubtype_ShortDescpt]						AS [REF_FormSubtype_ShortDesc],

			'Inst'																	AS [ClaimType],

			'Received'																AS [EDW_Claim_ClaimStatusID],

			COUNT (*)															AS [ClaimStatusCount],

			SUM ([Temp_Table_Claim_Line_Total].[ClaimLine_BilledCurrencyAmount]) AS [ClaimLine_BilledCurrencyAmt]

FROM		[#Temp_Table_Claim]				AS [Temp_Table_Claim]

INNER JOIN	[#Temp_Table_Claim_Line_Total]	AS [Temp_Table_Claim_Line_Total]

	ON		[Temp_Table_Claim_Line_Total].[ClaimID] = [Temp_Table_Claim].[EDW_Claim_ClaimID]

WHERE		[Temp_Table_Claim].[REF_FormSubtype_ShortDescpt]	= 'UB-04'

AND			[Temp_Table_Claim].[REF_ClaimStatus_Code]					<> 'PPD'

AND

			(

					[Temp_Table_Claim].[EDW_Claim_ReceivedDate]				>= @StartDate_In

		AND			[Temp_Table_Claim].[EDW_Claim_ReceivedDate]				< @EndDate_In

			)

GROUP BY [Temp_Table_Claim].[REF_FormSubtype_ShortDescpt]

UNION

SELECT		4																		AS [RowNumber],

			[Temp_Table_Claim].[REF_FormSubtype_ShortDescpt]						AS [REF_FormSubtype_ShortDesc],

			'Inst'																	AS [ClaimType],

			'Pended'																AS [EDW_Claim_ClaimStatusID],

			COUNT (*)															AS [ClaimStatusCount],

			SUM ([Temp_Table_Claim_Line_Total].[ClaimLine_BilledCurrencyAmount]) AS [ClaimLine_BilledCurrencyAmt]

FROM		[#Temp_Table_Claim]				AS [Temp_Table_Claim]

INNER JOIN	[#Temp_Table_Claim_Line_Total]	AS [Temp_Table_Claim_Line_Total]

	ON		[Temp_Table_Claim_Line_Total].[ClaimID] = [Temp_Table_Claim].[EDW_Claim_ClaimID]

WHERE		[Temp_Table_Claim].[REF_FormSubtype_ShortDescpt]	= 'UB-04'

AND			[Temp_Table_Claim].[REF_ClaimProcessingStatus_Code] IN

(

	'RTC', 'OPN'

)

AND			[Temp_Table_Claim].[REF_ClaimStatus_Code] IN

(

	'PDC', 'TBP', 'ADF'

)

AND

			(

					[Temp_Table_Claim].[EDW_Claim_LastModifiedDt]			>= @StartDate_In

		AND			[Temp_Table_Claim].[EDW_Claim_LastModifiedDt]			< @EndDate_In

			)

GROUP BY [Temp_Table_Claim].[REF_FormSubtype_ShortDescpt]

UNION

SELECT		5																		AS [RowNumber],

			[Temp_Table_Claim].[REF_FormSubtype_ShortDescpt]						AS [REF_FormSubtype_ShortDesc],

			'Inst'																	AS [ClaimType],

			'Denied'																AS [EDW_Claim_ClaimStatusID],

			COUNT (*)															AS [ClaimStatusCount],

			SUM ([Temp_Table_Claim_Line_Total].[ClaimLine_BilledCurrencyAmount]) AS [ClaimLine_BilledCurrencyAmt]

FROM		[#Temp_Table_Claim]				AS [Temp_Table_Claim]

INNER JOIN	[#Temp_Table_Claim_Line_Total]	AS [Temp_Table_Claim_Line_Total]

	ON		[Temp_Table_Claim_Line_Total].[ClaimID] = [Temp_Table_Claim].[EDW_Claim_ClaimID]

WHERE		[Temp_Table_Claim].[REF_FormSubtype_ShortDescpt]	= 'UB-04'

AND			[Temp_Table_Claim].[REF_ClaimProcessingStatus_Code] IN

(

	'RTC', 'CLS'

)

AND			[Temp_Table_Claim].[REF_ClaimStatus_Code] IN

(

	'CLD'

)

AND

			(

					[Temp_Table_Claim].[EDW_Claim_LastModifiedDt]			>= @StartDate_In

		AND			[Temp_Table_Claim].[EDW_Claim_LastModifiedDt]			< @EndDate_In

			)

GROUP BY [Temp_Table_Claim].[REF_FormSubtype_ShortDescpt]

UNION

SELECT		6															AS [RowNumber],

			[Temp_Table_Claim].[REF_FormSubtype_ShortDescpt]			AS [REF_FormSubtype_ShortDesc],

			'Inst'														AS [ClaimType],

			'Rejected'													AS [EDW_Claim_ClaimStatusID],

			COUNT (*)												AS [ClaimStatusCount],

			SUM ([Temp_Table_Claim_Line_Total].[ClaimLine_LineAmount]) AS [ClaimLine_LineAmount]

FROM		[#Temp_Table_Claim]				AS [Temp_Table_Claim]

INNER JOIN	[#Temp_Table_Claim_Line_Total]	AS [Temp_Table_Claim_Line_Total]

	ON		[Temp_Table_Claim_Line_Total].[ClaimID] = [Temp_Table_Claim].[EDW_Claim_ClaimID]

WHERE		[Temp_Table_Claim].[REF_FormSubtype_ShortDescpt]	= 'UB-04'

AND			[Temp_Table_Claim].[REF_ClaimProcessingStatus_Code] IN

(

	'RTC', 'CLS'

)

AND			[Temp_Table_Claim].[REF_ClaimStatus_Code] IN

(

	'RMD'

)

AND

			(

					[Temp_Table_Claim].[EDW_Claim_LastModifiedDt]			>= @StartDate_In

		AND			[Temp_Table_Claim].[EDW_Claim_LastModifiedDt]			< @EndDate_In

			)

GROUP BY [Temp_Table_Claim].[REF_FormSubtype_ShortDescpt]

UNION

SELECT		7																	AS [RowNumber],

			[Temp_Table_Claim_Paid].[REF_FormSubtype_ShortDescpt]				AS [REF_FormSubtype_ShortDesc],

			'Inst'																AS [ClaimType],

			'Paid'																AS [EDW_Claim_ClaimStatusID],

			COUNT (*)														AS [ClaimStatusCount],

			SUM ([Temp_Table_Claim_Paid_Total].[ClaimLine_ClaimLineTotalPaid]) AS [ClaimLine_LineAmount]

FROM		[#Temp_Table_Claim_Paid]		AS [Temp_Table_Claim_Paid]

INNER JOIN	[#Temp_Table_Claim_Paid_Total]	AS [Temp_Table_Claim_Paid_Total]

	ON		[Temp_Table_Claim_Paid_Total].[ClaimID] = [Temp_Table_Claim_Paid].[EDW_Claim_ClaimID]

WHERE		[Temp_Table_Claim_Paid].[REF_FormSubtype_ShortDescpt]	= 'UB-04'

AND			[Temp_Table_Claim_Paid].[REF_ClaimProcessingStatus_Code] IN

(

	'CLS'

)

AND			[Temp_Table_Claim_Paid].[REF_ClaimStatus_Code] IN

(

	'OTP', 'TBP', 'END'

)

AND

			(

					[Temp_Table_Claim_Paid].[EDW_Claim_LastModifiedDt]			>= @StartDate_In

		AND			[Temp_Table_Claim_Paid].[EDW_Claim_LastModifiedDt]			< @EndDate_In

			)

GROUP BY [Temp_Table_Claim_Paid].[REF_FormSubtype_ShortDescpt]

ORDER BY [RowNumber]

;
  2. Naomi Nosonovsky 7,856 Reputation points
    2024-03-08T19:11:39.6366667+00:00

    Can you do each of the union parts into separate temp tables first and then do

    select 1, ....

    from #tempTable1

    UNION

    select 1, 0, 0, 0, ... where not exists (select 1 from #tempTable1)

    UNION

    ....

    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.