Saturday, July 20, 2013

[T-SQL] Query performance

[T-SQL] Query performance

Query performance

Posted: 19 Jul 2013 06:13 AM PDT

Hi, When I add the below line of code to my stored procedure, the performance becoems very poor. Is there any other way I can do i ?AND CustNum = CASE WHEN ISNULL(@CustNum,0) = 0 THEN CustNum ELSE @CustNum ENDThanks,PSB

IF ELSE Query

Posted: 19 Jul 2013 06:35 AM PDT

I have written an IF ELSE statement into a sproc but I'd like to display the results (as in counts) from the statement in the "Results" pane after the sproc runs. How do I accomplish this?IF EXISTS ({SELECT Query}) BEGIN {INSERT Version 1} ENDELSE BEGIN {INSERT version 2} ENDThx.John

Record Being Duplicated In Join Statement

Posted: 19 Jul 2013 03:02 PM PDT

In the statement below, there is a problem where some records are occasionally duplicated. I have not been able to figure out where my problem is. When I complete the query below, it returns the correct number of records without any duplicates.[code="sql"]SELECT COUNT(*) AS Expr1FROM salesExport AS SAL LEFT OUTER JOIN tipTransactionPivot AS PIV ON SAL.rvcID = PIV.rvcID AND SAL.microsEmployeeNumber = PIV.microsEmployeeNumber AND SAL.businessDate = PIV.businessDate WHERE (SAL.businessDate = '7/10/2013')[/code]When I add the EMP table, it duplicates two of my records.[code="sql"]SELECT COUNT (*)FROM employee AS EMP1 RIGHT OUTER JOIN salesExport AS SAL ON EMP1.microsEmployeeNumber = SAL.microsEmployeeNumber LEFT OUTER JOIN tipTransactionPivot AS PIV ON SAL.rvcID = PIV.rvcID AND SAL.microsEmployeeNumber = PIV.microsEmployeeNumber AND SAL.businessDate = PIV.businessDateWHERE SAL.businessDate = '7/10/2013'[/code]I've tried a couple different approaches, but keep breaking something else...hopefully its just tired eyes. Any help is appreciated.(BTW, the actual query is not a count statement, but this returns the same problem and is significantly shorter.)

PIVOT-ing solution needed for diary viewer

Posted: 19 Jul 2013 03:48 AM PDT

Hi all,I have a table that stores "register" information, e.g. UserID, RegisterDate, Available/Unavailable, etc. but would like to display this on a month-by-month view for all members. Essentially, it would be showing the previous/current/next month name at the top, then for each member an entire list of dates within that month with a corresponding tick or cross to denote whether they were present or not.Here's my code so far:[code="sql"]CREATE TABLE #Register ( EntryID INTEGER IDENTITY(1,1) PRIMARY KEY ,EntryDate DATETIME ,EntryStatus VARCHAR(10) ,AssociatedUserID INTEGER)CREATE TABLE #Users ( UserID INTEGER ,Username VARCHAR(30))INSERT INTO #Users(UserID, Username)SELECT 1, 'Agent Smith'UNIONSELECT 2, 'The Oracle'UNIONSELECT 3, 'Neo'INSERT INTO #Register(EntryDate,EntryStatus,AssociatedUserID)SELECT '2013-07-12','X',1UNIONSELECT '2013-07-12','X',2UNIONSELECT '2013-07-12','0',3UNIONSELECT '2013-07-13','0',1UNIONSELECT '2013-07-13','0',2UNIONSELECT '2013-07-13','X',3UNIONSELECT '2013-07-14','X',1UNIONSELECT '2013-07-14','0',2UNIONSELECT '2013-07-14','0',3UNIONSELECT '2013-07-15','0',1UNIONSELECT '2013-07-15','0',2UNIONSELECT '2013-07-15','0',3UNIONSELECT '2013-07-16','X',1UNIONSELECT '2013-07-16','X',2UNIONSELECT '2013-07-16','0',3UNIONSELECT '2013-07-17','0',1UNIONSELECT '2013-07-17','X',2UNIONSELECT '2013-07-17','0',3UNIONSELECT '2013-07-18','0',1UNIONSELECT '2013-07-18','0',2UNIONSELECT '2013-07-18','0',3DECLARE @p_ActiveDate DATETIMEDECLARE @ActiveMonth INTEGER ,@ActiveYear INTEGERSELECT @ActiveMonth = MONTH(ISNULL(@p_ActiveDate,GETDATE())) ,@ActiveYear = YEAR(ISNULL(@p_ActiveDate,GETDATE())) CREATE TABLE #MonthDays ( MonthID INTEGER ,DayCount INTEGER ,[MonthName] VARCHAR(10) ,AbbrMonthName VARCHAR(3))INSERT INTO #MonthDays(MonthID, DayCount,[MonthName],AbbrMonthName)SELECT 1,31, 'January','Jan'UNIONSELECT 2, CASE WHEN @ActiveYear % 4 = 0 THEN 28 ELSE 29 END,'February','Feb'UNIONSELECT 3,31,'March','Mar'UNIONSELECT 4,30,'April','Apr'UNIONSELECT 5,31,'May','May'UNIONSELECT 6,30,'June','Jun'UNIONSELECT 7,31,'July','Jul'UNIONSELECT 8,31,'August','Aug'UNIONSELECT 9,30,'September','Sep'UNIONSELECT 10,31,'October','Oct'UNIONSELECT 11,30,'November','Nov'UNIONSELECT 12,31,'December','Dec' ;WITH cteDates (CalendarDate, DayCount, MonthID)AS ( SELECT CONVERT(DATE,CONVERT(VARCHAR(4),@ActiveYear) + '-' + CASE WHEN @ActiveMonth < 10 THEN '0' ELSE '' END + CONVERT(VARCHAR(2),@ActiveMonth) + '-' + '01') ,md.DayCount ,md.MonthID FROM #MonthDays AS md WHERE md.MonthID = @ActiveMonth UNION ALL SELECT DATEADD(DAY,1,c.CalendarDate), c.DayCount,c.MonthID FROM cteDates c INNER JOIN #MonthDays AS md2 ON( md2.MonthID = c.MonthID) WHERE DATEADD(DAY,1,c.CalendarDate) <= DATEADD(DAY,md2.DayCount-1,CONVERT(DATE,CONVERT(VARCHAR(4),@ActiveYear) + '-' + CASE WHEN @ActiveMonth < 10 THEN '0' ELSE '' END + CONVERT(VARCHAR(2),@ActiveMonth) + '-' + '01')))-- get the list of CalendarDates into a table variableSELECT *INTO #CalendarDatesFROM cteDatesDROP TABLE #RegisterDROP TABLE #UsersDROP TABLE #MonthDaysDROP TABLE #CalendarDates[/code]Now I have all the calendar dates, and subsequent registry events, I'd like to PIVOT it so that I have something like this: 1 2 3 4 5 6 7 8 9 10 ....-------------------------------------------------------------------------------------Name X 0 0 X 0 X 0 X 0 0...but unfortunately don't know how! As always, any help is gratefully received!Thanks,Kevin.

Parsing XML using SQL Server 2008

Posted: 19 Jul 2013 08:09 AM PDT

[code]DECLARE @X XML = <Movies> <Movie Name = "Titanic"> <Genres> <Genre Name="Romance"/> <Genre Name="Tragedy"/> </Genres> <Rate Value = "10"/> </Movie> <Movie Name = "ABC"> <Rate Value="15"/> </Movie> </Movies> [/code] My expected output is [code]Movie Genre ValueTitanic Romance 10Titanic Tragedy 10ABC NULL 15[/code]I hope you understood my requirement. I was able to do this when all the XML tags are present. If you have noticed, the GENRE tags for ABC movie are missing and my code is ignoring that movie. Can anyone help me on this please.Thanks in advance.

MSSQL OPENQUERY to ORACLE server

Posted: 19 Jul 2013 07:59 AM PDT

Hello.I wonder if anybody has an explanation for this:I have a MSSQL server, a database in it, and some tables. I made a openquery to get data from a remote Oracle database that I am trying to combine with some data on this server. Everything works fine, my question is about behavior. If my openquery has more than 3 WHERE clauses, it returns only one record.Like in WHERE field1='A' AND Filed2='B' AND filed3='C' is ok and WHERE field1='A' AND Filed2='B' AND filed3='C' AND 1=1 returns only one record.Thank you

How to add column and then sum

Posted: 19 Jul 2013 03:38 AM PDT

[img]http://social.msdn.microsoft.com/Forums/getfile/310366[/img]I have this same statement, can't seem to figure this out. How can I SUM the 'current amount' column by Month? I would like add an additional column and then populate it with the summed value of ' current amount' by month. So for example - January would have a summed total, Feb, etc.[code="plain"]select * from (SELECT distinct pehPErcontrol,case left(substring(pehPErcontrol,5,len(pehPErcontrol)),2) when '01' then 'January' when '02' then 'February' when '03' then 'March' when '04' then 'April' when '05' then 'May' when '06' then 'June' when '07' then 'July' when '08' then 'August' when '09' then 'September' when '10' then 'October' when '11' then 'November' when '12' then 'December' end as [UltiMonth],rtrim(eepNameLast) + ', ' + rtrim(eepNameFirst) + ' ' + coalesce(substring(eepNameMiddle,1,1) + '.', '') as Name, eepNameLast AS [Last Name],IsNull(eepNameSuffix,'') AS [Suffix],eepNameFirst AS [First Name],IsNull(eepNameMiddle,'') AS [Middle Name],pehCurAmt AS [Current Amount], pehCurHrs AS [Current Hours], pehCoID AS [Company ID], pehEEID AS [EE ID], pehEmpNo AS [Emp No], pehLocation AS [Location], pehJobCode AS [Job Code], pehOrgLvl1 AS [Org Level 1], pehOrgLvl2 AS [Org Level 2], pehOrgLvl3 AS [Org Level 3], pehOrgLvl4 AS [Org Level 4], pehPayGroup AS [Pay Group], pehProject AS [Project], pehShfShiftAmt AS [Shift Amount],pehearncode AS [Earn Code],pehIsVoided AS [IS Voided],pehIsVoidingRecord AS [Voiding Record],pehIsOvertime AS [Is Overtime]FROM EmpPers JOIN pearhist ph ON ph.pehEEID = eepEEID join WSISQL4.DASHBOARD.DBO.[OVERTIME_BUDGET_2013] ON [orglevel] = pehOrgLvl2) t right outer join WSISQL4.DASHBOARD.DBO.[OVERTIME_BUDGET_2013] ob on t.[UltiMonth] = ob.[month] and orglevel = [org level 2]where pehPerControl > '201301011' AND [EARN CODE] = '0002'AND [IS Voided] <> 'Y'AND [Voiding Record] <> 'Y' AND [Is Overtime] = 'Y' [/code]

same variable in stored proc

Posted: 19 Jul 2013 06:25 AM PDT

hiin my stored proc i have 3-4 while loop and i need to use different variable for iti m having my code under begin and end. so is there any way i can use same variable name for different while loop.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog