|
|
You're in the
sql server programming
group:Mixing dirived table and subqueries alltogether (long) REPOST
sql server programming:
-- Bemærk venligst, atHi I'm building an application where the data structure is hierarchical, I have used 5 tables related with each other throug x.nodeid=x+1.parentid down to 5 levels. Level5 is containing an extension number (serie). The point here is to give the user a tool to buils a structure for their organisation which is easy to finde data in ;o) tables involved is areas (root) centers center teams series (lowest level) I need to create some reports for the numbers of calls and present them per organisation. So the query to fetch the relevant extensions for a specific organisation could look like this select s.serie from centers cs, center c, teams t, series s where cs.nodeid=c.parentid and cs.nodename='OrganisationX' and c.nodeid=t.parentid and t.nodeid=s.parentid This will give me all the serie(s) for the selected organisation. so far so good. Now, the actual calls are stored in another table called V2tickets, these are "raw" data which I will have to handle. For this I have, with help from this group, a query which looks like this, which result is very close to what I need. declare @site int declare @calltype char(1) declare @ext char(4) set @site = 1 set @calltype = 'E' set @ext = '6%' select digits, total, besvaret, optaget, opgivet, ubesvaret from ( SELECT digits, COUNT(*) AS total, COUNT(CASE WHEN durationofconversation > 0 THEN 1 END) AS besvaret, COUNT(CASE WHEN durationofconversation = 0 AND releasecause = 'OC' THEN 1 END) AS optaget, COUNT(CASE WHEN durationofconversation = 0 AND releasecause = 'RL' AND durationofcall < 40 THEN 1 END) AS opgivet, COUNT(CASE WHEN durationofconversation = 0 AND durationofcall >= 40 THEN 1 END) AS ubesvaret FROM V2tickets WHERE siteid = @site AND calltype = @calltype AND LEN(digits) = 4 and digits like @ext GROUP BY digits) as dummy order by 1 desc Result digits total,besvaret,optaget, opgivet,ubesvaret 6999 ,177,171,1,3,2 6998 ,92,77,9,6,0 6996 ,131,127,1,2,1 6995 ,30,22,4,4,0 6994 ,3,2,0,0,1 6993 ,3,3,0,0,0 6991 ,17,16,0,1,0 6990 ,109,92,14,3,0 6989 ,13,11,0,2,0 6988 ,51,48,0,2,1 6986 ,2,1,0,1,0 6984 ,4,1,0,1,1 6983 ,1,0,0,0,0 6982 ,221,124,16,36,41 6981 ,65,35,1,7,5 6980 ,237,119,4,81,31 6979 ,1,0,0,1,0 6978 ,10,6,0,3,1 6977 ,47,38,3,3,3 6976 ,31,24,1,5,1 Then I add the 1st. query to the 2nd query declare @site int declare @calltype char(1) declare @ext char(4) set @site = 1 set @calltype = 'E' set @ext = '6%' select digits, total, besvaret, optaget, opgivet, ubesvaret from ( SELECT digits, COUNT(*) AS total, COUNT(CASE WHEN durationofconversation > 0 THEN 1 END) AS besvaret, COUNT(CASE WHEN durationofconversation = 0 AND releasecause = 'OC' THEN 1 END) AS optaget, COUNT(CASE WHEN durationofconversation = 0 AND releasecause = 'RL' AND durationofcall < 40 THEN 1 END) AS opgivet, COUNT(CASE WHEN durationofconversation = 0 AND durationofcall >= 40 THEN 1 END) AS ubesvaret FROM V2tickets WHERE siteid = @site AND calltype = @calltype AND LEN(digits) = 4 and digits in (select s.serie from centers cs, center c, teams t, series s where cs.nodeid=c.parentid and cs.nodename='OrganisationX' and c.nodeid=t.parentid and t.nodeid=s.parentid) GROUP BY digits) as dummy order by 1 desc Then I get this result 6811 ,120,88,26,4,2 6810 ,103,87,14,2,0 Which is okay, since 6810 and 6811 are the only "series" in my database right now. Okay, so finally my question, I would like to have a result like this c.Nodename CenterA ,221,124,16,36,41 CenterB ,65,35,1,7,5 CenterC ,237,119,4,81,31 CenterD ,1,0,0,1,0 CenterE ,10,6,0,3,1 CenterF ,47,38,3,3,3 CenterN ,31,24,1,5,1 Total (cs.nodename) OrgansiationX (all the above summerized), X,X,X,X,X How can I get c.nodename and a summery for all the sub nodes and at the end a total for the selected cs.nodename? Gosh it's hard to explain, I hope this makes any sense. regards Henry
Try this (untested since you didn't provide and DDL):
SELECT c.nodeid, total, besvaret, optaget, opgivet, ubesvaret FROM ( SELECT digits, COUNT(*) AS total, COUNT(CASE WHEN durationofconversation > 0 THEN 1 END) AS besvaret, COUNT(CASE WHEN durationofconversation = 0 AND releasecause = 'OC' THEN 1 END) AS optaget, COUNT(CASE WHEN durationofconversation = 0 AND releasecause = 'RL' AND durationofcall < 40 THEN 1 END) AS opgivet, COUNT(CASE WHEN durationofconversation = 0 AND durationofcall >= 40 THEN 1 END) AS ubesvaret FROM V2tickets INNER JOIN (SELECT s.serie FROM centers cs, center c, teams t, series s WHERE cs.nodeid=c.parentid and cs.nodename='OrganisationX' and c.nodeid=t.parentid and t.nodeid=s.parentid) AS H ON V2tickets.digits = H.serie WHERE siteid = @site AND calltype = @calltype AND LEN(digits) = 4 GROUP BY c.nodeid) AS dummy ORDER BY 1 [quoted text, click to view] "Henry" <nospam@thanks.com> wrote in message news:%23VMqg64FFHA.2736@TK2MSFTNGP09.phx.gbl... > > > -- > Bemærk venligst, atHi > > > I'm building an application where the data structure is hierarchical, I have > used 5 tables related with each other throug x.nodeid=x+1.parentid down to 5 > levels. > > Level5 is containing an extension number (serie). > > The point here is to give the user a tool to buils a structure for their > organisation which is easy to finde data in ;o) > > tables involved is > areas (root) > centers > center > teams > series (lowest level) > > I need to create some reports for the numbers of calls and present them per > organisation. > So the query to fetch the relevant extensions for a specific organisation > could look like this > > select s.serie from centers cs, center c, teams t, series s > where cs.nodeid=c.parentid and cs.nodename='OrganisationX' > and c.nodeid=t.parentid and t.nodeid=s.parentid > > This will give me all the serie(s) for the selected organisation. > so far so good. > > > Now, the actual calls are stored in another table called V2tickets, these > are "raw" data which I will have to handle. > > For this I have, with help from this group, a query which looks like this, > which result is very close to what I need. > > declare @site int > declare @calltype char(1) > declare @ext char(4) > > set @site = 1 > set @calltype = 'E' > set @ext = '6%' > > > select digits, total, besvaret, optaget, opgivet, ubesvaret > from ( > SELECT digits, COUNT(*) AS total, > COUNT(CASE WHEN durationofconversation > 0 THEN 1 END) AS besvaret, > COUNT(CASE WHEN durationofconversation = 0 AND releasecause = 'OC' > THEN 1 END) AS optaget, > COUNT(CASE WHEN durationofconversation = 0 AND releasecause = 'RL' > AND durationofcall < 40 THEN 1 END) AS opgivet, > COUNT(CASE WHEN durationofconversation = 0 AND durationofcall >= 40 > THEN 1 END) AS ubesvaret > FROM V2tickets > WHERE siteid = @site AND calltype = @calltype AND LEN(digits) = 4 and digits > like @ext > GROUP BY digits) as dummy > order by 1 desc > > Result > digits total,besvaret,optaget, opgivet,ubesvaret > 6999 ,177,171,1,3,2 > 6998 ,92,77,9,6,0 > 6996 ,131,127,1,2,1 > 6995 ,30,22,4,4,0 > 6994 ,3,2,0,0,1 > 6993 ,3,3,0,0,0 > 6991 ,17,16,0,1,0 > 6990 ,109,92,14,3,0 > 6989 ,13,11,0,2,0 > 6988 ,51,48,0,2,1 > 6986 ,2,1,0,1,0 > 6984 ,4,1,0,1,1 > 6983 ,1,0,0,0,0 > 6982 ,221,124,16,36,41 > 6981 ,65,35,1,7,5 > 6980 ,237,119,4,81,31 > 6979 ,1,0,0,1,0 > 6978 ,10,6,0,3,1 > 6977 ,47,38,3,3,3 > 6976 ,31,24,1,5,1 > > > Then I add the 1st. query to the 2nd query > > declare @site int > declare @calltype char(1) > declare @ext char(4) > > set @site = 1 > set @calltype = 'E' > set @ext = '6%' > > > select digits, total, besvaret, optaget, opgivet, ubesvaret > from ( > SELECT digits, COUNT(*) AS total, > COUNT(CASE WHEN durationofconversation > 0 THEN 1 END) AS besvaret, > COUNT(CASE WHEN durationofconversation = 0 AND releasecause = 'OC' > THEN 1 END) AS optaget, > COUNT(CASE WHEN durationofconversation = 0 AND releasecause = 'RL' > AND durationofcall < 40 THEN 1 END) AS opgivet, > COUNT(CASE WHEN durationofconversation = 0 AND durationofcall >= 40 > THEN 1 END) AS ubesvaret > FROM V2tickets > WHERE siteid = @site AND calltype = @calltype AND LEN(digits) = 4 and > digits in > > (select s.serie from centers cs, center c, teams t, series s > where cs.nodeid=c.parentid and cs.nodename='OrganisationX' > and c.nodeid=t.parentid and t.nodeid=s.parentid) > > GROUP BY digits) as dummy > order by 1 desc > > Then I get this result > 6811 ,120,88,26,4,2 > 6810 ,103,87,14,2,0 > > Which is okay, since 6810 and 6811 are the only "series" in my database > right now. > > > Okay, so finally my question, I would like to have a result like this > > c.Nodename > CenterA ,221,124,16,36,41 > CenterB ,65,35,1,7,5 > CenterC ,237,119,4,81,31 > CenterD ,1,0,0,1,0 > CenterE ,10,6,0,3,1 > CenterF ,47,38,3,3,3 > CenterN ,31,24,1,5,1 > > Total (cs.nodename) OrgansiationX (all the above summerized), X,X,X,X,X > > How can I get c.nodename and a summery for all the sub nodes and at the end > a total for the selected cs.nodename? > > Gosh it's hard to explain, I hope this makes any sense. > > regards > Henry > >
Henry
See the below example written by Itzik Ben-Gan USE testdb GO IF object_id('dbo.Employees') IS NOT NULL DROP TABLE Employees GO IF object_id('dbo.ufn_GetSubtree') IS NOT NULL DROP FUNCTION dbo.ufn_GetSubtree GO CREATE TABLE Employees ( empid int NOT NULL, mgrid int NULL, empname varchar(25) NOT NULL, salary money NOT NULL, CONSTRAINT PK_Employees_empid PRIMARY KEY(empid), CONSTRAINT FK_Employees_mgrid_empid FOREIGN KEY(mgrid) REFERENCES Employees(empid) ) CREATE INDEX idx_nci_mgrid ON Employees(mgrid) INSERT INTO Employees VALUES(1 , NULL, 'Nancy' , $10000.00) INSERT INTO Employees VALUES(2 , 1 , 'Andrew' , $5000.00) INSERT INTO Employees VALUES(3 , 1 , 'Janet' , $5000.00) INSERT INTO Employees VALUES(4 , 1 , 'Margaret', $5000.00) INSERT INTO Employees VALUES(5 , 2 , 'Steven' , $2500.00) INSERT INTO Employees VALUES(6 , 2 , 'Michael' , $2500.00) INSERT INTO Employees VALUES(7 , 3 , 'Robert' , $2500.00) INSERT INTO Employees VALUES(8 , 3 , 'Laura' , $2500.00) INSERT INTO Employees VALUES(9 , 3 , 'Ann' , $2500.00) INSERT INTO Employees VALUES(10, 4 , 'Ina' , $2500.00) INSERT INTO Employees VALUES(11, 7 , 'David' , $2000.00) INSERT INTO Employees VALUES(12, 7 , 'Ron' , $2000.00) INSERT INTO Employees VALUES(13, 7 , 'Dan' , $2000.00) INSERT INTO Employees VALUES(14, 11 , 'James' , $1500.00) GO CREATE FUNCTION dbo.ufn_GetSubtree ( @mgrid AS int ) RETURNS @tree table ( empid int NOT NULL, mgrid int NULL, empname varchar(25) NOT NULL, salary money NOT NULL, lvl int NOT NULL, path varchar(900) NOT NULL ) AS BEGIN DECLARE @lvl AS int, @path AS varchar(900) SELECT @lvl = 0, @path = '.' INSERT INTO @tree SELECT empid, mgrid, empname, salary, @lvl, '.' + CAST(empid AS varchar(10)) + '.' FROM Employees WHERE empid = @mgrid WHILE @@ROWCOUNT > 0 BEGIN SET @lvl = @lvl + 1 INSERT INTO @tree SELECT E.empid, E.mgrid, E.empname, E.salary, @lvl, T.path + CAST(E.empid AS varchar(10)) + '.' FROM Employees AS E JOIN @tree AS T ON E.mgrid = T.empid AND T.lvl = @lvl - 1 END RETURN END GO SELECT empid, mgrid, empname, salary FROM ufn_GetSubtree(3) GO /* empid mgrid empname salary 2 1 Andrew 5000.0000 5 2 Steven 2500.0000 6 2 Michael 2500.0000 */ /* SELECT REPLICATE (' | ', lvl) + empname AS employee FROM ufn_GetSubtree(1) ORDER BY path */ [quoted text, click to view] "Henry" <nospam@thanks.com> wrote in message
news:%23VMqg64FFHA.2736@TK2MSFTNGP09.phx.gbl... > > > -- > Bemærk venligst, atHi > > > I'm building an application where the data structure is hierarchical, I have > used 5 tables related with each other throug x.nodeid=x+1.parentid down to 5 > levels. > > Level5 is containing an extension number (serie). > > The point here is to give the user a tool to buils a structure for their > organisation which is easy to finde data in ;o) > > tables involved is > areas (root) > centers > center > teams > series (lowest level) > > I need to create some reports for the numbers of calls and present them per > organisation. > So the query to fetch the relevant extensions for a specific organisation > could look like this > > select s.serie from centers cs, center c, teams t, series s > where cs.nodeid=c.parentid and cs.nodename='OrganisationX' > and c.nodeid=t.parentid and t.nodeid=s.parentid > > This will give me all the serie(s) for the selected organisation. > so far so good. > > > Now, the actual calls are stored in another table called V2tickets, these > are "raw" data which I will have to handle. > > For this I have, with help from this group, a query which looks like this, > which result is very close to what I need. > > declare @site int > declare @calltype char(1) > declare @ext char(4) > > set @site = 1 > set @calltype = 'E' > set @ext = '6%' > > > select digits, total, besvaret, optaget, opgivet, ubesvaret > from ( > SELECT digits, COUNT(*) AS total, > COUNT(CASE WHEN durationofconversation > 0 THEN 1 END) AS besvaret, > COUNT(CASE WHEN durationofconversation = 0 AND releasecause = 'OC' > THEN 1 END) AS optaget, > COUNT(CASE WHEN durationofconversation = 0 AND releasecause = 'RL' > AND durationofcall < 40 THEN 1 END) AS opgivet, > COUNT(CASE WHEN durationofconversation = 0 AND durationofcall >= 40 > THEN 1 END) AS ubesvaret > FROM V2tickets > WHERE siteid = @site AND calltype = @calltype AND LEN(digits) = 4 and digits > like @ext > GROUP BY digits) as dummy > order by 1 desc > > Result > digits total,besvaret,optaget, opgivet,ubesvaret > 6999 ,177,171,1,3,2 > 6998 ,92,77,9,6,0 > 6996 ,131,127,1,2,1 > 6995 ,30,22,4,4,0 > 6994 ,3,2,0,0,1 > 6993 ,3,3,0,0,0 > 6991 ,17,16,0,1,0 > 6990 ,109,92,14,3,0 > 6989 ,13,11,0,2,0 > 6988 ,51,48,0,2,1 > 6986 ,2,1,0,1,0 > 6984 ,4,1,0,1,1 > 6983 ,1,0,0,0,0 > 6982 ,221,124,16,36,41 > 6981 ,65,35,1,7,5 > 6980 ,237,119,4,81,31 > 6979 ,1,0,0,1,0 > 6978 ,10,6,0,3,1 > 6977 ,47,38,3,3,3 > 6976 ,31,24,1,5,1 > > > Then I add the 1st. query to the 2nd query > > declare @site int > declare @calltype char(1) > declare @ext char(4) > > set @site = 1 > set @calltype = 'E' > set @ext = '6%' > > > select digits, total, besvaret, optaget, opgivet, ubesvaret > from ( > SELECT digits, COUNT(*) AS total, > COUNT(CASE WHEN durationofconversation > 0 THEN 1 END) AS besvaret, > COUNT(CASE WHEN durationofconversation = 0 AND releasecause = 'OC' > THEN 1 END) AS optaget, > COUNT(CASE WHEN durationofconversation = 0 AND releasecause = 'RL' > AND durationofcall < 40 THEN 1 END) AS opgivet, > COUNT(CASE WHEN durationofconversation = 0 AND durationofcall >= 40 > THEN 1 END) AS ubesvaret > FROM V2tickets > WHERE siteid = @site AND calltype = @calltype AND LEN(digits) = 4 and > digits in > > (select s.serie from centers cs, center c, teams t, series s > where cs.nodeid=c.parentid and cs.nodename='OrganisationX' > and c.nodeid=t.parentid and t.nodeid=s.parentid) > > GROUP BY digits) as dummy > order by 1 desc > > Then I get this result > 6811 ,120,88,26,4,2 > 6810 ,103,87,14,2,0 > > Which is okay, since 6810 and 6811 are the only "series" in my database > right now. > > > Okay, so finally my question, I would like to have a result like this > > c.Nodename > CenterA ,221,124,16,36,41 > CenterB ,65,35,1,7,5 > CenterC ,237,119,4,81,31
Hi Uri
Thanks for the example, that looks interesting, I'll be using that recursive solution next time ;o) However it doesn't solve the actual problem I'm having with joining the two results regards Henry
[quoted text, click to view]
> Try this (untested since you didn't provide and DDL): > > SELECT c.nodeid, total, besvaret, optaget, opgivet, ubesvaret > FROM ( > SELECT digits, COUNT(*) AS total, > COUNT(CASE WHEN durationofconversation > 0 THEN 1 > END) AS besvaret, > COUNT(CASE WHEN durationofconversation = 0 AND > releasecause = 'OC' THEN 1 END) AS optaget, > COUNT(CASE WHEN durationofconversation = 0 AND > releasecause = 'RL' AND durationofcall < 40 THEN 1 END) AS opgivet, > COUNT(CASE WHEN durationofconversation = 0 AND > durationofcall >= 40 THEN 1 END) AS ubesvaret > FROM V2tickets > INNER JOIN > (SELECT s.serie > FROM centers cs, center c, teams t, series s > WHERE cs.nodeid=c.parentid > and cs.nodename='OrganisationX' > and c.nodeid=t.parentid > and t.nodeid=s.parentid) AS H > ON V2tickets.digits = H.serie > WHERE siteid = @site > AND calltype = @calltype > AND LEN(digits) = 4 > GROUP BY c.nodeid) AS dummy > ORDER BY 1 Hi Dave Thanks I'll try this join, although I can see that the names/grouping by subnodes to OrganizationX (just the first level below OrganisationX, "c.nodename") will be missing here, do I need to do another join if I want the c.nodename? regards Henry
[quoted text, click to view]
> Try this (untested since you didn't provide and DDL): Hi> > SELECT c.nodeid, total, besvaret, optaget, opgivet, ubesvaret > FROM ( > SELECT digits, COUNT(*) AS total, > COUNT(CASE WHEN durationofconversation > 0 THEN 1 > END) AS besvaret, > COUNT(CASE WHEN durationofconversation = 0 AND > releasecause = 'OC' THEN 1 END) AS optaget, > COUNT(CASE WHEN durationofconversation = 0 AND > releasecause = 'RL' AND durationofcall < 40 THEN 1 END) AS opgivet, > COUNT(CASE WHEN durationofconversation = 0 AND > durationofcall >= 40 THEN 1 END) AS ubesvaret > FROM V2tickets > INNER JOIN > (SELECT s.serie > FROM centers cs, center c, teams t, series s > WHERE cs.nodeid=c.parentid > and cs.nodename='OrganisationX' > and c.nodeid=t.parentid > and t.nodeid=s.parentid) AS H > ON V2tickets.digits = H.serie > WHERE siteid = @site > AND calltype = @calltype > AND LEN(digits) = 4 > GROUP BY c.nodeid) AS dummy > ORDER BY 1 I gives an error "The column prefix 'c' does not match with a table or alias used in the query. and then a line number. I'll try to make some DDL. regards Henry
[quoted text, click to view]
>> How can I get c.nodename and a summary for all the sub nodes and at the end a total for the selected cs.nodename? <<You might want to Google the nested set model for hierarchies. It lets you do the summaries for all subtrees in one query in pure Standard SQL. No procedural code, no proprietary code. And it runs fast. I also have a book on TREES & HIERARCHIES IN SQL that will help.
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |