Wednesday, April 17, 2013

[T-SQL] Show Results When more than just my result shows up.

[T-SQL] Show Results When more than just my result shows up.

Show Results When more than just my result shows up.

Posted: 17 Apr 2013 01:06 AM PDT

Hey Guys, I'm trying to Show All location changes in our system in the last 5 days, now all active items are stored in a table called OITM and when updated the last iteration of the item is stored in a table called AITM however this can be any change to the product, not just a location change. Which means that I can have the same location show up multiple times. The second issue is that the update date is stored on the actual item and the previous update then moves to AITM however if there are 2 changes during this period I would like them all to show up as well. Here is a [url=http://sqlfiddle.com/#!3/22869/1] SQL FIDDLE[/url] of what I have so far, otherwise I will show the tables below:[code="sql"] CREATE TABLE AITM ([ItemCode] varchar(9), [FrgnName] varchar(11), [UpdateDate] datetime, [LogInstanc] int); INSERT INTO AITM ([ItemCode], [FrgnName], [UpdateDate], [LogInstanc])VALUES ('1513360GD', 'STACK-105', '2012-12-30 00:00:00', 1), ('1513360GD', 'STACK-105', '2013-04-12 00:00:00', 2), ('SEW-3035', NULL, '2013-03-21 00:00:00', 1), ('SEW-3035', NULL, '2013-04-13 00:00:00', 2), ('SEW-3035', 'D-34-35-B-M', '2013-04-14 00:00:00', 3), ('SEW-3035', 'b-13-b', '2013-04-15 00:00:00', 4), ('SEW-3035', 'B-13-B', '2013-04-15 00:00:00', 5);CREATE TABLE OITM ([ItemCode] varchar(9), [FrgnName] varchar(6), [UpdateDate] datetime, [LogInstanc] int); INSERT INTO OITM ([ItemCode], [FrgnName], [UpdateDate], [LogInstanc])VALUES ('1513360GD', 'FW-66', '2013-04-15 00:00:00', 0), ('SEW-3035', 'B-13-B', '2013-04-16 00:00:00', 0); [/code]And here is my current code:[code="sql"]SELECT DISTINCT T0.ItemCode, T1.FrgnName as [From Location], T0.FrgnName as [To Location], T0.UpdateDateFROM OITM T0 Left JOIN (Select ItemCode, FrgnName, UpdateDate From AITM A Where LogInstanc in (Select Max(LogInstanc) From AITM B Where A.ItemCode = B.ItemCode) Group By ItemCode, FrgnName, UpdateDate) as T1 ON T0.ItemCode = T1.ItemCode WHERE ISNULL(T0.FrgnName, 0) <> ISNULL(T1.FrgnName, 0)Group By T0.ItemCode, T1.FrgnName, T0.FrgnName, T0.UpdateDateHaving Max(T0.UpdateDate) > GETDATE()-4ORDER BY 4, 1[/code]The results now show like this:[code="other"]ITEMCODE FROM LOCATION TO LOCATION UPDATEDATE1513360GD STACK-105 FW-66 April, 15 2013 00:00:00+0000[/code]I would like the results to show all changes within that period only if the location changed as such:[code="other"]ITEMCODE FROM LOCATION TO LOCATION UPDATEDATE1513360GD STACK-105 FW-66 April, 15 2013 00:00:00+0000SEW-3035 NULL D-34-35-B-M April, 13 2013 00:00:00+0000SEW-3035 D-34-35-B-M B-13-B April, 14 2013 00:00:00+0000[/code]

Stored Procedure execution with parameters

Posted: 16 Apr 2013 10:24 PM PDT

While checking our production plan cache, I noticed that over half of the entries were like the following:exec mt_amstask7 'AMS','78609072','1045458320','20130417 05:05','AMH','20130417 05:07','U'exec mt_amstask7 'AMS','78609072','1045458304','20130417 05:05','AMH','20130417 05:07','U'exec mt_amstask7 'AMS','78609072','1045458320','20130417 05:05','AMH','20130417 05:06','U'etc.Of the 20,563 cached plans, 11,449 were of this type. It is obvious to me that the only difference is the value(s) of the parameters.My question: Is it possible to execute a stored procedure with parameters using sp_executesql? I haven't been able to make this work. I've tried this:DECLARE @SQL NVARCHAR(2048);DECLARE @SQLParms NVARCHAR(2048);DECLARE @SQLParm1 INT;DECLARE @Parm1 INT;DECLARE @SQLParm2 VARCHAR(10);DECLARE @Parm2 VARCHAR(10);DECLARE @SQLParm3 DATETIME;DECLARE @Parm3 DATETIME;SET @Parm1 = 1;SET @Parm2 = 'ABC';SET @Parm3 = '1958-11-14 04:25';SET @SQL = N'TestProc @SQLParm1=@Parm1,@SQLParm2=@Parm2,@SQLParm3=@Parm3;';EXECUTE sys.sp_executesql @SQL,@SQLParms,@SQLParm1,@SQLParm2,@SQLParm3;Msg 137, Level 15, State 2, Line 1Must declare the scalar variable "@Parm1".I'm thinking the DECLAREd parameters are out of scope for the execution.Any help would be appreciated.~ Jeff

How do I use group by on one column which is having many entries?

Posted: 16 Apr 2013 08:34 PM PDT

Hi,I want to use group by on one column which is having many entriestable_a Name price AAA 12 BBB 13 AAA 0 CCC 24 AAA 0 DDD 0Now I want to find out `Name` which is having `Price` as `0`but as I'm having entries `AAA` 3 times I can't directly write simple sql with condition `NOT Equal to 0`Please help me I want to print result for above table_a should beonly `D` as it is having `0` as `price`.

Compare date?

Posted: 16 Apr 2013 04:03 PM PDT

Hi All,Is it possible to copare dete in format 10:30 AM.for example i want to find out the date which is in between 10:30 AM to 11:30 AM.Thanks

loading massive files

Posted: 16 Apr 2013 06:57 AM PDT

I'm loading server large data files into a db. Some records in some table have carrage returns in the data which is acting like an end of line for the import process. does anyone know a way around this? i'm using bcp

Help with SQL code

Posted: 16 Apr 2013 05:13 AM PDT

Hello,I have my data in the below format but this is just a sample(but I am looking at data for the last 6 months):CCDATA ||||CCDATAcompletedTIme54 ||||2013-04-16 13:56:0052 ||||2013-04-16 13:55:0017 ||||2013-04-16 13:55:0011 ||||2013-04-16 13:55:0036 ||||2013-04-16 13:55:0073 ||||2013-04-16 13:55:0080 ||||2013-04-16 13:55:0047 ||||2013-04-16 13:55:0022 ||||2013-04-16 13:55:00235 ||||2013-04-16 13:55:0049 ||||2013-04-16 13:55:0016 ||||2013-04-16 13:55:0034 ||||2013-04-16 13:55:0072 ||||2013-04-16 13:55:0043 ||||2013-04-16 13:55:0050 ||||2013-04-16 13:55:001 ||||2013-04-16 13:55:0064 ||||2013-04-16 13:55:0081 ||||2013-04-15 13:54:0014 ||||2013-04-15 13:54:00103 ||||2013-04-15 13:54:0093 ||||2013-04-15 13:54:0031 ||||2013-04-15 13:54:001022 ||||2013-04-15 13:54:0020 ||||2013-04-15 13:54:0064 ||||2013-04-15 13:54:00142 ||||2013-04-14 13:53:0026 ||||2013-04-14 13:53:0023 ||||2013-04-14 13:53:009 ||||2013-04-14 13:53:0032 ||||2013-04-14 13:53:00991 ||||2013-04-14 13:53:0024 ||||2013-04-14 13:53:00123 ||||2013-04-14 13:53:0026 ||||2013-04-14 13:52:00113 ||||2013-04-13 13:51:0014 ||||2013-04-13 13:51:0066 ||||2013-04-13 13:51:0083 ||||2013-04-13 13:51:0027 ||||2013-04-13 13:51:0023 ||||2013-04-13 13:51:0040 ||||2013-04-13 13:51:0031 ||||2013-04-12 13:51:001 ||||2013-04-12 13:49:0066 ||||2013-04-12 13:48:0033 ||||2013-04-12 13:45:0049 ||||2013-04-12 13:45:0018 ||||2013-04-12 13:45:00133 ||||2013-04-12 13:45:0033 ||||2013-04-11 13:45:00I need to convert it to the following format:WEEK_Number ||MOnday_Averages||Tuesday_Averages||Wednesday_Averages||Thursday_Averages||Friday_Averages||Saturday_Averages||Sunday_AveragesIs this possible?Thanks for your help in advance

Help to get record for MAX REV ID

Posted: 16 Apr 2013 06:07 AM PDT

Hi friends,I ave 3 tables-1) JP,2) WO, the JP and WO are rellated by column : JP IDNow the problem Im facing is that a JP can have mutiple WOs example-JP1234 , WO67,WO69 and each WO has a revision # - say REV#1, and REV#2 and per my business requirement I want to get the WO with the highest REV# FROM JP -when I join these tables but that is not happening-below is the Left Inner Join I'm using---FROM JP-join on [WO] with [JP] left outer join [WorkForce_WorkOrder] on [JP].[JP_ID] = [WO].[JP_JP]THANKSdHANANJAY

Store proc Help

Posted: 16 Apr 2013 05:19 AM PDT

Hi, I have written a procedure to update and Insert data by checking row by row. Unfortunately the query updating the records but not inserting the new data. Could you please help to modify the query.I have to update and Insert the data.-------------------------------------------------------------------ALTER PROCEDURE [dbo].[InsUpd_AppId]AS BEGINDECLARE @MaxRow INTDECLARE @Count INTDECLARE @ServerName NVARCHAR(510)DECLARE @AppCount INTDECLARE @ACount INTDECLARE @ApplicationId NVARCHAR(510)SET @MaxRow =( SELECT MAX(ID) FROM dbo.LUT2013 )SET @Count = 1WHILE (@Count <= @MaxRow)BEGIN SELECT @ServerName = [SERVER NAME] ,@ApplicationId = [Application ID] FROM dbo.LUT2013 WHERE LUT2013.Id = @Count IF EXISTS(SELECT 1 FROM dbo.ApServer WHERE [ServerName] = @ServerName AND @ApplicationId IS NULL) BEGIN SELECT ID = IDENTITY(INT,1,1),* INTO #temp FROM ApServer WHERE [ServerName] = @ServerName SELECT @AppCount = (SELECT MAX(tmp.ID) FROM #temp tmp) SET @ACount = 1 WHILE(@ACount <= @AppCount) BEGIN IF(@ACount = @AppCount) BEGIN UPDATE LUT2013 SET LUT2013.[Application ID] = tmp.[Application ID] FROM LUT2013 INNER JOIN #temp tmp ON tmp.[ServerName] = LUT2013.[Server Name] AND tmp.ID = @AppCount AND LUT2013.[Application ID] IS NULL END ELSE BEGIN INSERT LUT2013([Server Name] ,[Application ID] ) SELECT [ServerName] ,[Application ID] FROM #temp WHERE ID = @ACount END SET @ACount = @ACount + 1 DROP TABLE #temp END END --To iterate while Loop SET @Count = @Count + 1 ENDEND ------------------------------------------------------------------

UniqueIdentifiers

Posted: 06 Jan 2011 05:49 AM PST

We have the "update" table with the clients' "client_fk" (format 'ntext') number that relates to the "client_pk" (format 'uniqueidentifier") number residing in the "client_demo" table. When I do a query and try join the tables on these two, I am told that I cannot use the ntext field in a comparison with '=' and it is not compatible with the uniqueidentifier. The actual raw string in the fields are the same. Is there any way around this ?Thank you !

Practical Used Of XML Path

Posted: 16 Apr 2013 03:58 AM PDT

Hi all experts,Every passing day i am learning new stuff in SQL Server. Since last few days i am learning XML with SQL Server. I am able to query SSMS for XML , if a problem statement is given to me.I am not able to exactly figure it out what would be practical used of XML with SQL Server.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog