SQL Server Group By/Sum By Company Name, Balance [英] SQL Server Group By/Sum By Company Name, Balance
问题描述
我在 SQL Server 2005 中工作.我有一个创建临时表并从数据库表中提取数据的存储过程.此临时表包含付款人类别的名称、余额/信用、时间段(月)和余额的年龄"(当前、逾期 30 天、逾期 60 天等).
我现在需要做的是按保险公司名称和年龄对这些数据进行分组/求和.例如，Aetna 的每条记录都减少为一条记录.该记录具有当前余额字段、逾期余额 30 天字段、逾期余额 60 天字段等.Aetna 的每个当前余额都应在一个 Aetna 记录的第一个字段中求和.Aetna 的逾期余额每 30 天应在一条 Aetna 记录的第二个字段中求和，依此类推.结果表应如下所示:
付款人类别 Cur 30 60 90 120 150安泰 1234.00 987.00 0.00 123.00 456.00 0.00蓝十字 3216.00 2100.00 321.00 0.00 212.00 401.00信诺 56123.00 30887.00 0.00 0.00 0.00 0.00凯撒 21003.00 1806.00 904.00 305.00 0.00 0.00我当前的存储过程获取从应用程序 (VB.NET) 传入的开始日期，计算期间(即什么是 30 天，什么是 60 天等)，然后将所有余额记录拉入临时表.有一些预先确定的分组(Medicare、Medicaid、Private)，但任何不属于这些组的任何内容都会被分配到一个付款人类别，该类别只是付款人的姓名(例如 Aetna、Blue Cross 等).
我的问题是我事先不知道可能有多少不同的保险公司记录，所以我不确定如何根据记录和年龄进行分组/求和.如果可以避免的话，我宁愿不为每个循环做一个.任何帮助将不胜感激！
当前:
创建程序 [AgedReport]@VCurrStart 日期时间作为开始设置无计数；开始尝试创建表#t_temp(payor_class VARCHAR(30),bal DECIMAL(11, 2),期间日期时间，年龄 VARCHAR(10))创建表#t_class(payor_class VARCHAR(30),balType VARCHAR(10),balTotal DECIMAL(11,2),balAdv DECIMAL(11,2),balCurr DECIMAL(11,2),bal30 十进制(11,2),bal60 十进制(11,2),bal90 十进制(11,2),bal120 十进制(11,2)，bal150 十进制(11,2)，bal180 十进制(11,2)，bal210 十进制(11,2)，bal240 十进制(11,2)，bal270 十进制(11,2))声明@VCurrEnd DATETIMEDECLARE @V30Start DATETIME声明@V60Start DATETIME声明@V90开始日期时间声明@V120开始日期时间声明@V150开始日期时间声明@V180开始日期时间声明@V210开始日期时间声明@V240开始日期时间SET @VCurrEnd = DATEADD(DAY,-1,(DATEADD(MONTH,1,@VCurrStart)))SET @V30Start = DATEADD(month,-1,@VCurrStart)SET @V60Start = DATEADD(month,-2,@VCurrStart)SET @V90Start = DATEADD(month,-3,@VCurrStart)SET @V120Start = DATEADD(month,-4,@VCurrStart)SET @V150Start = DATEADD(month,-5,@VCurrStart)SET @V180Start = DATEADD(month,-6,@VCurrStart)SET @V210Start = DATEADD(month,-7,@VCurrStart)SET @V240Start = DATEADD(month,-8,@VCurrStart)插入 #t_temp(payor_class，巴尔，时期，年龄)选择清楚的(案件(当 p.payor_specific = 1 THEN(SELECT TOP 1 ar_accountFROM payor_seg ps哪里 ps.payor_code = b.payor_codeAND ps.start_date <= @VCurrEndORDER BY ps.start_date DESC)ELSE(选择前 1 个 ar_accountFROM plan_seg 请WHERE pls.plan_code = b.plan_codeAND pls.start_date <= @VCurrEndORDER BY pls.start_date DESC) END)当123100"然后私人"当123110"然后医疗补助"当123120"然后医疗补助"当123130"然后医疗补助"当123140"然后Medicare A"当123150"然后Medicare B"ELSE(当 p.payor_specific = 1 THEN(CASE WHEN (SELECT TOP 1 payor_name来自付款人 pWHERE p.payor_code = b.payor_code) 为 NULL THEN'未知'WHEN (SELECT TOP 1 payor_name来自付款人 pWHERE p.payor_code = b.payor_code) = '' THEN'未知'ELSE '*' + SUBSTRING(p.payor_name,1,19) END)当 p.payor_specific = 0 THEN(CASE WHEN (SELECT TOP 1 plan_description)FROM 计划 plWHERE pl.plan_code = b.plan_code) 为 NULL THEN'未知'WHEN (SELECT TOP 1 plan_descriptionFROM 计划 plWHERE pl.plan_code = b.plan_code) = '' THEN'未知'ELSE '*' + SUBSTRING(pl.plan_description,1,19) END)其他未知"完) 完),b.bal,b.期间，(选择案例什么时候>@VCurrStart 然后'ADV'WHEN 句点 = @VCurrStart THEN 'CUR'WHEN 周期 = @V30Start THEN '30'WHEN 周期 = @V60Start THEN '60'WHEN 周期 = @V90Start THEN '90'WHEN 周期 = @V120Start THEN '120'WHEN 周期 = @V150Start THEN '150'WHEN 周期 = @V180Start THEN '180'WHEN 周期 = @V210Start THEN '210'WHEN 周期 = @V240Start THEN '240'WHEN期间<@V240Start THEN '270'结尾)来自余额 b WITH(NOLOCK)SELECT * FROM #t_temp结束尝试开始捕捉收尾结尾除非我遗漏了您的某些要求，否则应该执行以下操作:
SELECT payor_class, SUM(balCurr), SUM(bal30), SUM(bal60), SUM(bal90), SUM(bal120),SUM(bal150)、SUM(bal180)、SUM(bal210)、SUM(bal240)、SUM(bal270)从温度GROUP BY payor_classORDER BY payor_class如果 payor_class 不是保险公司 Name 而只是一些代码，那么我可以将 INNER JOIN 添加到具有全名的表中.
I am working in SQL Server 2005. I have a stored procedure that creates a temp table and pulls data in from a database table. This temp table contains the name of the payor class, the balance/credit, the time period (month), and the "age" of the balance (current, 30 days past due, 60 days past due, etc.).
What I now need to do is group/sum this data by insurance company name and age. For example, every record for Aetna is reduced to one record. That one record has a field for current balance, a field for 30 days past due balance, a field for 60 days past due balance, etc. Every current balance for Aetna should be summed in the first field of the one Aetna record. Every 30 days past due balance for Aetna should be summed in the second field of the one Aetna record, etc., etc. The resulting table should look something like:
Payor Class Cur 30 60 90 120 150
Aetna 1234.00 987.00 0.00 123.00 456.00 0.00
Blue Cross 3216.00 2100.00 321.00 0.00 212.00 401.00
Cigna 56123.00 30887.00 0.00 0.00 0.00 0.00
Kaiser 21003.00 1806.00 904.00 305.00 0.00 0.00
My current stored procedure gets the start date passed in from the application (VB.NET), calculates the periods (i.e. what is 30 days, what is 60 days, etc.), then pulls all balance records into a temp table. There are some predetermined groupings (Medicare, Medicaid, Private) but anything that doesn't fall into one of those groups is assigned to a payor class that is just the payor's name (e.g. Aetna, Blue Cross, etc.).
My problem is that I have no idea ahead of time how many different insurance company records there might be, so I'm not sure how to group/sum based on that and on the age. I'd rather not do a for each loop if I can avoid it. Any help would be much appreciated!
Current:
CREATE PROCEDURE [AgedReport]
@VCurrStart DATETIME
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
CREATE TABLE #t_temp
(
payor_class VARCHAR(30),
bal DECIMAL(11, 2),
period DATETIME,
age VARCHAR(10)
)
CREATE TABLE #t_class
(
payor_class VARCHAR(30),
balType VARCHAR(10),
balTotal DECIMAL(11,2),
balAdv DECIMAL(11,2),
balCurr DECIMAL(11,2),
bal30 DECIMAL(11,2),
bal60 DECIMAL(11,2),
bal90 DECIMAL(11,2),
bal120 DECIMAL(11,2),
bal150 DECIMAL(11,2),
bal180 DECIMAL(11,2),
bal210 DECIMAL(11,2),
bal240 DECIMAL(11,2),
bal270 DECIMAL(11,2)
)
DECLARE @VCurrEnd DATETIME
DECLARE @V30Start DATETIME
DECLARE @V60Start DATETIME
DECLARE @V90Start DATETIME
DECLARE @V120Start DATETIME
DECLARE @V150Start DATETIME
DECLARE @V180Start DATETIME
DECLARE @V210Start DATETIME
DECLARE @V240Start DATETIME
SET @VCurrEnd = DATEADD(DAY,-1,(DATEADD(MONTH,1,@VCurrStart)))
SET @V30Start = DATEADD(month,-1,@VCurrStart)
SET @V60Start = DATEADD(month,-2,@VCurrStart)
SET @V90Start = DATEADD(month,-3,@VCurrStart)
SET @V120Start = DATEADD(month,-4,@VCurrStart)
SET @V150Start = DATEADD(month,-5,@VCurrStart)
SET @V180Start = DATEADD(month,-6,@VCurrStart)
SET @V210Start = DATEADD(month,-7,@VCurrStart)
SET @V240Start = DATEADD(month,-8,@VCurrStart)
INSERT INTO #t_temp
(
payor_class,
bal,
period,
age
)
SELECT
DISTINCT
(CASE
(CASE WHEN p.payor_specific = 1 THEN
(SELECT TOP 1 ar_account
FROM payor_seg ps
WHERE ps.payor_code = b.payor_code
AND ps.start_date <= @VCurrEnd
ORDER BY ps.start_date DESC)
ELSE (SELECT TOP 1 ar_account
FROM plan_seg pls
WHERE pls.plan_code = b.plan_code
AND pls.start_date <= @VCurrEnd
ORDER BY pls.start_date DESC) END)
WHEN '123100' THEN 'Private'
WHEN '123110' THEN 'Medicaid'
WHEN '123120' THEN 'Medicaid'
WHEN '123130' THEN 'Medicaid'
WHEN '123140' THEN 'Medicare A'
WHEN '123150' THEN 'Medicare B'
ELSE (CASE WHEN p.payor_specific = 1 THEN
(CASE WHEN (SELECT TOP 1 payor_name
FROM payor p
WHERE p.payor_code = b.payor_code) IS NULL THEN
'UNKNOWN'
WHEN (SELECT TOP 1 payor_name
FROM payor p
WHERE p.payor_code = b.payor_code) = '' THEN
'UNKNOWN'
ELSE '*' + SUBSTRING(p.payor_name,1,19) END)
WHEN p.payor_specific = 0 THEN
(CASE WHEN (SELECT TOP 1 plan_description
FROM plans pl
WHERE pl.plan_code = b.plan_code) IS NULL THEN
'UNKNOWN'
WHEN (SELECT TOP 1 plan_description
FROM plans pl
WHERE pl.plan_code = b.plan_code) = '' THEN
'UNKNOWN'
ELSE '*' + SUBSTRING(pl.plan_description,1,19) END)
ELSE 'UNKNOWN'
END) END),
b.bal,
b.period,
(SELECT CASE
WHEN period > @VCurrStart THEN 'ADV'
WHEN period = @VCurrStart THEN 'CUR'
WHEN period = @V30Start THEN '30'
WHEN period = @V60Start THEN '60'
WHEN period = @V90Start THEN '90'
WHEN period = @V120Start THEN '120'
WHEN period = @V150Start THEN '150'
WHEN period = @V180Start THEN '180'
WHEN period = @V210Start THEN '210'
WHEN period = @V240Start THEN '240'
WHEN period < @V240Start THEN '270'
END)
FROM balance b WITH(NOLOCK)
SELECT * FROM #t_temp
END TRY
BEGIN CATCH
END CATCH
END
Unless I'm missing some your requirements, the following should do it:
SELECT payor_class, SUM(balCurr), SUM(bal30), SUM(bal60), SUM(bal90), SUM(bal120),
SUM(bal150), SUM(bal180), SUM(bal210), SUM(bal240), SUM(bal270)
FROM Temp
GROUP BY payor_class
ORDER BY payor_class
If payor_class is not an Insurance Company Name but just some code, then I can add INNER JOIN to the table that has full names.
这篇关于SQL Server Group By/Sum By Company Name, Balance的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
