ID: (primary, auto generated)
Work order : (non unique foreign key)
Status: Internal status
Modified date: date time of status change to work order
This keeps track of our work order status changes. We'd like to trend our internal orders and monitor status durations (so to know how long each work order spend on a particular status). Now, the only thing is missing is an "end date/time", thus I must generate it in either a query or a SQL report form. The issue, is that i need the "modified date" from the next highest combination of work order + ID values (since they are the only reference). Is there any way to get this to happen? So essentially, if I sort by "work order" and then "id", i'd want the "modified date" value from line 2 to be on line 1, and so on. Now, if the "work order" number changes, or there isn't a higher work order+ID, we leave it blank and move on. Any ideas?? I'm really stuck.
I have a need to execute a query in T-SQL on a numeric field in a SQL table. However if there is no exact match I'd likea query that will return the row that is just below my value. As an example if the table has values: 1,2,4,5,7,9, 15 and 20 for instance and I am matching with a varibale containing the value 9 then I'd like it to return that row. however if my variable has the value 19 I want the row containign 15 returned. Is this possible? Can anyone help me with such a query? Regards Clive
i have a column of dates. i make an index or key for this column so that i can perform binary search i hope. If the search is not succesful how do i get the query to return the two dates surrounding the date i was searching for.
I have a table which has a few fields, one being "datetime_traded". I need to write a query which returns the row which has the closest time (down to second) given a date/time. I'm using MS SQL.
Here's what I have so far:
Code:
select * from TICK_D where datetime_traded = (select min( abs(datediff(second,datetime_traded , Convert(datetime,'2005-05-30:09:31:09')) ) ) from TICK_D)
But I get an error - "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.".
Does anyone know how i could do this? Thanks a lot for any help!
Is there any way in SQL of selecting a date closest to another date.
i.e. I have a date say 4/4/2002, and in the database I have dates 4/2/2002 and earlier and 4/7/2002 and later.
I want to do a query and return the date 4/2/2002 as it is only 2 days away even though it is an earlier date.
So short of writing a loop and saying if the date is eq then good else, take date and add a day if not correct take initial date and subtract a day etc etc. This way is proving to be a little too processor heavy.
I’m trying to write a query that will select closest value to Amount field in #TestValue without go over #SM.GMAdj value.Outcome of the query should be something like
Tables are: SELECT PercRate, Amount, Code INTO #TestValues FROM ( SELECT 6.00, 172715, 13 UNION ALL SELECT 6.50, 172716, 13 UNION ALL SELECT 7.00, 181351, 13 UNION ALL SELECT 7.50, 190419, 13 UNION ALL
Hello,I need help in writing a SQL statement in MS SQL Server 2000 to selectthe latest date (i.e., the date closest to or equal to the current date)for a given date.For example, in a table I have the following records:Date Exchange-Rate01/Sep/03 0.5505/Sep/03 0.59If the given date is 02/Sep/03, then the rate 0.55 should be return.If the given date is 03/Sep/03, then the rate 0.55 should be return.If the given date is 04/Sep/03, then the rate 0.59 should be return.Thanks in advanced,Benny*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
I'm trying to manipulate the data in my Service_2 column to round off the Estimated_Miles to the closest 5K miles. I tried the round function (Round(EstimatedMiles, -4)) but this does not exactly give me the results I want. Below is my result set:
Row_ID Service_2 Estimated_Miles
1 40000 44012
2 50000 46124
3 120000 121512
4 30000 31857
5 10000 7547
6 80000 79500
And below is what I'd like to have
Row_ID Service_2 Estimated_Miles
1 45000 44012
2 50000 46124
3 125000 121512
4 35000 31857
5 10000 7547
6 80000 79500
The range of data I'm working with is pretty broad meaning my Min & Max Estimated_Miles is between 0 - 1,000,000. In essence, I would like to say is if the estimated mileage is between 0-4999 then Service_2 = the closest 5K miles otherwise round to the closest 10K miles. Any help is truly appreciated. Thanks
I apologise if this is a stupid question, but I would appreciated anyhelp on this subject.I want to create a view (VIEW_1 in example below) where I take numericvalues from a field in one table (DEPTH_FROM in TABLE_1) and find theclosest matching values from another field in another table (DEPTH_ATin TABLE_2) - the higher matching value and the lower matching value.TABLE_1BH_ID DEPTH_FROMB1 10.6B1 14.2B1 16.1B1 17.0TABLE_2BH_ID DEPTH_ATB1 9B1 10B1 11B1 12B1 13B1 14B1 15B1 16B1 17B1 18VIEW_1BH_ID DEPTH_FROM DEPTH_LOWER DEPTH_HIGHERB1 10.6 1011B1 14.2 1415B1 16.1 1617B1 17.0 1717Any assistance would be appreciated.Regards,Hennie
I have included the create table statements, sample data and a stored procedure.
I have a stored procedure that walks the associations so that I can assemble all the nodes of the tree. I think this is working properly.
I have two requests/needs/problems that I need to solve: (1) I want to create a NEW stored procedure that takes a WhoID and an AssocID. This new stored procedure would show the shortest or closest path between WhoID and AssocID.
For example, if I pass in 6,1 to this new proc I want the stored procedure to return the hierarchy with the closest path between them. Based on the sample data below, the path would be 6 - 8 8 - 2 2 - 3 3 - 1
(2) I might also need a proc that accepts two inputs (again, lets use 6,1) but would show all paths / branches of the tree where person 1 exists. Based on the stored procedure procCTE_Assoc 6, this solution would exclude the "fred and jeff" node but would include the other two nodes because person 1 is within the other two nodes.
Please provide your suggestions, comments, or solutions!
CREATE TABLE dbo.Who( WhoID bigint IDENTITY(1,1) NOT NULL, FName varchar(50) NOT NULL ) GO CREATE TABLE dbo.Assoc( WhoID bigint NOT NULL, AssocID bigint NOT NULL ) ON PRIMARY
SET IDENTITY_INSERT dbo.Who ON GO INSERT INTO Who (WhoID,FName) VALUES (1,'user1') INSERT INTO Who (WhoID,FName) VALUES (2,'user2') INSERT INTO Who (WhoID,FName) VALUES (3,'admin1') INSERT INTO Who (WhoID,FName) VALUES (4,'admin2') INSERT INTO Who (WhoID,FName) VALUES (5,'admin3') INSERT INTO Who (WhoID,FName) VALUES (6,'Keith') INSERT INTO Who (WhoID,FName) VALUES (7,'fred') INSERT INTO Who (WhoID,FName) VALUES (8,'Joe') INSERT INTO Who (WhoID,FName) VALUES (9,'Jack') INSERT INTO Who (WhoID,FName) VALUES (10,'Doug') INSERT INTO Who (WhoID,FName) VALUES (11,'Mark') INSERT INTO Who (WhoID,FName) VALUES (12,'Al') INSERT INTO Who (WhoID,FName) VALUES (13,'Jeff') INSERT INTO Who (WhoID,FName) VALUES (14,'Brandon') INSERT INTO Who (WhoID,FName) VALUES (15,'Ashley') INSERT INTO Who (WhoID,FName) VALUES (16,'Lisa') INSERT INTO Who (WhoID,FName) VALUES (17,'Julie') INSERT INTO Who (WhoID,FName) VALUES (18,'Brian') go SET IDENTITY_INSERT dbo.Who OFF GO INSERT INTO Assoc (WhoID,AssocID) VALUES (1,4) INSERT INTO Assoc (WhoID,AssocID) VALUES (2,1) INSERT INTO Assoc (WhoID,AssocID) VALUES (2,3) INSERT INTO Assoc (WhoID,AssocID) VALUES (6,7) INSERT INTO Assoc (WhoID,AssocID) VALUES (6,8) INSERT INTO Assoc (WhoID,AssocID) VALUES (7,13) INSERT INTO Assoc (WhoID,AssocID) VALUES (8,2) INSERT INTO Assoc (WhoID,AssocID) VALUES (14,8) INSERT INTO Assoc (WhoID,AssocID) VALUES (6,15) INSERT INTO Assoc (WhoID,AssocID) VALUES (15,18) INSERT INTO Assoc (WhoID,AssocID) VALUES (15,17) INSERT INTO Assoc (WhoID,AssocID) VALUES (17,5) INSERT INTO Assoc (WhoID,AssocID) VALUES (5,1)
go create proc dbo.procCTE_Assoc @WhoID bigint AS
WITH GroupsCTE (WhoID, AssocID, FName, FNameAssoc, Lvl, Sort, GroupWithIndent) AS ( -- Anchor Who definition SELECT M.WhoID, MA.AssocID, M.FName, AssocWho.FName, 0 AS Lvl, CONVERT(varchar(2000),AssocWho.FName) AS Sort, CONVERT(varchar(255), AssocWho.FName) FROM Who M INNER JOIN Assoc MA ON M.WhoID = MA.WhoID INNER JOIN Who AssocWho ON AssocWho.WhoID = MA.AssocID WHERE M.WhoID = @WhoID UNION ALL -- Recursive Who definition SELECT g1.WhoID, MA1.AssocID, g1.FName, g2.FName, Lvl + 1, CONVERT(varchar(2000),RTRIM(Sort) + '|' + g2.FName), CONVERT(varchar(255), REPLICATE ('| ' , Lvl+1) + g2.FName) FROM Who g1 INNER JOIN Assoc MA1 ON g1.WhoID = MA1.WhoID INNER JOIN Who g2 ON g2.WhoID = MA1.AssocID INNER JOIN GroupsCTE CTE ON MA1.WhoID = CTE.AssocID )
I have two tables and I am trying to pull a value closest to another date. Â There could be instances where the Eval_date in TableA matches the date in TableB so how would that be handled?
Hi all, I am seeking your expertise to create SQL codes (SQL server 2005) that can help me to answer the problem below.
I have two tables (points and station), presented in form of SQL codes below. I€™d like to find the 6 closest panels for each of the station. As can be seen in the result table below, the 6 closest panel names are arranged from the first closest (P1) to the sixth closest (P6). Similar procedure also applies for the distance column arrangement. This distance column (D1 €“ D6) is the distance of panels P1 €“ P6 to the station. The distance between two points (with x-y coordinates) can be calculated using a simple Cartesian formula: Distance = ( (X1 €“ X2)2 + (Y1 - Y2)2 ) 0.5 . As the sample, distance between station €˜A€™ and panel €˜P19-04W€™ is = ((737606.383 - 737599.964)2 + (9548850.844 - 9548856.856)2) 0.5 = 8.79. The expected result of the work is presented in the table below:
Table 1: create table 1 ( Panels varchar(20), X_Coord float, Y_Coord float ) go set nocount on
insert into 1 values('P19-03E','737640.722','9548882.875') insert into 1 values('P19-04E','737630.166','9548868.3') insert into 1 values('P19-05E','737619.611','9548853.726') insert into 1 values('P19-06E','737609.054','9548839.15') insert into 1 values('P19-07E','737598.495','9548824.571') insert into 1 values('P19-08E','737587.941','9548809.998') insert into 1 values('P19-09E','737577.386','9548795.425') insert into 1 values('P19-10E','737563.359','9548776.163') insert into 1 values('P19-11E','737552.795','9548761.578') insert into 1 values('P19-12E','737542.256','9548746.919') insert into 1 values('P19-13E','737531.701','9548732.345') insert into 1 values('P19-14E','737521.146','9548717.772') insert into 1 values('P19-03W','737610.519','9548871.43') insert into 1 values('P19-04W','737599.964','9548856.856') insert into 1 values('P19-05W','737589.404','9548842.275') insert into 1 values('P19-06W','737578.849','9548827.702') insert into 1 values('P19-07W','737568.294','9548813.128') insert into 1 values('P19-08W','737554.274','9548793.77') insert into 1 values('P19-09W','737543.718','9548779.195') insert into 1 values('P19-10W','737533.157','9548764.614') insert into 1 values('P19-11W','737522.603','9548750.041')
set nocount off go
Table 2: create table 2 ( Station varchar(20), X_Coord float, Y_Coord float ) go set nocount on
insert into 2 values('A','737606.383','9548850.844') insert into 2 values('B','737575.41','9548806.838') insert into 2 values('C','737544.437','9548762.832')
What I'm trying to select is the closest value from a list given by a parameter or select the matched value.
declare @compare as int set @compare = 8 declare @table table ( Number int ) insert into @table values(1), (2), (3), (4), (5), (10)
If the parameter value match one of the values from the table list, select that matched one.If the value does not exist in the table list, select the closest lower value from the table list, in this case, it would be value 5.
I'd like to get some ideas for the following: I am writing a quick mini-application that searches for records in a database, which is easy enough. However, if the search term comes up empty, I need to return 10 records before the positon the search term would be in if it existed, and 10 records after. (Obviously the results are ordered on the search term column) So for example, if I am searching on "Microsoft", and it doesn't exist in my table, I need to return the 10 records that come before Microsoft alphabetically, and then the 10 that come after it. I have a SP that does this, but it is pretty messy and I'd like to see if anyone else had some ideas that might be better. Thanks!
SELECT 1, 10 UNION ALL SELECT 2, 10 UNION ALL SELECT 3, 5
- a calculated table that told me the availability for each component of the BOM, sorted by date. (each row have a plus or minus of the quantity so it can by summarized)
INSERT INTO @WhareHouseMovement (ItemID, Quantity, Date) SELECT 1, 10, '2015-03-01'
[Code] ....
My question is: how do I check when is the closest date to manufacturing? I have to check that the quantity of ALL the components of the BOM is enough to produce the product, but I can't get how to do it.
If I'm not wrong the example should give the result 2015-03-26.
Basically, the sample raw data is a result from my last cte process which consolidate all the records. I want to find the closest date in PO ended using Receipt_date column but my problem, for this ESN R9000000000019761824, i wanted to get the latest or the last transaction date.
please see below DDL and sample data.
--this is the result from my last cte process Create table #sample (ESN nvarchar(35),ESN2 nvarchar(35), Receipt_Date datetime,PO_ENDED datetime)
insert into #sample(ESN,ESN2,Receipt_Date,PO_ENDED)values('990002036924452','990002036924452','2015-01-07 17:39:44.660','2014-01-09 04:13:29.000')
-- Get the new Customer Identifier, return as OUTPUT param SELECT @NoteID = @@IDENTITY
-- Insert new notes for all the users that the note pertains to, in this case this will be by the assigned -- users. IF @FK_UserIDList IS NOT NULL EXECUTE spInsertNotesByAssignedUsers @NoteID, @FK_UserIDList
-- Insert New Address record -- Retrieve Address reference into @AddressId -- EXEC spInsertForUserNote -- @FK_UserID, --@NoteID, -- @BeenRead -- @Fax, -- @PKId, -- @AddressId OUTPUT
COMMIT TRANSACTION
-------------------------------------------------- GO
ok can someone tell me why i get two different answers for the same query. (looking for last day of month for a given date)
SELECT DATEADD(ms, - 3, DATEADD(mm, DATEDIFF(m, 0, CAST('12/20/2006' AS datetime)) + 1, 0)) AS Expr1 FROM testsupplierSCNCR I am getting the result of 01/01/2007
"Error: 8624, Severity: 16, State: 1 Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services."
I have traced this to an insert statement that executes as part of a stored procedure.
INSERT INTO ledger (journal__id, account__id,account_recv_info__id,amount)
There is also an auto-increment column called id. There are FK contraints on all of the columns ending in "__id". I have found that if I remove the contraint on account__id the procedure will execute without error. None of the other constraints seem to make a difference. Of course I don't want to remove this key because it is important to the database integrity and should not be causing problems, but apparently it confuses the optimizer.
Also, the strange thing is that I can get the procedure to execute without error when I run it directly through management studio, but I receive the error when executing from .NET code or anything using ODBC (Access).
Hey, i've written a query to search a database dependant on variables chosen by user etc etc. Opened up a new sqldatasource, entered the query shown below and went on to the test query page. Entered some test variables, everything works as it should do. Try to get it to show in a datagrid on a webpage - nothing. No data shows. SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches FROM dbo.MAKES INNER JOIN dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) ) GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2 ORDER BY count(*) DESC
Here is the page source
<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %> <asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server"> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>" SelectCommand="	SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches 	FROM dbo.MAKES INNER JOIN 				 dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN 				 dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN 				 dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN 				 dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID 	WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) ) 	GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID 	HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2 	ORDER BY count(*) DESC "> <SelectParameters> <asp:ControlParameter ControlID="DropDownList1" Name="ATT_ID1" PropertyName="SelectedValue" /> <asp:ControlParameter ControlID="TextBox1" Name="VAL1" PropertyName="Text" /> <asp:Parameter Name="ATT_ID2" /> <asp:Parameter Name="VAL2" /> <asp:Parameter Name="ATT_ID3" /> <asp:Parameter Name="VAL3" /> <asp:Parameter Name="ATT_ID4" /> <asp:Parameter Name="VAL4" /> </SelectParameters> </asp:SqlDataSource> <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>" SelectCommand="SELECT * FROM [ATTRIBUTES]"></asp:SqlDataSource> <br /> <asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource2" DataTextField="ATTRIBUTE_NAME" DataValueField="ATTRIBUTE_ID"> </asp:DropDownList> <asp:TextBox ID="TextBox1" runat="server" AutoPostBack="True"></asp:TextBox><br /> <br /> <br /> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="DERIVATIVE_ID" DataSourceID="SqlDataSource1"> <Columns> <asp:BoundField DataField="DERIVATIVE_ID" HeaderText="DERIVATIVE_ID" InsertVisible="False" ReadOnly="True" SortExpression="DERIVATIVE_ID" /> <asp:BoundField DataField="Matches" HeaderText="Matches" ReadOnly="True" SortExpression="Matches" /> </Columns> </asp:GridView> </asp:Content> AFAIK I have configured the source to pick up the dropdownlist value and the textbox value (the text box is autopostback). Am i not submitting the data correctly? (It worked with a simple query...just not with this one). I have tried a stored procedure which works when testing just not when its live on a webpage. Please help!
(Visual Web Devleoper 2005 Express and SQL Server Management Studio Express)
However, as you can see, the original select query is run twice and joined together.What I was hoping for is this to be done in the original query without the need to duplicate the original query.
I'm trying to find the command to open up an odbc conection inside sql2005 express. I only have ues of an odbc connector, we're conection to remedy. We will eventually be using stored procedures to extract the data we need from remedy and doing additional data crunching. I'm a foxpro programmer so once I get the correct syntax for making the odbc connector I shold be ok. Also I need a really good advanced book on sql2005. The type of book that would have my odbc answer. I've spent all morning trying to find this information and was unable to.
Thanks in advance
Daniel Buchanan.
If this was the wrong forum to post this on, please move this question to the correct one. I need this answer soon.
We have a issue with a MDS server that have been over us for a couple of days, the original error msg from SQL Server Engine is the one "The query processor could not produce a query plan" but the ones we get on the Excel-Addin are "Sequece contains no elements" or "The value cannot be null" T
• Using Microsoft SQL Server 2012 (SP1) - 11.0.3393.0 (X64) for 6months on this server without issues
• Two weeks ago we started to have 2 errors: "Sequence Contains No Elements" | "The Value Cannot Be Null"
• We are using the last version of Excel Add-in
• We try to reinstall the MDS feature
• If I backup/restore MDS database to other server it works
• We updated to SQL 2012 SP2 + CU4 but the error persisted ...
Looking at the MDSTraceLog we are routed to the this msg
SQL Error Debug Info: Number: 8624, Message: Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services., Server: bbdvsql03inst01, Proc: udpMetadataEntityGetDetailsXML, Line: 28
At line 28 udpMetadataEntityGetDetailsXML is calling udfMetadataEntityGetDetailsXML … and here is where we stopped
** Error found when try to get data from a entity using Excel add-in ** =================================== Sequence contains no elements ------------------------------ Program Location: Â Â at Microsoft.MasterDataServices.AsyncEssentials.AsyncResultBase.EndInvoke() Â Â at Microsoft.MasterDataServices.ExcelAddInCore.AsyncProviderBase`1.EndOperation(IAsyncResult ar)
how do I get the variables in the cursor, set statement, to NOT update the temp table with the value of the variable ? I want it to pull a date, not the column name stored in the variable...
create table #temptable (columname varchar(150), columnheader varchar(150), earliestdate varchar(120), mostrecentdate varchar(120)) insert into #temptable SELECT ColumnName, headername, '', '' FROM eddsdbo.[ArtifactViewField] WHERE ItemListType = 'DateTime' AND ArtifactTypeID = 10 --column name declare @cname varchar(30)
-- The 3rd query uses an incorrect column name in a sub-query and succeeds but rows are incorrectly qualified. This is very DANGEROUS!!! -- The issue exists is in 2008 R2, 2012 and 2014 and is "By Design"
set nocount on go if object_id('tempdb.dbo.#t1') IS NOT NULL drop table #t1 if object_id('tempdb.dbo
[code]....
This succeeds when the invalid column name is a valid column name in the outer query. So in this situation the sub-query would fail when run by itself but succeed with an incorrectly applied filter when run as a sub-query. The danger here is that if a SQL Server user runs DML in a production database with such a sub-query which then the results are likely not the expected results with potentially unintended actions applied against the data. how many SQL Server users have had incorrectly applied DML or incorrect query results and don't even know it....?
For each customer, I want to add all of their telephone numbers to a different column. That is, multiple columns (depending on the number of telephone numbers) for each customer/row. How can I achieve that?
I want my output to be
CUSTOMER ID, FIRST NAME, LAST NAME, TEL1, TEL2, TEL3, ... etc
Each 'Tel' will relate to a one or more records in the PHONES table that is linked back to the customer.
do i need to nest a query in RS if i want a calculated column to be compared against a multi value variable? It looks like coding WHERE calcd name in (@variable) violates SQL syntax. My select looked like
SELECT ... ,CASE enddate WHEN null then 1 else 0 END calcd name FROM... WHERE ... and calcd name in (@variable)
When viewing an estimated query plan for a stored procedure with multiple query statements, two things stand out to me and I wanted to get confirmation if I'm correct.
1. Under <ParameterList><ColumnReference... does the xml attribute "ParameterCompiledValue" represent the value used when the query plan was generated?
2. Does each query statement that makes up the execution plan for the stored procedure have it's own execution plan? And meaning the stored procedure is made up of multiple query plans that could have been generated at a different time to another part of that stored procedure?
