Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 7,2000 » T-SQL » Grouping GROUP BY subtotals
Add to briefcase

Grouping GROUP BY subtotals Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
jcrawf02
Posted Thursday, June 26, 2008 1:04 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:09 PM
Points: 2,550, Visits: 18,882
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
Jeff Moden
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:33 PM
Points: 32,902, Visits: 26,783
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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #524685
jcrawf02
Posted Friday, June 27, 2008 5:51 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:09 PM
Points: 2,550, Visits: 18,882
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
Jeff Moden
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:33 PM
Points: 32,902, Visits: 26,783
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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #524938
jcrawf02
Posted Friday, June 27, 2008 7:08 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:09 PM
Points: 2,550, Visits: 18,882
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
Sergiy
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: Wednesday, May 15, 2013 5:01 PM
Points: 4,540, Visits: 8,184
jcrawf02,
can you explain this:
                ISNULL(count(distinct #PLANDATA_Claim.claimid),0) AS "FFS",

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


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:09 PM
Points: 2,550, Visits: 18,882
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
Jeff Moden
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:33 PM
Points: 32,902, Visits: 26,783
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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #525015
Jeff Moden
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:33 PM
Points: 32,902, Visits: 26,783
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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #525017
jcrawf02
Posted Friday, June 27, 2008 8:34 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:09 PM
Points: 2,550, Visits: 18,882


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

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.