return 0 when count=null
-
I have a sproc in which I group count results for insurance claims in 3 catagories: approved, denied and processed for each day of the month.
My issue is that when the COUNT clause can't find any claims for one of the categories, it simply does not return any results and that category does not show up for that date. I would like to set it up so that if the COUNT can't find anything it returns a 0(zero) for that category for that date.
Here is my SPROC:
CREATE PROCEDURE dbo.ASP_DEALERSTATUS_PER_DAY
@START_DAY varchar(8),
@END_DAY varchar(8),
@PACODE varchar(5)
AS
SELECT distinct
substring (fdis_claim.claimset_id, 1,8) as "DATE",
CASE
when substring (fdis_claim.claimset_id, 1,8) = NULL AND FDIS_CLAIM.CLAIM_STATUS=2 THEN 'MAXA'
when substring (fdis_claim.claimset_id, 1,8) = NULL AND FDIS_CLAIM.CLAIM_STATUS=3 THEN 'MAXD'
when substring (fdis_claim.claimset_id, 1,8) = NULL AND FDIS_CLAIM.CLAIM_STATUS=5 THEN 'MAXP'
when substring (fdis_claim.claimset_id, 1,8) = NULL AND FDIS_CLAIM.CLAIM_STATUS=NULL THEN 'CMAX'
when FDIS_CLAIM.CLAIM_STATUS=2 then 'APPROVED'
when FDIS_CLAIM.CLAIM_STATUS=3 then 'DENIED'
when FDIS_CLAIM.CLAIM_STATUS=5 then 'PROCESSED'
ELSE 'TOTALS'
END 'STATUS_NAME',
COUNT (FDIS_CLAIM.CLAIM_ID) AS CLAIM_COUNT
FROM FDIS_CLAIM, FDIS_CLAIMSET
WHERE
FDIS_CLAIM.CLAIMSET_ID = FDIS_CLAIMSET.CLAIMSET_ID
AND FDIS_CLAIMSET.PNA_CODE = @PACODE
AND FDIS_CLAIM.CLAIM_STATUS in (2,3,5)
AND FDIS_CLAIM.CLAIMSET_ID >= @START_DAY+'000000000000'
AND FDIS_CLAIM.CLAIMSET_ID <= @END_DAY+'999999999999'
GROUP BY substring (fdis_claim.claimset_id, 1,8), fdis_claim.claim_status
WITH CUBE
order by substring (fdis_claim.claimset_id, 1,8) asc
GO
Here is a set of results:
DATE STATUS_NAME CLAIM_COUNT
-------- ----------- -----------
NULL CMAX 19
NULL MAXA 14
NULL MAXD 2
NULL MAXP 3
20070601 APPROVED 2
20070601 DENIED 1
20070601 TOTALS 3
20070604 APPROVED 2
20070604 PROCESSED 1
20070604 TOTALS 3
20070605 APPROVED 1
20070605 PROCESSED 1
20070605 TOTALS 2
20070606 APPROVED 1
20070606 TOTALS 1
20070607 APPROVED 2
20070607 PROCESSED 1
20070607 TOTALS 3
20070608 APPROVED 2
20070608 TOTALS 2
20070611 DENIED 1
20070611 TOTALS 1
20070614 APPROVED 3
20070614 TOTALS 3
20070615 APPROVED 1
20070615 TOTALS 1
(26 row(s) affected)
Thanks For your continued Help. -
Yo can use the isnull command and maipulate with that.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
You will have to use a left outer join to ensure that you get all the categories from FDIS_CLAIM:
SELECT distinct
substring (fdis_claim.claimset_id, 1,8) as "DATE",
CASE
when substring (fdis_claim.claimset_id, 1,8) = NULL AND FDIS_CLAIM.CLAIM_STATUS=2 THEN 'MAXA'
when substring (fdis_claim.claimset_id, 1,8) = NULL AND FDIS_CLAIM.CLAIM_STATUS=3 THEN 'MAXD'
when substring (fdis_claim.claimset_id, 1,8) = NULL AND FDIS_CLAIM.CLAIM_STATUS=5 THEN 'MAXP'
when substring (fdis_claim.claimset_id, 1,8) = NULL AND FDIS_CLAIM.CLAIM_STATUS=NULL THEN 'CMAX'
when FDIS_CLAIM.CLAIM_STATUS=2 then 'APPROVED'
when FDIS_CLAIM.CLAIM_STATUS=3 then 'DENIED'
when FDIS_CLAIM.CLAIM_STATUS=5 then 'PROCESSED'
ELSE 'TOTALS'
END 'STATUS_NAME',
COUNT (FDIS_CLAIM.CLAIM_ID) AS CLAIM_COUNT
FROM FDIS_CLAIM
LEFT OUTER JOIN FDIS_CLAIMSET
on
FDIS_CLAIM.CLAIMSET_ID = FDIS_CLAIMSET.CLAIMSET_ID
AND FDIS_CLAIMSET.PNA_CODE = @PACODE
AND FDIS_CLAIM.CLAIM_STATUS in (2,3,5)
AND FDIS_CLAIM.CLAIMSET_ID >= @START_DAY+'000000000000'
AND FDIS_CLAIM.CLAIMSET_ID <= @END_DAY+'999999999999'
GROUP BY substring (fdis_claim.claimset_id, 1,8), fdis_claim.claim_status
WITH CUBE
order by substring (fdis_claim.claimset_id, 1,8) asc
Not tested but should give you the idea.
J
-
If I use as suggested with "left outer join on" then none of my where statements work correctly. I get 18,000 results instead of 26.
Thanks For your continued Help. -
Two ways suggest themselves
1. Keeping the current format
At the start of the sproc
select distinct substring (fdis_claim.claimset_id, 1,8) date into #temp1
FROM FDIS_CLAIM, FDIS_CLAIMSET
WHERE
FDIS_CLAIM.CLAIMSET_ID = FDIS_CLAIMSET.CLAIMSET_ID
AND FDIS_CLAIMSET.PNA_CODE = @PACODE
AND FDIS_CLAIM.CLAIM_STATUS in (2,3,5)
AND FDIS_CLAIM.CLAIMSET_ID >= @START_DAY+'000000000000'
AND FDIS_CLAIM.CLAIMSET_ID <= @END_DAY+'999999999999'
select date, type='PROCESSED', hits=0 into #temp2 from #temp1
union select date,'APPROVED',0
union etc
then make your main select a select into #temp3,
update #temp2 on the join with #temp3
and select from #temp2
2a. If you can live with having the values in columns rather than rows
select
substring (fdis_claim.claimset_id, 1,8) as "DATE",
sum (CASE
when substring (fdis_claim.claimset_id, 1,8) = NULL AND FDIS_CLAIM.CLAIM_STATUS=2 THEN 1 else 0 end) MAXA,
sum (CASE when substring (fdis_claim.claimset_id, 1,8) = NULL AND FDIS_CLAIM.CLAIM_STATUS=3 THEN 1 else 0 end) MAXD,
sum (CASE when substring (fdis_claim.claimset_id, 1,8) = NULL AND FDIS_CLAIM.CLAIM_STATUS=5 THEN 1 else 0 end) MAXP,
sum (CASE when substring (fdis_claim.claimset_id, 1,8) = NULL AND FDIS_CLAIM.CLAIM_STATUS=NULL then 1 else 0 end) CMAX,
sum (CASE when substring (fdis_claim.claimset_id, 1,8) is not null and FDIS_CLAIM.CLAIM_STATUS=2 then 1 else 0 end APPROVED etc
2b. Put the result of 2a into a temporary table
then select date,'PROCESSED' ,PROCESSED from temporary table
union select date,'MAXA', MAXA etc
-
Hi,
what I would recommend is creating a new table Status(code INT, description VARCHAR(20)) - I suppose you don't have it, otherwise you wouldn't have to assign values in CASE. Then it will be easier to do any manipulations of the kind you need. Generally, it isn't a good idea to hardcode descriptions in SQL, all should be in tables.
BTW, what about dates within specified range, when there are no rows at all - do you want to display these days with all existing states and 0 in the count, or not display them at all?
Oh, and I'm a bit stumped about the "WHEN substring(....) = NULL". First, you should always use IS NULL expression, and not "=" (described in BooksOnLine). Second, why bother with substring? The entire string must be NULL, there is no way that only a part of it would be NULL.
-
A. You can't use "= Null". Null is unknown and nothing can equal something that is unknown. Use "is null" or "is not null."
B. Use isnull() for returning 0 when columns are null.
-
COUNT clause can't find any claims for one of the categories, it simply does not return any results and that category does not show up for that date COUNT is not the problem, your query will only return the data that matches the where clause
What you need to do is produce a cartesian of the required dates (either the ones present or all dates within the range using a calendar table) and the CLAIM_STATUS values 2,3 and 5
Then LEFT JOIN the results of the select of FDIS_CLAIM and FDIS_CLAIMSET (with the where clause)
e.g.
SELECT c.[DATE],
CASE
WHEN c.[DATE] IS NULL AND s.CLAIM_STATUS=2 THEN 'MAXA'
WHEN c.[DATE] IS NULL AND s.CLAIM_STATUS=3 THEN 'MAXD'
WHEN c.[DATE] IS NULL AND s.CLAIM_STATUS=5 THEN 'MAXP'
WHEN c.[DATE] IS NULL AND s.CLAIM_STATUS IS NULL THEN 'CMAX'
WHEN s.CLAIM_STATUS=2 then 'APPROVED'
WHEN s.CLAIM_STATUS=3 then 'DENIED'
WHEN s.CLAIM_STATUS=5 then 'PROCESSED'
ELSE 'TOTALS'
END AS [STATUS_NAME],
COUNT(x.CLAIM_ID) AS [CLAIM_COUNT]
FROM (SELECT c1.[Date] FROM [Calendar] c1 WHERE c1.[Date] >= @START_DAY AND c1.[Date] < DATEADD(day,1,@END_DAY)) c
CROSS JOIN
(SELECT 2 AS [CLAIM_STATUS] UNION SELECT 3 UNION SELECT 5) s
LEFT JOIN
(SELECT cl.CLAIM_ID,CAST(SUBSTRING(cl.claimset_id,1,8) as [DATE],cl.CLAIM_STATUS
FROM FDIS_CLAIM cl
INNER JOIN FDIS_CLAIMSET cs ON cs.CLAIMSET_ID = cl.CLAIMSET_ID AND cs.PNA_CODE = @PACODE
WHERE cl.CLAIM_STATUS in (2,3,5)
AND cl.CLAIMSET_ID >= @START_DAY+'000000000000'
AND cl.CLAIMSET_ID <= @END_DAY+'999999999999') x
ON x.[Date] = c.[Date] AND x.CLAIM_STATUS = s.CLAIM_STATUS
GROUP BY c.[Date], s.CLAIM_STATUS WITH CUBE
ORDER BY c.[Date] ASC
Far away is close at hand in the images of elsewhere.
Anon. -
I am having trouble with your post...could we try something more simple for me to get the underlying logic? Maybe we can do a select of 4 days and keep it to a single table for the data.
my variables are actually strings NOT dates, that is why I was using substring.
USER INPUT:
@START_DAY = '20070601'
@END_DAY = '20070604'
SINGLE TABLE: [FDIS_CLAIM]
CREATE TABLE [dbo].[FDIS_CLAIM] (
[CLAIMSET_ID] [varchar] (30) NOT NULL ,
[CLAIM_ID] [varchar] (3) NOT NULL ,
[CLAIM_STATUS] [int] NOT NULL ,
[ACES_CODE] [varchar] (10) NULL ,
[CLARIFICATION_FLAG] [char] (1) NULL ,
[AUDIT_AGREE_FLAG] [char] (1) NULL
)
now I would like to select the the claim_status and count(claim_status) for each day 6-1 thru 6-5. My goal is outputing zeros for each record if there is not a status or count(status) for one of the days.
DESIRED RESULTS:
DATE STATUS COUNT
20070601 2 4
20070601 3 6
20070601 5 3
20070602 2 0 <--when no count(claim_status)
20070602 3 7
20070602 5 4
20070603 2 0
20070603 3 6
20070603 5 3
20070604 2 0
20070604 3 7
20070604 5 4
Thanks For your continued Help. -
Hi,
I have to repeat my question : what about dates within specified range, when there are no rows at all - do you want to display these days with all existing states and 0 in the count, or not display them at all?
Let's say, for the date 20070602 there are no rows at all. Do you want to display these rows or not :
20070602 2 0
20070602 3 0
20070602 5 0
Soultion will depend on what you need.
Generally I think that using auxiliary table containing all dates for several years back and into future will be the best way. Look for posts by Jeff Moden on this forum, about table "Numbers" (or Tally table).
-
sorry...YES. I do want to display all 3 status counts for each day regardless if there 0 counts for that status or 50 counts for that status. If there are no rows for 20070602 I would like to show how you typed it in
Thanks For your continued Help. -
Prepare permanent auxiliary table (also called "tally table") of all relevant dates, if you don't have it yet:
CREATE TABLE Dates (number INT, date_char CHAR(10) NOT NULL, date_dt datetime
CONSTRAINT PK_cislo PRIMARY KEY CLUSTERED (number)
WITH FILLFACTOR = 100)
INSERT INTO Dates (number, date_char, date_dt)
SELECT (a.Number * 256) + b.Number AS Number, convert(char(10),convert(datetime,(a.Number * 256) + b.Number),104) as date_char,
convert(datetime,(a.Number * 256) + b.Number) as date_dt
FROMmaster..spt_values a,
master..spt_values b
WHERE a.Type = 'p'
AND b.Type = 'p'
Then start with the Dates table, left join the result you already have on date... this will make sure no date will be missing.
Well, and as to the various statuses, I think you should create a table Status as I suggested before, and using a similar approach as with the dates, you should be able to produce one row for each date and status.
-
my variables are actually strings NOT dates, that is why I was using substring.
Does not matter, the input dates will be implicitly converted, I explicitly converted the date on the claim
First, as Vladan states, use a calendar table, e.g
CREATE TABLE dbo.[Calendar] ([Date] datetime)
INSERT INTO dbo.[Calendar] ([Date]) VALUES ('20070601')
INSERT INTO dbo.[Calendar] ([Date]) VALUES ('20070602')
INSERT INTO dbo.[Calendar] ([Date]) VALUES ('20070603')
INSERT INTO dbo.[Calendar] ([Date]) VALUES ('20070604')
for this example I just used the dates within the parameter range, you will need to insert dates for all possible ranges
Now for the query, first we need to get all the dates in the parameter range
SELECT c1.[Date] FROM [Calendar] c1 WHERE c1.[Date] >= @START_DAY AND c1.[Date] < DATEADD(day,1,@END_DAY)
This will produce
Date
----------
2007-06-01
2007-06-02
2007-06-03
2007-06-04
Next, get all required CLAIM_STATUS values
SELECT 2 AS [CLAIM_STATUS] UNION SELECT 3 UNION SELECT 5
This will produce
CLAIM_STATUS
------------
2
3
5
Then, produce a cartesian (CROSS JOIN) of each of these
SELECT *
FROM (SELECT c1.[Date] FROM [Calendar] c1 WHERE c1.[Date] >= @START_DAY AND c1.[Date] < DATEADD(day,1,@END_DAY)) c
CROSS JOIN
(SELECT 2 AS [CLAIM_STATUS] UNION SELECT 3 UNION SELECT 5) s
This will produce
Date CLAIM_STATUS
---------- ------------
2007-06-01 2
2007-06-01 3
2007-06-01 5
2007-06-02 2
2007-06-02 3
2007-06-02 5
2007-06-03 2
2007-06-03 3
2007-06-03 5
2007-06-04 2
2007-06-04 3
2007-06-04 5
Now LEFT JOIN your query, which gets the claims and claimset for the required CLAIM_STATUS and within the start/end dates. Note that I cast the date portion to datetime.
LEFT JOIN
(SELECT cl.CLAIM_ID,CAST(SUBSTRING(cl.claimset_id,1,8) as [DATE],cl.CLAIM_STATUS
FROM FDIS_CLAIM cl
INNER JOIN FDIS_CLAIMSET cs ON cs.CLAIMSET_ID = cl.CLAIMSET_ID AND cs.PNA_CODE = @PACODE
WHERE cl.CLAIM_STATUS in (2,3,5)
AND cl.CLAIMSET_ID >= @START_DAY+'000000000000'
AND cl.CLAIMSET_ID <= @END_DAY+'999999999999') x
ON x.[Date] = c.[Date] AND x.CLAIM_STATUS = s.CLAIM_STATUS
Say for argument sake there was only one claim for 2007-06-03 and it was status 3 then it will produce
Date CLAIM_STATUS CLAIM_ID
---------- ------------ --------
2007-06-01 2 null
2007-06-01 3 null
2007-06-01 5 null
2007-06-02 2 null
2007-06-02 3 null
2007-06-02 5 null
2007-06-03 2 null
2007-06-03 3 12345
2007-06-03 5 null
2007-06-04 2 null
2007-06-04 3 null
2007-06-04 5 null
Then all that is left is for the select
SELECT c.[DATE],
CASE
WHEN c.[DATE] IS NULL AND s.CLAIM_STATUS=2 THEN 'MAXA'
WHEN c.[DATE] IS NULL AND s.CLAIM_STATUS=3 THEN 'MAXD'
WHEN c.[DATE] IS NULL AND s.CLAIM_STATUS=5 THEN 'MAXP'
WHEN c.[DATE] IS NULL AND s.CLAIM_STATUS IS NULL THEN 'CMAX'
WHEN s.CLAIM_STATUS=2 then 'APPROVED'
WHEN s.CLAIM_STATUS=3 then 'DENIED'
WHEN s.CLAIM_STATUS=5 then 'PROCESSED'
ELSE 'TOTALS'
END AS [STATUS_NAME],
COUNT(x.CLAIM_ID) AS [CLAIM_COUNT]
Note, because we are COUNTing x.CLAIM_ID, COUNT will return zero for NULL's, ie no claims for a specific date/status
And the grouping/sorting
GROUP BY c.[Date], s.CLAIM_STATUS WITH CUBE
ORDER BY c.[Date] ASC
Far away is close at hand in the images of elsewhere.
Anon. -
Sorry for splitting it into 2 posts... now a hint at the solution, using the Dates and Status tables (I also had to add a date column to your simplified FDIS_CLAIM table):
CREATE TABLE [dbo].[FDIS_CLAIM] (
[CLAIMSET_ID] [varchar] (30) NOT NULL ,
[CLAIM_ID] [varchar] (3) NOT NULL ,
[CLAIM_STATUS] [int] NOT NULL ,
[ACES_CODE] [varchar] (10) NULL ,
[CLARIFICATION_FLAG] [char] (1) NULL ,
[AUDIT_AGREE_FLAG] [char] (1) NULL,
date_col datetime)
CREATE TABLE Status(code INT, description VARCHAR(20))
insert into status (code, description) values (1,'failure')
insert into status (code, description) values (2,'success')
insert into status (code, description) values (3,'being processed')
SELECT d.date_dt, s.[description], count(claim_id)
FROM Dates d
CROSS JOIN STATUS s
LEFT JOIN FDIS_CLAIM f on f.date_col = d.date_dt
WHERE d.date_dt between '20070601' AND '20070604'
GROUP BY d.date_dt, s.[description]
ORDER BY d.date_dt
CAUTION! This query is simplified and will only work if the date_col contains dates without time portion (time is 00:00:00). You may have to modify it for reliable results in production, but I wanted to keep it as simple as possible for this demo.
EDIT : Hello David
seems we were posting the same at the same time... -
seems we were posting the same at the same time Yeah, looks like it
Hope our solutions are on the right track as well
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply