Search Results

Search found 27337 results on 1094 pages for 't sql'.

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

Page 351/1094 | < Previous Page | 347 348 349 350 351 352 353 354 355 356 357 358  | Next Page >

  • find a duplicate series in SQL

    - by SomeMiscGuy
    I have a table with 3 columns containing a variable number of records based off of the first column which is a foreign key. I am trying to determine if I can detect when there is a duplicate across multiple rows for an entire series declare @finddupseries table ( portid int, asset_id int, allocation float ) ; INSERT INTO @finddupseries SELECT 250, 6, 0.05 UNION ALL SELECT 250, 66, 0.8 UNION ALL SELECT 250, 2, 0.105 UNION ALL SELECT 250, 4, 0.0225 UNION ALL SELECT 250, 5, 0.0225 UNION ALL SELECT 251, 13, 0.6 UNION ALL SELECT 251, 2, 0.3 UNION ALL SELECT 251, 5, 0.1 UNION ALL SELECT 252, 13, 0.8 UNION ALL SELECT 252, 2, 0.15 UNION ALL SELECT 252, 5, 0.05 UNION ALL SELECT 253, 13, 0.4 UNION ALL SELECT 253, 2, 0.45 UNION ALL SELECT 253, 5, 0.15 UNION ALL SELECT 254, 6, 0.05 UNION ALL SELECT 254, 66, 0.8 UNION ALL SELECT 254, 2, 0.105 UNION ALL SELECT 254, 4, 0.0225 UNION ALL SELECT 254, 5, 0.0225 select * from @finddupseries The records for portid 250 and 254 match. Is there any way I can write a query to detect this? edit: yes, the entire series must match. Also, if there was a way to determine which one it DID match would be helpful as the actual table has around 10k records. thanks!

    Read the article

  • One-to-many relationship related to many tables

    - by Andrey
    I have a scenario where: there are two (or more) tables that represent independent items. lets say Users and Companies Both of these tables need addresses stored. Each one can have one or more address In a normal 1 to many scenario Addresses table woudl just have a UserId or a CompanyId creating a normal 1 to many relationship. In this case i have a few approaches i can think of the Addresses table could have both a UserId and a CompanyId and only one would be used for each record. 2 keys could be used ObjectId and ObjectType So Object id would have a UserId or a CompanyId, and ObjectType woudl be User or Company Create an ObjectTable and add ObjectId to Users and Companies. Addresses would then have an OjbectId I do not really like any of these solutions. i am wondering what is the best approach here. On another note i will most likely user linqtosql for my data access layer.

    Read the article

  • Dynamic SQL and Funtions

    - by Unlimited071
    Hi all, is there any way of accomplishing something like the following: CREATE FUNCTION GetQtyFromID ( @oricod varchar(15), @ccocod varchar(15), @ocmnum int, @oinnum int, @acmnum int, @acttip char(2), @unisim varchar(15) ) AS BEGIN DECLARE @Result decimal(18,8) DECLARE @SQLString nvarchar(max); DECLARE @ParmDefinition nvarchar(max); --I need to execute a query stored in a cell which returns the calculated qty. --i.e of AcuQry: select @cant = sum(smt) from table where oricod = @oricod and ... SELECT @SQLString = AcuQry FROM OinActUni WHERE (OriCod = @oricod) AND (ActTipCod = @acttip) AND (UniSim = @unisim) AND (AcuEst > 0) SET @ParmDefinition = N' @oricod varchar(15), @ccocod varchar(15), @ocmnum int, @oinnum int, @acmnum int, @cant decimal(18,8) output'; EXECUTE sp_executesql @SQLString, @ParmDefinition, @oricod = @oricod, @ccocod = @ccocod, @ocmnum = @ocmnum, @oinnum = @oinnum, @acmnum = @acmnum, @cant = @result OUTPUT; RETURN @Result END The problem with this approach is that it is prohibited to execute sp_excutesql in a function... What I need is to do something like: select id, getQtyFromID(id) as qty from table The main idea is to execute a query stored in a table cell, this is because the qty of something depends on it's unit. the unit can be days or it can be metric tons, so there is no relation between the units, therefore the need of a specific query for each unit.

    Read the article

  • Data mixing SQL Server

    - by Pythonizo
    I have three tables and a range of two dates: Services ServicesClients ServicesClientsDone @StartDate @EndDate Services: ID | Name 1 | Supervisor 2 | Monitor 3 | Manufacturer ServicesClients: IDServiceClient | IDClient | IDService 1 | 1 | 1 2 | 1 | 2 3 | 2 | 2 4 | 2 | 3 ServicesClientsDone: IDServiceClient | Period 1 | 201208 3 | 201210 Period = YYYYMM I need to insert into ServicesClientsDone the months range from @StartDate up @EndDate. I have also a temporary table (#Periods) with the following list: Period 201208 201209 201210 The query I need is to give me back the following list: IDServiceClient | Period 1 | 201209 1 | 201210 2 | 201208 2 | 201209 2 | 201210 3 | 201208 3 | 201209 4 | 201208 4 | 201209 4 | 201210 Which are client services but the ranks of the temporary table, not those who are already inserted This is what i have: Table periods: DECLARE @i int DECLARE @mm int DECLARE @yyyy int, DECLARE @StartDate datetime DECLARE @EndDate datetime set @EndDate = (SELECT GETDATE()) set @StartDate = (SELECT DATEADD(MONTH, -3,GETDATE())) CREATE TABLE #Periods (Period int) set @i = 0 WHILE @i <= DATEDIFF(MONTH, @StartDate , @EndDate ) BEGIN SET @mm= DATEPART(MONTH, DATEADD(MONTH, @i, @FechaInicio)) SET @yyyy= DATEPART(YEAR, DATEADD(MONTH, @i, @FechaInicio)) INSERT INTO #Periods (Period) VALUES (CAST(@yyyy as varchar(4)) + RIGHT('00'+CONVERT(varchar(6), @mm), 2)) SET @i = @i + 1; END Relation between ServicesClients and Services: SELECT s.Name, sc.IDClient FROM Services JOIN ServicesClients AS sc ON sc.IDService = s.ID Services already done and when: SELECT s.Name, scd.Period FROM Services JOIN ServicesClients AS sc ON sc.IDService = s.ID JOIN ServicesClientsDone AS scd ON scd.IDServiceClient = sc.IDServiceClient

    Read the article

  • Value is zero after filter SQL in C#

    - by Chuki2
    I`m new in C#.. I have write function to filter department. And this function will return idDepartment. New problem is, department keep value "System.Windows.Forms.Label, Text : ADMIN ", that`s why i got zero. So how can i take "ADMIN" only and keep to department? Update : public partial class frmEditStaff : Form { private string connString; private string userId, department; //Department parameter coming from here private string conString = "Datasource"; public frmEditStaff(string strUserID, string strPosition) { InitializeComponent(); //Pass value from frmListStaff to userID text box tbStaffId.Text = strUserID.ToString(); userId = strUserID.ToString(); department = strPosition.ToString(); } This code below is working, don`t have any problem. public int lookUpDepart() { int idDepart=0; using (SqlConnection openCon = new SqlConnection(conString)) { string lookUpDepartmenId = "SELECT idDepartment FROM tbl_department WHERE department = '" + department + "';"; openCon.Open(); using (SqlCommand querylookUpDepartmenId = new SqlCommand(lookUpDepartmenId, openCon)) { SqlDataReader read = querylookUpDepartmenId.ExecuteReader(); while (read.Read()) { idDepart = int.Parse(read[0].ToString()); break; } } openCon.Close(); return idDepart; } } Thanks for help. Happy nice day!

    Read the article

  • help with sql-server query - dates

    - by Gold
    hi i have this table: id|date 1 | 10/11/2009 2 | 13/11/2009 1 | 20/12/2009 3 | 21/12/2009 1 | 30/12/2009 if i stand on the last record (id=1) and i need to see the last date where id=1 is appear -- will show me: 1 | 20/12/2009 what query will do it ? thank's in advance

    Read the article

  • SQL insert default value

    - by Stan
    Say if I have a table like CREATE TABLE [Message] ( [MessageIdx] [int] IDENTITY (1, 1) NOT NULL , [Message] [varchar] (1024) COLLATE Latin1_General_CI_AS NOT NULL , [ValidUntil] [datetime] NULL , CONSTRAINT [PK_Message] PRIMARY KEY CLUSTERED ( [MessageIdx] ) WITH FILLFACTOR = 90 ON [PRIMARY] ) ON [PRIMARY] GO I am trying to insert value without specify column names explicitly. Below statement causes error. How can I do that? Thanks. set identity_insert caconfig..fxmessage on; insert into message values (DEFAULT,'blah',DEFAULT); set identity_insert caconfig..fxmessage off;

    Read the article

  • FreeText COUNT query on multiple tables is super slow

    - by Eric P
    I have two tables: **Product** ID Name SKU **Brand** ID Name Product table has about 120K records Brand table has 30K records I need to find count of all the products with name and brand matching a specific keyword. I use freetext 'contains' like this: SELECT count(*) FROM Product inner join Brand on Product.BrandID = Brand.ID WHERE (contains(Product.Name, 'pants') or contains(Brand.Name, 'pants')) This query takes about 17 secs. I rebuilt the FreeText index before running this query. If I only check for Product.Name. They query is less then 1 sec. Same, if I only check the Brand.Name. The issue occurs if I use OR condition. If I switch query to use LIKE: SELECT count(*) FROM Product inner join Brand on Product.BrandID = Brand.ID WHERE Product.Name LIKE '%pants%' or Brand.Name LIKE '%pants%' It takes 1 secs. I read on MSDN that: http://msdn.microsoft.com/en-us/library/ms187787.aspx To search on multiple tables, use a joined table in your FROM clause to search on a result set that is the product of two or more tables. So I added an INNER JOINED table to FROM: SELECT count(*) FROM (select Product.Name ProductName, Product.SKU ProductSKU, Brand.Name as BrandName FROM Product inner join Brand on product.BrandID = Brand.ID) as TempTable WHERE contains(TempTable.ProductName, 'pants') or contains(TempTable.BrandName, 'pants') This results in error: Cannot use a CONTAINS or FREETEXT predicate on column 'ProductName' because it is not full-text indexed. So the question is - why OR condition could be causing such as slow query?

    Read the article

  • Java: Do something on event in SQL Database?

    - by wretrOvian
    Hello I'm building an application with distributed parts. Meaning, while one part (writer) maybe inserting, updating information to a database, the other part (reader) is reading off and acting on that information. Now, i wish to trigger an action event in the reader and reload information from the DB whenever i insert something from the writer. Is there a simple way about this? Would this be a good idea? : // READER while(true) { connect(); // reload info from DB executeQuery("select * from foo"); disconnect(); }

    Read the article

  • SQL and/or LINQ query for determining daily increases in viewers

    - by Gio
    We're montioring usage of a certain resources by monitoring users logins (We can see user logins growing daily). After filtering out repeat inter day logins for users, we'd like to track the # of users using the service each day, and then using that info to determine overall incremental gains for each calendar day. Our table is pretty simple: class ServiceLogin { String login; DateTime loginTime; }

    Read the article

  • Need a set based solution to group rows

    - by KM
    I need to group a set of rows based on the Category column, and also limit the combined rows based on the SUM(Number) column to be less than or equal to the @Limit value. For each distinct Category column I need to identify "buckets" that are <=@limit. If the SUM(Number) of all the rows for a Category column are <=@Limit then there will be only 1 bucket for that Category value (like 'CCCC' in the sample data). However if the SUM(Number)@limit, then there will be multiple bucket rows for that Category value (like 'AAAA' in the sample data), and each bucket must be <=@Limit. There can be as many buckets as necessary. Also, look at Category value 'DDDD', its one row is greater than @Limit all by itself, and gets split into two rows in the result set. Given this simplified data: DECLARE @Detail table (DetailID int primary key, Category char(4), Number int) SET NOCOUNT ON INSERT @Detail VALUES ( 1, 'AAAA',100) INSERT @Detail VALUES ( 2, 'AAAA', 50) INSERT @Detail VALUES ( 3, 'AAAA',300) INSERT @Detail VALUES ( 4, 'AAAA',200) INSERT @Detail VALUES ( 5, 'BBBB',500) INSERT @Detail VALUES ( 6, 'CCCC',200) INSERT @Detail VALUES ( 7, 'CCCC',100) INSERT @Detail VALUES ( 8, 'CCCC', 50) INSERT @Detail VALUES ( 9, 'DDDD',800) INSERT @Detail VALUES (10, 'EEEE',100) SET NOCOUNT OFF DECLARE @Limit int SET @Limit=500 I need one of these result set: DetailID Bucket | DetailID Category Bucket -------- ------ | -------- -------- ------ 1 1 | 1 'AAAA' 1 2 1 | 2 'AAAA' 1 3 1 | 3 'AAAA' 1 4 2 | 4 'AAAA' 2 5 3 OR 5 'BBBB' 1 6 4 | 6 'CCCC' 1 7 4 | 7 'CCCC' 1 8 4 | 8 'CCCC' 1 9 5 | 9 'DDDD' 1 9 6 | 9 'DDDD' 2 10 7 | 10 'EEEE' 1

    Read the article

  • Is this SQL is valid?

    - by Beck
    UPDATE polls_options SET `votes`=`votes`+1, `percent`=ROUND((`votes`+1) / (SELECT voters FROM polls WHERE poll_id=? LIMIT 1) * 100,1) WHERE option_id=? AND poll_id=? Don't have table data yet, to test it properly. :) And by the way, in what type % integers should be stored in database? Thanks for the help!

    Read the article

  • SQL - Getting Most Recent Date From Multiple Columns

    - by ScottSEA
    Assume a rowset containing the following EntryID Name DateModified DateDeleted ----------------------------------------------- 1 Name1 1/2/2003 NULL 2 Name1 1/3/2005 1/5/2008 3 Name1 1/3/2006 NULL 4 Name1 NULL NULL 5 Name1 3/5/2008 NULL I need to return the largest (i.e. most recent) non-null date from DateModified and DateDeleted, in this case 3/5/2008.

    Read the article

  • NUmber of rows in Oracle SQL Select?

    - by twelshesgi
    I need to nkw how many records were returned in a select in oracle. Currently, I do two queries: SELECT COUNT(ITEM_ID) FROM MY_ITEMS; SELECT * FROM MY_ITEMS; I need to know the COUNT but I hate doing two queries. Is there a way to do: SELECT * FROM MY_ITEMS and then find out how many records are in there?

    Read the article

  • Dynamically set the result of a TSQL query using CASE WHEN

    - by Name.IsNullOrEmpty
    SELECT MyTable.Name,(SELECT CASE WHEN ISNULL(SUM(TotalDays), 0) <= 0 THEN 0 ELSE SUM(TotalDays) END AS Total FROM Application AS Applications WHERE (ID = MyTable.id)) - MIN(Assignments) AS Excesses FROM MyTable The above TSQL statement is a subquery in a main query. When i run it, if TotalDays is NULL or <=0, then Total is set to 0 (zero). What i would like to do here is to set the result of the whole query(Excesses) to 0. I want (Excesses) which is the result of Total - Min(Assignments) to be set to 0 if its NULL or <=0. I want the CASE WHEN to apply to the whole query but am struggling to get it right.

    Read the article

  • SQL: How to use a column that was just added

    - by sbenderli
    I am trying to add 2 columns and then I would like to set some values to them but I get a compile-error saying the column does not exist. I am using the following script: IF NOT EXISTS (select column_name from INFORMATION_SCHEMA.columns where table_name = 'SYSTM_FRM' and column_name = 'SF_Ip_TXT') ALTER TABLE SYSTM_FRM add SF_Ip_TXT NVARCHAR(20) IF NOT EXISTS (select column_name from INFORMATION_SCHEMA.columns where table_name = 'SYSTM_FRM' and column_name = 'SF_Port_NUM') ALTER TABLE SYSTM_FRM add SF_Port_NUM int Update dbo.SYSTM_FRM SET dbo.SYSTM_FRM.SF_Ip_TXT = dbo.FRM.FRM_Ip_TXT, dbo.SYSTM_FRM.SF_Port_NUM = dbo.FRM.FRM_Port_NUM FROM dbo.FRM INNER JOIN dbo.SYSTM_FRM ON dbo.FRM.FRM_RCRD_NUM = dbo.SYSTM_FRM.SF_FrameRecord_NUM Is there any way to use a column that I am adding in the same script?

    Read the article

  • Simple check for SELECT query empty result

    - by den-javamaniac
    Hi. Can anyone point out how to check if a select query returns non empty result set? For example I have next query: SELECT * FROM service s WHERE s.service_id = ?; Should I do something like next: ISNULL(SELECT * FROM service s WHERE s.service_id = ?) to test if result set is not empty?

    Read the article

  • alternative to lag SQL command

    - by mahen
    I have a table which has a table like this. Month-----Book_Type-----sold_in_Dollars Jan----------A------------ 100 Jan----------B------------ 120 Feb----------A------------ 50 Mar----------A------------ 60 Mar----------B------------ 30 and so on I have to calculate the expected sales for each month and book type based on the last 2 months sales. So for March and type A it would be (100+50)/2 = 75 For March and type B it is 120/1 since no data for Feb is there. I was trying to use the lag function but it wouldn't work since there is data missing in a few rows. Any ideas on this?

    Read the article

  • Problem in removing hardcoded values using temp table...

    - by Shahsra
    Hi All, First of all Wish u all Happy New Year. I have a problem in writing query. While executing my query I am getting an error. Query: select case when S.R1 = '6' then 5 when S.R1 = '7' then 6 when S.R1 = '8' then 7 when S.R1 = '9' then 8 when S.R1 ='10' then 9 else S.R1 end as Q FROM [HelpService].[dbo].[help] s ----------------------------------------------- SELECT [Source], [Score] INTO #Temp_Q FROM [HelpDesk].[dbo].[Survey] WHERE [data_Source Name] = 'Text Data' ----------------------------------------------- select CONVERT(REAL, a.[Dell Score]) as Q FROM [HelpService].[dbo].[help] s LEFT OUTER JOIN #CE_Temp_Q a on s.[R1] = a.[Source] ERROR Msg 8114, Level 16, State 5, Line 1 Error converting data type varchar to real. What I am asked to do is I need to remove the hard coded values and need to write queries with a temp table. Thanks in Advance, Shashra

    Read the article

  • Stored Procedure, 'incorrect syntax error'

    - by jacksonSD
    Attempting to figure out sp's, and I'm getting this error: "Msg 156, Level 15, State 1, Line 5 Incorrect syntax near the keyword 'Procedure'." the error seems to be on the if, but I can drop other existing tables with stored procedures the exact same way so I'm not clear on why this isn't working. can anyone shed some light? Begin Set nocount on Begin Try Create Procedure uspRecycle as if OBJECT_ID('Recycle') is not null Drop Table Recycle create table Recycle (RecycleID integer constraint PK_integer primary key, RecycleType nchar(10) not null, RecycleDescription nvarchar(100) null) insert into Recycle (RecycleID,RecycleType,RecycleDescription) values ('1','Compost','Product is compostable, instructions included in packaging') insert into Recycle (RecycleID,RecycleType,RecycleDescription) values ('2','Return','Product is returnable to company for 100% reuse') insert into Recycle (RecycleID,RecycleType,RecycleDescription) values ('3','Scrap','Product is returnable and will be reclaimed and reprocessed') insert into Recycle (RecycleID,RecycleType,RecycleDescription) values ('4','None','Product is not recycleable') End Try Begin Catch DECLARE @ErrMsg nvarchar(4000); SELECT @ErrMsg = ERROR_MESSAGE(); Throw 50001, @ErrMsg, 1; End Catch -- checking to see if table exists and is loaded: If (Select count(*) from Recycle) >1 begin Print 'Recycle table created and loaded '; Print getdate() End set nocount off End

    Read the article

  • ASP error 424 Object required

    - by jeff
    I've got a problem with the following code pasted below, the problem seems to be coming from the openastextstream this carries on from another question: Set str_text_stream = obj_file.OpenAsTextStream(ForReading, TristateUseDefault) response.Write "<table>" int_j = 0 int_x = 0 Err.number = 0 Do While Not str_text_stream.AtEndOfStream str_po_insert_sql = "INSERT into tbl_purchase_orders (purchase_order_number, purchase_order_vendor_number, purchase_order_vendor_name) " str_po_insert_sql = str_po_insert_sql & " VALUES (" str_line = str_text_stream.readline arr_line = Split(str_line, """,""", -1) if Ubound(arr_line) <> 2 then Response.write "<tr><td>The line " & str_line & " could not be imported.</td></tr>" int_x = 1 else int_y = Instr(arr_line(2), """,") - 1 str_field_0 = Replace(arr_line(0), """", "") str_field_0 = Replace(str_field_0, "'", "''") str_field_1 = Replace(arr_line(1), "'", "''") str_field_2 = Left(arr_line(2), int_y) str_field_2 = Replace(str_field_2, "'", "''") str_po_insert_sql = str_po_insert_sql & "'" & str_field_0 & "', '" & arr_line(1) & "', '" & str_field_2 & "')" rs_po_insert.Open str_po_insert_sql, dbConnection, 3 if Err.number <> 0 then Response.write "<tr><td>The line " & str_po_insert_sql & " was imported.</td></tr>" end if Err.number = 0 int_j = int_j + 1 end if loop data from test import: "4501366934","800002","Clancy Docwra Ltd",04/05/2010 00:00:00 "4501366935","800004","Clancy Docwra Ltd",04/05/2010 00:00:00 "4501366936","800004","Clancy Docwra Ltd",04/05/2010 00:00:00

    Read the article

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

< Previous Page | 347 348 349 350 351 352 353 354 355 356 357 358  | Next Page >