Search Results

Search found 92070 results on 3683 pages for 'hyper v server 2008 r2'.

Page 218/3683 | < Previous Page | 214 215 216 217 218 219 220 221 222 223 224 225  | Next Page >

• Visual Studio error on valid SQL

  - by Sam

  I'm getting an annoying error in visual studio for SQL that executes fine. SELECT InvoiceLines.LineID, InvoiceLines.InvoiceID, InvoiceLines.Text, InvoiceLines.Rate, InvoiceLines.Count, InvoiceLines.Rate * InvoiceLines.Count AS LineTotal, ((InvoiceLines.Rate * InvoiceLines.Count) * (1 + Invoices.VatRate / 100)) * (1 - CAST(Invoices.Discount AS money) * InvoiceLines.ApplyDiscount / 100) AS LineTotalIncVat, InvoiceLines.ApplyDiscount FROM InvoiceLines LEFT JOIN Invoices ON Invoices.InvoiceID = InvoiceLines.InvoiceID What LineTotalIncVat is trying to do is compute the total for the invoice item while adding the vat and subtracting the discount, and yes, probably better to do this in code (would if I could) The error visual studio gives is: There was an error parsing the query [token line number =1, token line offset =14, token in error = InvoiceLines] Even though it will validate and execute without a problem in the query builder...

  Read the article

• switch case in where clause

  - by Nimesh

  hi, i need to check three conditions: if @filter = 1 { **select * from employeestable where rating is not null** } else if @filter = 2 { **select * from employeestable where rating is null** } else { **select * from employeestable** } This i need to do using a case statement. now i have more than 30 lines of query, if i use case i can reduce my code upto 70% Please let mek now how can i do this.

  Read the article

• Cross Join 'n' times a table

  - by SDReyes

  It is possible to write a generic function/procedure/select/somethingElse to cross-join a table against himself 'n' times? (yes, 'n' is a given parameter : ) How would you do it? Example Having this table: Value ------- 1 2 3 cross join it 2 times, would return: Value | Value ------------------ 1 1 1 2 1 3 2 1 2 2 2 3 3 1 3 2 3 3

  Read the article

• Need help with SQL table structure transformation

  - by Arnis L.

  I need to perform update/insert simultaneously changing structure of incoming data. Think about Shops that have defined work time for each day of the week. Hopefully, this might explain better what I'm trying to achieve: worktimeOrigin table: columns: shop_id day val data: 123 | "monday" | "9:00 AM - 18:00" 123 | "tuesday" | "9:00 AM - 18:00" 123 | "wednesday" | "9:00 AM - 18:00" shop table: columns: id worktimeDestination.id worktimeDestination table: columns: id monday tuesday wednesday My aim: I would like to insert data from worktimeOrigin table into worktimeDestination and specify appropriate worktimeDestination for shop. shop table data: 123 1 (updated) worktimeDestination table data: 1 | "9:00 AM - 18:00" | "9:00 AM - 18:00" | "9:00 AM - 18:00" (inserted) Any ideas how to do that?

  Read the article

• add ANOTHER primary key to a table which is UNIQUE

  - by gdubs

  so im having problems with adding another primary key to my table. i have 3 columns: 1. Account ID (Identity) 2. EmailID 3. Data field when i made the table i had this to make the Account ID and the Email ID unique PRIMARY KEY (AccountID, EmailID) i thought that would make my emailid unique, but then after i tried inserting another row with the same emailid it went through. so i thought i missed something out. now for my question: IF, i had to use alter, How do i alter the table/PK Constraint to modify the EmailID field and make it Unique IF i decided to drop the table and made a new one, how do i make those two primary keys uniqe? Thanks a bunch!!

  Read the article

• Can I select a set of Data and directly insert that into a table in SQL?

  - by VJ

  Hi I guess we cannot do this but was just curious if I could do something like - Select * from Employee where EmployeeId=1 and then use the data in the above statement and directly insert into a table with just changing the employeeid...or just this way- insert into Employee ( Select * from Employee where EmployeeId=1) its probably stupid from my side...but I just felt the need to do this a lot of times...so just was curious if there was any way to achieve it..

  Read the article

• how to retrieve distinct values from multiple columns

  - by ANIL MANE

  Hello Experts, I have a flat text file data which I import into MSSQL table. It creates and table with specified name along with multiple columns as per data file. now I need a query which will return the data and its count. e.g. data file : BREAD,MILK BREAD,DIAPER,BEER,EGGS MILK,DIAPER,BEER,COKE BREAD,MILK,DIAPER,BEER BREAD,MILK,DIAPER,COKE BREAD,ICE,MANGO JUICE,BURGER Result should be BREAD | 5 MILK | 4 DIAPER| 4 and so on.

  Read the article

• Select column value that matches a combination of other columns values on the same table

  - by Ala

  I have a table called Ads and another Table called AdDetails to store the details of each Ad in a Property / Value style, Here is a simplified example with dummy code: [AdDetailID], [AdID], [PropertyName], [PropertyValue] 2 28 Color Red 3 28 Speed 100 4 27 Color Red 5 28 Fuel Petrol 6 27 Speed 70 How to select Ads that matches many combinations of PropertyName and PropertyValue, for example : where PropertyName='Color' and PropertyValue='Red' And where PropertyName='Speed' and CAST(PropertyValue AS INT) > 60

  Read the article

• how to create a subquery in sql using count based on outer query

  - by user1754716

  I hope someone can help me with this query. Basically I have two queries that I want to "combine". I want the second query as an extra column along with the first query. The first one is this : SELECT t_Item_Storage_Location.Storage_Loc_Nbr, t_Storage_Location.Storage_Loc_Type_Code, Count(t_Load.Load_Id) AS CurrentLoadCount, t_load.MMM_Id_Nbr FROM t_Load INNER JOIN (t_Storage_Location INNER JOIN t_Item_Storage_Location ON t_Storage_Location.Storage_Loc_Nbr = t_Item_Storage_Location.Storage_Loc_Nbr) ON (t_Load.Storage_Loc_Nbr = t_Item_Storage_Location.Storage_Loc_Nbr) AND (t_Load.MMM_Id_Nbr = t_Item_Storage_Location.MMM_Id_Nbr) where ((((t_Item_Storage_Location.MMM_Id_Nbr) Between '702004%' And '702011%') AND ((t_Item_Storage_Location.Storage_Loc_Nbr) Like '%A') AND ((t_Storage_Location.Storage_Loc_Type_Code)='CD') AND ((t_Load.Active_Status_Ind)='A') AND ((t_Load.QC_Status_Code) Like 'R%') AND ((t_Load.MMM_Facility_Code)='MC')) OR (((t_Item_Storage_Location.Storage_Loc_Nbr) Like '%B')) OR (((t_Item_Storage_Location.Storage_Loc_Nbr) Like '%C')) OR (((t_Item_Storage_Location.Storage_Loc_Nbr) Like '%D')) OR (((t_Item_Storage_Location.Storage_Loc_Nbr) Like '%E')) ) GROUP BY t_Item_Storage_Location.MMM_Id_Nbr, t_Item_Storage_Location.Storage_Loc_Nbr, t_Storage_Location.Storage_Loc_Type_Code, t_Load.MMM_Facility_Code, t_load.MMM_Id_Nbr HAVING Count(t_Load.Load_Id)<4 The second one, is based on the t_load.MMM_Id_Nbr of the first one. Basically I want a count of all the loads with that mmm_id_nbr. SELECT count(Load_ID) as LoadCount, MMM_Id_Nbr, storage_Loc_Nbr FROM t_load WHERE QC_Status_Code like 'R%' and mmm_Facility_Code ='MC' and Active_Status_Ind='A' GROUP by MMM_Id_Nbr, storage_loc_Nbr

  Read the article

• What is your most irritating quirk of t-sql

  - by Jude Wood

  I realise this is a bit flippant but it may highlight some 'interesting' features of t-sql. Or just provide some light distraction on a Friday morning... I'm currently torn between t-sql allows three part naming when dropping tables but not when dropping procudures and When using Coalesce to produce a delimited string the presence of a leading delimiter is dependant on whether the delimited string was initialised prior to calling Coalesce I'd be interested to hear yours......

  Read the article

• database setup for web application

  - by vbNewbie

  I have an application that requires a database and I have already setup tables but not sure if they match the requirements of the app. The app is a crawler which fetches web urls, crawls and stores appropriate urls and posts and all this is based on client requests which are stored as projects. So for each url stored there is one post and for client there are many projects and for each project there are many types of requests. So we get a client with a request and assign them a project name and then use the request to search for content and store the url and post. A request could already exist and should not be duplicated but should be associated with the right client and project and post etc. Here is my schema now: url table: urlId PK queryId FK url post table: postId PK urlId FK post date request table: queryId PK request client table: clientId PK client Name projectId FK project table: projectID PK queryID FK project Does this look right? or does anyone have suggestions. Of course my stored procedures and insert statements will have to be in depth.

  Read the article

• SQL - Two foreign keys that have a dependency between them

  - by Brian

  The current structure is as follows: Table RowType: RowTypeID Table RowSubType: RowSubTypeID FK_RowTypeID Table ColumnDef: FK_RowTypeID FK_RowSubTypeID (nullable) In short, I'm mapping column definitions to rows. In some cases, those rows have subtype(s), which will have column definitions specific to them. Alternatively, I could hang those column definitions that are specific to subtypes off their own table, or I could combine the data in RowType and RowSubType into one table and work with a single ID, but I'm not sure either is a better solution (if anything, I'd lean towards the latter, as we mostly end up pulling ColumnDefs for a given RowType/RowSubType). Is the current design SQL Blasphemy? If I keep the current structuree, how do I maintain that if RowSubTypeID is specified in ColumnDef, that it must correspond to the RowType specified by RowTypeID? Should I try to enforce this with a trigger or am I missing a simple redesign that would solve the problem?

  Read the article

• Which SQL query is faster? Filter on Join criteria or Where clause?

  - by Jon Erickson

  Compare these 2 queries. Is it faster to put the filter on the join criteria or in the were clause. I have always felt that it is faster on the join criteria because it reduces the result set at the soonest possible moment, but I don't know for sure. I'm going to build some tests to see, but I also wanted to get opinions on which would is clearer to read as well. Query 1 SELECT * FROM TableA a INNER JOIN TableXRef x ON a.ID = x.TableAID INNER JOIN TableB b ON x.TableBID = b.ID WHERE a.ID = 1 /* <-- Filter here? */ Query 2 SELECT * FROM TableA a INNER JOIN TableXRef x ON a.ID = x.TableAID AND a.ID = 1 /* <-- Or filter here? */ INNER JOIN TableB b ON x.TableBID = b.ID

  Read the article

• How to find loginname, database username, or roles of sqlserver domain user who doesn't have their own login?

  - by Adam Butler

  I have created a login and database user called "MYDOMAIN\Domain Users". I need to find what roles a logged on domain user has but all the calls to get the current user return the domain username eg. "MYDOMAIN\username" not the database username eg. "MYDOMAIN\Domain Users". For example, this query returns "MYDOMAIN\username" select original_login(),suser_name(), suser_sname(), system_user, session_user, current_user, user_name() And this query returns 0 select USER_ID() I want the username to query database_role_members is there any fuction that will return it or any other way I can get the current users roles?

  Read the article

• how to insert record in database with each date of month on single button click ?

  - by Mr. Goo

  I have two textbox .... textbox1 and textbox2 textbox1 == choose from date textbox2 == choose to date if user select from date in textbox1 as 01-May-2011 and in textbox2 as 30-May-2011 then all the dates from 01-MAy-2011 to 30-May-2011 will be inserted in each row of mssql2005 datatable... example. . : IN database Table1 structure ... ID Date 1 01-MAy-2011 2 02-MAy-2011 3 03-MAy-2011 4 04-MAy-2011 5 05-MAy-2011 and so on till 30-May-2011

  Read the article

• Changing MSSQL Database sorting

  - by plaisthos

  I have a request to change the collation of a MS SQL Database: ALTER DATABASE solarwind95 collate SQL_Latin1_General_CP1_CI_AS but I get this strange error: Meldung 5075, Ebene 16, Status 1, Zeile 1 Das 'Spalte'-Objekt 'CustomPollerAssignment.PollerID' ist von 'Datenbanksortierung' abhängig. Die Datenbanksortierung kann nicht geändert werden, wenn ein schemagebundenes Objekt von ihr abhängig ist. Entfernen Sie die Anhängigkeiten der Datenbanksortierung, und wiederholen Sie den Vorgang. Sorry for the german errror message. I do not know how to switch the language to english, but here is a translation: Translation: Message 5075, Layer 16, Status 1, Row 1 The 'column' object 'CustomPollerAssignment.PollerID' depends on 'Database sorting. The database sorting cannot be changed if a schema bound object depends on it. Remove the dependency of the database sortieren and retry. I got a ton more of the errors like that.

  Read the article

• sql - How do I enforce a two-column constraint?

  - by hamlin11

  I have a CustomerID column and an EffectiveDate column in a table. I need the combination of these two to be unique. However, I already have a primary key on an auto-numbered integer column. What is a good way to accomplish my goal? Thanks

  Read the article

• How can I run a .sql file with the sqlcmd utility specifying to continue if there are errors?

  - by Matt

  How can I run a .sql file with the sqlcmd utility specifying to continue if there are errors? Right now if an error occurs, the entire sql file aborts. Is there away around this?

  Read the article

• Simple Self Join Query Bad Performance

  - by user1514042

  Could anyone advice on how do I improve the performance of the following query. Note, the problem seems to be caused by where clause. Data (table contains a huge set of rows - 500K+, the set of parameters it's called with assums the return of 2-5K records per query, which takes 8-10 minutes currently): USE [SomeDb] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Data]( [x] [money] NOT NULL, [y] [money] NOT NULL, CONSTRAINT [PK_Data] PRIMARY KEY CLUSTERED ( [x] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO The Query select top 10000 s.x as sx, e.x as ex, s.y as sy, e.y as ey, e.y - s.y as y_delta, e.x - s.x as x_delta from Data s inner join Data e on e.x > s.x and e.x - s.x between xFrom and xTo --where e.y - s.y > @yDelta -- when uncommented causes a huge delay Update 1 - Execution Plan <?xml version="1.0" encoding="utf-16"?> <ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.2" Build="11.0.2100.60" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"> <BatchSequence> <Batch> <Statements> <StmtSimple StatementCompId="1" StatementEstRows="100" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.0263655" StatementText="select top 100&#xD;&#xA;s.x as sx,&#xD;&#xA;e.x as ex,&#xD;&#xA;s.y as sy,&#xD;&#xA;e.y as ey,&#xD;&#xA;e.y - s.y as y_delta,&#xD;&#xA;e.x - s.x as x_delta&#xD;&#xA;from Data s &#xD;&#xA; inner join Data e&#xD;&#xA; on e.x &gt; s.x and e.x - s.x between 100 and 105&#xD;&#xA;where e.y - s.y &gt; 0.01&#xD;&#xA;" StatementType="SELECT" QueryHash="0xAAAC02AC2D78CB56" QueryPlanHash="0x747994153CB2D637" RetrievedFromCache="true"> <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" /> <QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="24" CompileTime="13" CompileCPU="13" CompileMemory="424"> <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" /> <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="52199" EstimatedPagesCached="14561" EstimatedAvailableDegreeOfParallelism="4" /> <RelOp AvgRowSize="55" EstimateCPU="1E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="100" LogicalOp="Compute Scalar" NodeId="0" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0263655"> <OutputList> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="x" /> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="y" /> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="x" /> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="y" /> <ColumnReference Column="Expr1004" /> <ColumnReference Column="Expr1005" /> </OutputList> <ComputeScalar> <DefinedValues> <DefinedValue> <ColumnReference Column="Expr1004" /> <ScalarOperator ScalarString="[SomeDb].[dbo].[Data].[y] as [e].[y]-[SomeDb].[dbo].[Data].[y] as [s].[y]"> <Arithmetic Operation="SUB"> <ScalarOperator> <Identifier> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="y" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="y" /> </Identifier> </ScalarOperator> </Arithmetic> </ScalarOperator> </DefinedValue> <DefinedValue> <ColumnReference Column="Expr1005" /> <ScalarOperator ScalarString="[SomeDb].[dbo].[Data].[x] as [e].[x]-[SomeDb].[dbo].[Data].[x] as [s].[x]"> <Arithmetic Operation="SUB"> <ScalarOperator> <Identifier> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="x" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="x" /> </Identifier> </ScalarOperator> </Arithmetic> </ScalarOperator> </DefinedValue> </DefinedValues> <RelOp AvgRowSize="39" EstimateCPU="1E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="100" LogicalOp="Top" NodeId="1" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="0.0263555"> <OutputList> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="x" /> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="y" /> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="x" /> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="y" /> </OutputList> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRows="100" ActualEndOfScans="1" ActualExecutions="1" /> </RunTimeInformation> <Top RowCount="false" IsPercent="false" WithTies="false"> <TopExpression> <ScalarOperator ScalarString="(100)"> <Const ConstValue="(100)" /> </ScalarOperator> </TopExpression> <RelOp AvgRowSize="39" EstimateCPU="151828" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="100" LogicalOp="Inner Join" NodeId="2" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0263455"> <OutputList> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="x" /> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="y" /> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="x" /> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="y" /> </OutputList> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRows="100" ActualEndOfScans="0" ActualExecutions="1" /> </RunTimeInformation> <NestedLoops Optimized="false"> <OuterReferences> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="x" /> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="y" /> </OuterReferences> <RelOp AvgRowSize="23" EstimateCPU="1.80448" EstimateIO="3.76461" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Scan" NodeId="3" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="1640290"> <OutputList> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="x" /> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="y" /> </OutputList> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRows="15225" ActualEndOfScans="0" ActualExecutions="1" /> </RunTimeInformation> <IndexScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="x" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="y" /> </DefinedValue> </DefinedValues> <Object Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Index="[PK_Data]" Alias="[e]" IndexKind="Clustered" /> </IndexScan> </RelOp> <RelOp AvgRowSize="23" EstimateCPU="0.902317" EstimateIO="1.88387" EstimateRebinds="1" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="100" LogicalOp="Clustered Index Seek" NodeId="4" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0263655" TableCardinality="1640290"> <OutputList> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="x" /> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="y" /> </OutputList> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRows="100" ActualEndOfScans="15224" ActualExecutions="15225" /> </RunTimeInformation> <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore"> <DefinedValues> <DefinedValue> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="x" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="y" /> </DefinedValue> </DefinedValues> <Object Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Index="[PK_Data]" Alias="[s]" IndexKind="Clustered" /> <SeekPredicates> <SeekPredicateNew> <SeekKeys> <EndRange ScanType="LT"> <RangeColumns> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="x" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="[SomeDb].[dbo].[Data].[x] as [e].[x]"> <Identifier> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="x" /> </Identifier> </ScalarOperator> </RangeExpressions> </EndRange> </SeekKeys> </SeekPredicateNew> </SeekPredicates> <Predicate> <ScalarOperator ScalarString="([SomeDb].[dbo].[Data].[x] as [e].[x]-[SomeDb].[dbo].[Data].[x] as [s].[x])&gt;=($100.0000) AND ([SomeDb].[dbo].[Data].[x] as [e].[x]-[SomeDb].[dbo].[Data].[x] as [s].[x])&lt;=($105.0000) AND ([SomeDb].[dbo].[Data].[y] as [e].[y]-[SomeDb].[dbo].[Data].[y] as [s].[y])&gt;(0.01)"> <Logical Operation="AND"> <ScalarOperator> <Compare CompareOp="GE"> <ScalarOperator> <Arithmetic Operation="SUB"> <ScalarOperator> <Identifier> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="x" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="x" /> </Identifier> </ScalarOperator> </Arithmetic> </ScalarOperator> <ScalarOperator> <Const ConstValue="($100.0000)" /> </ScalarOperator> </Compare> </ScalarOperator> <ScalarOperator> <Compare CompareOp="LE"> <ScalarOperator> <Arithmetic Operation="SUB"> <ScalarOperator> <Identifier> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="x" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="x" /> </Identifier> </ScalarOperator> </Arithmetic> </ScalarOperator> <ScalarOperator> <Const ConstValue="($105.0000)" /> </ScalarOperator> </Compare> </ScalarOperator> <ScalarOperator> <Compare CompareOp="GT"> <ScalarOperator> <Arithmetic Operation="SUB"> <ScalarOperator> <Identifier> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="y" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="y" /> </Identifier> </ScalarOperator> </Arithmetic> </ScalarOperator> <ScalarOperator> <Const ConstValue="(0.01)" /> </ScalarOperator> </Compare> </ScalarOperator> </Logical> </ScalarOperator> </Predicate> </IndexScan> </RelOp> </NestedLoops> </RelOp> </Top> </RelOp> </ComputeScalar> </RelOp> </QueryPlan> </StmtSimple> </Statements> </Batch> </BatchSequence> </ShowPlanXML>

  Read the article

• Refreshing metadata on user functions t-SQL

  - by luckyluke

  I am doing some T-SQL programming and I have some Views defines on my database. The data model is still changing these days and I have some table functions defined. Sometimes i deliberately use select * from MYVIEW in such a table function to return all columns. If the view changes (or table) the function crashes and I need to recompile it. I know it is in general good thing so that it prevents from hell lotta errors but still... Is there a way to write such functions so the dont' blow up in my face everytime I change something on the underlying table? Or maybe I am doing something completely wrong... Thanks for help

  Read the article

• Database design: Calculating the Account Balance

  - by 001

  How do I design the database to calculate the account balance? 1) Currently I calculate the account balance from the transaction table In my transaction table I have "description" and "amount" etc.. I would then add up all "amount" values and that would work out the user's account balance. I showed this to my friend and he said that is not a good solution, when my database grows its going to slow down???? He said I should create separate table to store the calculated account balance. If did this, I will have to maintain two tables, and its risky, the account balance table could go out of sync. Any suggestion? EDIT: OPTION 2: should I add an extra column to my transaction tables "Balance". now I do not need to go through many rows of data to perform my calculation. Example John buys $100 credit, he debt $60, he then adds $200 credit. Amount $100, Balance $100. Amount -$60, Balance $40. Amount $200, Balance $240.

  Read the article

• Join Where Rows Don't Exist or Where Criteria Matches...?

  - by Greg

  I'm trying to write a query to tell me which orders have valid promocodes. Promocodes are only valid between certain dates and optionally certain packages. I'm having trouble even explaining how this works (see psudo-ish code below) but basically if there are packages associated with a promocode then the order has to have one of those packages and be within a valid date range otherwise it just has to be in a valid date range. The whole "if PrmoPackage rows exist" thing is really throwing me off and I feel like I should be able to do this without a whole bunch of Unions. (I'm not even sure if that would make it easier at this point...) Anybody have any ideas for the query? if `OrderPromoCode` = `PromoCode` then if `OrderTimestamp` is between `PromoStartTimestamp` and `PromoEndTimestamp` then if `PromoCode` has packages associated with it //yes then if `PackageID` is one of the specified packages //yes code is valid //no invalid //no code is valid Order: OrderID* | OrderTimestamp | PackageID | OrderPromoCode 1 | 1/2/11 | 1 | ABC 2 | 1/3/11 | 2 | ABC 3 | 3/2/11 | 2 | DEF 4 | 4/2/11 | 3 | GHI Promo: PromoCode* | PromoStartTimestamp* | PromoEndTimestamp* ABC | 1/1/11 | 2/1/11 ABC | 3/1/11 | 4/1/11 DEF | 1/1/11 | 1/11/13 GHI | 1/1/11 | 1/11/13 PromoPackage: PromoCode* | PromoStartTimestamp* | PromoEndTimestamp* | PackageID* ABC | 1/1/11 | 2/1/11 | 1 ABC | 1/1/11 | 2/1/11 | 3 GHI | 1/1/11 | 1/11/13 | 1 Desired Result: OrderID | IsPromoCodeValid 1 | 1 2 | 0 3 | 1 4 | 0

  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

• T-SQL MERGE - finding out which action it took

  - by IanC

  I need to know if a MERGE statement performed an INSERT. In my scenario, the insert is either 0 or 1 rows. Test code: DECLARE @t table (C1 int, C2 int) DECLARE @C1 INT, @C2 INT set @c1 = 1 set @c2 = 1 MERGE @t as tgt USING (SELECT @C1, @C2) AS src (C1, C2) ON (tgt.C1 = src.C1) WHEN MATCHED AND tgt.C2 != src.C2 THEN UPDATE SET tgt.C2 = src.C2 WHEN NOT MATCHED BY TARGET THEN INSERT VALUES (src.C1, src. C2) OUTPUT deleted.*, $action, inserted.*; SELECT inserted.* The last line doesn't compile (no scope, unlike a trigger). I can't get access to @action, or the output. Actually, I don't want any output meta data. How can I do this?

  Read the article

• blank to numeric conversion derived column

  - by praveen

  Hi All, I have a source column with blank (not "NULL"), and target as numeric. while converting using the data conversion it is not converting due to balnk source value so I used derived column to replace a blank value with NULL or 0 as (source column == " ") ? "0" : source column but its not giving the value as 0 in the blank place. thanks prav

  Read the article

< Previous Page | 214 215 216 217 218 219 220 221 222 223 224 225  | Next Page >