i have a table with productID and OrderID. For ech product there are orders.
So for each productID there are lot Of OrderID's are present.
My data like
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
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#);
The boss has decided that the data from a table we have on one database (containing daily data) needs to be copied to a "history" table on a different database, on a different server.
The transfer will probably be done with a scheduled stored procedure, and all columns will be transferred EXCEPT for two columns in the source table, which are not present in the destination table. This means instead of an "INSERT Dest SELECT * FROM Source" I have to do an "INSERT Dest SELECT yada,yada,yada... FROM Source" in order to disregard the columns we don't care about. NO problem.
I was thinking (you should smell trouble - and sawdust burning at this point) that this leaves us open to a punch below the belt later when a new column is added to the Source table, since we are using a definitive list of columns to move rather than a *, and the new column could be added without the otherwise system-generated, *-sponsored "gentle reminder" that it also needs to be added to the Dest table. I'm not as convinced as The Boss that "Oh, I'm sure we'll remember to add it to the history table when that happens" :lol:
So, long story shorter (nah...too late for that) - I figure I can write a check at each day's historical transfer on the count of columns in one table verses the other, and send an email or fail the job if the count doesn't make sense.
For example, if the Source table has 34 columns, since we don't care about two of them, the Dest table should have 32 columns, if things are still in synch. OK, I think (insert burning sawdust smell here), I can use a SysObjects thang to count rows. BOL points out INFORMATION_SCHEMA.COLUMNS as a possibility.
Here is the code I think (sawdust) can be used:select ((select count(*) from SourceDb.INFORMATION_SCHEMA.columns where table_name = 'Source') - (select count(*) from HISTSERVER.DestDb.INFORMATION_SCHEMA.columns where table_name = 'Dest')) as ColumnDiff
Trouble is...this fails because apparently the INFORMATION_SCHEMA thang doesn't do well with remote servers.
Can anyone figure a way around this, or suggest an alternative? I'm still looking, but thought I'd also toss it out onto the board for your generous consideration.
Thanks - and can you BELIEVE the year is already 1/12th of the way OVER?!?!?! Paul
how count column in pivot table- and add result row i need to calculate each column for example day1 day2 day3 day4 day5 ------------------------------------------------------------------------- 1 2 1 2 3 1 2 3 2 2 2 3 2 1 2 2 3 0 0 0 -----------------------------------------------------------new result row ok ok 1|2|3 1 3
i need to check each column if i have twice each number if not show the missing number TNX
Code Block DECLARE @Employee TABLE (ID INT, Date SMALLDATETIME, ShiftID TINYINT) DECLARE @WantedDate SMALLDATETIME, -- Should be a parameter for SP @BaseDate SMALLDATETIME, @NumDays TINYINT SELECT @WantedDate = '20080301', -- User supplied parameter value @BaseDate = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @WantedDate), '19000101'), @NumDays = DATEDIFF(DAY, @BaseDate, DATEADD(MONTH, 1, @BaseDate))
IF @Numdays > 28 BEGIN SELECT p.ID, p.[1] , p.[2],p.[3], p.[4], p.[5], p.[6], p.[7], p.[8], p.[9], p.[10], p.[11], p.[12], p.[13], p.[14], p.[15], p.[16], p.[17], p.[18], p.[19], p.[20], p.[21], p.[22], p.[23], p.[24], p.[25], p.[26], p.[27], p.[28], p.[29], p.[30], p.[31] FROM ( SELECT ID, DATEPART(DAY, Date) AS theDay, ShiftID FROM v_Employee WHERE Date >= @BaseDate AND Date < DATEADD(MONTH, 1, @BaseDate) ) AS y PIVOT ( min(y.ShiftID) FOR y.theDay IN ([1], [2], [3], [4], [5], [6], [7],[8] , [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31]) ) AS p END
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?
Tab_Name & Load_Status columns provides the information about Name of table and status of data getting loaded to this table.
To this I would like to add a column to display the running count in the table, I mean how many records so far loaded, as below the recordCount coming from the same table tbl_collection
Hi guys, In the query below, I need to insert a new Column that shows the last Document date. In example, Document Date Last Date 2006/01/15 2007/11/30 2007/03/25 2007/11/30 2007/11/30 2007/11/30 I need to show the last (max) date for each row. Thanks in advance for any help, Aldo.
Code Block SELECT Stock.VALUEDATE AS 'Document Date'
FROM Stock As Stock INNER JOIN ACCOUNTS AS Accounts LEFT OUTER JOIN ACCSORTNAMES AS AccSortNames ON Accounts.SORTGROUP = AccSortNames.ACCSORTCODE LEFT OUTER JOIN EXTRANOTES AS ExtraNotes01 ON Accounts.ACCOUNTKEY = ExtraNotes01.KEF AND ExtraNotes01.NOTEID IN ('17') LEFT OUTER JOIN EXTRANOTES AS ExtraNotes02 ON Accounts.ACCOUNTKEY = ExtraNotes02.KEF AND ExtraNotes02.NOTEID IN ('18') ON Stock.ACCOUNTKEY = Accounts.ACCOUNTKEY INNER JOIN DOCUMENTSDEF AS DocumentsDef ON Stock.DOCUMENTID = DocumentsDef.DOCUMENTID
WHERE Accounts.SORTGROUP Between '0' AND '379' AND Accounts.SORTGROUP Not Between '100' AND '150' AND Stock.DOCUMENTID In ('1', '4', '5', '11', '78', '95') AND Stock.STATUS Not In ('0', '2') AND Accounts.SORTGROUP Between '0' AND '379' AND Accounts.ACCOUNTKEY Like '313%'
Is there a way to alter the default 256 value to some other value in a transac SQL script? The value can be changed manually in the advanced tab of the current connection options. I need to be able to set and reset this value in a script and/or stored procedure. Kind of urgent for an answer to this problem. Thanks in advance.
In a query Analyser, there is a provision to change the "Maximum characters per column 256 " to our size in advanced option.
It works ok.
We are using VisualInterdev for developing ASP's -- there thro ADO - we are getting the rows from sql server 7.0 -- there data more than 256 characters are truncated.
How will i get the actual data ?.. where i can do the settings..?
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
On table TABLENAME, I have increased the size of column C to decimal(38, 14); maximum size and an excessive amount of precision. If I do a SELECT MAX(CONVERT(DECIMAL(7,2), A) / B) FROM TABLENAME SELECT MIN(CONVERT(DECIMAL(7,2), A) / B) FROM TABLENAME I get 3.0000000000000 for the MAX and 0.1000000000000 for the MIN value, yet if I update UPDATE TABLENAME SET C = CONVERT(DECIMAL(7,2), A) / B WHERE B <> 0 Msg 8115, Level 16, State 8, Line 1 Arithmetic overflow error converting numeric to data type numeric. The statement has been terminated. Obviously I have obscured the column names and table name, but I am puzzled as to why values in the range 0.1 to 3.0 should give an overflow error. I am using SQL2005 SP2
I have some code I build 2 weeks ago which I’ve been running daily but it’s suddenly stopped working with the following error.
“The table "tbl_Intraday_Tmp" has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes. INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit” When I google this there seems to be a related to tables with vast numbers of columns.
My table tbl_Intraday_tmp is relatively small. It has 7 columns. 1 of varchar(5), 3 of decimal(9,3) and 2 of decimal(18,0). The bit I’m puzzled with is it was working and stopped.
I don’t recall changing anything but I wouldn’t rule that out. I ‘ve inspected the source files and I don’t believe they have changed either.
HiAm Using ASP.NET With SQL SERVER 2005 Backend AGENT CODE 3008000003
NAME agent code dropdownlist values like 1005000006,2009000002,3008000003select dropdownlist value it display corresponding values related to that codewhen i select first 2 values its run properly,But when i select 3008000003 i will get following error messagein SQL SERVER 2005 Agent Code Date Type is "bigint"" The conversion of the nvarchar value '3008000003' overflowed an int column. Maximum integer value exceeded. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: The conversion of the nvarchar value '3008000003' overflowed an int column. Maximum integer value exceeded.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace: Please Help me to solve this issueThanks With RegardsS.Senthil Nathan
is there any more efficient way for example to implement the next query?
SELECT s1.article, dealer, s1.price FROM shop s1 JOIN ( SELECT article, MAX(price) AS price FROM shop GROUP BY article) AS s2 ON s1.article = s2.article AND s1.price = s2.price; WHERE dealer = 'dealer sample'
Dear Experts, i've one database with around 1400 tables. is there any possibilities to know at a time what is the count(*) in each table? actually i need tables which are having maximum data.
my expected result is like this table num_rows table1 20000 table2 10000
like this thank you very much
Vinod Even you learn 1%, Learn it with 100% confidence.
I am using this below query to sum and select maximum values from table. I have converted the cost column here and how can I possibly sum the cost column?
select ID, MAX(Dates) Dates,'$ ' + replace(convert(varchar(100), convert(money, Cost), 1), '.00', '') Cost, MAX(Funded) Funded from Application group by ID, Dates, Cost, Funded
SQL 2000I have a table with 5,100,000 rows.The table has three indices.The PK is a clustered index and has 5,000,000 rows - no otherconstraints.The second index has a unique constraint and has 4,950,000 rows.The third index has no constraints and has 4,950,000 rows.Why the row count difference ?Thanks,Me.
--drop table #temp create table #temp (id int, idvalue int) insert into #temp(id,idvalue) select 1095,75
[code]...
I need to take the id value from maximum's id, and compare the rest id value from the table. i need to check the diffrence , if diffrence is more than 18, then i need to raise the flag as failure otherwise the whole test is success. i need to take 63 and compare rest 69,65,61,75.check the diffrence less than 18 or not.
Can any one help me, i'm building a dynamic database driven site using dreamweaver and MS SQL2000 andi'm haveing problem storing over 8000 characters in a table filed (IE: it wont let me!!) is there a special table field value that i need to set to get more characters in a table field or is this a limitation of SQL.
Create table code ( id identity(1,1) code parentcode internalreference)
There are other columns but I have omitted them for clarity.
The clustered index is on the ID.
There are indexes on the code, parentcode and internalreference columns.
The problem is the table stores a parentcode with an internalreference and around 2000 codes which are children of the parentcode. I realise the table is very badly designed, but the company love orms!!
The table currently holds around 300 millions rows.
The application does the following two queries to find the first internalreference of a code and the last internal refernce of a code:
--Find first internalrefernce SELECT TOP 1 ID, InternalReference FROM code WHERE ParentCode = 'M222' Order By InternalReference
-- Find last ineternalreference SELECT TOP 1 ID, InternalReference FROM code WHERE ParentCode = 'M222' Order By InternalReference DESC
These queries are running for a very long time, only because of the sort. If I run the query without the sort, then they return the results instantly, but obviously this doesn't find the first and last internalreference for a parentCode.
I realize the best way to fix this is to redesign the table, but I cannot do that at this time.
Is there a better way to do this so that two queries which individually run very slowly, can be combined into one that is more efficient?
I have a problem (who not?) with a function which i'm using in a view.This function is a function which calculates a integer value of adate. For example: '12/31/2004 00:00:00" becomes 20041231. This isvery handy in a datawarehouse and performes superfast. But here is myproblem.My calendar table is limited by a couple of years. What happens isthat sometimes a value is loaded which is not in the range of theCalendardate. What we want to do is when a date is loaded is that thisfunction insert a minimum date when date < minimum date and a maximumdate when date > maximum date.Yes i know you're thinking : This is datamanipulation and yes this istrue. But now we loose information in our cubes and reports by innerjoining. So if we can use a minimum and a maximum than a user wouldsay: "This is strange, a lot of values on 1980/1/1!" instead of "Ithink that i have not all the data!"GreetzHennie
i got a error while retrieving more than 100000 rows (records) from a table .. can any one tell me what is the maximum number of rows retrieve from a database to reports... and how can i overcome this issue...
SQL Server 2000 8.00.760 (SP3)I've been working on a test system and the following UDF worked fine.It runs in the "current" database, and references another database onthe same server called 127-SuperQuote.CREATE FUNCTION fnGetFormattedAddress(@WorkID int)RETURNS varchar(130)ASBEGINDECLARE@Address1 As varchar(50)@ReturnAddress As varchar(130)SELECT@Address1 = [127-SuperQuote].dbo.tblCompany.Address1FROM[Work] INNER JOIN[127-SuperQuote].dbo.tblCompany ON [Work].ClientID =[127-SuperQuote].dbo.tblCompany.CompanyIDWHERE[Work].WorkID = @WorkIDIF @Address1 IS NOT NULLSET @ReturnAddress = @ReturnAddress + @Address1 + CHAR(13)+ CHAR(10)RETURN @ReturnAddressENDSo now the system has gone live and it turns out that the live"SuperQuote" database is on a different server.I've linked the server and changed the function as below, but I get anerror both in QA and when checking Syntax in the UDF builder:The number name 'Zen.SuperQuote.dbo.tblCompany' contains more than themaximum number of prefixes. The maximum is 3.CREATE FUNCTION fnGetFormattedAddress(@WorkID int)RETURNS varchar(130)ASBEGINDECLARE@Address1 As varchar(50)@ReturnAddress As varchar(130)SELECT@Address1 = Zen.SuperQuote.dbo.tblCompany.Address1FROM[Work] INNER JOINZen.SuperQuote.dbo.tblCompany ON [Work].ClientID =Zen.SuperQuote.dbo.tblCompany.CompanyIDWHERE[Work].WorkID = @WorkIDIF @Address1 IS NOT NULLSET @ReturnAddress = @ReturnAddress + @Address1 + CHAR(13)+ CHAR(10)RETURN @ReturnAddressENDHow can I get round this? By the way, I've rather simplified thefunction to ease readability. Also, I haven't posted any DDL because Idon't think that's the problem!ThanksEdward
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.
I am TRYING to write code to combine two tables and then return the maximum value of one table, but SQL Server keeps telling me that the column is not valid.... I have added attached screenshots to show that it IS a valid column, so I cannot figure out what is the retarded issue!!
