InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 18:07:56 GMT20http://www.sqlservercentral.com/Forums/Topic524513-8-1.aspx[quote]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.[/quote]:Whistling: it was a test, so you guys wouldn't get bored when looking at my example. thanks, SergiyFri, 27 Jun 2008 08:34:01 GMTjcrawf02http://www.sqlservercentral.com/Forums/Topic524513-8-1.aspx[quote][b]jcrawf02 (6/27/2008)[/b][hr][quote][b]Sergiy (6/27/2008)[/b][hr]jcrawf02,can you explain this:[code] ISNULL(count(distinct #PLANDATA_Claim.claimid),0) AS "FFS",[/code]What ISNULL is doing here?[/quote]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. [/quote]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.Fri, 27 Jun 2008 08:07:34 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic524513-8-1.aspx[quote][b]jcrawf02 (6/27/2008)[/b][hr]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 )[/quote]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.Fri, 27 Jun 2008 08:05:39 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic524513-8-1.aspx[quote][b]Sergiy (6/27/2008)[/b][hr]jcrawf02,can you explain this:[code] ISNULL(count(distinct #PLANDATA_Claim.claimid),0) AS "FFS",[/code]What ISNULL is doing here?[/quote]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. Fri, 27 Jun 2008 07:33:03 GMTjcrawf02http://www.sqlservercentral.com/Forums/Topic524513-8-1.aspxjcrawf02,can you explain this:[code] ISNULL(count(distinct #PLANDATA_Claim.claimid),0) AS "FFS",[/code]What ISNULL is doing here?Fri, 27 Jun 2008 07:27:58 GMTSergiyhttp://www.sqlservercentral.com/Forums/Topic524513-8-1.aspx[quote][b]Jeff Moden (6/27/2008)[/b][hr]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.[/quote]Sorry, didn't use that in this example. What I ended up doing in my live version is this:[code]LEFT JOIN #member ON (ISNULL(PLANDATA_Claim.enrollid,ENCOUNTER_Claim.enrollid) = #member.enrollid )[/code]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. Fri, 27 Jun 2008 07:08:34 GMTjcrawf02http://www.sqlservercentral.com/Forums/Topic524513-8-1.aspxI 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.Fri, 27 Jun 2008 06:58:13 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic524513-8-1.aspxThanks, 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?Fri, 27 Jun 2008 05:51:02 GMTjcrawf02http://www.sqlservercentral.com/Forums/Topic524513-8-1.aspxFirst, 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 OFFSo 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...[code]DROP TABLE JBMTestGO--===== 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)[/code]Of course, you can modify that for your own needs....Thu, 26 Jun 2008 19:37:50 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic524513-8-1.aspxFile 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 - [code]CASE WHEN #member.programid = 'QMXHPQ1621' THEN ISNULL(count(distinct #PLANDATA_Claim.claimid), count(distinct #ENCOUNTER_Claim.claimid)) ELSE END AS "ABD",[/code]) 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 FFS Encounter ABD CFC test1 26 0 0 26 test1 26 0 0 26 test2 1 0 0 1 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: [url]http://www.sqlservercentral.com/Forums/Topic2534-8-2.aspx?Highlight=group[/url] 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:[code]IF object_id('Tempdb..#files') IS NOT NULL BEGIN DROP TABLE #files ENDCREATE TABLE #files (controlID char(10), claimid int, memberid int)IF object_id('Tempdb..#member') IS NOT NULL BEGIN DROP TABLE #member ENDCREATE TABLE #member (memberid int, programid char(10))IF object_id('Tempdb..#PLANDATA_Claim') IS NOT NULL BEGIN DROP TABLE #PLANDATA_Claim ENDCREATE TABLE #PLANDATA_Claim (nameFile char(10), claimid int)IF object_id('Tempdb..#ENCOUNTER_Claim') IS NOT NULL BEGIN DROP TABLE #ENCOUNTER_Claim ENDCREATE TABLE #ENCOUNTER_Claim (nameFile char(10), claimid int)-- INSERT data into #filesINSERT 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 #memberINSERT 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_ClaimINSERT 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 intentionallySELECT "File Name" AS "File Name", sum(FFS) AS FFS, sum(Encounter) AS Encounter, sum(ABD) AS ABD, sum(CFC) AS CFCFROM ( 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*/ ) myButtGROUP BY "File Name"/* Results: (as desired) Now what the heck does the warning mean?File Name FFS Encounter ABD CFCtest1 52 0 0 52test2 1 0 0 1(2 row(s) affected)Warning: Null value is eliminated by an aggregate or other SET operation.*/[/code]Thu, 26 Jun 2008 13:04:36 GMTjcrawf02