Friday, March 22, 2013

[T-SQL] Store Procedure Help

[T-SQL] Store Procedure Help

Store Procedure Help

Posted: 21 Mar 2013 12:17 PM PDT

Hi Guys, I need big favor. I create new table Table_Track (ID, StoreProcedureName, CreatedDate, CreatedUserId). I want to know it is possible, if any store procedure runs though Application then Insert SP Name and UserId in Table_Track that i just created.and One more question:- I want to add One new Variable in more than 100 SPs is there any way i can use any query to add all in once instead of one by one.Please let me know if my question is not clear....Any advise would be great appreciate.

Performance Problem In a Procedure

Posted: 21 Mar 2013 09:47 PM PDT

Hi,I feel sorry that in my previous post I put very limited information about my question and apologize for the same. So I am putting as much information as possible about tables, indexes, views etc.I have a procedure whose performance is pretty bad. I execute it using the following statementexec USP_ChartofAccountsDetail @numDomainID=163,@dtToDate='2013-03-21 07:21:43:563',@vcAccountId='5076,5080'I am attaching the procedure schema, table\index schema and the execution plan as vital components.On a side note, I do believe that there is a view inside the procedure having many left and right joins which are not so great but are needed when we have data distribution in a particular fashion.Please give me few inputs so that I can try making this better.RegardsChandan Jha

multiple a number with time dataType

Posted: 21 Mar 2013 10:10 PM PDT

i want to minus two time(time(7) datattype in sql 2008) datatype and multiple(hour and minute) it to 30.how can i do that?this just work for hour[code="sql"]SELECT datediff(HOUR,[EnterTime],[ExitTime] )*30 from Karmand [/code]

Calculate time difference between multiple rows

Posted: 05 Feb 2013 01:02 AM PST

Hi everyone,I'm fairly new to T-SQL and I got stuck on a query.We have a large DB with a lot of tables. From those tables I have to use 3 tables in my query which is as follows:SELECT AU.Useraccount_First_Name, AU.Useraccount_Last_Name, ST.Useraccount_Status_Type_Name, US.Useraccount_Status_DateTime, US.Useraccount_Status_IDFROM Application_Useraccounts_Status AS US INNER JOIN Application_Useraccounts AS AU ON US.Useraccount_ID = AU.Useraccount_ID INNER JOIN Application_Useraccounts_Status_Types AS ST ON US.Useraccount_Status_Type_ID = ST.Useraccount_Status_Type_IDWHERE (US.Useraccount_Status_DateTime BETWEEN @beginDatum AND @eindDatum)ORDER BY AU.Useraccount_First_Name, AU.Useraccount_Last_Name, US.Useraccount_Status_DateTimeWhich gives me a result as followsMichel Hoekzema User Login 06-12-12 15:29:32 44989Michel Hoekzema Niet beschikbaar 06-12-12 15:29:35 44988Michel Hoekzema Niet beschikbaar 06-12-12 15:29:41 44987Michel Hoekzema User Logout 06-12-12 15:29:46 44986Michel Hoekzema User Login 07-12-12 09:54:29 44953Michel Hoekzema Niet beschikbaar 07-12-12 09:54:33 44952Michel Hoekzema Niet beschikbaar 07-12-12 09:54:40 44951etc...What I need now as result is the time difference in minutes (or seconds) between the 1st and 2nd row, the 2nd and 3rd row and so on.At the end I also need to calculate the total amount of minutes per status for the whole day and this by day.To get this result I need your help to adjust the query.In attachment you will find a text file to create the test tables and fill them with data.Thank you very much for your help and assistance.Greetz,Geert

Execution Plan with Sort

Posted: 21 Mar 2013 12:57 PM PDT

I am playing around with and continuing to learn SQL. I am using the AdventureWorks2012 database with the following query to get a list of people's first and last name and job title, with their job title = Sales Representative and ordering by Last Name:SELECT p.FirstName ,p.LastName ,e.JobTitleFROM AdventureWorks2012.Person.Person p JOIN AdventureWorks2012.HumanResources.Employee e ON p.BusinessEntityID = e.BusinessEntityIDWHERE e.JobTitle = 'Sales Representative'ORDER BY p.LastNameI also created an index on each table which my query uses:USE [AdventureWorks2012]GO/****** Object: Index [IX_JobTitle_BusinessEntityID] Script Date: 3/21/2013 8:48:01 PM ******/CREATE NONCLUSTERED INDEX [IX_JobTitle_BusinessEntityID] ON [HumanResources].[Employee]( [JobTitle] ASC, [BusinessEntityID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]GO--------------------------------USE [AdventureWorks2012]GO/****** Object: Index [IX_LastName_BusinessEntityID_IN_FirstName] Script Date: 3/21/2013 8:48:52 PM ******/CREATE NONCLUSTERED INDEX [IX_LastName_BusinessEntityID_IN_FirstName] ON [Person].[Person]( [BusinessEntityID] ASC, [LastName] ASC)INCLUDE ( [FirstName]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]GOIf I run this query without the order by clause or with a column in the Employee table instead of p.LastName, I do not get a sort operator in the execution plan. When ordering by last name, I get a sort operator between the Nested Loops (Inner Join) and Select in the Execution Plan.The question becomes would there be a way to remove the sort operator and do the sorting in one of the indexes before the Nested Loops operator or by changing my query?Should I not worry about and this would be the best it could get?I am not fully sure what I am looking for within the execution plan so any help would be appreciated.Thank you.

Need Help with TSQL for Date Display

Posted: 21 Mar 2013 07:34 AM PDT

Dear Friends,Currently from the DB table I get the Date and Time in DATE TIME STAMP format 1/2/2013 12:00:00 AMBut I want to display it without the TimeStamp i.e. 1/2/2013Kind RegardsDhananjay

Find the first appointment by patient and month

Posted: 21 Mar 2013 02:54 PM PDT

I am trying write an efficient t-sql query that will return, for a specific date range, the first appointment for each patient for each month. For example, a particular patient has the folowing appointments for 1/1/2012 - 3/31/2012:Patient Name Appt Number Appt Timestamp------------- ------------- ---------------John Smith 18374832 2012-01-05 08:15:00John Smith 19837289 2012-01-07 14:30:00John Smith 19982776 2012-02-15 09:00:00John Smith 20091092 2012-02-27 15:45:00John Smith 20100939 2012-02-28 07:25:00John Smith 20110938 2012-03-05 16:50:00Given that data the query will only return:John Smith 18374832 2012-01-05 08:15:00John Smith 19982776 2012-02-15 09:00:00John Smith 20110938 2012-03-05 16:50:00Any ideas what an efficient code would be go create such a result set?Thanks in advance!BabakPhoenix, AZ

Broadcast calender generator

Posted: 21 Mar 2013 03:10 AM PDT

Hi guys,Did anyone ever have to create a function/stored procedure that would take a date as an input and generate the values for the broadcast calender like:- Media_Week_Start_Date- Media_Week- Media_Month- Media_MonthName- Media_Quarter- Media_YearThanks in advance!

Need help with recursive query

Posted: 21 Mar 2013 08:49 AM PDT

Hello,Here is my scenario. Let's say I have two tables "Car" and "CarPart". The car consist of many parts and each part can belong to mutliple cars. One complication in my case is that each part gets a new PartID, even if it's the same part, which simply belongs to a different car. This is something I have no control over, so just bear with me. Here is the script to set things up.[code="sql"]IF OBJECT_ID('Car') IS NOT NULL DROP TABLE CarCREATE TABLE Car ( CarID INT, CarName VARCHAR(16) )IF OBJECT_ID('CarPart') IS NOT NULL DROP TABLE CarPartCREATE TABLE CarPart ( PartID INT, PartName VARCHAR(16), CarID INT ) INSERT INTO CarVALUES (1, 'Chevy'), (2, 'Ford'), (3, 'Toyota'), (4, 'Honda'), (5, 'Nissan')INSERT INTO CarPart VALUES (110, 'Engine', 1), (120, 'Engine', 2), (210, 'Door', 1), (220, 'Door', 3), (310, 'Seat', 4), (320, 'Seat', 5), (410, 'Window', 3), (510, 'Wheel', 2)[/code]As you can see, the part "Engine" belongs to both "Chevy" and "Ford" and is listed twice with different IDs. Once again, this is a design limitation I have to live with. Here is what I need to accomplish. Given a car, I need to find all of the parts for this car and all of the other cars that these parts belong to. I have to continue finding parts and cars in a recursive manner until I reach the end of the chain. I tried this query:[code="sql"]DECLARE @StartCar VARCHAR(16) = 'Chevy';WITH cte (CarName, PartName)AS( SELECT c.CarName, cp.PartName FROM CarPart cp JOIN Car c ON cp.CarID = c.CarID WHERE c.CarName = @StartCar UNION ALL SELECT c.CarName, cp.PartName FROM CarPart cp JOIN Car c ON cp.CarID = c.CarID JOIN cte cte ON cp.PartName = cte.PartName)SELECT CarName, PartNameFROM cte[/code]However, it gets into an infinite loop and terminates. I would expect see the output similar to this:[b]CarName PartName[/b]Chevy EngineChevy DoorFord EngineFord WheelToyota DoorToyota WindowI appreciante any pointers.Thank you!

Performance tuning

Posted: 21 Mar 2013 07:15 AM PDT

Hi,SELECT FS.Bol#, FS.CustNum, FS.PostedDateFROM dbo.FACT_SalesAnalysis FS where ocr IN ('r') AND IsUsedForFACT <> 1 group by FS.CustNum, FS.Bol#,FS.PostedDatehaving COUNT(*) >= 1 EXCEPTSELECT FS.Bol#, FS.CustNum, FS.PostedDateFROM dbo.FACTTAbleFS whereocr IN ('c') AND IsUsedForFact <> 1 group by FS.CustNum, FS.Bol#,FS.PostedDatehaving COUNT(*) >= 1 EXCEPT SELECT FS.Bol#, FS.CustNum, FS.PostedDateFROM dbo.FACTTable FS where ocr IN ('o') AND IsUsedForFact <> 1group by FS.CustNum, FS.Bol#,FS.PostedDatehaving COUNT(*) >= 1The above query works but it is really poor performance query. Any alternatives to this ?SELECT FS.Bol#, FS.CustNum, FS.PostedDateFROM dbo.FACT_SalesAnalysis FS where ocr IN ('o') AND IsUsedForFACT <> 1 group by FS.CustNum, FS.Bol#,FS.PostedDatehaving COUNT(*) >= 1 INTERSECTSELECT FS.Bol#, FS.CustNum, FS.PostedDateFROM dbo.FACTTAbleFS whereocr IN ('r) AND IsUsedForFact <> 1 group by FS.CustNum, FS.Bol#,FS.PostedDatehaving COUNT(*) >= 1 EXCEPT SELECT FS.Bol#, FS.CustNum, FS.PostedDateFROM dbo.FACTTable FS where ocr IN ('c') AND IsUsedForFact <> 1group by FS.CustNum, FS.Bol#,FS.PostedDatehaving COUNT(*) >= 1The above query works but it is really poor performance query. Any alternatives to this ?

Update a column based on values in the same column

Posted: 21 Mar 2013 04:36 AM PDT

I am trying to update the GP%age column without a loop.I have a table with 3 columns:PostDate, Category and AmountPostDate Category Amount3/1/2013 1 Gross Sales 5003/1/2013 2 Sales Adjustments -503/1/2013 3 Freight 253/1/2013 4 Net Sales 4753/1/2013 5 COGS 3003/1/2013 5 Gross Profit 1753/1/2013 6 GP%age 03/1/2013 8 Operating Expense -503/4/2013 1 Gross Sales 10003/4/2013 2 Sales Adjustments -1003/4/2013 3 Freight 1003/4/2013 4 Net Sales 10003/4/2013 5 COGS 7003/4/2013 5 Gross Profit 3003/4/2013 6 GP%age 03/4/2013 8 Operating Expense -25I need to take '5 Gross Profit' / '4 Net Sales' and put that into '6 GP%age' grouping by day.I appreciate the help I am totally blanking on a slick solution. I am using Excel PowerPivot to pivot the data, very cool. Just need the %age. Thanks

How to select a column based on the value of another column

Posted: 21 Mar 2013 05:43 AM PDT

If I have a table that has:id | answer1 | answer2 | answer3 | answer4 | answer1 abc efg klm rst 2 2 abc efg klm rst 3How can I get the answer based on the column name e.g. for id=1 needs to retun me 'efg' because the answer is stored in answer2for id=2 needs to retun me 'klm' because the answer is stored in answer3Thank you

How to select from a temporary table using a dynamic query

Posted: 21 Mar 2013 04:51 AM PDT

How do I sleect from a populated temporary table using a dynamic querydeclare @Tmp table (.....)SET @SQLString =N'select * from @Tmp'EXECUTE sp_executesql @SQLStringI am getting : Must declare the table variable "@Tmp".
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog