Friday, June 14, 2013

[T-SQL] Help with Locking Tables for Stored Proc

[T-SQL] Help with Locking Tables for Stored Proc

Help with Locking Tables for Stored Proc

Posted: 13 Jun 2013 11:56 PM PDT

Instead of placing the database into single user mode, while we load new data, I am trying to figure out the SQL command: SET TRANSACTION ISOLATION LEVEL, but there is not a lot of information on the MS page. (only one example)What I would like to do is put the locks in place (so we can ovoid the deadlocks we are getting with users during the load process, since it always kills the stored proc).From what I am reading.. the SQL would look something like..SET TRANSACTION ISOLATION LEVEL TABLOCK HOLDLOCKGOBEGIN TRANSACTION(CURRENT SQL CODE)END TRANSACTIONIs that correct?? Wouldn't I also need to change the transaction isolation back to normal?

Need help to create a column, add values and then compare with another table

Posted: 14 Jun 2013 12:50 AM PDT

Hi allI need some basic help in adding values to a column in a table(like making a key in EXCEL) and making a similar key in a different table, and then compare which rows are present in one and not in another table and vice versa. For example, one table contains values:ANUM BNUM STARTDATE DURATION260975649803 0965000066 2013-04-20 00:02:55 49260976522522 0977144491 2013-04-20 10:23:35 161260977313710 0977809127 2013-04-20 07:21:48 272260977206661 0976320516 2013-04-20 20:39:36 51260977948010 0966952330 2013-04-20 00:34:04 55260979121229 0977261566 2013-04-20 00:33:25 13Note that all field types are varchar. I want to make a key which will give me substring(ANUM,4,10) &"_"& substring(BNUM,2,10) &"_"& only the HHMM of the STARTDATE &"_"& DURATION.i have altered the same table and created a column "KEY_IN" with varchar(50) but need help in populating the values from the relevant column fields into it. My expected result for the above sample rows should look like:ANUM BNUM STARTDATE DURATION KEY_IN260975649803 0965000066 2013-04-20 00:02:55 49 975649803_965000066_00:02_49 260976522522 0977144491 2013-04-20 10:23:35 161 976522522_977144491_10:23_161260977313710 0977809127 2013-04-20 07:21:48 272 977313710_977809127_07:21_272260977206661 0976320516 2013-04-20 20:39:36 51 977206661_976320516_20:39_51 Thanks in anticipation!

Need help in soling a SQL issue

Posted: 14 Jun 2013 12:49 AM PDT

Hi ,I am trying to get the schedule for a resource. It may have multiple weekly schedules & onetime schedule.If it is onetime schedule it always take reference over weekly.But while checking the records for a resource we have to make sure there exists at least one row for IsResSchedule = 1.CREATE TABLE [dbo].[W_O_Schedule]( [LocationId] [int] NOT NULL, [resourceid] [int] NOT NULL, [availability] [varchar](11) NOT NULL, [ScheduleRepeat] [varchar](7) NOT NULL, [StartTime] [datetime] NULL, [EndTime] [datetime] NULL, IsResSchedule int ) ON [PRIMARY]GOInsert into [W_O_Schedule]Values--Only row 1 and 2 from here (1, 2, 'Available', 'Weekly', '2000-01-03 09:00:00', '2000-01-03 12:00:00',0),(1, 2, 'Available', 'Weekly', '2000-01-03 13:00:00', '2000-01-03 17:00:00',0),(1, 2, 'Available', 'Weekly', '2000-01-03 09:00:00', '2000-01-03 17:00:00',1),--From the below 2 I wasnt only row with 'Unavailable' (1,4, 'Available', 'Weekly', '2000-01-03 09:00:00', '2000-01-03 17:00:00',1),(1,4, 'Unavailable', 'OneTime', '2013-06-03 00:00:00', '2013-06-04 00:00:00',0),--Nothing from here since IsResSchedule = 0 for both rows (1,5, 'Available', 'Weekly', '2013-06-03 00:00:00', '2013-06-03 20:00:00',0),(1,5, 'Available', 'OneTime', '2013-06-03 00:00:00', '2013-06-03 30:00:00',0)--If I want to get schedule for '2013-06-03' for resource 2, 4 & 5--My result sould be(1, 2, 'Available', 'Weekly', '2000-01-03 09:00:00', '2000-01-03 12:00:00',0)(1, 2, 'Available', 'Weekly', '2000-01-03 13:00:00', '2000-01-03 17:00:00',0)(1,4, 'Unavailable', 'OneTime', '2013-06-03 00:00:00', '2013-06-04 00:00:00',0)

Need help with an insert

Posted: 13 Jun 2013 06:45 AM PDT

Hello,I am trying to insert into a log table that stores the reason behind the non execution of a stored proc. The stored proc runs only if a specific condition is met and for that to execute I am using an if condition. Now the log table comes into picture in the else part and here is what I have in my code for logging:[code="sql"]else select @ID1=ID from [CBH] where [SID] = 2select @ID2=ID from [PBH] where [SID] = 2INSERT into dbo.Logtable ( AdditionalInfo ) values('Another Process with ID:'+@ID1+'is currently in use')INSERT into dbo.Logtable ( AdditionalInfo ) values('Another Process with ID:'+@ID2+'is currently in use')ENDend[/code]But what I really want to do is:If @ID1 exists then insert that value in to the logtableand if @ID2 exists then insert that value in to the logtable.If both @ID1 and @ID2 exists then insert both into the logtable with the same text.Please note that the @ID1 and @ID2 are of type bigint and I am trying to insert that along with the text into the log table.Thanks for your help on this.

Work out 2nd, 3rd, 4th Puchases

Posted: 13 Jun 2013 09:49 PM PDT

Hey guys,I am querying a database to find out the dates of customers 1st, 2nd, 3rd, 4th purchases.I am so far querying the first transaction (using MIN) and the most recent transaction (using MAX).My question is: how can I query the database further to get the 2nd, 3rd and 4th purchases etc?My query is this:SELECT mem.MemberID, FirstName + ' ' + LastName AS 'Customer', MIN(TransactionDate) AS 'First Transaction', MAX(TransactionDate) AS 'Most Recent Transaction', COUNT(*) AS 'Total Number Of Purchases'FROM Transaction t INNER JOIN Members m ON t.MemberId = m.MemberIdGROUP BY mem.MemberId, FirstName + ' ' + LastNameHAVING NOT FirstName + ' ' + LastName = '' --where members name has not been provided AND COUNT(*) > 1 AND MIN(TransactionDate) > '2011-01-01'ORDER BY COUNT(*) DESCAll advice will be greatly appreciated.Thanks,Dan

Ranking and Summing

Posted: 13 Jun 2013 11:01 AM PDT

Any help would be appreciated. I need to sum per product type and then I need to also sum depending on whether the product ranking is good or bad.I could probably do this with multi-stage temporary table type processing but I imagine a single select could it as well. I just can't imagine the specifics.So total fruit sales was 139. Good fruit sales was 84. So 60% of fruit sales was good, 40 bad.Desired results set is like this:Fruits Good Grade 60Fruits Bad Grade 40Vegetables Good Grade 80Vegetables Bad Grade 20Dairy Good Grade 38Dairy Bad Grade 61CREATE TABLE #Products( ProductID int, ProductType char(10), ProductSales int, ProductRank int) INSERT INTO #Products ( ProductID, ProductType, ProductSales,ProductRank) VALUES (1,'Fruits',55,3)INSERT INTO #Products ( ProductID, ProductType, ProductSales,ProductRank) VALUES (2,'Fruits',57,5)INSERT INTO #Products ( ProductID, ProductType, ProductSales,ProductRank) VALUES (3,'Fruits',27,6)INSERT INTO #Products ( ProductID, ProductType, ProductSales,ProductRank) VALUES (4,'Vegetables',67,1)INSERT INTO #Products ( ProductID, ProductType, ProductSales,ProductRank) VALUES (5,'Vegetables',46,5)INSERT INTO #Products ( ProductID, ProductType, ProductSales,ProductRank) VALUES (6,'Vegetables',73,5)INSERT INTO #Products ( ProductID, ProductType, ProductSales,ProductRank) VALUES (7,'Vegetables',124,9)INSERT INTO #Products ( ProductID, ProductType, ProductSales,ProductRank) VALUES (8,'Vegetables',27,6)INSERT INTO #Products ( ProductID, ProductType, ProductSales,ProductRank) VALUES (9,'Dairy',5,7)INSERT INTO #Products ( ProductID, ProductType, ProductSales,ProductRank) VALUES (10,'Dairy',97,3)INSERT INTO #Products ( ProductID, ProductType, ProductSales,ProductRank) VALUES (11,'Dairy',56,8)SELECT CASE WHEN ProductRank >= 5 THEN 'Good Grade' WHEN ProductRank < 5 THEN 'Bad Grade' END AS Grade, * FROM #ProductsDROP TABLE #Products

PatIndex or CharIndex

Posted: 13 Jun 2013 09:52 AM PDT

Hi, can someone help me with PatIndex or CharIndex?I have a table belowselect 2011 as Year, 1111 as Count into #tinsert into #t select 2012, 2222 insert into #t select 2013, 3333-- Year is smallint typeselect * from #t where Year in (2011,2012)Can I use PatIndex or CharIndex or any other ways to do same thing as above result using parameter @Year below?declare @Year varchar(50)set @Year = '2012,2011'select * from #t where Year ......
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog