We have an helpdesk sytem on SQL 2000. I am trying to show how much calls have been assigned to each parent category plus it's child categories in a single row. The thing is when I run my query it display parent and child categories and each on it's own rows. I do get the call totals for each row, but I would like to add the totals of the rows together and display it on row. The new table must have then 6 rows (because there is 6 parent categories) with the total of all calls for that parent category, as well as it's childs. Example:
parameter # Calls
------------ --------
desktoppp 5
desktopqq 6 {This is what I am getting at this stage}
desktop t 4
network
r 9
networkgg 10
softwarevv 3
I need to add up the number of people who joined this month and compare that number to the number of people who joined last month and display the results in a report:
FirstName..LastName.....StartedDate Randy......Simpson......5/4/2007 10:00:00 PM Steve......Rowe.........5/2/2007 10:00:00 PM Eric.......Dickerson....5/4/2007 10:00:00 PM Gloria.....Sanches......5/1/2007 12:00:29 AM Andres.....Marcelino....5/1/2007 12:06:31 AM katie......ryan.........6/4/2007 12:08:35 AM Denise.....River........6/4/2007 12:27:14 AM Kellog.....Stover.......6/5/2007 12:37:20 AM Glenn......Sanders......6/1/2007 12:42:40 AM
Lets suppose I have a db which looks like:District FundVAE AVAW AVAE BDC CDC ANYS BMD CVAW AI would like a query which displays the following dynamically. I don't know each unique member of the Fund field, they could be added by the end user. It might include M,J,S etc. I need the columns dynamically created. I can do it statically if I know the distinct members of fund. I would like to be able to do it dynamically. Any ideas? The static version with multiple queries is at the end.District Total A B CVAE 2 1 1 VAW 2 2 DC 3 2 1 NYS 1 1 MD 1 1 Query Name =GroupByDistSELECT district, COUNT(district) AS dcountFROM dbo.mydbGROUP BY districtQuery Name=DCountASELECT district, COUNT(district) AS dcountFROM dbo.mydbWHERE (fund = N'A')GROUP BY district Query Name=DCountBSELECT district, COUNT(district) AS dcountFROM dbo.mydbWHERE (fund = N'B')GROUP BY district Query Name=DCountCSELECT district, COUNT(district) AS dcountFROM dbo.mydbWHERE (fund = N'C')GROUP BY district SELECT GroupByDist.district, GroupByDist.dcount AS Total, DCountA.dcount AS A, DCountB.dcount AS B, DCountC.dcount AS C From GroupByDist LEFT OUTER JOIN DCountC ON GroupByDist.district = DCountC.district LEFT OUTER JOIN DCountB ON GroupByDist.district = DCountB.district LEFT OUTER JOIN DCountA ON GroupByDist.district = DCountA.district
hi, i have a stored procedure SELECT UserName AS Visitor, COUNT(VisitID) AS TotalVisit FROM UserVisits WHERE (ProductID = @ProductID) AND (AnonimIP IS NULL) GROUP BY UserName UNION SELECT AnonimIP AS Visitor, COUNT(VisitID) AS TotalVisit FROM UserVisits AS UserVisits_1 WHERE (ProductID = @ProductID) AND (UserName IS NULL) GROUP BY AnonimIP this will return something like: zuperboy90 - 4 visits ANONIMOUS - 6 visits 85.104.103 - 2 visits etc how can i count the rows returned in both selections (4+6+2 = 12) ? thank you
Hi everyone,Does exist an easy way to count the actually number of queries executed within a page?I've searched here and in google but found anything...Thanks in advance!
I am working on an ASP.NET site to display information about authors and books. I'm working on the Author page right now and I'm having trouble getting it to display the data I want. I'm trying to do everything with declarative controls and as little code-behind as possible. I have a datasource on the main page that selects the Author data and provides this to a FormView. Inside the FormView is another datasource that takes the AuthorID as a parameter and selects the list of books, which is provided to a DataList. Inside the DataList I display some limited Book information. I also want to display some child items of the Book (grandchildren of the author), for example other authors who contributed to that book, or alternative titles. I have tried using datasources in each DataList row with a parameter set to the BookID, and it works just great, but the response time is just not acceptable. Each datasource of possible several dozen is making its own call to the database and it's just too slow. If I use code I can grab all the data in one operation and use relations in the DataBinding event (I believe) to select the data I want, but this is a bit cumbersome and I expect will cause trouble if I want to eventually use an ObjectDataSource. Incidentally, on a display-only page I use the XMLDataSource and it works great, but I need read-write access on the Edit page. Is there a good way to do what I'm trying to do?
I have a parent Child attribute in my dimension. It show the correct ID value in the reports but when I change the value from the NameColumn to be something else it still only shows the ID value. I have an identical case in another dimension and it is working correctly. However this dimension for some reason does not seem to work and I believe they are built the same way I must be missing something. I have tried to show the OrgNodeID and tried to display the OrgNodename but neither displays all it displays is the intdimParentOrgNodeID.My dimension table looks something like this
intdimOrgNodeID int Key (surreget key) intOrgNodeID int (Actual ID) intDimParentOrgNodeID intOrgNodeName
My query below displays a total row, but it displays above the city, I want it to display below the city. But now their is an additional caveat, I have a table that sets the order each city & total row should display in. How can that be added to my query to set the display order?This is what I have that displays the total above the city, could this be ammended to join in the order table and display in that order? SQL Server 2008
The 3 OrgUnits with no parentID in one column (parentOU), then any OrgUnits who's parentID matches the orgunitid of the 3 OrgUnits in ParentOU in the next column (Child OU1), followed by any OrgUnits who's parentID matched the orgunitID of the OrgUnits in 'Child OU1' would be in the third column (Child OU2)
The dataset I'm working on contains ~700 rows and the OrgUnitNames can be anything, they aren't labelled like the example dataset with 1.1.1 and so on.
declare @table table ( ParentID INT, ChildID INT, Value float ) INSERT INTO @table SELECT 1,1,1.2
[code]....
This case ParentID - Child 1 ,1 & 2,2 and 3,3 records are called as parent where as null , 1 is child whoose parent is 1 similarly null,2 records are child whoose parent is 2 , .....
Now my requirement is to display parent records with value ascending and display next child records to the corresponding parent and parent records are sorted ascending
I have a table with three fileds 1 - SmsTime (datatype = datetime) 2 - MsgCnt (datatype = int) 3 - Message (datatype = varchar)
Here MsgCnt means it will give the number of sms part (70 charector is equal to One SMS). So in each row MsgCnt may be vary. Some time it may be 1 or 3 or 5 or 2 etc. Now here I need to count how many MsgCnt is received on Today. Its not to count the rows but counting the MsgCnt fild value. Is there any sql way to find it.
I report SQL Server table information (table names, field names, field datatypes, etc.) to my users using an ACCESS front end. Most of this information exists in the system tables. But I can't find a record count per table in the system tables. What's the fastest way to get a record count for all tables?
I was wondering if i would be able to add a column in a view that assigns a value to each record and incriments the number each time by 1. Like the way an identity field works in a table.
I have a table that contains a list of departments (about 50) and another table that contains helpdesk tickets, each record storing the ID of the department sending the ticket. So I'm trying to count how manay tickets per department, I tought of using a subquery and a WHILE cycle but Its just not happening..I sketch up this query:
Code: WHILE (SELECT MAX(DepartmentID) AS c FROM dbo.tblDepartment) i < i.c BEGIN SELECT COUNT(DepartmentID) AS DepartmentCount FROM dbo.tblTask WHERE (DepartmentID = @Variable) END
how could I build this query or what better way of doing the job there are...
Hi,I am struggling with a simple query, but I just don't see it.I have the following example table.Table MessagesID Subject Reply_to1 A 02 Ax 13 A 14 B 05 By 46 C 0The table holds new messages as well as replies to messages.Messages with Reply_to = 0 are top messages, the other messages arereplies to a top message. The subject of a reply message does notnecessarily have to be the same as the subject of the top message.What I would like to have returned is this: a list of messages whereReply_to = 0 and the number of replies to this message.ID Subject Num_replies_to1 A 24 B 16 C 0Any assistance would be greatly appreciated.
I seem to remember reading many moons ago about a function where youcan retrieve a count of the last recordset you opened.For example:I've got a stored procedure that returns a recordset using the TOP 10so I only get the top 10 records. I need to know the recordcount but Idont want to reuse the SELECT statement because its quite complex.Any ideas?What does @@Count do?Thanks in advance
Table2 contains fields Group, Name,Category, Dimension (Group and Name are not in Table1)
So basically I need to read the records in Table1 using Groupid and each time there is a Groupid then select records from Table2 where Table2.Category in (Select Catergory from Table1) and Table2.Dimension in (Select Dimension from Table1)
In Table1 There might be 10 Groupid records all of which are different.
is there a way to get a count of records for each table in a database by table in one query? I can query each table using a count, but this is pretty tedious when you have 50+ tables. Anybody have any ideas?
I am trying to write a query that will retrieve all students of a particular class and also any rows in HomeworkLogLine if they exist (but return null if there is no row). I thought this should be a relatively simple LEFT join but I've tried every possible combination of joins but it's not working.
SELECT Student.StudentSurname + ', ' + Student.StudentForename AS Fullname, HomeworkLogLine.HomeworkLogLineTimestamp, HomeworkLog.HomeworkLogDescription, ROW_NUMBER() OVER (PARTITION BY HomeworkLogLine.HomeworkLogLineStudentID ORDER BY
[Code] ...
It's only returning two rows (the students where they have a row in the HomeworkLogLine table).
Before I start driving myself nuts, I'd like to make sure my approach is correct.I want to create a simple job posting board.I have a text boxes for company name, email, job title, and job description, and a submit button.I created a table in my database called "JobPostings", with columns called "CoName", "CoEmail", "JobTitle", and "JobDesc"When someone fills out the fields and clicks submit, it will insert the new records.So,1. Is this the correct approach so far?2. What is the best way to display the job listings? A grid view?3. At submit time, how can I include that day's date?4. How can I get the records in the database to delete after 90 days?Thanks.
I am importing data from a paradox table and trying to clean it up. I have this query that finds all the child records that are not in the parent table.
Select MemberID FROM memtype AS a WHERE NOT EXISTS (SELECT * FROM members AS b WHERE a.MemberID IN (b.MemberID));
Now I'm trying to delete all those child records instead of just selecting them so I tried...
Delete MemberID FROM memtype AS a WHERE NOT EXISTS (SELECT * FROM members AS b WHERE a.MemberID IN (b.MemberID));
I have a parts table which has partid (GUID) column and parentpartId (GUID) column. Need to copy the records to the same table with new GUIDs for partids. How to do that? cursor or temp tables?
I have an application that has an existing query which returns org units (parent and child) from organization table with orderby on createddate + orgid combination.
Also I added another log table Organization_log with exact columns as Organization table and additional 'IS_DELETED' bool column.
WITH Org_TREE AS ( SELECT *, null as 'IS_DELETED', convert (varchar(4000), convert(varchar(30),CREATED_DT,126) + Org_Id) theorderby FROM Organization WHERE PARENT_Org_ID IS NULL and case_ID='43333'
[code]...
I need to modify the query:
1. To display the records both from the Organization table and Organization_Log table. 2. The orderby should be sorted on 'Organization Name' asc and it should follow the child order in alpha sort as well.
so if i give input say categoryid=1[This falls under main category-boxing] i need to get result as 1 boxing [main category] 4 mayweather [sub category] 5 tyson [sub category] 6 clinton woods [sub category]
if i give categoryid=5[Note:Tyson] result should be as 1 boxing [main category] 5 tyson [sub category]
We have a complex functionality of migrating data from a single record into multiple parent child tables.
To give you an example, lets us assume that we have a single table src_orders in the source database. We have a parent Order table and a child OrderDetails table in the target database. We need to pick one row from src_orders and insert this row in the Order table, pick up its PK (which is an identity column) and then use this to insert rows (say 5) in the OrderDetails table.
Again, we go back to the source, take a row, insert it into Orders, pick up the Orders PK and insert n rows in OrderDetails.
As of now, we are using the following approach for achieving this functionality.
1. Get the identity generated from the target table and store both the source table id and the target table id in a recordset.
2. Use the recordset as the source to a foreachloop , using foreachADO enumerator
3. Use data flow tasks to get the fields from the parent table for the source id, that needs to be inserted into the target child table
In case I have not ended up confusing everyone, can anyone validate this or suggest a better approach? :)
