Search Results

Search found 32223 results on 1289 pages for 'sql 2012'.

Page 202/1289 | < Previous Page | 198 199 200 201 202 203 204 205 206 207 208 209  | Next Page >

• SQL Server: How do I delimit this data?

  - by codingguy3000

  declare @mydata nvarchar(4000) set @mydata = '36|0, 77|5, 132|61' I have this data that I need to get into a table. So for Row1 columnA would be 36 and columnB would be 0. For Row2 columnA would be 77 and columnB would be 5 etc. What is the best way to do this? Thanks

  Read the article

• Efficient paging with large tables in sql 2008

  - by Kumar

  for tables with 1,000,000 rows and possibly many many more ! haven't done any benchmarking myself so wanted to get the experts opinion. Looked at some articles on row_number() but it seems to have performance implications What are the other choices/alternatives ?

  Read the article

• Getting Null value Of variable in sql server

  - by Neo

  Strange situation In a trigger i assign a column value to variable but gives exception while inserting into other table using that variable. e.g select @srNO=A.SrNo from A where id=123; insert into B (SRNO) values (@srNo) // here it gives null I run above select query in query pane it works fine but in trigger it gives me null any suggestions

  Read the article

• Better way to write this SQL

  - by AngryHacker

  I have the following table: create table ARDebitDetail(ID_ARDebitDetail int identity, ID_Hearing int, ID_AdvancedRatePlan int) I am trying to get the latest ID_AdvancedRatePlan based on a ID_Hearing. By latest I mean with the largest ID_ARDebitDetail. I have this query and it works fine. select ID_AdvancedRatePlan from ARDebitDetails where ID_Hearing = 135878 and ID_ARDebitDetail = ( select max(ID_ARDebitDetail) from ARDebitDetails where ID_AdvancedRatePlan > 0 and ID_Hearing = 135878 ) However, it just looks ugly and smells bad. Is there a way to rewrite it in a more concise manner?

  Read the article

• SQL Server Update with left join and group by having

  - by Marty Trenouth

  I'm making an update to our datbase and would like to update rows that do not have existing items in another table. I can join the tables together, but am having trouble grouping the table to get a count of the number of rows UPDATE dpt SET dpt.active = 0 FROM DEPARTMENT dpt LEFT JOIN DOCUMENTS doc on dpt.ID = doc.DepartmentID GROUP BY dpt.ID HAVING COUNT(doc.ID) = 0 What should I be doing?

  Read the article

• SQl rows to columns conversion

  - by Thihara

  Hi, I have a table ClassAttendance and I'm using MSSQL 2005 studentID--attendanceDate---------------------------------------status 1004--------2010-03-17--------------------------------------------------0 1005--------2010-03-17--------------------------------------------------1 1006--------2010-03-17--------------------------------------------------0 1007--------2010-03-17--------------------------------------------------0 1004--------2010-03-19--------------------------------------------------0 1005--------2010-03-19--------------------------------------------------1 1006--------2010-03-19--------------------------------------------------0 1007--------2010-03-19--------------------------------------------------0 1004--------2010-03-20--------------------------------------------------1 as you can see studentID is a foreign Key for a table called StudentData and attendedDate has an unknown number of rows. Can i get the output like below by using a query? I need the dates in one month to be columns and the value of the date columns will be values in the status column. The number of date records per studentID is the same its the number of dates in the attendanceDate filed that is unknown. studentID---------2010-03-17--------2010-03-19------2010-03-20 1004-----------------------------0----------------------0--------------------1 etc. This is for a creating a report so I need to do it in a query. Please help if you can.

  Read the article

• SQL Query To Get Multiple Max Values From Multiple Columns

  - by Sheridan

  I am trying to figure out how to pull multiple max values from multiple columns. Here is some sample data: DATE | A | B | C 4/4/2011 | 64.4 | 62.1 | 33.3 4/5/2011 | 34.6 | 33.5 | 32.3 4/6/2011 | 33.1 | 49.4 | 32.1 4/7/2011 | 55.2 | 32.8 | 33.5 4/8/2011 | 31.2 | 50.1 | 30.4 4/9/2011 | 31.7 | 31.1 | 30.4 I want the top 5 so: 4/4/2011 | 64.4 4/4/2011 | 62.1 4/7/2011 | 55.2 4/8/2011 | 50.1 4/6/2011 | 49.4 Thanks

  Read the article

• SQL 2005: Select top N, group by ID with joins

  - by Suzy Fresh

  I'm having real difficulty with a query involving 3 tables. I need to get the 3 newest users per department grouped by department names. The groups should be sorted by the users.dateadded so the department with the newest activity is first. The users can exist in multiple departments so Im using a lookup table that just contains the userID and deptID. My tables are as follows. Department - depID|name Users - userID|name|dateadded DepUsers - depID|userID The output I need would be Receiving John Doe - 4/23/2010 Bill Smith - 4/22/2010 Accounting Steve Jones - 4/22/2010 John Doe - 4/21/2010 Auditing Steve Jones - 4/21/2010 Bill Smith - 4/21/2010

  Read the article

• Retrieving multipel rows in MS SQL but distinct filteringen only on one

  - by Nicklas

  I have this: SELECT Product.ProductID, Product.Name, Product.GroupID, Product.GradeID, AVG(tblReview.Grade) AS Grade FROM Product left Join tblReview ON Product.GroupID = tblReview.GroupID WHERE (Product.CategoryID = @CategoryID) GROUP BY Product.ProductID, Product.Name, Product.GroupID, Product.GradeID I would like to return only the rows where Product.Name is unique. If I make a SELECT DISTINCT the ProductID is diffrent on every row so all the rows are unique. Thanks in andvance

  Read the article

• Max (SQL-Server)

  - by rah.deex

  Hello everyone. I have a table that looks like this: BARCODE | PRICE | STARTDATE 007023819815 | 159000 | 2008-11-17 00:00:00.000 007023819815 | 319000 | 2009-02-01 00:00:00.000 How can I select so I can get the result like this: BARCODE | PRICE | STARTDATE 007023819815 | 319000 | 2009-02-01 00:00:00.000 select by using max date. Thanks in advance.

  Read the article

• How to transform vertical table into horizontal table?

  - by avivo

  Hello, I have one table Person: Id Name 1 Person1 2 Person2 3 Person3 And I have its child table Profile: Id PersonId FieldName Value 1 1 Firstname Alex 2 1 Lastname Balmer 3 1 Email [email protected] 4 1 Phone +1 2 30004000 And I want to get data from these two tables in one row like this: Id Name Firstname Lastname Email Phone 1 Person1 Alex Balmer [email protected] +1 2 30004000 What is the most optimized query to get these vertical (key, value) values in one row like this? Now I have a problem that I done four joins of child table to parent table because I need to get these four fields. Some optimization is for sure possible. I would like to be able to modify this query in easy way when I add new field (key,value). What is the best way to do this? To create some StoreProcedure? I would like to have strongly types in my DB layer (C#) and using LINQ (when programming) so it means when I add some new Key, Value pair in Profile table I would like to do minimal modifications in DB and C# if possible. Actually I am trying to get some best practices in this case.

  Read the article

• How can i check sql server 'views' dependencies

  - by rs

  Is there a way that i can find out what base tables are being used by views using a custom query or stored procedure?

  Read the article

• SQL SERVER 2008 Dynamic query problem

  - by priyanka.sarkar

  I have a dynamic query which reads like this Alter PROCEDURE dbo.mySP -- Add the parameters for the stored procedure here ( @DBName varchar(50), @tblName varchar(50) ) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here declare @string as varchar(50) declare @string1 as varchar(50) set @string1 = '[' + @DBName + ']' + '.[dbo].' + '[' + @tblName + ']' set @string = 'select * from ' + @string1 exec @string END I am calling like this dbo.mySP 'dbtest1','tblTest' And I am experiencing an error "Msg 203, Level 16, State 2, Procedure mySP, Line 27 The name 'select * from [dbtest1].[dbo].[tblTest]' is not a valid identifier." What is wrong? and How to overcome? Thanks in advance

  Read the article

• SQL Server combining 2 rows into 1 from the same table

  - by Maton

  Hi, I have a table with an JobID (PK), EmployeeID (FK), StartDate, EndDate containing data such as: 1, 10, '01-Jan-2010 08:00:00', '01-Jan-2010 08:30:00' 2, 10, '01-Jan-2010 08:50:00', '01-Jan-2010 09:05:00' 3, 10, '02-Feb-2010 10:00:00', '02-Feb-2010 10:30:00' I want to return a record for each EndDate for a Job and then the same employees StartDate for his next immediate job (by date time). So from the data above the result would be Result 1: 10, 01-Jan-2010 08:30:00, 01-Jan-2010 08:50:00 Result 2: 10, 01-Jan-2010 09:05:00, 02-Feb-2010 10:00:00 Greatly appreciate any help!

  Read the article

• SQL Server 2008 - Keyword search using table Join

  - by Aaron Wagner

  Ok, I created a Stored Procedure that, among other things, is searching 5 columns for a particular keyword. To accomplish this, I have the keywords parameter being split out by a function and returned as a table. Then I do a Left Join on that table, using a LIKE constraint. So, I had this working beautifully, and then all of the sudden it stops working. Now it is returning every row, instead of just the rows it needs. The other caveat, is that if the keyword parameter is empty, it should ignore it. Given what's below, is there A) a glaring mistake, or B) a more efficient way to approach this? Here is what I have currently: ALTER PROCEDURE [dbo].[usp_getOppsPaged] @startRowIndex int, @maximumRows int, @city varchar(100) = NULL, @state char(2) = NULL, @zip varchar(10) = NULL, @classification varchar(15) = NULL, @startDateMin date = NULL, @startDateMax date = NULL, @endDateMin date = NULL, @endDateMax date = NULL, @keywords varchar(400) = NULL AS BEGIN SET NOCOUNT ON; ;WITH Results_CTE AS ( SELECT opportunities.*, organizations.*, departments.dept_name, departments.dept_address, departments.dept_building_name, departments.dept_suite_num, departments.dept_city, departments.dept_state, departments.dept_zip, departments.dept_international_address, departments.dept_phone, departments.dept_website, departments.dept_gen_list, ROW_NUMBER() OVER (ORDER BY opp_id) AS RowNum FROM opportunities JOIN departments ON opportunities.dept_id = departments.dept_id JOIN organizations ON departments.org_id=organizations.org_id LEFT JOIN Split(',',@keywords) AS kw ON (title LIKE '%'+kw.s+'%' OR [description] LIKE '%'+kw.s+'%' OR tasks LIKE '%'+kw.s+'%' OR requirements LIKE '%'+kw.s+'%' OR comments LIKE '%'+kw.s+'%') WHERE ( (@city IS NOT NULL AND (city LIKE '%'+@city+'%' OR dept_city LIKE '%'+@city+'%' OR org_city LIKE '%'+@city+'%')) OR (@state IS NOT NULL AND ([state] = @state OR dept_state = @state OR org_state = @state)) OR (@zip IS NOT NULL AND (zip = @zip OR dept_zip = @zip OR org_zip = @zip)) OR (@classification IS NOT NULL AND (classification LIKE '%'+@classification+'%')) OR ((@startDateMin IS NOT NULL AND @startDateMax IS NOT NULL) AND ([start_date] BETWEEN @startDateMin AND @startDateMax)) OR ((@endDateMin IS NOT NULL AND @endDateMax IS NOT NULL) AND ([end_date] BETWEEN @endDateMin AND @endDateMax)) OR ( (@city IS NULL AND @state IS NULL AND @zip IS NULL AND @classification IS NULL AND @startDateMin IS NULL AND @startDateMax IS NULL AND @endDateMin IS NULL AND @endDateMin IS NULL) ) ) ) SELECT * FROM Results_CTE WHERE RowNum >= @startRowIndex AND RowNum < @startRowIndex + @maximumRows; END

  Read the article

• Help with MySQL query

  - by Michael S.

  I have a table that contains the next columns: ip(varchar 255), index(bigint 20), time(timestamp) each time something is inserted there, the time column gets current timestamp. I want to run a query that returns all the rows that have been added in the last 24 hours. This is what I try to execute: SELECT ip, index FROM users WHERE ip = 'some ip' AND TIMESTAMPDIFF(HOURS,time,NOW()) < 24 And it doesn't work. Can someone help me out? Thanks :)

  Read the article

• SSIS - Skip Missing Files

  - by Greg

  I have a SSIS 2008 package that calls about 10 other SSIS packages (legacy issues, don't ask). Each of those child packages loads a specific file into a table. But sometimes one or more of these input files will be missing. How can I let a child package fail (because a file is missing) but let the rest of the parent package keep on running? I've tried increasing the maximum error count on the parent package, the tasks in the parent package that call each child, and in the child package itself. None of that seemed to make any difference. I still get this error when I run it with a file missing: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. Edit: failpackageonfailure and faulparentonfailure are already all set to false everywhere.

  Read the article

• What is the Datatype for 10:00 AM PST in SQL Server

  - by Rita

  Hi I have a table that need to store Timeslot such as 10:00 AM PST, 10:15 AM PST.... etc at a constant interval of 15 Min. I am wondering if there is any Time datatype available for this kind of data? Thanks

  Read the article

• SQL Query Help Part 2 - Add filter to joined tables and get max value from filter

  - by Seth

  I asked this question on SO. However, I wish to extend it further. I would like to find the max value of the 'Reading' column only where the 'state' is of value 'XX' for example. So if I join the two tables, how do I get the row with max(Reading) value from the result set. Eg. SELECT s.*, g1.* FROM Schools AS s JOIN Grades AS g1 ON g1.id_schools = s.id WHERE s.state = 'SA' // how do I get row with max(Reading) column from this result set The table details are: Table1 = Schools Columns: id(PK), state(nvchar(100)), schoolname Table2 = Grades Columns: id(PK), id_schools(FK), Year, Reading, Writing...

  Read the article

• UNIQUE Constraints in SQL (SQL Server)

  - by rockbala

  Why are UNIQUE Constraints needed in database ? Can you provide any examples ? Primary Key is UNIQUE by default... Understandable as they are referred in other tables as Foreign keys... relation is needed to connect them for rdbms platform... but why would one refer to other columns as UNIQUE, what is benefit of doing so ?)

  Read the article

• Using PIVOT in SQL Server

  - by Nev_Rahd

  How can i convert below using PIVOT as Thanks

  Read the article

• SQL Server 2005: When copy table structure to other database "CONSTRAINT" keywords lost

  - by StreamT

  Snippet of original table: CREATE TABLE [dbo].[Batch]( [CustomerDepositMade] [money] NOT NULL CONSTRAINT [DF_Batch_CustomerDepositMade] DEFAULT (0) Snippet of copied table: CREATE TABLE [dbo].[Batch]( [CustomerDepositMade] [money] NOT NULL, Code for copy database: Server server = new Server(SourceSQLServer); Database database = server.Databases[SourceDatabase]; Transfer transfer = new Transfer(database); transfer.CopyAllObjects = true; transfer.CopySchema = true; transfer.CopyData = false; transfer.DropDestinationObjectsFirst = true; transfer.DestinationServer = DestinationSQLServer; transfer.CreateTargetDatabase = true; Database ddatabase = new Database(server, DestinationDatabase); ddatabase.Create(); transfer.DestinationDatabase = DestinationDatabase; transfer.Options.IncludeIfNotExists = true; transfer.TransferData();

  Read the article

• Left Join Returning Extra Rows T-SQL?

  - by davemackey

  I have the following query: select * from ACADEMIC a left join RESIDENCY r on a.PEOPLE_CODE_ID = r.PEOPLE_CODE_ID where a.ACADEMIC_TERM='Fall' and r.ACADEMIC_TERM='Fall' and a.ACADEMIC_SESSION='' and a.ACADEMIC_YEAR = (Select Year(GetDate())) and r.ACADEMIC_YEAR = (Select Year(GetDate())) and (CLASS_LEVEL LIKE 'FR%' OR a.CLASS_LEVEL LIKE 'SO' OR a.CLASS_LEVEL LIKE 'JR' OR a.CLASS_LEVEL LIKE 'SR%') and r.RESIDENT_COMMUTER='R' For each person in the database it returns two rows with identical information. Yet, when I do the same query without the left join: select * from ACADEMIC a where a.ACADEMIC_TERM='Fall' and a.ACADEMIC_SESSION='' and a.ACADEMIC_YEAR = (Select Year(GetDate())) and (CLASS_LEVEL LIKE 'FR%' OR a.CLASS_LEVEL LIKE 'SO' OR a.CLASS_LEVEL LIKE 'JR' OR a.CLASS_LEVEL LIKE 'SR%') ORDER BY PEOPLE_ID It returns only one row for each person. I'm doing a left join - why is it adding an extra row? Shouldn't it only do that if I add a right join?

  Read the article

• Remove and Replace multiple chars ( spaces, hyphen, brackets, period) from string in sql

  - by Muhammad Kashif Nadeem

  +39 235 6595750 19874624611 +44 (0)181 446 5697 +431 6078115-2730 +1 617 358 5128 +48.40.23755432 +44 1691 872 410 07825 893217 0138 988 1649 (415) 706 2001 00 44 (0) 20 7660 4650 (765) 959-1504 07731 508 486 please reply by email dont have one +447769146971 Please see the above given phone numbers. I need to replace all spaces, hyphen, period, brackets and leading 0 etc from these numbers. I need this format +447469186974 If number has leading plus sign then don't replace it otherwise I have to concatenate + sign with it. E.G +39 235 6595750 in this number I just need to remove spaces. +44 (0)181 446 5697 in this i need to removes spaces and brackets and 0 in between brackets i.e (0) 07825 893217 in this I need to replace leading 0 with + sign and remove spaces (415) 706 2001 in this replace '(' with + sign and remove ')' and spaces. 'please reply by email' This is the entry in phone number field and I just need to ignore this. +48.40.23755432 Remove period in phone number (765) 959-1504 Remove brackets and spaces and hyphen and add + sign in front of number. 7798724250 just need to add + sign in front of number 00 44 (0) 20 7660-4650 Need to remove leading 0 I.E '00' remove spaces and brackets and 0 in between brackets and hyphen and add + sign in front of number Only leading '0' will be replaced not anyother occourence of '0' The desired result is +447769146971 Should I use nested REPLACE, CHARINDES, PATINDES for each char I want to replace? Thanks.

  Read the article

• sql query --need some suggestions

  - by benjamin button

  I have a table with list of cycle codes.CYCLE_DEFINITION. each and every cycle_code has 12 months entries in another table(PM1_CYCLE_STATE). Each and every month has a cycle_start_date and a cycle_close_date. i will check with a particular date(lets say sysdate) and check what is the current month of every cycle.additionally i will also get the list of future 3 more months of that particular cycle. the query i have written is as below: SELECT cycd,cm,sd,ed,ld FROM (SELECT pcs.cycle_code CYCD,LTRIM(pcs.cycle_month,'0')+0 CM, pcs.cycle_start_date SD,pcs.cycle_close_date ED,ld.logical_date LD FROM pm1_cycle_state pcs,logical_date ld WHERE ld.logical_date BETWEEN pcs.cycle_start_date AND pcs.cycle_close_date and ld.logical_date_type='B') UNION SELECT cycd,cm,sd,ed,ld FROM (SELECT pcs.cycle_code CYCD,DECODE(LTRIM(pcs.cycle_month,'0')+1,13,1,14,2,15,3,LTRIM(pcs.cycle_month,'0')+1) CM ,pcs.cycle_start_date SD,pcs.cycle_close_date ED,ld.logical_date LD FROM pm1_cycle_state pcs,logical_date ld WHERE ld.logical_date BETWEEN pcs.cycle_start_date AND pcs.cycle_close_date and ld.logical_date_type='B') UNION SELECT cycd,cm,sd,ed,ld FROM (SELECT pcs.cycle_code CYCD,DECODE(LTRIM(pcs.cycle_month,'0')+2,13,1,14,2,15,3,LTRIM(pcs.cycle_month,'0')+2) CM ,pcs.cycle_start_date SD,pcs.cycle_close_date ED,ld.logical_date LD FROM pm1_cycle_state pcs,logical_date ld WHERE ld.logical_date BETWEEN pcs.cycle_start_date AND pcs.cycle_close_date and ld.logical_date_type='B') UNION SELECT cycd,cm,sd,ed,ld FROM (SELECT pcs.cycle_code CYCD,DECODE(LTRIM(pcs.cycle_month,'0')+3,13,1,14,2,15,3,LTRIM(pcs.cycle_month,'0')+3) CM ,pcs.cycle_start_date SD,pcs.cycle_close_date ED,ld.logical_date LD FROM pm1_cycle_state pcs,logical_date ld WHERE ld.logical_date BETWEEN pcs.cycle_start_date AND pcs.cycle_close_date and ld.logical_date_type='B') This query is running perfectly fine. This will result in all the cycle_codes with exactly 4 rows for current month and future 3 months. Now the requirement is if any of the month is missing.how could i show it? for eg: the output of the above query is cycd cm 102 1 102 10 102 11 102 12 103 1 103 10 103 11 103 12 104 1 104 10 104 11 104 12 Now lets say the row with cycd=104 and cm=11 is not present in the table,then the above query will not get the row 104 11. I want to display only those rows. how could i do it?

  Read the article

< Previous Page | 198 199 200 201 202 203 204 205 206 207 208 209  | Next Page >