Friday, August 30, 2013

[SQL Server] I have written a Stored Procedure having 3 nested while loops in it .This is Working fine and I am getting desired result as I have tested this on small set of data ,but while running on large set of records it taking huge of amount of time, might be due to multiple nested while looping . Co

[SQL Server] I have written a Stored Procedure having 3 nested while loops in it .This is Working fine and I am getting desired result as I have tested this on small set of data ,but while running on large set of records it taking huge of amount of time, might be due to multiple nested while looping . Co

I have written a Stored Procedure having 3 nested while loops in it .This is Working fine and I am getting desired result as I have tested this on small set of data ,but while running on large set of records it taking huge of amount of time, might be due to multiple nested while looping . Co

Posted: 29 Aug 2013 11:18 PM PDT

Hi Guys, I have written a Stored Procedure having 3 nested while loops in it .This is Working fine and I am getting desired result as I have tested this on small set of data ,but while running on large set of records it taking huge of amount of time, might be due to multiple nested while looping .Could someone please help me out to optimize this SP or eliminate some while looping without affecting its business logic, or any other solution?Here is my Business Logic:Loop Calculates Number of times customer has visited that particular city in given time span (DateProvided to Till Date).Below is the Pseudo code, Sample Data and Sample results for your reference.Pseudo code:1. Select Customer in a table. Eg. There are 5 customers in a table with Id 1, 2,3,4,52. Select Each City for Particular customer. Eg . Customer whose Id is 1 travelling to 3 different cities let's say Mumbai, Delhi and Bangalore.3. Now I have to calculate the Visiting status of those Customers based on Date Provided column to till date for each city. a. If Customer visited particular city in one year from date provided to till date then M1 for each city.b. If Customer visited particular city in two year from date provided to till date then M2 for each city.C. If Customer visited particular city more than 3 year from date provided to till date then M3 for each city .Sample Date: Customer City DateProvided Eg. 1 Mumbai 12/02/2011 Delhi 07/30/2008 Delhi 05/18/2009 Bangalore 04/13/2012 Expected Result: Customer City Status1 Mumbai M2 Delhi M3 Delhi M3 Bangalore M1

Help

Posted: 30 Aug 2013 08:27 AM PDT

How to unlink a linked server?I have two database server and i just want to unlink them from each other.

Clause Group By

Posted: 30 Aug 2013 12:50 AM PDT

Hi all, hope in your help.I try this sql query:[code]SELECT [NAMES], [NUMBER]FROM [CV].[dbo].[T40]WHERE [NUMBER] = '44644'GROUP BY [NAMES], [NUMBER];[/code]the output is:[CODE]NAMES NUMBER BENCORE S.R.L. 44644BENCORES.R.L. 44644[/CODE]I need instead this other output, can you help me?[code]NAMES NUMBER BENCORE S.R.L. 44644[/code]

5nf

Posted: 29 Aug 2013 04:56 PM PDT

can anybody explain me about 5 normal form ? I have searched the internet but I am not convinced!please helpThanks and Regards

Help

Posted: 29 Aug 2013 09:55 PM PDT

I am DBA with two year of experience on SQL server 2008 R2,i have some questions that should i stick to this field or switch to BI as a SQL Developerlearning SSIS . So friends please give suggestions :-)

Sql query Help

Posted: 30 Aug 2013 06:21 AM PDT

Hello all,i have view which displays duplicate id numbers. Is there somehow i can alter my view to not pull up duplicate id's? Any help would be greatly appreciated. Thanks:-)[code="sql"]SELECT DISTINCTCAST(NAME_MASTER.ID_NUM AS VARCHAR) AS 'ID_NUM',NAME_MASTER.PREFERRED_NAME,NAME_MASTER.FIRST_NAME,NAME_MASTER.LAST_NAME,NAME_MASTER.MIDDLE_NAME,IND_POS_HIST.POS_TITLE 'POSITION_TITLE',IND_POS_HIST.POS_START_DTE,EMPL_MAST.TERMINATION_DTE,CAST(IND_POS_HIST.SUPER_ID_NUM AS VARCHAR) AS 'SUPERVISOR_ID',EMPL_MAST.UDEF_5A_1 AS 'DEPT_CODE',BIOGRAPH_MASTER.BIRTH_DTE,ADDRESS_MASTER.ADDR_LINE_1 AS 'EMAIL',mse_cardsystem_swipevalue.swipe_valueFROM NAME_MASTER INNER JOIN IND_POS_HIST ON NAME_MASTER.ID_NUM = IND_POS_HIST.ID_NUM INNER JOIN EMPL_MAST ON NAME_MASTER.ID_NUM = EMPL_MAST.ID_NUM AND IND_POS_HIST.ID_NUM = EMPL_MAST.ID_NUM INNER JOIN BIOGRAPH_MASTER ON NAME_MASTER.ID_NUM = BIOGRAPH_MASTER.ID_NUM INNER JOIN ADDRESS_MASTER ON NAME_MASTER.ID_NUM = ADDRESS_MASTER.ID_NUM INNER JOIN mse_cardsystem_swipevalue ON NAME_MASTER.ID_NUM = mse_cardsystem_swipevalue.id_num WHERE ADDRESS_MASTER.ADDR_CDE = '*EML' AND IND_POS_HIST.POS_STS = 'P' AND EMPL_MAST.ACT_INACT_STS = 'A' AND IND_POS_HIST.POS_TITLE != 'Title'[/code]
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog