|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Query help - resultset too large... impossible!help... thanks to everyone that can help me out - I really appreciate it! DECLARE @startdate smalldatetime, @enddate smalldatetime SET @startdate = '2/7/2006 12:00AM' SET @enddate = '2/7/2006 11:59PM' SELECT Referrers.Name [Name], Sites.Name [University], totalNew.Signups [Signups], overall.Overall [Overall] FROM Sites JOIN Referrers ON Referrers.SiteId = Sites.Id LEFT JOIN ( SELECT Referrers.Name [Name], Sites.Name [University], count(Subscribers.Id) [Signups] FROM Referrers JOIN Sites on Sites.Id = Referrers.SiteId JOIN Subscribers ON Subscribers.ReferrerId = Referrers.Id WHERE Referrers.ShowInList = '1' AND Subscribers.DateEntered BETWEEN '2/7/2006 12:00AM' AND '2/7/2006 11:59PM' GROUP BY Sites.Name, Referrers.Name, Subscribers.Id ) totalNew ON totalNew.Signups = Signups LEFT JOIN ( SELECT Referrers.Name [Name], Sites.Name [University], count(Subscribers.Id) [Overall] FROM Referrers JOIN Sites on Sites.Id = Referrers.SiteId JOIN Subscribers ON Subscribers.ReferrerId = Referrers.Id WHERE Referrers.ShowInList = '1' GROUP BY Sites.Name, Referrers.Name, Subscribers.Id ) overall ON overall.Overall = Overall First, format your code (it might be formatted, but not in this newsgroup :)
If not, try: http://www.wangz.net/cgi-bin/pp/gsqlparser/sqlpp/sqlformat.tpl Second,break it down piece by piece, starting with the derived tables. Make sure each part is returning the right data. I am confused by your join clauses for the drived tables: overall.Overall = Overall totalnew.signups = signups This looks like it will return every row. You probably need to be returning the SiteId from your derived tables and joining on it. Just a quick guess. If this isn't helpful, try documenting your code, down to each clause. Sometimes that moment of clarity will help you find what is wrong (I know this is how I solve query problems, document a section, validate it, test it. Pretty soon I always find the problem. -- Show quoteHide quote---------------------------------------------------------------------------- Louis Davidson - http://spaces.msn.com/members/drsql/ SQL Server MVP "Arguments are to be avoided: they are always vulgar and often convincing." (Oscar Wilde) <andrew.ta***@gmail.com> wrote in message news:1139532886.279192.87930@z14g2000cwz.googlegroups.com... > Does anyone see any problems with this query? I need some serious > help... thanks to everyone that can help me out - I really appreciate > it! > > DECLARE @startdate smalldatetime, @enddate smalldatetime > SET @startdate = '2/7/2006 12:00AM' > SET @enddate = '2/7/2006 11:59PM' > SELECT Referrers.Name [Name], Sites.Name [University], totalNew.Signups > [Signups], > overall.Overall [Overall] FROM Sites > JOIN Referrers ON Referrers.SiteId = Sites.Id > LEFT JOIN ( > SELECT Referrers.Name [Name], Sites.Name [University], > count(Subscribers.Id) [Signups] > FROM Referrers > JOIN Sites on Sites.Id = Referrers.SiteId > JOIN Subscribers ON Subscribers.ReferrerId = Referrers.Id > WHERE Referrers.ShowInList = '1' AND Subscribers.DateEntered BETWEEN > '2/7/2006 12:00AM' AND '2/7/2006 11:59PM' > GROUP BY Sites.Name, Referrers.Name, Subscribers.Id > ) totalNew ON totalNew.Signups = Signups > LEFT JOIN ( > SELECT Referrers.Name [Name], Sites.Name [University], > count(Subscribers.Id) [Overall] > FROM Referrers > JOIN Sites on Sites.Id = Referrers.SiteId > JOIN Subscribers ON Subscribers.ReferrerId = Referrers.Id > WHERE Referrers.ShowInList = '1' > GROUP BY Sites.Name, Referrers.Name, Subscribers.Id > ) overall ON overall.Overall = Overall > Sorry about this. This query is actually getting the results to closer
to what I want. Basically, giving a total within a range and then the overall total. However, it's giving multiple results... posted below the query for example... DECLARE @startdate SMALLDATETIME, @enddate SMALLDATETIME SET @startdate = '2/7/2006 12:00AM' SET @enddate = '2/7/2006 11:59PM' SELECT REFERRERS.NAME [Name], SITES.NAME [University], TOTALNEW.C [Signups], OVERALL.C [Overall] FROM SITES JOIN REFERRERS ON REFERRERS.SITEID = SITES.ID LEFT JOIN (SELECT REFERRERS.NAME [Name], SITES.NAME [University], COUNT(SUBSCRIBERS.ID) [c] FROM REFERRERS JOIN SITES ON SITES.ID = REFERRERS.SITEID JOIN SUBSCRIBERS ON SUBSCRIBERS.REFERRERID = REFERRERS.ID WHERE REFERRERS.SHOWINLIST = '1' AND SUBSCRIBERS.DATEENTERED BETWEEN '2/7/2006 12:00AM' AND '2/7/2006 11:59PM' GROUP BY REFERRERS.NAME, SITES.NAME) TOTALNEW ON TOTALNEW.NAME = REFERRERS.NAME LEFT JOIN (SELECT REFERRERS.NAME [Name], SITES.NAME [University], COUNT(SUBSCRIBERS.ID) [c] FROM REFERRERS JOIN SITES ON SITES.ID = REFERRERS.SITEID JOIN SUBSCRIBERS ON SUBSCRIBERS.REFERRERID = REFERRERS.ID WHERE REFERRERS.SHOWINLIST = '1' GROUP BY REFERRERS.NAME, SITES.NAME) OVERALL ON OVERALL.NAME = REFERRERS.NAME ---------------------- Name University Signups Overall ---------- -------------------------- ---------- ---------- Adam University of Florida (null) (null) Alex University of Florida 8 187 Alexandra University of Florida (null) (null) Andrew University of Florida (null) 5 Anthony University of Florida (null) 149 Brad University of Florida 2 9 Brittany University of Florida 1 68 Charles University of Florida 4 128 Claribel University of Florida (null) (null) Colleen University of Florida (null) (null) Danielle University of Florida 1 57 Danielle University of Florida 1 1 Danielle University of Florida 1 57 Danielle University of Florida 1 1 Dawn University of Florida (null) 112 DJ University of Florida (null) (null) Eian University of Florida 2 28 Gibran University of Florida (null) (null) Guy University of Florida (null) (null) Hyzens University of Florida 2 149 Jenny University of Florida 3 13 Jericka University of Florida (null) 23 Jocelyn University of Florida (null) 113 Jonathon University of Florida (null) (null) Josh University of Florida (null) (null) Kitty University of Florida (null) (null) Kristin University of Florida (null) 6 Lander University of Florida (null) 58 Laura University of Florida (null) 119 Lindsey University of Florida (null) (null) Lisa University of Florida (null) (null) Mandy University of Florida (null) 47 Mike University of Florida (null) 18 Montana University of Florida (null) (null) Natalie University of Florida (null) (null) Nina University of Florida (null) (null) Rene University of Florida (null) 7 Ruby University of Florida (null) 11 Shayna University of Florida (null) 111 Alan Santa Fe Community College 1 8 Ashley Santa Fe Community College (null) 9 Clarence Santa Fe Community College 3 6 Dan Santa Fe Community College 5 14 Danielle Santa Fe Community College 1 57 Danielle Santa Fe Community College 1 1 Danielle Santa Fe Community College 1 57 Danielle Santa Fe Community College 1 1 Hunter Santa Fe Community College (null) 8 Joe Santa Fe Community College (null) (null) Kaydian Santa Fe Community College 1 11 Marcgenson Santa Fe Community College (null) 15 Michael Santa Fe Community College 4 19 Patricia Santa Fe Community College (null) 11 Scott Santa Fe Community College (null) (null) 54 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 16/ms] [Executed: 2/9/06 8:36:30 PM EST ] [Execution: 390/ms] ------------------ Thanks for your help - it's greatly appreciated! Always concerns me when you join on a name. For example, this part of the
results: > Danielle University of Florida 1 57 Is the value 'Danielle' really unique? Post the DDL for thes tables, it > Danielle University of Florida 1 1 > Danielle University of Florida 1 57 > Danielle University of Florida 1 1 seems you probably have some design issues, but can only guess from this query. -- Show quoteHide quote---------------------------------------------------------------------------- Louis Davidson - http://spaces.msn.com/members/drsql/ SQL Server MVP "Arguments are to be avoided: they are always vulgar and often convincing." (Oscar Wilde) <andrew.ta***@gmail.com> wrote in message news:1139535519.873438.108590@o13g2000cwo.googlegroups.com... > Sorry about this. This query is actually getting the results to closer > to what I want. Basically, giving a total within a range and then the > overall total. However, it's giving multiple results... posted below > the query for example... > > DECLARE > @startdate SMALLDATETIME, > @enddate SMALLDATETIME > > SET @startdate = '2/7/2006 12:00AM' > > SET @enddate = '2/7/2006 11:59PM' > > SELECT REFERRERS.NAME [Name], > SITES.NAME [University], > TOTALNEW.C [Signups], > OVERALL.C [Overall] > FROM SITES > JOIN REFERRERS > ON REFERRERS.SITEID = SITES.ID > LEFT JOIN (SELECT REFERRERS.NAME [Name], > SITES.NAME [University], > COUNT(SUBSCRIBERS.ID) [c] > FROM REFERRERS > JOIN SITES > ON SITES.ID = REFERRERS.SITEID > JOIN SUBSCRIBERS > ON SUBSCRIBERS.REFERRERID = REFERRERS.ID > WHERE REFERRERS.SHOWINLIST = '1' > AND SUBSCRIBERS.DATEENTERED BETWEEN > '2/7/2006 12:00AM' > AND > '2/7/2006 11:59PM' > GROUP BY REFERRERS.NAME, > SITES.NAME) TOTALNEW > ON TOTALNEW.NAME = REFERRERS.NAME > LEFT JOIN (SELECT REFERRERS.NAME [Name], > SITES.NAME [University], > COUNT(SUBSCRIBERS.ID) [c] > FROM REFERRERS > JOIN SITES > ON SITES.ID = REFERRERS.SITEID > JOIN SUBSCRIBERS > ON SUBSCRIBERS.REFERRERID = REFERRERS.ID > WHERE REFERRERS.SHOWINLIST = '1' > GROUP BY REFERRERS.NAME, > SITES.NAME) OVERALL > ON OVERALL.NAME = REFERRERS.NAME > > ---------------------- > > > Name University Signups Overall > ---------- -------------------------- ---------- ---------- > Adam University of Florida (null) (null) > Alex University of Florida 8 187 > Alexandra University of Florida (null) (null) > Andrew University of Florida (null) 5 > Anthony University of Florida (null) 149 > Brad University of Florida 2 9 > Brittany University of Florida 1 68 > Charles University of Florida 4 128 > Claribel University of Florida (null) (null) > Colleen University of Florida (null) (null) > Danielle University of Florida 1 57 > Danielle University of Florida 1 1 > Danielle University of Florida 1 57 > Danielle University of Florida 1 1 > Dawn University of Florida (null) 112 > DJ University of Florida (null) (null) > Eian University of Florida 2 28 > Gibran University of Florida (null) (null) > Guy University of Florida (null) (null) > Hyzens University of Florida 2 149 > Jenny University of Florida 3 13 > Jericka University of Florida (null) 23 > Jocelyn University of Florida (null) 113 > Jonathon University of Florida (null) (null) > Josh University of Florida (null) (null) > Kitty University of Florida (null) (null) > Kristin University of Florida (null) 6 > Lander University of Florida (null) 58 > Laura University of Florida (null) 119 > Lindsey University of Florida (null) (null) > Lisa University of Florida (null) (null) > Mandy University of Florida (null) 47 > Mike University of Florida (null) 18 > Montana University of Florida (null) (null) > Natalie University of Florida (null) (null) > Nina University of Florida (null) (null) > Rene University of Florida (null) 7 > Ruby University of Florida (null) 11 > Shayna University of Florida (null) 111 > Alan Santa Fe Community College 1 8 > Ashley Santa Fe Community College (null) 9 > Clarence Santa Fe Community College 3 6 > Dan Santa Fe Community College 5 14 > Danielle Santa Fe Community College 1 57 > Danielle Santa Fe Community College 1 1 > Danielle Santa Fe Community College 1 57 > Danielle Santa Fe Community College 1 1 > Hunter Santa Fe Community College (null) 8 > Joe Santa Fe Community College (null) (null) > Kaydian Santa Fe Community College 1 11 > Marcgenson Santa Fe Community College (null) 15 > Michael Santa Fe Community College 4 19 > Patricia Santa Fe Community College (null) 11 > Scott Santa Fe Community College (null) (null) > > 54 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 16/ms] > > [Executed: 2/9/06 8:36:30 PM EST ] [Execution: 390/ms] > > ------------------ > > > Thanks for your help - it's greatly appreciated! > >> I need some serious help... << YES, you do! But it might be beyond the scope of a Newsgroup. Themuch tooooo vague "name" can be a University or a person or a dog or whatever, right? You have no data dictionary, do you? That "name" is an attribute of an entity, not a data element. Then you spit on ISO-8601 temporal formats. The same data element has multiple names, anf there almost certainly a ton of other DDL disasters in this crap. If you want to see how stinking bad this schema is just pull out the data element name "id" and try to define it interms of an entity and attribute. The only thing I found that was right in this was the use of plurals for table names. I feel edified, anyone else? Show of hands?
-- Show quoteHide quote---------------------------------------------------------------------------- Louis Davidson - http://spaces.msn.com/members/drsql/ SQL Server MVP "Arguments are to be avoided: they are always vulgar and often convincing." (Oscar Wilde) "--CELKO--" <jcelko***@earthlink.net> wrote in message news:1139545423.338944.226830@g14g2000cwa.googlegroups.com... >>> I need some serious help... << > > YES, you do! But it might be beyond the scope of a Newsgroup. The > much tooooo vague "name" can be a University or a person or a dog or > whatever, right? You have no data dictionary, do you? That "name" is > an attribute of an entity, not a data element. Then you spit on > ISO-8601 temporal formats. > > The same data element has multiple names, anf there almost certainly a > ton of other DDL disasters in this crap. If you want to see how > stinking bad this schema is just pull out the data element name "id" > and try to define it interms of an entity and attribute. > > The only thing I found that was right in this was the use of plurals > for table names. > If you want a good laugh check this out...
http://groups.google.co.uk/group/comp.databases.theory/browse_frm/thread/478cf728d84877ae/a6070008a235fed5?lnk=st&q=--celko--+%22can+i+do+this+using+sql%22&rnum=1&hl=en#a6070008a235fed5 CELKO writes a CURSOR ! Show quoteHide quote "Louis Davidson" <dr_dontspamme_sql@hotmail.com> wrote in message news:ekSp$8lLGHA.1048@TK2MSFTNGP10.phx.gbl... >I feel edified, anyone else? Show of hands? > > -- > ---------------------------------------------------------------------------- > Louis Davidson - http://spaces.msn.com/members/drsql/ > SQL Server MVP > "Arguments are to be avoided: they are always vulgar and often > convincing." > (Oscar Wilde) > > "--CELKO--" <jcelko***@earthlink.net> wrote in message > news:1139545423.338944.226830@g14g2000cwa.googlegroups.com... >>>> I need some serious help... << >> >> YES, you do! But it might be beyond the scope of a Newsgroup. The >> much tooooo vague "name" can be a University or a person or a dog or >> whatever, right? You have no data dictionary, do you? That "name" is >> an attribute of an entity, not a data element. Then you spit on >> ISO-8601 temporal formats. >> >> The same data element has multiple names, anf there almost certainly a >> ton of other DDL disasters in this crap. If you want to see how >> stinking bad this schema is just pull out the data element name "id" >> and try to define it interms of an entity and attribute. >> >> The only thing I found that was right in this was the use of plurals >> for table names. >> > > Well, he does admit writing a cursor three times in his career. I guess we
are up to four now :) -- Show quoteHide quote---------------------------------------------------------------------------- Louis Davidson - http://spaces.msn.com/members/drsql/ SQL Server MVP "Arguments are to be avoided: they are always vulgar and often convincing." (Oscar Wilde) "Tony Rogerson" <tonyroger***@sqlserverfaq.com> wrote in message news:uzzSYEmLGHA.2812@TK2MSFTNGP14.phx.gbl... > If you want a good laugh check this out... > > http://groups.google.co.uk/group/comp.databases.theory/browse_frm/thread/478cf728d84877ae/a6070008a235fed5?lnk=st&q=--celko--+%22can+i+do+this+using+sql%22&rnum=1&hl=en#a6070008a235fed5 > > CELKO writes a CURSOR ! > > -- > Tony Rogerson > SQL Server MVP > http://sqlserverfaq.com - free video tutorials > > > "Louis Davidson" <dr_dontspamme_sql@hotmail.com> wrote in message > news:ekSp$8lLGHA.1048@TK2MSFTNGP10.phx.gbl... >>I feel edified, anyone else? Show of hands? >> >> -- >> ---------------------------------------------------------------------------- >> Louis Davidson - http://spaces.msn.com/members/drsql/ >> SQL Server MVP >> "Arguments are to be avoided: they are always vulgar and often >> convincing." >> (Oscar Wilde) >> >> "--CELKO--" <jcelko***@earthlink.net> wrote in message >> news:1139545423.338944.226830@g14g2000cwa.googlegroups.com... >>>>> I need some serious help... << >>> >>> YES, you do! But it might be beyond the scope of a Newsgroup. The >>> much tooooo vague "name" can be a University or a person or a dog or >>> whatever, right? You have no data dictionary, do you? That "name" is >>> an attribute of an entity, not a data element. Then you spit on >>> ISO-8601 temporal formats. >>> >>> The same data element has multiple names, anf there almost certainly a >>> ton of other DDL disasters in this crap. If you want to see how >>> stinking bad this schema is just pull out the data element name "id" >>> and try to define it interms of an entity and attribute. >>> >>> The only thing I found that was right in this was the use of plurals >>> for table names. >>> >> >> > > Celko also mentions that at least one of those cursors could have been
avoided if the CASE expression were available at the time. Judging by the example in Tony's post at least one more could have been avoided if the party in question were not intoxicated. I could be wrong. Who's up for a beer? ML --- http://milambda.blogspot.com/ Hi ML,
Yep; its Friday night, mind you I've got a jippy tummy from somewhere so I'm probably going to have to make it better by soaking whatever bug is down there with a few pints of real ale and a good size steak!! Having said that, I'm still battling to get my dam exchange server up and running again; mind you - no email is a good thing :) Tony Show quoteHide quote "ML" <M*@discussions.microsoft.com> wrote in message news:4FE8EE67-1134-4A5F-92A3-E85DE524A959@microsoft.com... > Celko also mentions that at least one of those cursors could have been > avoided if the CASE expression were available at the time. Judging by the > example in Tony's post at least one more could have been avoided if the > party > in question were not intoxicated. I could be wrong. Who's up for a beer? > > > ML > > --- > http://milambda.blogspot.com/ I know exactly what you mean. On all counts. :)
Exchange... how not to design a server. ;) ML --- http://milambda.blogspot.com/  
Other interesting topics
Problem updating two tables in a transaction.
Does dynamic SQL allow table variables? DBCC SHOWCONTIG question Insert Trigger Any way to avoid using a cursor and a script on this one? Query assistance or advice Problem with Cursor and Union in select Optimizing query with UDF and table vars and IN SELECT problem in stored procedure Moving indexes from a filegroup to another |
|||||||||||||||||||||||
