March 19, 2008 at 6:36 am
Hi all,
Further below is some code that creates, populates, queries and then drops a temp table. I'm trying to rewrite it as a sproc that truncates, populates and then queries a permanent table.
So I'll need to write two pieces of code, one to create the permanent table, and the other to create the actual sproc.
Thanks much!
Jaybee.
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
Create Table#ReceiptStatusHistory (ReceiptStatusHistoryId int, ReceiptId int, StatusId Int, OldStatusId Int)
Create TableTempDB.dbo.UserInteractions
(ReceiptId Int, [Order Placed Date] DateTime, [Order Placed Time] DateTime,
[Operator] VarChar(50), OldStatusId Int, StatusId Int,
[Action] VarChar(50), [Action Date] DateTime, [Action Time] DateTime,
[Severity] Int, [GBP] Float)
Declare@ReceiptId int, @ReceiptStatusHistoryId int, @StatusId int, @OldStatusId int, @MaxReceiptStatusHistoryId Int
Declare@DateEntered datetime
Declare@StartTime datetime, @EndTime datetime ,@DayBegining varchar(10),@TodayDate varchar(10)
/*Set your date range here*/
Select@DayBegining='Monday'
Set@EndTime=Convert(varchar, getdate(),101)
Set@TodayDate=DateName(dw,@EndTime)
If@DayBegining=@TodayDate
Set@StartTime=DateAdd(dd, -3, @EndTime)
Else
Set@StartTime=DateAdd(dd, -1, @EndTime)
DeclareStatusHistoryCursor ForSelect ReceiptStatusHistoryId, ReceiptId, StatusId, DateEntered
FromReceiptStatusHistory RSH With (NoLock)
JoinSecUser SU With (NoLock) on RSH.UserId = SU.UserId
WhereRSH.DateEnteredBetween @StartTime And @EndTime
OpenStatusHistory
Fetch Next From StatusHistory Into @ReceiptStatusHistoryId, @ReceiptId, @StatusId, @DateEntered
While @@Fetch_Status = 0
Begin
Set @MaxReceiptStatusHistoryId= (Select Max(ReceiptStatusHistoryId)
FromReceiptStatusHistory With(NoLock)
WhereReceiptId = @ReceiptId
AndReceiptStatusHistoryId < @ReceiptStatusHistoryId)
Set @OldStatusId=(SelectStatusId
FromReceiptStatusHistory With(NoLock)
WhereReceiptStatusHistoryId = @MaxReceiptStatusHistoryId)
Insert Into#ReceiptStatusHistory
Values(@ReceiptStatusHistoryId, @ReceiptId, @StatusId, @OldStatusId)
Fetch Next From StatusHistory Into @ReceiptStatusHistoryId, @ReceiptId, @StatusId, @DateEntered
End
CloseStatusHistory
DeAllocateStatusHistory
Insert IntoTempDB.dbo.UserInteractions
(ReceiptId, [Order Placed Date], [Order Placed Time], [Operator], [OldStatusId], [StatusId],
[Action], [Action Date], [Action Time], [Severity], [GBP])
SelecttmpRSH.ReceiptId,
Convert(VarChar, R.DateEntered, 101),
Convert(VarChar, R.DateEntered, 114),
SU.Name,
tmpRSH.OldStatusId,
tmpRSH.StatusId,
Case
When tmpRSH.StatusId = 21000 And tmpRSH.OldStatusId = 20000 Then 'Order Accepted'
When tmpRSH.StatusId = 21000 And tmpRSH.OldStatusId = 20500 Then 'POA Accepted'
When tmpRSH.StatusId = 20500 And tmpRSH.OldStatusId = 20000 Then 'POA Requested'
When tmpRSH.StatusId = 71000 And tmpRSH.OldStatusId = 20000 Then 'Order Cancelled'
When tmpRSH.StatusId = 71000 And tmpRSH.OldStatusId = 20500 Then 'Failed POA'
End As [Action],
Convert(VarChar, RSH.DateEntered, 101) As [Action Date],
Convert(VarChar, RSH.DateEntered, 114) As [Action Time],
Sum(FL.Severity) As Score,
PL.AuthAmount/PL.ExchangeRate AS GBP
From#ReceiptStatusHistorytmpRSHWith (NoLock)
JoinReceiptStatusHistoryRSHWith (NoLock)on tmpRSH.ReceiptStatusHistoryId = RSH.ReceiptStatusHistoryId
JoinSecUserSUWith (NoLock) onRSH.UserId = SU.UserId
JoinReceiptRWith (NoLock) onRSH.ReceiptId = R.ReceiptId And R.ReceiptTypeId = 0
JoinFraudLedgerFLWith (NoLock)onRSH.ReceiptId = FL.ReceiptId
JoinPaymentLedger PLOnPL.ReceiptId= R.ReceiptId
AndPL.AuthAmount > 0
AndPL.AuthDateIs Not Null
AndPL.ReturnCode= 1
WheretmpRSH.OldStatusId In (20000,20500)
Group By tmpRSH.ReceiptId, R.DateEntered, Su.Name, tmpRSH.OldStatusId, tmpRSH.StatusId, RSH.DateEntered, tmpRSH.ReceiptStatusHistoryId, PL.AuthAmount, PL.ExchangeRate
Order BytmpRSH.ReceiptId Asc, tmpRSH.ReceiptStatusHistoryId Asc
/*Remember to drop the temporary table!*/
Drop Table#ReceiptStatusHistory
SET NOCOUNT OFF
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
March 19, 2008 at 6:55 am
Hi Jay,
I'm not sure what the issue is here. You seem to know how to create a table so it's just a question of creating the table beforehand and then removing the create & drop table statements from your code and wrapping the rest of it up in a stored procedure.
Is there a reason why you'd want to use a permanent table instead of a temporary table?
Incidentally, why are you creating a permanent table in tempdb? I wouldn't have thought that is ideal. I'm referring to the table UserInteractions.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply