Thursday, September 19, 2013

[T-SQL] how to write a stored procedure for this

[T-SQL] how to write a stored procedure for this

how to write a stored procedure for this

Posted: 18 Sep 2013 09:44 PM PDT

i have a table emp with columns empname and highprority jobs.for example i have 3 emp and 10 high priority jobs(assume that high priority jobs are from h1- h10).I want to make sure to assign highpriority jobs in such a way that each employee must assign same no of jobs,( for example ,tsaliki has here 2 high priority jobs,sasi 2 and srinivas 1 .SO while inserting the next high priority job first it should assign the high priorty job h6 to srinivas since he has low high priorrity jobs compared to others.likewise everytime while assigning the jobs to each employee i want to make sure that all employess must have same no of high priority jobs or just 1 more .Becasue here there are 10 high priority jobs.So ultimately my goal is to assign 3 high priority jobs to each emp and the other can be assigned to any emp since all other 3 has same high proroty jobs.The records of emp table are as follows-empname highprorityjostsaliki h1sasi h2Srinivas h3 tsaliki h4sasi h5.

Help to get my desired output using stored procedure

Posted: 18 Sep 2013 09:13 PM PDT

i have a table emp with columns empname and highprority jobs.for example i have 3 emp and 10 high priority jobs(assume that high priority jobs are from h1- h10).I want to make sure to assign highpriority jobs in such a way that each employee must assign same no of jobs,( for example ,tsaliki has here 2 high priority jobs,sasi 2 and srinivas 1 .SO while inserting the next high priority job first it should assign the high priorty job h6 to srinivas since he has low high priorrity jobs compared to others.likewise everytime while assigning the jobs to each employee i want to make sure that all employess must have same no of high priority jobs or just 1 more .Becasue here there are 10 high priority jobs.So ultimately my goal is to assign 3 high priority jobs to each emp and the other can be assigned to any emp since all other 3 has same high proroty jobs.The records are as follows-empname highpriorityjobs tsaliki h1,h2sasi h3,h4 srinivas h5

How to retrieve integer and decimals from string

Posted: 18 Sep 2013 11:10 PM PDT

Hi,I would like to know how to retrieve integer,decimals from string in table format.Input string: < 2% annual < 0.16% monthlyInput string: < 5% annual < 0.41% monthlyInput string: > 3% annual > 0.25% monthlyInput string: > 3% annual > 25% monthlyExpected output:Num1 Num2-------------2 0.165 0.413 0.253 25Create table script:create table tbl_RetrieveNumbers( Samplestring varchar(100))insert into tbl_RetrieveNumbers values('< 2% annual < 0.16% monthly')insert into tbl_RetrieveNumbers values('< 5% annual < 0.41% monthly')insert into tbl_RetrieveNumbers values('> 3% annual > 0.25% monthly')insert into tbl_RetrieveNumbers values('> 3% annual > 25% monthly')Thanks,Kumar.

how to get the desired output correctly

Posted: 18 Sep 2013 11:29 PM PDT

i have a table emp with records as follows:Create table testemp(empname varchar(50),highprtjobs varchar(50))insert into testemp values ('tsaliki','h1')insert into testemp values ('tsaliki','h4')insert into testemp values ('sasi','h2')insert into testemp values ('sasi','h5')insert into testemp values ('srinivas','h3')select * from testempin this i wrote a select statement as follows:select empname,count(highprtjobs)as noofhighprtjobs from testemp group by empname. i got the result as empname noofhighprtjobssasi 2srinivas 1tsaliki 2In this now i am trying to get the one which is having min noofhighprtjobs( here srinivas result i should get it as output) So i wrote the above statement but it is throwing some error.So how do i get the empname whose noofhighprtjobs is minimum.select empname,count(highprtjobs)as noofhighprtjobs from testemp group by empnamehaving min(count(highprtjobs))

Get the Maximum Value from a related table based on date

Posted: 18 Sep 2013 10:12 PM PDT

Hi Folks,Can someone help with the following on SQL Server 2008...I have a table Customer and an Orders Table...I want to return back against each customer the Order number from the last order placed (highest OrderDate)...For example, if Customer X has 3 orders and Customer Y has 3 orders, I want X and Y customer names returned along with the the order number form each customer's last placed order.Setup script for the 2 tables plus data as follows...SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[Customer]( [CustomerID] [int] NOT NULL, [CustomerName] [varchar](50) NULL, CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED ( [CustomerID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFFGOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[Orders]( [OrderID] [int] NOT NULL, [OrderDate] [datetime] NULL, [OrderNumber] [varchar](50) NULL, [CustomerID] [int] NULL, CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED ( [OrderID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFFGOINSERT INTO [Customer] ([CustomerID],[CustomerName])VALUES (1,'David'),(2,'John'),(3,'Sue')GOINSERT INTO [Orders] ([OrderID],[OrderDate],[OrderNumber],[CustomerID])VALUES(1, '2013-01-01', '0001', 1),(2, '2013-01-03', '0002', 1),(3, '2013-02-12', '0003', 1),(4, '2013-01-17', '0004', 2),(5, '2013-01-18', '0005', 2),(6, '2013-02-01', '0006', 2),(7, '2013-01-14', '0007', 3),(8, '2013-01-21', '0008', 3)GOWhat I'd Like back is...'David', '0003''John', '0006''Sue', '0008'So far I've got...SELECT A.[CustomerName], ISNULL(X.[MaxOrderNo], '') AS [LastOrderNumber]FROM [Customer] ALEFT OUTER JOIN (SELECT A.[CustomerID], ISNULL(MAX(A.[OrderNumber]), '') AS [MaxOrderNo] FROM [Orders] A GROUP BY A.[CustomerID]) X ON X.[CustomerID] = A.[CustomerID]However, although this gives the correct result with the current data, I need to check that the value from the order returned = the last order date as I can't guarentee that the highest order number for a customer has the highest date - this can be demonstrated by swapping the dates round on the last 2 orders (my query would give the wrong result)...Many thanks for any help :-)

T-SQL help

Posted: 18 Sep 2013 03:19 AM PDT

Hi,MyTotUnits field should get updated from field Units based on the condition below : When Project ,Product ( UC only) and (LIKE) SSum (P-110 from ID = 1 IS CONTAINED IN ID = 2 FOR THE SAME PROJECT AND Product . ANd SNum for ID = 2 contains for characters than SNum for ID =1 ) then Units = 430 should be updated to MyTotUnits from ID =2 CREATE TABLE #temptable ( ID INT IDENTITY(1,1),Project VARCHAR(10),Product VARCHAR(20) ,SNum VARCHAR(20),Units FLOAT , MyTotUnis FLOAT)INSERT INTO #temptable (Project ,Product ,SNum ,Units , MyTotUnis)SELECT 'P1','xxx','P-110',20,0UNIONSELECT 'P1','xxx','P-110',30,0UNIONSELECT 'P1','UC','P-110',40,0UNIONSELECT 'P1','xxx','P-110_ABC',40,0UNIONSELECT 'P1','xxx','P-110_ABC',40,0UNIONSELECT 'P1','xxx','P-110_ABC',40,0UNIONSELECT 'P1','UC','P-110_ABC',430,0UNIONSELECT 'P2','xxx','P-123',40,0UNIONSELECT 'P2','xxx','P-123',70,0SELECT * FROM #temptableDROP TABLE #temptable

Sql Query Output Join with another table

Posted: 18 Sep 2013 09:36 AM PDT

I have a query which gives me the following output : [code="sql"]select PD.ProductId, TotalCalls = COUNT(DISTINCT PD.LogId), TrueCalls = COUNT(DISTINCT case when PD.ExceptionCode = ' ' then PD.LogId END), ErrorCalls =COUNT(DISTINCT case when PD.ExceptionCode != ' ' then PD.LogId END), PassPercentage = CONVERT(DECIMAL(10,1),100 - (CAST(COUNT(DISTINCT case when PD.ExceptionCode != ' ' then PD.LogId END) as float)/CAST(COUNT(PD.LogId) as float)*100)) from Log P INNER JOIN LogProduct PD ON P.LogId = PD.LogId WHERE (ResponseTime < '2013-09-28' and RequestTime > '2013-09-01') Group By PD.ProductId[/code]It gives me the following output : [code="other"]ProductId TotalCalls TrueCalls ErrorCalls PassPercentage1 6 6 0 100.02 1 0 1 85.73 33 15 18 92.2[/code]Now I have another Table : Levels :[code="other"]LevelId Min Max Bool ProductId1 100 100 0 22 80 99 0 23 60 79 0 24 40 59 0 25 1 39 1 26 0 0 0 27 -1 -1 0 21 100 100 0 12 80 99 0 13 60 79 1 14 40 59 0 15 1 39 0 16 0 0 0 17 -1 -1 0 1[/code]What I would like to do is compare the output of the first query and add a new LevelId column : example : I am looking for an output like this : [code="plain"]ProductId TotalCalls TrueCalls ErrorCalls PassPercentage LevelId1 6 6 0 100.0 12 1 0 1 85.7 2[/code]The logic here is that : I would like to compare the PassPercentage for each row for that particular product and find out which level it falls in . In the example above : PassPercentage is 85.7 for product 2 . If you check the Levels table above for ProductId 2 ,Level 2 should be chosen as 80 < 87.5 < 99I cannot figure out How I can do this..Please let me know how I go forward from here ... or give me ideas of what I ought to do ??

Adding column if not exist with value

Posted: 18 Sep 2013 08:15 AM PDT

Hi all, using the below, how do I set the DBVersion to 1 with this line, if the column DBVersion exist I don't want to change the number but if it doesn't exist I want to create it and have DBVersion set to 1if not exists (select * from syscolumns where id=object_id('ConfigTB') and name='DBVersion') alter table ConfigTB add DBVersion INTThanks

SQL - Find Week Nbr

Posted: 18 Sep 2013 02:42 AM PDT

Hello all, First let me say thank you for the tips/tricks/best practices that I have gleaned by reading the forums. I have learned so much!!!My issue is I need to determine a week number. Hopefully I'll explain it succintly. I have a jobtask table. Each job has a schedule start date. Using the schedule start date I need to find the week nbr (tnbr). Jobs scheduled in current week are T00, next week T01, etc. Weeks run from Sunday 00:00 to Saturday 23:59. I am doing this for a different report in a stored procedure using a cursor. After having been reading the forums it occurred to me that maybe I don't need to use a cursor. I've included pared-down table and query for brevity sake. --===== If the test table already exists, drop it IF OBJECT_ID('TempDB..#jobtask','U') IS NOT NULL DROP TABLE #jobtask--===== Create the test table with CREATE TABLE #jobtask ( jobnbr varchar(8), tasknbr varchar(3), schstrdt datetime )INSERT into #jobtask (jobnbr, tasknbr, schstrdt)SELECT '12500173','500','2013-09-18 07:30:00.000' UNION ALLSELECT '13502889','500','2013-09-25 08:00:00.000' UNION ALLSELECT '13506023','0','2013-09-20 07:30:00.000' UNION ALLSELECT '13506779','0','2013-09-26 07:30:00.000' UNION ALLSELECT '13506780','0','2013-09-29 07:30:00.000' UNION ALLSELECT '13510071','500','2013-09-18 07:30:00.000' UNION ALLSELECT '13510263','500','2013-09-23 07:30:00.000' UNION ALLSELECT '13510321','500','2013-09-25 07:30:00.000' UNION ALLSELECT '13510331','500','2013-09-25 07:30:00.000' UNION ALLSELECT '13510527','500','2013-09-30 00:00:00.000' UNION ALLSELECT '13510578','500','2013-10-02 00:00:00.000' UNION ALLSELECT '13510733','500','2013-09-18 07:30:00.000' UNION ALLSELECT '13510887','500','2013-09-18 07:30:00.000' UNION ALLSELECT '13510945','500','2013-09-18 07:30:00.000' UNION ALLSELECT '13510983','500','2013-09-23 00:00:00.000' Here is what the data should look like.jobnbr task schstrdt tnbr12500173 500 09/18/2013 07:30 T0013502889 500 09/25/2013 08:00 T0113506023 0 09/20/2013 07:30 T0013506779 0 09/26/2013 07:30 T0113506780 0 09/29/2013 07:30 T0213510071 500 09/18/2013 07:30 T0013510263 500 09/23/2013 07:30 T0113510321 500 09/25/2013 07:30 T0113510331 500 09/25/2013 07:30 T0113510527 500 09/30/2013 00:00 T0213510578 500 10/02/2013 00:00 T0213510733 500 09/18/2013 07:30 T0013510887 500 09/18/2013 07:30 T0013510945 500 09/18/2013 07:30 T0013510983 500 09/23/2013 00:00 T01Thanks in advance and please let me know if something isn't clear.cmw
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog