I'm hoping someone can help with with a task I've been given. I need to write a trigger which will act effectively as a method of automatically distributing of incoming call ticket records. See DDL below for creation of the Assignment table, which holds information on the call ticket workload.
SELECT COUNT(CallID) AS [Total Calls], AssignmentGroup, Assignee FROM #Assignment GROUP BY AssignmentGroup, Assignee ORDER BY COUNT(CallID) DESC , AssignmentGroup, Assignee
What I need to do is write a trigger for on INSERT to automatically update the Assignee column with the name of the person who currently has the least active calls. For example, using the data above, the next PC Support call will go to Mickey Mouse, and the next two Service Desk calls will go to Jim Smith.
So, the logic for the trigger would be
UPDATE #Assignment SET Assignee = (SELECT Assignee FROM #Assignment WHERE COUNT(CallID) = MIN(COUNT(CallID))
But that's only the logic, and obviously it doesn't work with the syntax being nothing like correct.
Does any one have an idea or pointers as to how I should go about this?
We have an application that runs Jobs, each of which affect ## number of child objects (usually around 1M). When a thread gets to 5000 updated child objects it bulk inserts into a table called ActionLog with the child Id and JobId.
When the job is complete a sproc SUMs the children from the ActionLog table: select sum(id) from ACTIONLOG where JOBID = @JobId;
It then updates the Jobs table AffectedObjectCount column with the sum(*) from above.
Instead of writing to the ActionLog table and calculating the SUM at the end I would like to do this 'real time'. After the bulk insert I would like to update the AffectedObjectCount column with the number of rows that were just bulk inserted. I tried this in the past and ran into major contention issues. There are usually 20 threads running a job so there exists a lot of potential for deadlocks.
Is there a recommended way to handle updating one column on one row from multiple threads? What is the best practice for a counter like this?
I use SQL 2000 I have a Column named Bool , the value in this Column is 0�0�1�1�1 I no I can use Count() to count this column ,the result would be "5" but what I need is "2" and "3" and then I will show "2" and "3" in my DataGrid as the True is 2 and False is 3 the Query will have some limited by a Where Query.. but first i need to know .. how to have 2 result count could it be done by Count()? please help. thank you very much
Hi Guys, I cannot seem to get the syntax right on this one. I am trying to Update a field in a table with the recount of another table. Is it even possible to do this???
Update P_Dates SET RecCount = RecCnt FROM (SELECT Count(ACCTNO) As RecCnt FROM IMPORT Where @sDate = StartDate AND @eDate = EndDate)
I am trying to update a field in a temptable with the count of items in another table. To illustrate, CustomerID=23 and I want the number of occurences in the temp table. Here's the code which DOESN'T work:
INSERT INTO TempTable ( CustomerID, FirstName, LastName, DateAdded, AlbumPicture, LayoutCount ) SELECT Albums.CustomerID, Customers.FirstName, Customers.LastName, DateAdded, AlbumPicture, COUNT(*) FROM Layouts WHERE Layout.customerID = Albums.CustomerID FROM Albums JOIN Customers on (Albums.CustomerID=Customers.CustomerID)
Please take a look at the COUNT line. Here I want to count the occurences of a specific customerid in another table and put in into th LayoutCount field.
SQL server reports "Incorrect syntax near the keyword 'FROM'". Any ideas how to achieve this?
I have a table with Bill_Doc, Bill_Item and Bill_Doc_Count fields. I need to update Bill_Doc_Count with 1 only on the first Bill_Item. Is this possible? I tried min and max but they don't work for me.
Field Names: NOs Code Code1a UniqueID61 10 888 1062 10 888 1163 10 888 12Logic: If Count(code >1) & Count (Code1a >1)Update the (Nos) to EQUAL the same Value.ALL the Nos for the above examble should be the same value forall three records whether it's 61 for all three records of anyof the other two numbers, it doesn't matter as long as the equal the same value.How can this be done via sql?
We have SharePoint list which has, say, two columns. Column A and Column B.
Column A can have three values - red, blue & green.
Column B can have four values - pen, marker, pencil & highlighter.
A typical view of list can be:
Column A - Column B red  - pen red - pencil red - highlighter blue - marker blue - pencil green - pen green - highlighter red  - pen blue - pencil blue - highlighter blue - pencil
We are looking to create a report from SharePoint List using SSRS which has following view:
          red   blue  green   pen       2    0    1   marker    0    1    0   pencil      1    3    0   highlighter  1    1    1Â
We tried Sum but not able to display in single row.
Hi is there away of doing both my select and update of a field in my database (m not using store procedures)
Basically all I need to do is get the value of DownloadTimes then add 1 and re update it. Also to stop missing increments what the best way to lock the database while an update is in progress?
This is basically firing after a user clicks a file to download. I bring up the dialogue for them to save it is it possible to get which button they press and what is the best way to deliver it to them eg response.writeFile()??
I am importing a text file that list invoice columns. The invoice detail table needs the line items to be listed with sequential numbers. I import the file to a temp table to do the work in and I know I need to have the cursor do loop through and count, however, I have no more hair to pull out.
I have added some SQL to an Access form which updates the dbo_BM_Map table when the user hits the Apply button. There is a temp table with various fields, two being "Chapter_No" and "Initial_Mapping_Complete" which the update is based on.
I want this update to only apply to chapters that only have one name in the "Initial_Mapping_Complete" column. If a chapter has more than one then the update should ignore it. The attached screengrab shows you. The update should ignore chapter 19 as there are two people (Jim and James) in the Initial_Mapping_Complete field. Here is my code.
pdate dbo_BM_Map inner Join Temp_Progression_Populate on dbo_BM_Map.Product_ID = Temp_Progression_Populate.Product_ID Set dbo_BM_Map.Initial_Mapping_Complete = Temp_Progression_Populate.Initial_Mapping_Complete Where dbo_BM_Map.Chapter_No = Temp_Progression_Populate.Chapter_No And Temp_Progression_Populate.Initial_Mapping_Complete in (Select count(Initial_Mapping_Complete), Chapter_No from Temp_Progression_Populate Group by Chapter_No Having Count(Initial_Mapping_Complete) = 1)
updating the # of Payer from below query to match with the # of rows for each payer record. See the Current and desired results below. The query is currently counting the # of rows for all payers together and updating 3 as # of payers. I need it to count # of rows for each payer like shown inDesired result below. It should be showing 1 for first payer and 2 for 2nd & 3rd based on # of times each payer is repeated..
SELECT b.FILING_IND, b.PYR_CD, b. PAYER_ID, b. PAYER_NAME,a.CLAIM_ICN, (Select Count(*) From MMITCGTD.MMIT_CLAIM a, MMITCGTD.MMIT_TPL b , MMITCGTD.MMIT_ATTACHMENT_LINK c where a.CLAIM_ICN_NU = c.CLAIM_ICN and b.TPL_TS = c.TPL_TS and a.CLAIM_TYPE_CD = 'X'Â
I want to create a trigger that, when a field is updated or a record is inserted, counts the number of words in "field1", inserting this numeric value into another field in the same table. What would be the most efficient, fastest method for doing this?
select * from HistoryLog I will got a return result with Mainindex �Sales�Date 3 kinds of columns Data , If I need to add one column by myself let the result like below Mainindex �Sales�Date�Total the Total's value is from count(MainIndex) of HistoryLogDetail Table , result will look like below MainIndex Sales Date Total1 John Jan-05-1999 2 →(because this record's index 1 exist in HistoryLogDetail Table twice so the total is 2)2 John Jan-07-1999 1 →(because this record's index 1 exist in HistoryLogDetail Table twice so the total is 1)3 Liz Jan-08-1999 14 Yumi Jan-08-1999 0 I trid wirte a code to got the total column but what I got is like thisMainIndex Sales Date Total 1 John Jan-05-1999 4 2 John Jan-07-1999 43 Liz Jan-08-1999 4 4 Yumi Jan-08-1999 4 what the columns of Total shows is .. how many record are in HistoryLogDetail Table but that's not I need ..can you help?thank you very much
I am new to SQL so please bear with me. I was wondering how to create a new column that counts the EntityId COUNT(CdsQuotes.EntityId) and puts it into a seperate column.
What I am trying to achieve is a count of entity IDs from one table and a count of entity IDs from another table - but the 2nd count must be from the first counts results (hope this makes sense).
So it returns a count from table 1 and then a corresponding count from table 2 (based on table 1 entityIDs)
Any ideas?
SELECT
Entity.EntityName, SuspectBond.EntityId, COUNT(SuspectBond.EntityId) as "Suspect Bonds"
FROM SuspectBond
INNER JOIN Entity ON SuspectBond.EntityID = Entity.EntityId
I'm starting to use SQL 2008 recently, and I'm just having trouble with the following problem:
The following query:
SELECT t_Category.Name as [Category] FROM t_Assets, t_Category, t_Priority, t_Location, t_User_Assets WHERE t_Assets.Asset_ID = t_User_Assets.Asset_ID AND t_Category.Category_ID = t_User_Assets.Category_ID AND t_Priority.Priority_ID = t_User_Assets.Priority_ID AND t_Location.Location_ID = t_User_Assets.Location_ID
Returns this result:
Category BMS BMS Water BMS BMS Air
And the following query:
SELECT COUNT(t_Category.Category_ID) AS AssetQty FROM t_Assets, t_Category, t_Priority, t_Location, t_User_Assets WHERE t_Assets.Asset_ID = t_User_Assets.Asset_ID AND t_Category.Category_ID = t_User_Assets.Category_ID AND t_Priority.Priority_ID = t_User_Assets.Priority_ID AND t_Location.Location_ID = t_User_Assets.Location_ID GROUP BY t_Category.Category_ID
Returns this result:
AssetQty 4 1 1
I need to have both of those results returned, as a single result. Such as:
Category AssetQty
BMS 4 WATER 1 AIR 1
However, I'm not able to, due to the fact, that if I add the "t_Category.Category.Name" in the SELECT clause, it gives me the following error:
Column 't_Category.Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
And if I try to use the "Name" as part of the count clause, it won't work, as text are not acceptable data types for aggregations.
I have a table for blog comments I want to add a column that counts the number of comments for each article. existing table looks like this: CommentID ArticleID (FK) commentAuthor authorEmail comment commentDate (getDate()) I would like to add a column that counts the number of total comments for each article.This will give me what I want using the VS query tool: "SELECT COUNT(comment) AS Expr1 FROM UserComments WHERE (articleid = @articleid)" But can I add that to the table somehow so it does it automagically???
I m using SQL Server 2000. I have Tabel named Topic and have a column name lineage. lineage has data like following: //////546707//546707//546707/43213/ Now I want to get records who has only one "/" in it's crreponding lineage column. Can somone tell me how to do that in SQL Server 2000? Thanks Khushbu
Assuming that I have a table similar to this simplified version:
CREATE TABLE [SomeTable] ([Field1] NVARCHAR(50));
How could I count and rank the instances of specific values in the "field1" column? For example, let's say that I want to show the frequency of the column values like so:
field1 count some value 24 another word 20 a value 5
I want to count number of occurrence in column c3. i.e., count 1 occurred value 9 times, count 2 occurred value 3 times, count 3 occurred value 3 times.
My goal is to with one update statement, fill TABLE1.counter (currently empty) with the total count of TABLE2.e# (employee). Also, if TABLE1.e# isn't in TABLE2.e# then it sets it to "0" (TABLE1.e# 8 and 9 should have a counter of 0) This is for sqlplus.
e.g. TABLE2:
e# -- 1 2 3 4 5 5 6 7 7 1 2 3 4 5 UPDATE TABLE1 SET counter = ( SELECT COUNT(TABLE2.e#) FROM TABLE2 INNER JOIN TABLE1 ON (TABLE2.e# = TABLE1.e#) GROUP BY TABLE2.e#);
I have a few million rows of a data in a table that store indexing information for scanned customer documents. Fields like last name, first name, document description (descript), document type and scan date (scandate).
I want to be able to query the document description column where it'll show how many times each document description was scanned/used over the course of the last 6 months or year.
I am trying to determine the number of columns that are returned froma stored procedure using TSQL. I have a situation where users will becreating their own procedures of which I need to call and place thoseresults in a temp table. I will not be able to modify those usersprocedures. I figure if I have the number of columns I can dynamicallycreate a temp table with the same number of columns, at which point Ican then perform an INSERT INTO #TempTableCreatedDynamically EXEC@UserProcCalled. With that said, does anyone have any idea how todetermine the number of rows that an SP will return in TSQL?Thanks!
I have a table of users and date when they logged on to a system. I am trying to count how many distinct users logged on for each day of the week. The SQL below works when there's at least a user for each day. But when there is no user for a particular day such as Sunday, I still want it to return "SUN
0 "
I learned that you can use GROUP BY ALL and it works but the "ALL" is deprecated beyond SQL 2005.
------------------------------------ SELECT UPPER(LEFT(DATENAME(dw, StartTime), 3)) AS DayOfWeek, COUNT(DISTINCT UserID) AS NumberOfUser
FROM testUserLoginDuration WHERE Archived = 0 GROUP BY UPPER(LEFT(DATENAME(dw, StartTime), 3)) ORDER BY CASE WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'MON' THEN 1 WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'TUE' THEN 2 WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'WED' THEN 3 WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'THU' THEN 4 WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'FRI' THEN 5 WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'SAT' THEN 6 WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'SUN' THEN 7 END