---> SELECT monthSlot, SUM(VerifiedAmount) VerifiedAmount, SUM(requestedAmount) requestedAmount FROM #_claimTableName# WHERE 1=1 AND claimDate > AND claimDate <= AND sku = '#url.sku#' AND claimantPartnerId = #val(url.DistributorId)# AND contractPartnerId = #val(url.OperatorId)# GROUP BY monthSlot, monthNbr ORDER BY monthNbr SELECT requestedAmount as Amount FROM qmd_Month WHERE 1=1 AND monthSlot = '#url.month#' SELECT SUM(requestedAmount) YTDAmount, Max(requestedAmount) MaxMonthAmount, Min(requestedAmount) MinMonthAmount, AVG(requestedAmount) AvgMonthAmount FROM qmd_Month SELECT requestedAmount as Amount FROM qmd_Month WHERE 1=1 AND monthSlot = '#_priorMonth#' SELECT 0.0 xmth, 0.0 xYTD, 0.0 xMax, 0.0 xMin, 0.0 xAvg, 0.0 xPmth, '' xYear FROM qmd_Month WHERE 1=2 SELECT claimStatusName status, CASE WHEN claimStatusName = 'Repayment' THEN SUM(verifiedAmount) ELSE 0.0 END as RepaymentVerifiedAmount, CASE WHEN claimStatusName = 'Repayment' THEN SUM(requestedAmount) ELSE 0.0 END as RepaymentRequestedAmount, CASE WHEN claimStatusName = 'Cleared' THEN SUM(verifiedAmount) WHEN claimStatusName = 'Repayment' THEN SUM(requestedAmount) - SUM(verifiedAmount) WHEN claimStatusName = 'InProcess' THEN SUM(verifiedAmount) WHEN claimStatusName = 'OnHold' THEN SUM(verifiedAmount) ELSE 0.0 END as amount FROM #_claimTableName# WHERE 1=1 AND claimDate > AND claimDate <= AND monthSlot = '#url.month#' AND sku = '#url.sku#' AND claimantPartnerId = #val(url.DistributorId)# AND contractPartnerId = #val(url.OperatorId)# GROUP BY claimStatusName UNION SELECT TOP 1 'Cleared', 0.0, 0.0, 0.0 FROM #_claimTableName# ORDER BY claimStatusName SELECT SUM(RepaymentVerifiedAmount) RepaymentVerifiedAmount, SUM(RepaymentRequestedAmount) RepaymentRequestedAmount FROM result WHERE status = 'Repayment' SELECT status, SUM(amount) amount FROM result GROUP BY status SELECT monthNbr, monthSlot mth, claimStatusName Status, CASE WHEN claimStatusName = 'Repayment' THEN SUM(verifiedAmount) ELSE 0.0 END as RepaymentVerifiedAmount, CASE WHEN claimStatusName = 'Cleared' THEN SUM(verifiedAmount) WHEN claimStatusName = 'Repayment' THEN SUM(requestedAmount) - SUM(verifiedAmount) WHEN claimStatusName = 'InProcess' THEN SUM(verifiedAmount) WHEN claimStatusName = 'OnHold' THEN SUM(verifiedAmount) ELSE 0.0 END as Amount FROM #_claimTableName# WHERE 1=1 AND claimDate > AND claimDate <= AND sku = '#url.sku#' AND claimantPartnerId = #val(url.DistributorId)# AND contractPartnerId = #val(url.OperatorId)# GROUP BY monthNbr, monthSlot, claimStatusName UNION SELECT TOP 1 #i#, '#MonthAsString(i)#' mth, 'Cleared' Status, 0, 0 FROM #_claimTableName# ORDER BY monthNbr, status SELECT claimStatusName Status FROM #_claimTableName# GROUP BY claimStatusName SELECT '' mth ,0.0 #status# FROM qmd_Month WHERE 1=2 SELECT monthNbr, Status, SUM(amount) amount FROM qmd_month WHERE status = '#qmd_statusNames.status#' AND monthNbr = #qmd_month.monthNbr# GROUP BY monthNbr, Status SELECT SUM(repaymentVerifiedAmount) repaymentVerifiedAmount FROM qmd_month WHERE monthNbr = #qmd_month.monthNbr# AND status = 'Repayment' GROUP BY monthNbr, Status SELECT Sku, SkuDesc Description, SUM(VerifiedAmount) amount FROM #_claimTableName# WHERE 1=1 AND sku = '#url.sku#' AND monthSlot = '#url.month#' AND claimantPartnerId = #val(url.DistributorId)# AND contractPartnerId = #val(url.OperatorId)# GROUP BY Sku, SkuDesc ORDER BY Sku, SkuDesc SELECT claimantPartnerId DistributorId, claimantPartnerName Description, SUM(VerifiedAmount) amount FROM #_claimTableName# WHERE claimantPartnerType = 'DISTRIBUTOR' AND claimantPartnerId = #val(url.DistributorId)# AND monthSlot = '#url.month#' AND sku = '#url.sku#' AND claimantPartnerId = #val(url.DistributorId)# AND contractPartnerId = #val(url.OperatorId)# GROUP BY claimantPartnerId, claimantPartnerName ORDER BY claimantPartnerName SELECT contractPartnerId OperatorId, contractPartnerName Description, SUM(VerifiedAmount) amount FROM #_claimTableName# WHERE contractPartnerType = 'OPERATOR' AND contractPartnerId = #val(url.OperatorId)# AND monthSlot = '#url.month#' AND sku = '#url.sku#' AND claimantPartnerId = #val(url.DistributorId)# AND contractPartnerId = #val(url.OperatorId)# GROUP BY contractPartnerId, contractPartnerName ORDER BY contractPartnerName select baseData.claimantPartnerName as Distributor, baseData.SubmittedClaims as SubmittedClaims, baseData.DeductedAmt as DeductedAmt, baseData.VerifiedAmt as VerifiedAmt, baseData.UnverifiedAmt as UnverifiedAmt, baseData.InaccuracteClaims as InaccuracteClaims, (cast(baseData.InaccuracteClaims as DECIMAL(10,2))/cast(baseData.SubmittedClaims as DECIMAL(10,2))) *100 as pctInaccurateClaims, (cast(baseData.UnverifiedAmt as DECIMAL(10,2))/cast(baseData.DeductedAmt as DECIMAL(10,2))) *100 as pctUnverifiedAmt, ( case when baseData.SubmittedClaims < 100.1 then 'VERY_LOW_CLAIM_VOL' when baseData.SubmittedClaims > 100.1 and baseData.SubmittedClaims < 750.1 then 'LOW_CLAIM_VOL' when baseData.SubmittedClaims > 750.1 and baseData.SubmittedClaims < 7500.1 then 'MED_CLAIM_VOL' else 'HIGH_CLAIM_VOL' end ) as DistributorScale from ( select claimantPartnerName, claimantPartnerId, count (*) as SubmittedClaims, sum (requestedAmount) as DeductedAmt, sum (verifiedAmount) VerifiedAmt, sum (requestedAmount - verifiedAmount) as UnverifiedAmt, ( select count (*) from #_claimTableName# where (requestedAmount - verifiedAmount) > 0 and claimantPartnerId = ClaimsTbl.claimantPartnerId ) as InaccuracteClaims from #_claimTableName# as ClaimsTbl group by claimantPartnerId,claimantPartnerName ) baseData select baseData.sku as sku, baseData.claimantPartnerName as Distributor, baseData.SubmittedClaims as SubmittedClaims, baseData.DeductedAmt as DeductedAmt, baseData.VerifiedAmt as VerifiedAmt, baseData.UnverifiedAmt as UnverifiedAmt, baseData.InaccuracteClaims as InaccuracteClaims, (cast(baseData.InaccuracteClaims as DECIMAL(10,2))/cast(baseData.SubmittedClaims as DECIMAL(10,2))) *100 as pctInaccurateClaims, (cast(baseData.UnverifiedAmt as DECIMAL(10,2))/cast(baseData.DeductedAmt as DECIMAL(10,2))) *100 as pctUnverifiedAmt from ( select sku, claimantPartnerId, claimantPartnerName, count (*) as SubmittedClaims, sum (requestedAmount) as DeductedAmt, sum (verifiedAmount) VerifiedAmt, sum (requestedAmount - verifiedAmount) as UnverifiedAmt, ( select count (*) from #_claimTableName# where (requestedAmount - verifiedAmount) > 0 and claimantPartnerId = ClaimsTbl.claimantPartnerId and sku = ClaimsTbl.sku ) as InaccuracteClaims from #_claimTableName# as ClaimsTbl where '1' = '1' and claimantPartnerName = '#(URLDecode(url.distributor))#' group by sku,claimantPartnerId,claimantPartnerName ) baseData SELECT * FROM #_claimTableName# WHERE 1=2 Datasource:
Record Count: -- ElapsedTime:
#queryToJSON(result,"#_numericFieldList#")#