Search Results

Search found 1208 results on 49 pages for 'tsql'.

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 27/49 | < Previous Page | 23 24 25 26 27 28 29 30 31 32 33 34  | Next Page >

  • SQL SERVER - Understanding how MIN(text) works.

    - by tmercer
    I'm doing a little digging and looking for a explanation on how SQL server evaluates MIN(Varchar). I found this remark in BOL: MIN finds the lowest value in the collating sequence defined in the underlying database So if I have a table that has one row with the following values: Data AA AB AC Doing a SELECT MIN(DATA) would return back AA. I just want to understand the why behind this and understand the BOL a little better. Thanks!

    Read the article

  • Sql Calculation And Sort By Date

    - by mahesh
    I have Confusion against utilize If,Else Statement against calculation of stock By date. And sort the same by date. There is real challenge to calculate running total between equal date: If date is equal If date is greater than If date is less than My Table Schema Is: TransID int, Auto Increment Date datetime, Inwards decimal(12,2) Outward decimal(12,2) Suppose If I have Records as Below: TransID Date(DD/MM/YYYY) Inward Outward 1 03/02/2011 100 2 12/04/2010 200 3 03/02/2011 400 Than Result Should be: TransID Date(DD/MM/YYYY) Inward Outward Balance 1 03/02/2011 100 -100 2 12/04/2010 200 -300 3 03/02/2011 400 100 I wants to calculate Inward - outwards = Balance and Balance count as running total as above. but the condition that it should be as per date order How to sort and calculate it by date and transID? What is transact SQL IN SQL_SERVER-2000**?.

    Read the article

  • Creating Two Cascading Foreign Keys Against Same Target Table/Col

    - by alram
    I have the following tables: user (userid int [pk], name varchar(50)) action (actionid int [pk], description nvarchar(50)) being referenced by another table that captures the relationship: <user1> <action>'s <user2>. I did this with the following table: userAction (userActionId int [pk], actionid int [fk: action.actionid], **userId1 int [fk ref's user.userid; on del/update cascade], userId2 int [fk ref's user.userid; on del/update cascade]**). However, when I try to save the userAction table i get an error because I have two cascading fk's against user.userid. Is there any way to remedy this or must I use a trigger?

    Read the article

  • SQL Server WHERE Clause using Temporary Columns

    - by AbeP
    Hi all, I have the following query, which uses a CASE statement. Is there anyway to add into the where clause WHERE IsBusinessDayFinal = 0? without using a temporary table? thanks so much! SELECT ac.DateTimeValue, CASE WHEN pc.IsBusinessDay IS NOT NULL THEN pc.IsBusinessDay ELSE ac.IsBusinessDay END AS IsBusinessDayFinal, ac.FullYear, ac.MonthValue, ac.DayOfMonth, ac.DayOfWeek, ac.Week FROM [dbo].[AdminCalendar] ac LEFT JOIN [dbo].ProjectCalendar pc ON ac.DateTimeValue = pc.DateTimeValue AND pc.ProjectId = @projectId WHERE ac.DateTimeValue >= @startDate AND ac.DateTimeValue <= @finishDate;

    Read the article

  • Date ranges intersections..

    - by Puzzled
    MS Sql 2008: I have 3 tables: meters, transformers (Ti) and voltage transformers (Tu) ParentId MeterId BegDate EndDate 10 100 '20050101' '20060101' ParentId TiId BegDate EndDate 10 210 '20050201' '20050501' 10 220 '20050801' '20051001' ParentId TuId BegDate EndDate 10 300 '20050801' '20050901' where date format is yyyyMMdd (year-month-day) Is there any way to get periods intersection and return the table like this? ParentId BegDate EndDate MeterId TiId TuId 10 '20050101' '20050201' 100 null null 10 '20050201' '20050501' 100 210 null 10 '20050501' '20050801' 100 null null 10 '20050801' '20050901' 100 220 300 10 '20050901' '20051001' 100 220 null 10 '20051001' '20060101' 100 null null Here is the table creation script: --meters declare @meters table (ParentId int, MeterId int, BegDate smalldatetime, EndDate smalldatetime ) insert @meters select 10, 100, '20050101', '20060101' --transformers declare @ti table (ParentId int, TiId int, BegDate smalldatetime, EndDate smalldatetime ) insert @ti select 10, 210, '20050201', '20050501' union all select 10, 220, '20050801', '20051001' --voltage transformers declare @tu table (ParentId int, TuId int, BegDate smalldatetime, EndDate smalldatetime ) insert @tu select 10, 300, '20050801', '20050901'

    Read the article

  • NHibernate 'IdentifierGenerationException' on an Update trigger

    - by Jan Jongboom
    In my database I have an id column defined as [autonumber] [int] IDENTITY(1,1) NOT NULL which is mapped in my .hbm.xml like: <id name="Id" column="autonumber" type="int"> <generator class="identity" /> </id> When calling session.Save() updates are successful committed to the database. When adding a versioning trigger I however get the error this id generator generates Int64, Int32, Int16 of type IdentifierGenerationException. The trigger is defined as: ALTER TRIGGER [dbo].[CatchUpdates_NVM_FDK_Kenmerken] ON [dbo].[NVM_FDK_Kenmerken] INSTEAD OF UPDATE AS BEGIN SET NOCOUNT ON UPDATE NVM_FDK_Kenmerken SET idIsActive = 0 WHERE internalId IN (SELECT internalId FROM INSERTED) INSERT INTO dbo.NVM_FDK_Kenmerken ( vestigingNummer , internalId , someOtherColumns, dateInserted, idIsActive ) SELECT vestigingNummer, internalId, someOtherColumns, GETDATE(), 1 FROM INSERTED END What am I doing wrong here? When doing manual updates everything works just fine and as expected.

    Read the article

  • How do I accurately handle a batch separator for SQL from C#

    - by Sam Saffron
    For Data Explorer I would like to add support for a Batch separator. So for example if users type in: select 'GO' go select 1 as go Go select 100 I would like to return the three result sets. Its clear that I need some sort of parser here, my hope is that this is a solved problem and I can just plug it in. (writing a full T-SQL parser is not something I would like to do) What component / demo code could achieve splitting this batch into its 3 parts?

    Read the article

  • How to get an id from the results in two tables

    - by Chris Lively
    Consider an order. An order will have one or more line items. Each line item is for a particular product. Given a filter table with a couple of products, how would I get the order id's that had at least all of the products listed in the second table? table Orders( OrderId int ) table LineItems ( OrderId int, LineItemId int, ProductId int ) table Filter ( ProductId int ) data Orders OrderId -------- 1 2 3 LineItems OrderId LineItemId ProductId ------- ---------- --------- 1 1 401 1 2 502 2 3 401 3 4 401 3 5 603 3 6 714 Filter ProductId --------- 401 603 Desired result of the query: OrderId: 3

    Read the article

  • Optimal way to convert to date

    - by IMHO
    I have legacy system where all date fields are maintained in YMD format. Example: 20101123 this is date: 11/23/2010 I'm looking for most optimal way to convert from number to date field. Here is what I came up with: declare @ymd int set @ymd = 20101122 select @ymd, convert(datetime, cast(@ymd as varchar(100)), 112) This is pretty good solution but I'm wandering if someone has better way doing it

    Read the article

  • SQL Server Merge statement issue

    - by George2
    Hello everyone, I am learning and using SQL Server 2008 new Merge statement, merge statement will compare/operate source table and destination table row by row ("operate" I mean operations performed for when matched or not-matched conditions). My question is whether the whole merge process will be one transaction or each row comparison/operation will be one transaction? Appreciate if any document to prove it. thanks in advance, George

    Read the article

  • Merge overlapping date intervals

    - by leoinfo
    Is there a better way of merging overlapping date intervals? The solution I came up with is so simple that now I wonder if someone else has a better idea of how this could be done. /***** DATA EXAMPLE *****/ DECLARE @T TABLE (d1 DATETIME, d2 DATETIME) INSERT INTO @T (d1, d2) SELECT '2010-01-01','2010-03-31' UNION SELECT '2010-04-01','2010-05-31' UNION SELECT '2010-06-15','2010-06-25' UNION SELECT '2010-06-26','2010-07-10' UNION SELECT '2010-08-01','2010-08-05' UNION SELECT '2010-08-01','2010-08-09' UNION SELECT '2010-08-02','2010-08-07' UNION SELECT '2010-08-08','2010-08-08' UNION SELECT '2010-08-09','2010-08-12' UNION SELECT '2010-07-04','2010-08-16' UNION SELECT '2010-11-01','2010-12-31' UNION SELECT '2010-03-01','2010-06-13' /***** INTERVAL ANALYSIS *****/ WHILE (1=1) BEGIN UPDATE t1 SET t1.d2 = t2.d2 FROM @T AS t1 INNER JOIN @T AS t2 ON DATEADD(day, 1, t1.d2) BETWEEN t2.d1 AND t2.d2 -- AND t1.d2 <= t2.d2 /***** this condition is useless *****/ IF @@ROWCOUNT = 0 BREAK END /***** RESULT *****/ SELECT StartDate = MIN(d1) , EndDate = d2 FROM @T GROUP BY d2 ORDER BY StartDate, EndDate /***** OUTPUT *****/ /***** StartDate EndDate 2010-01-01 2010-06-13 2010-06-15 2010-08-16 2010-11-01 2010-12-31 *****/ EDIT: I realized that the t1.d2 <= t2.d2 condition is not really useful.

    Read the article

  • How to add additional rows to result set by condition

    - by Puzzled
    I have a table like this: ObjId Date Value 100 '20100401' 12 200 '20100501' 45 200 '20100401' 37 300 '20100501' 75 300 '20100401' 69 400 '20100401' 87 I have to add additional rows to result set for objId's, where there is no data at '20100501' **100 '20100501' null** 100 '20100401' 12 200 '20100501' 45 200 '20100401' 37 300 '20100501' 75 300 '20100401' 69 **400 '20100501' null** 400 '20100401' 87 What is the best way to do this? Here is the T-SQL script for the initial table: declare @datesTable table (objId int, date smalldatetime, value int) insert @datesTable select 100, '20100401', 12 union all select 200, '20100501', 45 union all select 200, '20100401', 37 union all select 300, '20100501', 75 union all select 300, '20100401', 69 union all select 400, '20100401', 87 select * from @datesTable

    Read the article

  • SQL 2005 Merge / concatenate multiple rows to one column

    - by Dave
    Hi, We have a bit of a SQL quandry. Say I have a results that look like this... 61E77D90-D53D-4E2E-A09E-9D6F012EB59C | A 61E77D90-D53D-4E2E-A09E-9D6F012EB59C | B 61E77D90-D53D-4E2E-A09E-9D6F012EB59C | C 61E77D90-D53D-4E2E-A09E-9D6F012EB59C | D 7ce953ca-a55b-4c55-a52c-9d6f012ea903 | E 7ce953ca-a55b-4c55-a52c-9d6f012ea903 | F is there a way I can group these results within SQL to return as 61E77D90-D53D-4E2E-A09E-9D6F012EB59C | A B C D 7ce953ca-a55b-4c55-a52c-9d6f012ea903 | E F Any ideas people? Many thanks Dave

    Read the article

  • Stored Procedure Does Not Fire Last Command

    - by jp2code
    On our SQL Server (Version 10.0.1600), I have a stored procedure that I wrote. It is not throwing any errors, and it is returning the correct values after making the insert in the database. However, the last command spSendEventNotificationEmail (which sends out email notifications) is not being run. I can run the spSendEventNotificationEmail script manually using the same data, and the notifications show up, so I know it works. Is there something wrong with how I call it in my stored procedure? [dbo].[spUpdateRequest](@packetID int, @statusID int output, @empID int, @mtf nVarChar(50)) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DECLARE @id int SET @id=-1 -- Insert statements for procedure here SELECT A.ID, PacketID, StatusID INTO #act FROM Action A JOIN Request R ON (R.ID=A.RequestID) WHERE (PacketID=@packetID) AND (StatusID=@statusID) IF ((SELECT COUNT(ID) FROM #act)=0) BEGIN -- this statusID has not been entered. Continue SELECT ID, MTF INTO #req FROM Request WHERE PacketID=@packetID WHILE (0 < (SELECT COUNT(ID) FROM #req)) BEGIN SELECT TOP 1 @id=ID FROM #req INSERT INTO Action (RequestID, StatusID, EmpID, DateStamp) VALUES (@id, @statusID, @empID, GETDATE()) IF ((@mtf IS NOT NULL) AND (0 < LEN(RTRIM(@mtf)))) BEGIN UPDATE Request SET MTF=@mtf WHERE ID=@id END DELETE #req WHERE ID=@id END DROP TABLE #req SELECT @id=@@IDENTITY, @statusID=StatusID FROM Action SELECT TOP 1 @statusID=ID FROM Status WHERE (@statusID<ID) AND (-1 < Sequence) EXEC spSendEventNotificationEmail @packetID, @statusID, 'http:\\cpweb:8100\NextStep.aspx' END ELSE BEGIN SET @statusID = -1 END DROP TABLE #act END Idea of how the data tables are connected:

    Read the article

  • How to get the right order for creation of stored procedure, user-defined functions and triggers

    - by PeeWee2201
    I read that object dependencies have been improved in SQL server 2008. I have a rather complex database schema containing stored procedure, user-defined functions, triggers. Can anybody give me a query that would return the right order of creation of those items based on their dependencies ? I read here that there are tools that can do the job, but I am looking for something scriptable. Also, they often give the dependencies of one object and I would like a database-wide solution. Thank you.

    Read the article

  • T-SQL Conditonal Select Statement

    - by msarchet
    So this isn't really a normal conditional select statement. I'm helping a colleague out with some SQL and we have this issue. I current there is a coulumn ID and a column Number. Either ID is 0 or Number is 0. So my select statement needs to do Select colA, colB, colC, crazy part From Table A. the crazy part is this: (If ID > 0, ID, Number) basically select and return that column that isn't 0. Is there any way to do this in T-SQL?

    Read the article

  • SQL CASE Question

    - by docsql
    Hiya, I dont know if this can be done but i'd though i'd ask. What I want to do is have a case statement query and if a 1 begin another action. if 0 dont do anything. For Example select CASE WHEN client.deathofdeath = yes THEN 1 do another select in here (which is another table) Else 0 End AS DeathDate From Client client Can this be done?

    Read the article

  • T-SQL query and group by reporting help

    - by Dayton Brown
    So I have some data that looks like this. `USERID1 USERID2` 1 10 2 20 2 30 3 40 3 50 1 10 2 20 2 30 3 50 I want a query that produces the following `USERID1 COUNT` 2 2 3 2 It's a group by query that shows me the count of unique USERID2 for each USERID1 that has more than 1 USERID2 associated with it. God I hope you aren't as confused as I am by that last statement.

    Read the article

  • help with t-sql self join

    - by stackoverflowuser
    Based on the following table ID Date State ----------------------------- 1 06/10/2010 Complete 1 06/04/2010 Pending 2 06/06/2010 Active 2 06/05/2010 Pending I want the following ouptut ID Date State --------------------------- 1 06/04/2010 Complete 2 06/05/2010 Active So date is the earliest one and State is the latest one. I am failing to apply self join on the table to get the output. Thanks

    Read the article

  • how to Invoke User-Defined Functions That Return a Table Data Type

    - by nectar
    here my code- create function dbo.emptable() returns Table as return (select id, name, salary from employee) go select dbo.emptable() error: Msg 4121, Level 16, State 1, Line 1 Cannot find either column "dbo" or the user-defined function or aggregate "dbo.emptable", or the name is ambiguous. while when I run sp_helptext emptable it shows- create function dbo.emptable() returns Table as return (select id, name, salary from employee) it means function exists in database then why it is giving such error?

    Read the article

  • Is there a better way to find the max count in a table

    - by nXqd
    select NV.PHG From Nhanvien NV Group by NV.phg Having count(nv.Manv) >= all (select count(NV.MANV from nhanvien nv group by nv.MANV)) I'm finding a better way to find the 'max count' NV of a PHG ( in this example ) . I think, we meet this case all the time when we do SQL, i should've a better way . Thanks for reading this :)

    Read the article

  • SQL Duplicates Issue SQL SERVER 2000

    - by jeff
    I have two tables : Product and ProductRateDetail. The parent table is Product. I have duplicate records in the product table which need to be unique. There are entries in the ProductRateDetail table which correspond to duplicate records in the product table. Somehow I need to update the ProductRateDetail table to match the original (older) ID from the Product table and then remove the duplicates from the product table. I would do this manually but there are 100's of records. i.e. something like UPDATE tbl_productRateDetail SET productID = (originalID from tbl_product) then something like DELETE from tbl_product WHERE duplicate ID and only delete the recently added ID data example: (sorry can't work out this formatting thing) tbl_Product select * from dbo.Product where ProductCode = '10003' ProductID ProductTypeID ProductDescription ProductCode ProductSize 365 1 BEND DOUBLE FLANGED 10003 80mmX90deg 1354 1 BEND DOUBLE FLANGED 10003 80mmX90deg tbl_ProductRateDetail SELECT * FROM [MSTS2].[dbo].[ProductRateDetail] WHERE ProductID in (365,1354) ProductRateDetailID ProductRateID ProductID UnitRate 365 1 365 16.87 1032 5 365 16.87 2187 10 365 16.87 2689 11 365 16.87 3191 12 365 16.87 7354 21 1354 21.30 7917 22 1354 21.30 8480 23 1354 21.30 9328 25 1354 21.30 9890 26 1354 21.30 10452 27 1354 21.30 Please help!

    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 | 23 24 25 26 27 28 29 30 31 32 33 34  | Next Page >