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 ?
I have two fields in one of my database table. They are date fields, one for the start and the other for the end of the "problem solving". What I want to do is to show the concrete elapsed time between the two.
My first problem is that I'm not able to show hour AND minutes. I wrote this for instance: SELECT DATEDIFF(hour, fld_date_debut, fld_date_fin) As elapsed_time I tried HH:mm instead of hour, but it is not working.
My second problem is that I can have more than one start and end hour for the same "problem solving". In the database it's like:
start hour: 10:00 End hour: 11:00 Number of the problem: 1 start hour: 13:00 End hour: 16:00 Number of the problem: 1
So I would like to add these to my elapsed time. I want (11:00 - 10:00) + (16:00 - 13:00)...but how can i do this in my SQL query ?
How can I do a lookup which doens't directly link two columns but uses another statement?
I tried in advanced with:
Code Snippetselect * from (select * from [dbo].[Employees]) as refTable where [refTable].[EM_ID] = ? and [refTable].[EM_From] <= ? and ([refTable].[EM_Until] > ? or [refTable].[EM_Until] IS NULL)
and adding 2 parameters.
Error 1 Validation error. Fill Planning: Lookup [2376]: An input column with the lineage ID 1760, referenced in the ParameterMap custom property with the parameter on position number 1, cannot be found in the input columns collection. Package.dtsx 0 0 So I guess that's not the way to go. Any other way to tackle this?
Can someone put me on the right track?I have a .bak file from an MSSQL 7.0 database. I want to restore thedatabase in SQL 2000 or 2005, but the restore fails with the error:"An exception occurred while executing a Transact-SQL statement or batch.(Microsoft.SqlServer.Express.ConnectionInfo)"and"I/O error (bad page ID) detected during read at offset 0x000000001be000 infile 'C:Program FilesMicrosoft SQL ServerMSSQLDataCLI_JP.mdf'. (.NetSqlClient Data Provider)"So it doesn't look good. I don't really know the meaning of the error andall I have is the .bak file. Is there a way to somehow convert the file, orread it?--Alvin
I have a bunch of contacts that I've scored how well their names match to other contacts in the same business. I can programmatically figure out how to parse the results, but would like to know how to do this via SQL. My problem is for Business_fk 968976 I have 7 contacts. In the end I should have 4 contacts based on name match. For the business key listed Gerardo Lopez is in the ContactScore table twice for Contact keys 7355719 and 57028145. I then have two rows like so:
Each reference each other, and 2 is a good case, a more difficult case would have key 1 listed 10 times showing a ContactMatch_fk of 2 - 11, and then Contact_fk 2 listed 10 times with a ContactMatch_fk of 1, 3-11.I know 57028145 maps to 7355719 from the first row in the ContactScore table, so when Contact_fk of 7355719 comes up I should be able to skip it and not process that match. Hopefully that makes sense. Anyway here is the test data:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ContactScore]') AND type in (N'U')) DROP TABLE [dbo].[ContactScore]; GO CREATE TABLE [dbo].[ContactScore] ( [ContactScore_pk]INT NOT NULL, [Contact_fk]INT NOT NULL,
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.
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!
I have a table named conversion. In this table there is not a field for "first name", but only a field for "last name" and "Contact Name". The "Contact Name" holds the full name (First and Last), and the "Last Name" holds the last name.
In theory I was thinking that I could take the "Last Name" field and subtract the amount of characters + 1 additional (to account for space in between Contact Name Field - FirstName LastName) and then I would have the first name?
I can find the amount of characters in a field name by using this:
SELECT LEN(lastname_column) AS 'Length', lastname_column FROM conv
Here's a fun little puzzle that I thought up based on some rather gnarly problems I faced today. It might brighten up your morning. See if you can figure out what it does and how it does it.
How does it work? When will it fail as written (this example does not fail)? Why will it fail? How can you change it to increae its capacity?-- ptp 20080117 SQL puzzle
DECLARE @iINT , @cVARCHAR(360)
SET @i = 90 SET @c = ''
WHILE 64 < @i BEGIN SET @c = Char(@i) + Space(@i - 64) + @c SET @i = @i - 1 END
1. A duplicate record is defined as a record where PPN_I and CPN_I have identical ID's. My new DB doesn't allow duplicate records. In order to 'strip' out the unwanted records - I need to enforce a couple of data conditions:
I delete both records for this case only - if qty required =1 and a duplicate record has qty required = 0 then both get deleted. In the above example - records 4 & 5 both get deleted. In all other duplicate record scenarios - I want to keep the record with the highest ECN_02 number. So, records 2 & 3 get deleted because they are duplicate (same PPN_I and CPN_I id's) and their ECN_02 is less than the highest value (4506). If no duplicate record exists - no action taken (records 6,7,8).
So, records 4 & 5 - both get deleted. Records 1,2 & 3 become one record (highest ECN_02) records 1 stays - records 2 & 3 deleted.
8 records become 4.
Any thoughts? How do I 'loop thru the table and identify duplicate records and then enforce some kind of rule?
I'm using the Transact-SQL Debugger for the first time here.I go to Object Browser, find my stored procedure, right click andchoose "Debug".The "Debug Procedure" window comes up, and I click on the Executebutton.The Debug environment comes up and automatically executes myprocedure. It doesn't allow me to single step through the code.What am I missing here?All of the "step" buttons, "restart", and "stop debugging" buttonsare dimmed out.Thanks.Ken
Updating the Table . I need to update Two Column .. DEPT and Product .
For a Same EmployeeName DEPT and Product Name should Repeat till Next Value in encountered.
Example For EmployeeName 'A' Dept Value 100 should Repeat till 20 comes, based on Date, because 100 comes before 20 and Project P1 should repeat till P2 comes.
Below is the Sample Source Table.
CREATE TABLE #MYTEMP3 (NAME VARCHAR(50), DEPT INT ,PROJECT VARCHAr(100), ORDERDT DATE) INSERT INTO #MYTEMP3 SELECT 'A',100,'P1', '2015-01-01' UNION ALL SELECT 'A','','', '2015-01-02' UNION ALL SELECT 'A','','', '2015-01-03' UNION ALL
[Code] ....
Looking for UPDATE Query . I do not want to use Common table expression to achieve this .
