I'm pretty sure I am doing that?
SET NOCOUNT ON
DECLARE @CLAIMS int
DECLARE @from_date_entered date
DECLARE @to_date_entered date
----------------------------
SET @from_date_entered = GETDATE()
SET @to_date_entered = GETDATE()
--SET @from_date_entered = N'03/16/2016'
--SET @to_date_entered = N'03/16/2016'
----------------------------
SELECT @CLAIMS = batchtype_id
FROM batchtype
WHERE batchtype_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
FROM environment_info
IF OBJECT_ID('tempdb..#rpt_data') IS NOT NULL
begin
drop table #rpt_data
end
CREATE TABLE #rpt_data (CompanyName varchar(100) NULL,
Batch char(3) NULL,
UserName varchar(35) NULL,
SQLUserName varchar (25) NULL,
claim_form_type_id int null,
Claim_type varchar (10) NULL,
status_type varchar(15) NULL,
each int NULL,
member_last varchar(35) NULL,
fromdate smalldatetime NULL,
todate smalldatetime NULL)
INSERT INTO #rpt_data ( CompanyName,
Batch,
UserName,
SQLUserName,
claim_form_type_id,
Claim_type,
status_type,
each,
member_last,
fromdate,
todate )
--countofentered as (select )
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
WHERE len(batch_user.batch) = 3
and substring(claim.claim_ud, 9, 3) = batch_user.batch
and (claim.date_created < @end AND claim.date_created >= @begin)
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
INSERT INTO #rpt_data ( CompanyName,
Batch,
UserName,
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
WHERE len(batch_user.batch) = 2
and substring(claim.claim_ud, 9, 2) = batch_user.batch
and (claim.date_created < @end AND claim.date_created >= @begin)
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
INSERT INTO #rpt_data ( CompanyName,
Batch,
UserName,
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
WHERE len(batch_user.batch) = 1
and substring(claim.claim_ud, 9, 1) = batch_user.batch
and (claim.date_created < @end AND claim.date_created >= @begin)
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
CompanyName as Company,
Batch,
USERNAME as [User Name],
SQLUserName as [System User Name],
--claim_form_type_id as [Claim Form Type],
CASE WHEN [claim_form_type_id] IS NULL THEN ('N/A') END as [Claim Form Type],
Claim_type as [Claim Type],--altered
status_type as [Status Type],
each as [Each],
member_last as [Member Name],
fromdate as [From Date],
todate as [To Date],
CASE WHEN [Status_Type] = 'Entered' THEN 1 ELSE 0 END AS Entered,
CASE WHEN [Status_Type] = 'Approved' THEN 1 ELSE 0 END AS Approved,
CASE WHEN [Status_Type] = 'Pending' THEN 1 ELSE 0 END AS Pending
FROM #rpt_data
