March 15, 2016 at 8:01 am
Hi,
I have a query below that I think is close to what I need, but I need to add a column(s) that has the
Number of APPROVED claims
Number of ENTERED claims
Number of PENDING claims
As of now, I am not sure how to get the totals, grouped by the batch.
DECLARE@CLAIMS int
DECLARE @from_date_entereddate
DECLARE @to_date_entereddate
----------------------------
SET @from_date_entered = GETDATE()
SET @to_date_entered= GETDATE()
--SET @from_date_entered = N'03/08/2016'
--SET @to_date_entered= N'03/14/2016'
----------------------------
SELECT@CLAIMS = batchtype_id
FROMbatchtype
WHEREbatchtype_ud = 'CLAIMS'
DECLARE @begin smalldatetime
SELECT @begin = CONVERT(smalldatetime, @from_date_entered)
DECLARE @end smalldatetime
SELECT @end = DATEADD (dd,1,CONVERT(smalldatetime, @to_date_entered))
DECLARE @begind smalldatetime
SELECT @begind = CONVERT(smalldatetime, @from_date_entered)
DECLARE @endd smalldatetime
SELECT @endd = CONVERT(smalldatetime, @to_date_entered)
DECLARE @CompanyName varchar(100)
SELECT @CompanyName = environment_info.company_name
FROMenvironment_info
IF OBJECT_ID('tempdb..#rpt_data') IS NOT NULL
begin
drop table #rpt_data
end
CREATE TABLE #rpt_data (CompanyNamevarchar(100) NULL,
Batchchar(3)NULL,
UserNamevarchar(35)NULL,
SQLUserNamevarchar (25)NULL,
claim_form_type_idintnull, --altered 11/25/02
Claim_type varchar (10) NULL,
status_typevarchar(15)NULL,
eachintNULL,
member_lastvarchar(35)NULL,
fromdatesmalldatetimeNULL,
todatesmalldatetimeNULL)
INSERT INTO #rpt_data (CompanyName,
Batch,
UserName, /*This field is named batch_sql_user_name in BALI */
SQLUserName,
claim_form_type_id,--altered 11/25/02
Claim_type,
status_type,
each,
member_last,
fromdate,
todate )
/***************************************************
GetBatch where length is 3
select * from claim_status
***/
SELECT DISTINCT@CompanyName,
substring(batch_user.batch, 1, 3) as batch_init,
batch_user.batch as user_name,
batch_user.batch_sql_user_name,
claim_form_type_id,--altered 11/25/02
null,--altered 11/25/02
claim_status.claim_status_ud,
claim.claim_id,
claim.member_last_name as member_last,
@begind,
@endd
FROM batch_user
inner join batchtype
on batch_user.batchtype_id = batchtype.batchtype_id,
claim
inner join claim_status
on claim.claim_status_id = claim_status.claim_status_id
WHERElen(batch_user.batch) = 3
and substring(claim.claim_ud, 9, 3) = batch_user.batch
and (claim.date_created < @end AND claim.date_created >= @begin)
--and batchtype.batchtype_id = @CLAIMS--altered 11/25/02
GROUP BY claim.claim_form_type_id, batch_user.batch_sql_user_name, batch_user.batch, claim_status.claim_status_ud, claim.member_last_name, claim.claim_id
/***************************************************
GetBatch where length is 2
***/
INSERT INTO #rpt_data (CompanyName,
Batch,
UserName, /*This field is named batch_sql_user_name in BALI */
SQLUserName,
claim_form_type_id,
Claim_type,
status_type,
each,
member_last,
fromdate,
todate )
SELECT DISTINCT @CompanyName,
substring(batch_user.batch, 1, 2) as batch_init,
batch_user.batch as user_name,
batch_user.batch_sql_user_name,
claim_form_type_id,
null,--altered
claim_status.claim_status_ud,--altered
claim.claim_id,
claim.member_last_name as member_last,
@begind,
@endd
FROM batch_user
inner join batchtype
on batch_user.batchtype_id = batchtype.batchtype_id,
claim
inner join claim_status
on claim.claim_status_id = claim_status.claim_status_id
WHERElen(batch_user.batch) = 2
and substring(claim.claim_ud, 9, 2) = batch_user.batch
and (claim.date_created < @end AND claim.date_created >= @begin)
--and batchtype.batchtype_id = @CLAIMS--altered 11/25/02
GROUP BY claim.claim_form_type_id, batch_user.batch_sql_user_name, batch_user.batch, claim_status.claim_status_ud, claim.member_last_name,claim.claim_id
/***************************************************
GetBatch where length is 1
***/
INSERT INTO #rpt_data (CompanyName,
Batch,
UserName, /*This field is named batch_sql_user_name in BALI */
SQLUserName,
claim_form_type_id,
Claim_type,
status_type,
each,
member_last,
fromdate,
todate )
SELECT DISTINCT@CompanyName,
substring(batch_user.batch, 1, 1) as batch_init,
batch_user.batch as user_name,
batch_user.batch_sql_user_name,
null,--altered
claim_status.claim_status_ud,--altered
claim_status.claim_status_ud,
claim.claim_id,
claim.member_last_name as member_last,
@begind,
@endd
FROM batch_user
inner join batchtype
on batch_user.batchtype_id = batchtype.batchtype_id,
claim
inner join claim_status
on claim.claim_status_id = claim_status.claim_status_id
WHERElen(batch_user.batch) = 1
and substring(claim.claim_ud, 9, 1) = batch_user.batch
and (claim.date_created < @end AND claim.date_created >= @begin)
--and batchtype.batchtype_id = @CLAIMS--altered 11/25/02
GROUP BY claim.claim_form_type_id, batch_user.batch_sql_user_name, batch_user.batch, claim_status.claim_status_ud, claim.member_last_name,claim.claim_id
update #rpt_data
set claim_type = 'HCFA'
where (claim_form_type_id = 2 or claim_form_type_id is null)
update #rpt_data
set claim_type = 'UB92'
where (claim_form_type_id = 1)
SELECT * FROM #rpt_data
March 15, 2016 at 8:03 am
How about you give us one query, not ~200 lines of code, along with create table statements and some sample data?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 15, 2016 at 2:22 pm
krypto69 (3/15/2016)
Hi,I have a query below that I think is close to what I need, but I need to add a column(s) that has the
Number of APPROVED claims
Number of ENTERED claims
Number of PENDING claims
The standard pattern for this type of requirement is
SUM(CASE WHEN Status = 'Approved' THEN 1 ELSE 0 END) AS NumberApproved
You can probably figure out where to add that to your code. If not, then do as Gail suggests and post a repro script that is as short as possible and contains all the ingredients that we can run it in our playpen database.
March 17, 2016 at 9:57 am
I tend to do what Hugo has suggested. Use a CASE to only return values when some criteria is met, then sum those.
March 17, 2016 at 10:46 am
For a single column, you could use COUNT(*) OVER(PARTITION BY Status)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply