June 26, 2008 at 1:04 pm
File test1 has a total of 52 claims.
There are zero Encounter or ABD claims
(intentionally, trying to mirror live data as much as possible)
When I run this internal query and group by the File Name only,
I get an error because enrollkeys.programid is not in either an aggregate function,
or a Group By clause.
however, it is within an aggregate (sort of -
CASE
WHEN #member.programid = 'QMXHPQ1621'
THEN ISNULL(count(distinct #PLANDATA_Claim.claimid),
count(distinct #ENCOUNTER_Claim.claimid))
ELSE
END AS "ABD",)
except it acts as a criteria, not a result.
Placing it explicitly within the Group By clause causes incorrect totals, as duplicate counts result from
double counting programids QMXHPQ0001 and QMXHPQ0002 (both are subsets of CFC).
Looks like this:
File Name FFSEncounterABDCFC
test1260026
test1260026
test21001
If I attempt to use the CASE statements that are in the SELECT within the GROUP, it tells me:
Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause,
so it obviously recognizes the aggregate.
So I read up on the forums, found some Group By questions, and found this:
http://www.sqlservercentral.com/Forums/Topic2534-8-2.aspx?Highlight=group
which looks like I should be using a derived table as below. (myButt)
While this works on the test data, I'm concerned that it will be slow when I'm processing the typical volumes
of 200,000 claims, which could be anywhere up to a million rows of junk. I've actually cleaned up the JOINs to
just deal with what I needed for this question, the live version has to LEFT JOIN about six more tables,
depending on how much detail I need, due to the chaotic manner in which they built the dbs.
thoughts? Am I just being paranoid?
Secondary question - I think the JOIN on ISNULL(#PLANDATA_Claim.claimid,#ENCOUNTER_Claim.claimid) is working
because it is returning what I'm expecting, which leads me to think I'm overlooking something. Is this a legal
manner in which to JOIN the data? The claimid will always either be in one or the other, never both.
Data below:
IF object_id('Tempdb..#files') IS NOT NULL BEGIN DROP TABLE #files END
CREATE TABLE #files (controlID char(10), claimid int, memberid int)
IF object_id('Tempdb..#member') IS NOT NULL BEGIN DROP TABLE #member END
CREATE TABLE #member (memberid int, programid char(10))
IF object_id('Tempdb..#PLANDATA_Claim') IS NOT NULL BEGIN DROP TABLE #PLANDATA_Claim END
CREATE TABLE #PLANDATA_Claim (nameFile char(10), claimid int)
IF object_id('Tempdb..#ENCOUNTER_Claim') IS NOT NULL BEGIN DROP TABLE #ENCOUNTER_Claim END
CREATE TABLE #ENCOUNTER_Claim (nameFile char(10), claimid int)
-- INSERT data into #files
INSERT INTO #files (controlID, claimid, memberid) VALUES ('test1', 1, 1)
INSERT INTO #files (controlID, claimid, memberid) VALUES ('test1', 2, 2)
INSERT INTO #files (controlID, claimid, memberid) VALUES ('test1', 3, 3)
INSERT INTO #files (controlID, claimid, memberid) VALUES ('test1', 4, 4)
INSERT INTO #files (controlID, claimid, memberid) VALUES ('test1', 5, 5)
INSERT INTO #files (controlID, claimid, memberid) VALUES ('test1', 6, 6)
INSERT INTO #files (controlID, claimid, memberid) VALUES ('test1', 7, 7)
INSERT INTO #files (controlID, claimid, memberid) VALUES ('test1', 8, 8)
INSERT INTO #files (controlID, claimid, memberid) VALUES ('test1', 9, 9)
INSERT INTO #files (controlID, claimid, memberid) VALUES ('test1', 10, 10)
INSERT INTO #files (controlID, claimid, memberid) VALUES ('test1', 11, 11)
INSERT INTO #files (controlID, claimid, memberid) VALUES ('test1', 12, 12)
INSERT INTO #files (controlID, claimid, memberid) VALUES ('test1', 13, 13)
INSERT INTO #files (controlID, claimid, memberid) VALUES ('test1', 14, 14)
INSERT INTO #files (controlID, claimid, memberid) VALUES ('test1', 15, 15)
INSERT INTO #files (controlID, claimid, memberid) VALUES ('test1', 16, 16)
INSERT INTO #files (controlID, claimid, memberid) VALUES ('test1', 17, 17)
INSERT INTO #files (controlID, claimid, memberid) VALUES ('test1', 18, 18)
INSERT INTO #files (controlID, claimid, memberid) VALUES ('test1', 19, 19)
INSERT INTO #files (controlID, claimid, memberid) VALUES ('test1', 20, 20)
INSERT INTO #files (controlID, claimid, memberid) VALUES ('test1', 21, 21)
INSERT INTO #files (controlID, claimid, memberid) VALUES ('test1', 22, 22)
INSERT INTO #files (controlID, claimid, memberid) VALUES ('test1', 23, 23)
INSERT INTO #files (controlID, claimid, memberid) VALUES ('test1', 24, 24)
INSERT INTO #files (controlID, claimid, memberid) VALUES ('test1', 25, 25)
INSERT INTO #files (controlID, claimid, memberid) VALUES ('test1', 26, 26)
INSERT INTO #files (controlID, claimid, memberid) VALUES ('test1', 27, 27)
INSERT INTO #files (controlID, claimid, memberid) VALUES ('test1', 28, 28)
INSERT INTO #files (controlID, claimid, memberid) VALUES ('test1', 29, 29)
INSERT INTO #files (controlID, claimid, memberid) VALUES ('test1', 30, 30)
INSERT INTO #files (controlID, claimid, memberid) VALUES ('test1', 31, 31)
INSERT INTO #files (controlID, claimid, memberid) VALUES ('test1', 32, 32)
INSERT INTO #files (controlID, claimid, memberid) VALUES ('test1', 33, 33)
INSERT INTO #files (controlID, claimid, memberid) VALUES ('test1', 34, 34)
INSERT INTO #files (controlID, claimid, memberid) VALUES ('test1', 35, 35)
INSERT INTO #files (controlID, claimid, memberid) VALUES ('test1', 36, 36)
INSERT INTO #files (controlID, claimid, memberid) VALUES ('test1', 37, 37)
INSERT INTO #files (controlID, claimid, memberid) VALUES ('test1', 38, 38)
INSERT INTO #files (controlID, claimid, memberid) VALUES ('test1', 39, 39)
INSERT INTO #files (controlID, claimid, memberid) VALUES ('test1', 40, 40)
INSERT INTO #files (controlID, claimid, memberid) VALUES ('test1', 41, 41)
INSERT INTO #files (controlID, claimid, memberid) VALUES ('test1', 42, 42)
INSERT INTO #files (controlID, claimid, memberid) VALUES ('test1', 43, 43)
INSERT INTO #files (controlID, claimid, memberid) VALUES ('test1', 44, 44)
INSERT INTO #files (controlID, claimid, memberid) VALUES ('test1', 45, 45)
INSERT INTO #files (controlID, claimid, memberid) VALUES ('test1', 46, 46)
INSERT INTO #files (controlID, claimid, memberid) VALUES ('test1', 47, 47)
INSERT INTO #files (controlID, claimid, memberid) VALUES ('test1', 48, 48)
INSERT INTO #files (controlID, claimid, memberid) VALUES ('test1', 49, 49)
INSERT INTO #files (controlID, claimid, memberid) VALUES ('test1', 50, 50)
INSERT INTO #files (controlID, claimid, memberid) VALUES ('test1', 51, 51)
INSERT INTO #files (controlID, claimid, memberid) VALUES ('test1', 52, 52)
INSERT INTO #files (controlID, claimid, memberid) VALUES ('test2', 53, 53)
-- INSERT data into #member
INSERT INTO #member (memberid,programid) VALUES (1, 'QMXHPQ0001')
INSERT INTO #member (memberid,programid) VALUES (2, 'QMXHPQ0001')
INSERT INTO #member (memberid,programid) VALUES (3, 'QMXHPQ0001')
INSERT INTO #member (memberid,programid) VALUES (4, 'QMXHPQ0001')
INSERT INTO #member (memberid,programid) VALUES (5, 'QMXHPQ0001')
INSERT INTO #member (memberid,programid) VALUES (6, 'QMXHPQ0001')
INSERT INTO #member (memberid,programid) VALUES (7, 'QMXHPQ0001')
INSERT INTO #member (memberid,programid) VALUES (8, 'QMXHPQ0001')
INSERT INTO #member (memberid,programid) VALUES (9, 'QMXHPQ0001')
INSERT INTO #member (memberid,programid) VALUES (10, 'QMXHPQ0001')
INSERT INTO #member (memberid,programid) VALUES (11, 'QMXHPQ0001')
INSERT INTO #member (memberid,programid) VALUES (12, 'QMXHPQ0001')
INSERT INTO #member (memberid,programid) VALUES (13, 'QMXHPQ0001')
INSERT INTO #member (memberid,programid) VALUES (14, 'QMXHPQ0001')
INSERT INTO #member (memberid,programid) VALUES (15, 'QMXHPQ0001')
INSERT INTO #member (memberid,programid) VALUES (16, 'QMXHPQ0001')
INSERT INTO #member (memberid,programid) VALUES (17, 'QMXHPQ0001')
INSERT INTO #member (memberid,programid) VALUES (18, 'QMXHPQ0001')
INSERT INTO #member (memberid,programid) VALUES (19, 'QMXHPQ0001')
INSERT INTO #member (memberid,programid) VALUES (20, 'QMXHPQ0001')
INSERT INTO #member (memberid,programid) VALUES (21, 'QMXHPQ0001')
INSERT INTO #member (memberid,programid) VALUES (22, 'QMXHPQ0001')
INSERT INTO #member (memberid,programid) VALUES (23, 'QMXHPQ0001')
INSERT INTO #member (memberid,programid) VALUES (24, 'QMXHPQ0001')
INSERT INTO #member (memberid,programid) VALUES (25, 'QMXHPQ0001')
INSERT INTO #member (memberid,programid) VALUES (26, 'QMXHPQ0001')
INSERT INTO #member (memberid,programid) VALUES (27, 'QMXHPQ0001')
INSERT INTO #member (memberid,programid) VALUES (28, 'QMXHPQ0001')
INSERT INTO #member (memberid,programid) VALUES (29, 'QMXHPQ0001')
INSERT INTO #member (memberid,programid) VALUES (30, 'QMXHPQ0001')
INSERT INTO #member (memberid,programid) VALUES (31, 'QMXHPQ0001')
INSERT INTO #member (memberid,programid) VALUES (32, 'QMXHPQ0001')
INSERT INTO #member (memberid,programid) VALUES (33, 'QMXHPQ0001')
INSERT INTO #member (memberid,programid) VALUES (34, 'QMXHPQ0001')
INSERT INTO #member (memberid,programid) VALUES (35, 'QMXHPQ0001')
INSERT INTO #member (memberid,programid) VALUES (36, 'QMXHPQ0001')
INSERT INTO #member (memberid,programid) VALUES (37, 'QMXHPQ0001')
INSERT INTO #member (memberid,programid) VALUES (38, 'QMXHPQ0002')
INSERT INTO #member (memberid,programid) VALUES (39, 'QMXHPQ0002')
INSERT INTO #member (memberid,programid) VALUES (40, 'QMXHPQ0002')
INSERT INTO #member (memberid,programid) VALUES (41, 'QMXHPQ0002')
INSERT INTO #member (memberid,programid) VALUES (42, 'QMXHPQ0002')
INSERT INTO #member (memberid,programid) VALUES (43, 'QMXHPQ0002')
INSERT INTO #member (memberid,programid) VALUES (44, 'QMXHPQ0002')
INSERT INTO #member (memberid,programid) VALUES (45, 'QMXHPQ0002')
INSERT INTO #member (memberid,programid) VALUES (46, 'QMXHPQ0002')
INSERT INTO #member (memberid,programid) VALUES (47, 'QMXHPQ0002')
INSERT INTO #member (memberid,programid) VALUES (48, 'QMXHPQ0002')
INSERT INTO #member (memberid,programid) VALUES (49, 'QMXHPQ0002')
INSERT INTO #member (memberid,programid) VALUES (50, 'QMXHPQ0002')
INSERT INTO #member (memberid,programid) VALUES (51, 'QMXHPQ0002')
INSERT INTO #member (memberid,programid) VALUES (52, 'QMXHPQ0002')
INSERT INTO #member (memberid,programid) VALUES (53, 'QMXHPQ0002')
-- INSERT data into #PLANDATA_Claim
INSERT INTO #PLANDATA_Claim (nameFile, claimid) VALUES ('QMXHPQ0001', 1)
INSERT INTO #PLANDATA_Claim (nameFile, claimid) VALUES ('QMXHPQ0001', 2)
INSERT INTO #PLANDATA_Claim (nameFile, claimid) VALUES ('QMXHPQ0001', 3)
INSERT INTO #PLANDATA_Claim (nameFile, claimid) VALUES ('QMXHPQ0001', 4)
INSERT INTO #PLANDATA_Claim (nameFile, claimid) VALUES ('QMXHPQ0001', 5)
INSERT INTO #PLANDATA_Claim (nameFile, claimid) VALUES ('QMXHPQ0001', 6)
INSERT INTO #PLANDATA_Claim (nameFile, claimid) VALUES ('QMXHPQ0001', 7)
INSERT INTO #PLANDATA_Claim (nameFile, claimid) VALUES ('QMXHPQ0001', 8)
INSERT INTO #PLANDATA_Claim (nameFile, claimid) VALUES ('QMXHPQ0001', 9)
INSERT INTO #PLANDATA_Claim (nameFile, claimid) VALUES ('QMXHPQ0001', 10)
INSERT INTO #PLANDATA_Claim (nameFile, claimid) VALUES ('QMXHPQ0001', 11)
INSERT INTO #PLANDATA_Claim (nameFile, claimid) VALUES ('QMXHPQ0001', 12)
INSERT INTO #PLANDATA_Claim (nameFile, claimid) VALUES ('QMXHPQ0001', 13)
INSERT INTO #PLANDATA_Claim (nameFile, claimid) VALUES ('QMXHPQ0001', 14)
INSERT INTO #PLANDATA_Claim (nameFile, claimid) VALUES ('QMXHPQ0001', 15)
INSERT INTO #PLANDATA_Claim (nameFile, claimid) VALUES ('QMXHPQ0001', 16)
INSERT INTO #PLANDATA_Claim (nameFile, claimid) VALUES ('QMXHPQ0001', 17)
INSERT INTO #PLANDATA_Claim (nameFile, claimid) VALUES ('QMXHPQ0001', 18)
INSERT INTO #PLANDATA_Claim (nameFile, claimid) VALUES ('QMXHPQ0001', 19)
INSERT INTO #PLANDATA_Claim (nameFile, claimid) VALUES ('QMXHPQ0001', 20)
INSERT INTO #PLANDATA_Claim (nameFile, claimid) VALUES ('QMXHPQ0001', 21)
INSERT INTO #PLANDATA_Claim (nameFile, claimid) VALUES ('QMXHPQ0001', 22)
INSERT INTO #PLANDATA_Claim (nameFile, claimid) VALUES ('QMXHPQ0001', 23)
INSERT INTO #PLANDATA_Claim (nameFile, claimid) VALUES ('QMXHPQ0001', 24)
INSERT INTO #PLANDATA_Claim (nameFile, claimid) VALUES ('QMXHPQ0001', 25)
INSERT INTO #PLANDATA_Claim (nameFile, claimid) VALUES ('QMXHPQ0001', 26)
INSERT INTO #PLANDATA_Claim (nameFile, claimid) VALUES ('QMXHPQ0001', 27)
INSERT INTO #PLANDATA_Claim (nameFile, claimid) VALUES ('QMXHPQ0001', 28)
INSERT INTO #PLANDATA_Claim (nameFile, claimid) VALUES ('QMXHPQ0001', 29)
INSERT INTO #PLANDATA_Claim (nameFile, claimid) VALUES ('QMXHPQ0001', 30)
INSERT INTO #PLANDATA_Claim (nameFile, claimid) VALUES ('QMXHPQ0001', 31)
INSERT INTO #PLANDATA_Claim (nameFile, claimid) VALUES ('QMXHPQ0001', 32)
INSERT INTO #PLANDATA_Claim (nameFile, claimid) VALUES ('QMXHPQ0001', 33)
INSERT INTO #PLANDATA_Claim (nameFile, claimid) VALUES ('QMXHPQ0001', 34)
INSERT INTO #PLANDATA_Claim (nameFile, claimid) VALUES ('QMXHPQ0001', 35)
INSERT INTO #PLANDATA_Claim (nameFile, claimid) VALUES ('QMXHPQ0001', 36)
INSERT INTO #PLANDATA_Claim (nameFile, claimid) VALUES ('QMXHPQ0001', 37)
INSERT INTO #PLANDATA_Claim (nameFile, claimid) VALUES ('QMXHPQ0002', 38)
INSERT INTO #PLANDATA_Claim (nameFile, claimid) VALUES ('QMXHPQ0002', 39)
INSERT INTO #PLANDATA_Claim (nameFile, claimid) VALUES ('QMXHPQ0002', 40)
INSERT INTO #PLANDATA_Claim (nameFile, claimid) VALUES ('QMXHPQ0002', 41)
INSERT INTO #PLANDATA_Claim (nameFile, claimid) VALUES ('QMXHPQ0002', 42)
INSERT INTO #PLANDATA_Claim (nameFile, claimid) VALUES ('QMXHPQ0002', 43)
INSERT INTO #PLANDATA_Claim (nameFile, claimid) VALUES ('QMXHPQ0002', 44)
INSERT INTO #PLANDATA_Claim (nameFile, claimid) VALUES ('QMXHPQ0002', 45)
INSERT INTO #PLANDATA_Claim (nameFile, claimid) VALUES ('QMXHPQ0002', 46)
INSERT INTO #PLANDATA_Claim (nameFile, claimid) VALUES ('QMXHPQ0002', 47)
INSERT INTO #PLANDATA_Claim (nameFile, claimid) VALUES ('QMXHPQ0002', 48)
INSERT INTO #PLANDATA_Claim (nameFile, claimid) VALUES ('QMXHPQ0002', 49)
INSERT INTO #PLANDATA_Claim (nameFile, claimid) VALUES ('QMXHPQ0002', 50)
INSERT INTO #PLANDATA_Claim (nameFile, claimid) VALUES ('QMXHPQ0002', 51)
INSERT INTO #PLANDATA_Claim (nameFile, claimid) VALUES ('QMXHPQ0002', 52)
INSERT INTO #PLANDATA_Claim (nameFile, claimid) VALUES ('QMXHPQ0002', 53)
-- #ENCOUNTER_Claim is empty intentionally
SELECT "File Name" AS "File Name",
sum(FFS) AS FFS,
sum(Encounter) AS Encounter,
sum(ABD) AS ABD,
sum(CFC) AS CFC
FROM
(
SELECT rtrim(ltrim(#files.controlID)) AS "File Name",
ISNULL(count(distinct #PLANDATA_Claim.claimid),0) AS "FFS",
ISNULL(count(distinct #ENCOUNTER_Claim.claimid),0) AS "Encounter",
CASE
WHEN #member.programid = 'QMXHPQ1621'
THEN ISNULL(count(distinct #PLANDATA_Claim.claimid),count(distinct #ENCOUNTER_Claim.claimid))
ELSE 0
END AS "ABD",
CASE
WHEN #member.programid = 'QMXHPQ1621'
THEN 0
ELSE ISNULL(count(distinct #PLANDATA_Claim.claimid),count(distinct #ENCOUNTER_Claim.claimid))
END AS "CFC"
-- ABD is QMXHPQ1621
FROM #files
JOIN #member ON #files.memberid = #member.memberid
LEFT JOIN #PLANDATA_Claim ON #files.claimid = #PLANDATA_Claim.claimid
LEFT JOIN #ENCOUNTER_Claim ON #files.claimid = #ENCOUNTER_Claim.claimid
WHERE #files.controlID IN ('test1','test2')
GROUP BY
#files.controlID,
#member.programid -- causes duplicates in the totals
-- attempts to correctly group below
--CASE WHEN #member.programid = 'QMXHPQ1621' THEN 'ABD' ELSE 'CFC' END -- says not in an aggregate
--CASE WHEN #member.programid = 'QMXHPQ1621'
--THEN ISNULL(count(distinct #PLANDATA_Claim.claimid),count(distinct #ENCOUNTER_Claim.claimid))
-- ELSE 0
-- END, -- says can't use an aggregate
--CASE WHEN #member.programid = 'QMXHPQ1621'
-- THEN 0
-- ELSE ISNULL(count(distinct #PLANDATA_Claim.claimid),count(distinct #ENCOUNTER_Claim.claimid))
--END -- says can't use an aggregate
/*ORDER BY #files.controlID*/
) myButt
GROUP BY "File Name"
/* Results: (as desired) Now what the heck does the warning mean?
File NameFFSEncounterABDCFC
test1520052
test21001
(2 row(s) affected)
Warning: Null value is eliminated by an aggregate or other SET operation.
*/
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
June 26, 2008 at 7:37 pm
First, the warning about nulls is just that... a warning to let you know that some rows had nulls and they were ignored. You can suppress that message with this...
SET ANSI_WARNINGS OFF
So far as building a pot wad of data for mega-row testing, consider something like the following which generates a million rows of constrained randomized data in about a minute or so on the first run and less than that on subsequent runs...
DROP TABLE JBMTest
GO
--===== Create and populate a 1,000,000 row test table.
-- Column "RowNum" has a range of 1 to 1,000,000 unique numbers
-- Column "SomeInt" has a range of 1 to 50,000 non-unique numbers
-- Column "SomeLetters2" has a range of "AA" to "ZZ" non-unique 2 character strings
-- Column "SomeMoney has a range of 0.0000 to 99.9999 non-unique numbers
-- Column "SomeDate" has a range of >=01/01/2000 and <01/01/2010 non-unique date/times
-- Column "SomeCSV" contains 'Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10'
-- for all rows.
-- Column "SomeHex12" contains 12 random hex characters (ie, 0-9,A-F)
-- Jeff Moden
SELECT TOP 1000000
SomeID = IDENTITY(INT,1,1),
SomeInt = ABS(CHECKSUM(NEWID()))%50000+1,
SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID()))%26+65)
+ CHAR(ABS(CHECKSUM(NEWID()))%26+65),
SomeCSV = CAST('Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10' AS VARCHAR(80)),
SomeMoney = CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS MONEY),
SomeDate = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME),
SomeHex12 = RIGHT(NEWID(),12)
INTO dbo.JBMTest
FROM Master.dbo.SysColumns t1,
Master.dbo.SysColumns t2 --Lack of join criteria makes this a CROSS-JOIN
--===== A table is not properly formed unless a Primary Key has been assigned
-- Takes about 1 second to execute.
ALTER TABLE dbo.JBMTest
ADD PRIMARY KEY CLUSTERED (SomeID)
Of course, you can modify that for your own needs....
--Jeff Moden
Change is inevitable... Change for the better is not.
June 27, 2008 at 5:51 am
Thanks, I've seen you post that before, but never saved it (until now). 16 seconds (first run) on my system.
So, is the JOIN on ISNULL ok? Or is that a bad idea?
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
June 27, 2008 at 6:58 am
I don't see where you've used that particular ISNULL as a join. It think it would be a problem if it were actually in a join because it uses two tables. But, using it in the SELECT list of a derived table like you have is not a problem. In fact, that's one of the reasons why ISNULL and COALESCE exist.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 27, 2008 at 7:08 am
Jeff Moden (6/27/2008)
I don't see where you've used that particular ISNULL as a join. It think it would be a problem if it were actually in a join because it uses two tables. But, using it in the SELECT list of a derived table like you have is not a problem. In fact, that's one of the reasons why ISNULL and COALESCE exist.
Sorry, didn't use that in this example. What I ended up doing in my live version is this:
LEFT JOIN #member ON (ISNULL(PLANDATA_Claim.enrollid,ENCOUNTER_Claim.enrollid) = #member.enrollid )
Had to do that because the member had multiple enrollments in the member table, but I only wanted the one on the claim. (I didn't have that column in the #member or claims tables in my example, guess I wrote this up before I thought of it)
SQL's not yelling at me, and I'm getting results, so I think it's working, but it's kind of bothering me, because it seems like it could be wrong. The two claim tables are mutually exclusive, and theoretically every claimid will be in one of the tables.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
June 27, 2008 at 7:27 am
jcrawf02,
can you explain this:
ISNULL(count(distinct #PLANDATA_Claim.claimid),0) AS "FFS",
What ISNULL is doing here?
_____________
Code for TallyGenerator
June 27, 2008 at 7:33 am
Sergiy (6/27/2008)
jcrawf02,can you explain this:
ISNULL(count(distinct #PLANDATA_Claim.claimid),0) AS "FFS",What ISNULL is doing here?
I have three environments that I need to query to find my data. #files table is in one, #PLANDATA is another, #ENCOUNTER is the third.
I am counting distinct claimids from #PLANDATA (user knows it as FFS) or #ENCOUNTER (user knows it as Encounter) and returning that value within the same row of the result set.
I'm basically changing the total to 0 for this column in the result set in the case that there aren't any claimids, so that it won't be null.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
June 27, 2008 at 8:05 am
jcrawf02 (6/27/2008)
Sorry, didn't use that in this example. What I ended up doing in my live version is this:LEFT JOIN #member ON (ISNULL(PLANDATA_Claim.enrollid,ENCOUNTER_Claim.enrollid) = #member.enrollid )
Hmmmm.... I suppose that could be ok... never really tried that before. Don't know if it's optimal or not compared to having two derived tables. Probably works out the same way, but not sure. I'll have to try that sometime in the future and see if it differs.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 27, 2008 at 8:07 am
jcrawf02 (6/27/2008)
Sergiy (6/27/2008)
jcrawf02,can you explain this:
ISNULL(count(distinct #PLANDATA_Claim.claimid),0) AS "FFS",What ISNULL is doing here?
I have three environments that I need to query to find my data. #files table is in one, #PLANDATA is another, #ENCOUNTER is the third.
I am counting distinct claimids from #PLANDATA (user knows it as FFS) or #ENCOUNTER (user knows it as Encounter) and returning that value within the same row of the result set.
I'm basically changing the total to 0 for this column in the result set in the case that there aren't any claimids, so that it won't be null.
Nah... "Must look eye!"... Sergiy doesn't need to know about all of that... he's trying to get you to go look back at your code to see why you don't need ISNULL.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 27, 2008 at 8:34 am
Nah... "Must look eye!"... Sergiy doesn't need to know about all of that... he's trying to get you to go look back at your code to see why you don't need ISNULL.
:Whistling: it was a test, so you guys wouldn't get bored when looking at my example.
thanks, Sergiy
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply