Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Grouping GROUP BY subtotals Expand / Collapse
Author
Message
Posted Thursday, June 26, 2008 1:04 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, October 17, 2014 9:45 AM
Points: 1,330, Visits: 19,305
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 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:
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 Name FFS Encounter ABD CFC
test1 52 0 0 52
test2 1 0 0 1

(2 row(s) affected)

Warning: Null value is eliminated by an aggregate or other SET operation.
*/



---------------------------------------------------------
How best to post your question
How to post performance problems
Tally Table:What it is and how it replaces a loop

"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."
Post #524513
Posted Thursday, June 26, 2008 7:37 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:43 AM
Points: 35,342, Visits: 31,876
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #524685
Posted Friday, June 27, 2008 5:51 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, October 17, 2014 9:45 AM
Points: 1,330, Visits: 19,305
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
How to post performance problems
Tally Table:What it is and how it replaces a loop

"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."
Post #524897
Posted Friday, June 27, 2008 6:58 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:43 AM
Points: 35,342, Visits: 31,876
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #524938
Posted Friday, June 27, 2008 7:08 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, October 17, 2014 9:45 AM
Points: 1,330, Visits: 19,305
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
How to post performance problems
Tally Table:What it is and how it replaces a loop

"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."
Post #524946
Posted Friday, June 27, 2008 7:27 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, October 13, 2014 8:08 PM
Points: 4,573, Visits: 8,353
jcrawf02,
can you explain this:
                ISNULL(count(distinct #PLANDATA_Claim.claimid),0) AS "FFS",

What ISNULL is doing here?
Post #524978
Posted Friday, June 27, 2008 7:33 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, October 17, 2014 9:45 AM
Points: 1,330, Visits: 19,305
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
How to post performance problems
Tally Table:What it is and how it replaces a loop

"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."
Post #524984
Posted Friday, June 27, 2008 8:05 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:43 AM
Points: 35,342, Visits: 31,876
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #525015
Posted Friday, June 27, 2008 8:07 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:43 AM
Points: 35,342, Visits: 31,876
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #525017
Posted Friday, June 27, 2008 8:34 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, October 17, 2014 9:45 AM
Points: 1,330, Visits: 19,305


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.


it was a test, so you guys wouldn't get bored when looking at my example.

thanks, Sergiy


---------------------------------------------------------
How best to post your question
How to post performance problems
Tally Table:What it is and how it replaces a loop

"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."
Post #525049
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse