select t.* from test t join test2 t2 on t2.id=t.id where CHECKSUM(t.col2,t.col3)<>CHECKSUM(t2.col2,t2.col3)
--The purpose of the above script is to check for any updates in the two tables. It returns two rows. But as you can see both these rows were present in the table before. So I modify the script to - --SCRIPT B select t.* from test t join test2 t2 on t2.col2=t.col2 where CHECKSUM(t.col3)<>CHECKSUM(t2.col3)
-- In this case no row is returned.This is exactly what I need. The problem - Now execute the script below.
TRUNCATE TABLE TEST TRUNCATE TABLE TEST2
insert test values(4,4,'d','02/06/2004') insert test values(4,4,'d','02/01/2004')
--Now when I execute script B two rows are returned which is not what I want. Since the rows are identical no row should be returned. So depending on what column changes (col2 or col3), I have to alter the script. I seek advise on the method to calculate checksum. Again the PK is ID and Col1 only.
My table is laid out as such:ID (int) What (varchar 20) TimeStamp (smalldatetime)------- ------------- ---------------73 Start <T1>73 Misc <T2>73 End <T3>81 Start <T1'>81 Misc <T2'>81 End <T3'>....I need to calculate End - Start for each unique ID (i.e. T3-T1 andT3'-T1') and then take the average of those (2 in this case) entries.Any help is appreciated.Alex.
hello sir Thank for ur response to me in such a way. I m student of cs in final semester & designing a project for online test series for all subject like mathematics, physics, chemistry, geography. Ihave a pb for storing mathematical questions which includes special symbols in organic chemistry structural farmulas etc.How can we store in database. i m sending a test paper for better understanding. plz help me.I will be hioghly obliged to u for this.
I've created a sql statement that retrieves number data from various table joins. The number data is then grouped according to various categories. What I need to do is to calculate the average of all the number data in a particular group. How do i go about this? Once calculated, the average needs to be displayed under the data.
For example, the report will list say five numbers (some sets may have more, it depends on how much data is returned based on the query), then under the five numbers, the average is given
2 4 3 6 0
Average: 5
What technique is best? Do I have to calculate the numbers in sql or do I need to configure the report to calculate the average? If so, how? Can someone show me step by step how to do averages for a set of data in the reporting services?
I am completely new to doing reports, I'm just a hobbyist, and I've only used databases to retrieve basic data, but not make manipulations for reports.
I would like to is there any way we can store mathematical equations, that can be typed using equations 3.0, in sql server database as a column. What would be the datatype for the same.
Hi,I would like to know if its possible to add/subtract/multiply/divide values between a cell in a database and a textbox/label on a web form? how would I go about doing this? Also, I would like a number from a cell to appear in a label when a page first loads. How can I achieve this?
I am having 4 Columns Qty decimal(12,3),CF1 Decimal(12,3),CF2 Decimal(12,3),Flag TinyInt.
I want to perform following without using case if it is possible.
When value of Flag is 0 then Qty*(CF2/CF1) When value of Flag is 1 then Qty
And i Don't want to use any functions like isnull,NullIf,IIF even not union or union all.How to do this calculation without using any function.
Actually i am having more then 100000 rows in table and if i use functions then my index might not be called.,that why want to avoid cases and functions.
When I am executing a query that uses a mathematical function on values from 2 tables the query takes much longer than the same query that uses values from 1 table, even though the join remains the same.
Why is this happening? Is there a way to bypass this problem?
Long query ( values from 2 tables ) : SELECT MAX ( ( SIGN ( attribute.keyValue- ( -2027587559 ) ) *SIGN ( attribute.keyValue- ( -2027587559 ) ) -1 ) *-1*data.val ) AS maxVal FROM DATA data, ATTR attribute, TREE_ELEMENT elm, TREE_ELEMENT subject WHERE data.elmId=elm.id AND attribute.keyValue IN ( 345647222,1569153803,1569146115,-2027587559 ) AND subject.id=elm.subjectId AND subject.name = ‘test’
Short query ( values from 1 table ) : SELECT MAX ( ( SIGN ( data.keyValue- ( -2027587559 ) ) *SIGN ( data.keyValue- ( -2027587559 ) ) -1 ) *-1*data.val ) AS maxVal FROM DATA data, ATTR attribute, TREE_ELEMENT elm, TREE_ELEMENT subject WHERE data.elmId=elm.id AND attribute.keyValue IN ( 345647222,1569153803,1569146115,-2027587559 ) AND subject.id=elm.subjectId AND subject.name = ‘test’
I have the following SELECT statement. It returns the first record from EVENT_TEACHERS. I want the last record. 'iID' is the unique identifier so I want to select this stuff where iID is max. How can I add this to my string? I can't get the block out of my head of wanting to put the aggregate in the WHERE clause. str = "SELECT vwStaffList4Assessment.*, EVENT_TEACHERS.iID, EVENT_TEACHERS.iAsmtID, ASMT.* FROM EVENT_TEACHERS INNER JOIN ASMT ON EVENT_TEACHERS.iAsmtID = ASMT.iAsmtID INNER JOIN vwStaffList4Assessment ON ASMT.DVN=vwStaffList4Assessment.DVN" Thanks
I am trying to insert to our DB. Here is my insert statement. str = "INSERT INTO SURVEY (q3) VALUES (" & q3 & ") WHERE iID="&iID Here is my debug: DEBUG: INSERT INTO SURVEY (q3) VALUES (1) WHERE iID=23 And here is my error: Incorrect syntax near the keyword 'WHERE'. Can someone please tell me what I am doing wrong? Thanks in advance.
I am new to SQL and need some help. Would anyone be able to tell how to write a statmement, that would bring back several columns with specific amounts of spaces in it? I don't want to change the sizes of the columns forever, just for this one query.
I START A NEW JOB WITH A COMPANY PROVIDES IT SUPPORT TO CALL CENTERS. WE USE VB6 CONNECTING VIA ADO TO SQL SERVER 2000 STORED PROCEDURES. OUR ONLINE REPORTING TOOL USES CRYSTAL REPORTS & ASP (LITTLE VB6). CAN ANYONE RECOMMEND GOOD BOOKS AND WEBSITES TO PREPARE ME FOR MY NEW POSITION WITH THIS COMPANY?
ALSO, HOW DOES ONE GO ABOUT BUYING RELEVANT CODE IN VB6, TRANSACT SQL, ASP?
WITH CALL CENTERS (OUR CLIENTS) MOVING OVERSEAS-OUTSOURCING, WHAT ABOUT ENRICHING OUR POTENTIAL CLIENT BASE...FOR EXAMPLE, RECOMMENDING MARKETING EFFORTS TO 'NAILED DOWN' CLIENTS LIKE PHARMACEUTICAL COMPANIES WHERE THE GOVERNMENT DEMANDS SUCH WORK BE DONE IN AMERICA. OR EXPANDING OUR ROLE TO ALSO DO...?
ANY IDEAS TO ABOVE THREE ISSUES PLEASE-PLEASE EMAIL mikelynn@comcast.net
Hi: Can any one please tell me the difference b/w TSQL and simple SQL. Also there is a a TSQL use in my VB6 Program, how to i find that SQL in SQL Server? cnADOSQL.Execute "DBCC CHECKIDENT ('tbl_distlist_balrange', RESEED, 0)"
I am trying to use LIKE in a statement but I can't seem to pull all the rows for Age of Empire. I have at least 50 rows with the word Age of Empire but I only get two rows. What is the best way to use LIKE in the statement below so that it pulls all rows with the words Age of Empire?
Select * from dbo.proddesc where codeabbreviation Like '%Age of empire%'
Hi Im reading about active and temporal databases. I have a quesiton though. In which category does SQL server falls into? Also What is the difference between Transact SQL and TSQL2?
I am trying to tally up information from two tables, but I am runningin to trouble and I can't seem to figure this out. I have aCreditsTable and a DebitsTable and I am trying to create a querry thatsums up the info from each per UserID and returns TotalCredits,TotalDebits, and a Ballance.CreditsTableUserID Ammount Applied+----------+----------+----------+| 192 | 1 | True || 192 | 2 | True || 207 | 1 | True || 207 | 1 | True || 207 | 2 | True || 212 | 3 | True |DebitsTableUserID Ammount Applied+----------+----------+----------+| 192 | 1 | True || 207 | 1 | True || 207 | 1 | True |***This is the Function I have tried, but it doesn't return the correctresultsALTER FUNCTION [dbo].[BallanceTotals]()RETURNS TABLEASRETURN(SELECT DISTINCTdbo.CreditsTable.UserID, SUM(dbo.CreditsTable.Ammount) AS TotalCredits,SUM(dbo.DebitsTable.Ammount) AS TotalDebits,SUM(dbo.CreditsTable.Ammount - dbo.DebitsTable.Ammount) AS BallanceFROMdbo.CreditsTable FULL OUTER JOINdbo.DebitsTable ON dbo.CreditsTable.UserID = dbo.DebitsTable.UserIDWHERE(dbo.CreditsTable.Applied = 1) OR (dbo.DebitsTable.Applied = 1)GROUP BYdbo.CreditsTable.UserID)*** This is what it returns, it is not adding things up correctly (itlooks like it is adding NULL values as 1 instead of 0 or something)BallanceTotalsTotal TotalUserID Credits Debits Ballance+----------+----------+----------+----------+| 192 | 3 | 2 | 1 || 207 | 4 | 3 | 1 || 212 | 3 | | |*** This is what I want it to return!BallanceTotalsTotal TotalUserID Credits Debits Ballance+----------+----------+----------+----------+| 192 | 3 | 1 | 2 || 207 | 4 | 2 | 2 || 212 | 3 | 0 | 3 |I would really appreciate some help in getting this to work correctly!-Daniel
Why is my where skewing data? Â This 1st statement shows everything perfectly, but this go around Ineed to add one more stipulation and adding in the where returns 0 results. Â But if I run a simple select with the where added, I get the intended result set.
--works with cte as(select *, row_number() over(partition by time1, time2, userID order by userID ) as rn from timetable) delete from cte where rn > 1
--no work with cte as(select *, row_number() over(partition by time1, time2, userID order by userID ) as rn from timetable where timeadded > 0) delete from cte where rn > 1
Below is my SQL code and after that output i get. For result i would like to get SUM of column Expr1 (which is 60.668,66) so that means only 1 row not 5. I know it's not that hard but i've been searching around and trying but no luck...
SELECT INVENTTABLE_1.ITEMID AS MA, INVENTDIM_1.INVENTLOCATIONID, SUM(INVENTSUM_1.PHYSICALINVENT) AS Expr1 FROM dbo.INVENTDIM AS INVENTDIM_1 INNER JOIN dbo.INVENTSUM AS INVENTSUM_1 ON INVENTDIM_1.INVENTDIMID = INVENTSUM_1.INVENTDIMID INNER JOIN dbo.ECORESPRODUCTTRANSLATION AS ECORESPRODUCTTRANSLATION_1 INNER JOIN dbo.INVENTTABLE AS INVENTTABLE_1 ON ECORESPRODUCTTRANSLATION_1.PRODUCT = INVENTTABLE_1.PRODUCT ON
I have 3 tables in SQL 2005. I have dumbed them down significantly, to illustrate my question.
1) Item Table - Contains a list of 2 items, "Coca-Cola" and "Pepsi-Cola". 2) Market Share - Contains the 'market share' information for the items in "Item Table". 1 to 1 relationship. 3) Retail Price - Contains the 'average retail' price data for the items in "Item Table". 1 to 1 relationship.
All three tables are linked by "iItemID".
I am trying to build a simply query that generates the following results:
Where the first two values for Market Share are repeated again for row 3 and 4.
How do I syntax my SQL statement to return a single data set in the following fashion? ---------------------------------------------------------- Item Name Market Share Retail Price ----------------------------------------------------------- Coca-Cola mm.mm% $xx.xx Pepsi-Cola mm.mm% $xx.xx
I know this is simple for someone who write queries all the time. But this is driving me crazy. I have tried various JOINS (LEFT OUTER, RIGHT, etc.), but I am missing something.
I need to return a computed value and have no idea how to do it.
I have a table of dates: Column DataType RowID int Person nvarchar(20) Percent real Date smalldatetime
What I need is the Annual Fiscal Year Average. The fiscal year starts and ends in June. I need it to add all Percent values between the months of June 2001 - June 2002, June 2002 - June 2003, and so on and on....
A result set like this...
Person - FiscalYear - FiscalYearResult John D - 2001 - .58 John D - 2002 - .52 John D - 2003 - .50 Jane D - 2001 - .58 Jane D - 2002 - .52 Jane D - 2003 - .50
so on and so on...
How do I write this in Transact-SQL to get this result set...
Currently, I am running the code in an Access/VBA module through an ODBC connection, but I would like to be able to run all my code directly on the server.
Thanks in advance for any help you might be able to provide.
I came to know one of the better option in Oracle SQL for TOP-N Analysis-To find the top 5 usage accounts, top 10 users, etc. Is there any such way in SQL server ?
Is there any ways of using JOINS ( using keyword INNER & OUTER ) ? Some of the scripts that is generated automatically in Microsoft access with Join condition is not even working in MSSQL server 6.5. Is it true ? Or is there any other way that I can use these scripts in SQL 6.5.
If possible, pls. help me with some sample scripts ?
Hi! I need help! We have 98% transact log full. After run DUMP TRANSACTION WITH TRUNCATE ONLY it still doesn't purge the log. How can we resolve problem.
Ok here is the situation I need a little help with.
I am passing an XML document to a stored procedure. My XML document looks like this: <ROOT> <customer ID="10" Address="1234 Somewhere"></customer> <customer ID="20" Address=""></customer> </ROOT>
Now I use sp_xml_preparedocument and OpenXML to Insert the XML ID and Address attributes into a table in my database. The table has 2 fields (ID int) and (Address varchar(100) nullable)
The result of the insertion is 2 rows in my table with the ID field containing (10 and 20) and the Address field containing (1234 Somewhere and an empty string).
My first question is, why isn't the second row Address field in my database null instead of an empty string since thats what I would like happen. Is it because the XML Address atribute = "".
If that is the case then that should bring up my second question. Since the OpenXML inserts Address="" as an empty string instead of NULL, why doesn't a regular TransactSQL INSERT statement called from ASP does the same thing?? If I lost you here then let me give a quick example. An html web form with a text field (txtAddress). The user leaves the text box empty and submits the form. My ASP takes the value of the form, Request.Form("txtAddress") and passes it to stored proceudre through an ASP parameter object. My stored procedure simply does an INSERT into tablename (ID, Address) VALUES (@passedID, @PassedAddress). Now the outcome in the database is that the Address field is NULL and not an empty string.
That is why im confused. Why arent the OpenXML and the straight-forward insertion yield the same result (either null or empty string). It seems to me that an empty text box is an empty string and not null. ASP's ISNull(txtAddress) agrees and returns false.
Any insight ot help on this issue is greatly appreciated.
What I am trying to do is iterate through a set of records from a table, and setting one field to a common value on each record, and setting another to an incremented value. Here it is:
Table contains columns A, B, and C. A is a unique ID. B is an ID. C is another value.
I want to, for all records where field B = "AAA", Set B to "AAB", and set A on each record to the next unique ID that I get from somewhere else. Here's my SQL Stored Proc:
DECLARE MYCURSOR SCROLL Cursor FOR SELECT A, B FROM MyTable where B = 1 FOR UPDATE.
Loop:
FETCH NEXT INTO @vcA, @vcB FROM MYCURSOR. UPDATE MYTABLE SET A = @nctr, B = "AAB" WHERE CURRENT OF MYCURSOR SELECT @nctr = @nctr + 1 If @@FETCH_STATUS = 0 goto Loop:
I hit an error as soon as I get to the Update - "Cursorfetch: Number of variables declared in the INTO list must match that of selected columns"
Go Figure. I really need an answer on this ASAP! Help!
