February 7, 2013 at 12:23 pm
Hey guys,
I have a Merge script which seeds data first time into a fact table. I have to design a script which checks for updates every day at midnight from the previous day and loads data using this merge script. I need help in designing a loop where the updates from the source data set are checked for a day using @currentday and then a date range say @startdate and @enddate is created using @currentDay. After the load is complete the @currentday is incremented and the loop runs to check if there are more updates. This script will be run everyday.
The following is the first time seeding script and I tried to simplify the best I could
I would appreciate your help.
Thank you.
-- truncate table factRegistration
declare @startDate as Datetime, @endDate as Datetime, @lastRunDate as datetime
select @lastRunDate = lastrundate from dbo.reportingJobs where JobCode = 'factRegistration' and active = 1
if ( @@rowcount = 0 )
Begin
print 'Job has not been defined or has been inactivated.'
return
end
select @lastRunDate = dateadd(ms, 3, isnull(@lastRunDate, '12/31/1989 23:59:59.997')) -- To start from begining and add a secon
select @startDate = dateadd(d, -1 , convert(datetime, convert(varchar, @lastRunDate, 101) )) , @endDate = dateadd(ms, -3 , convert(datetime, convert(varchar, GETDATE(), 101) ))
set @startDate = '1/1/1900'
set @endDate = '12/19/2012 23:59:59.997'
if ( @startDate >= @endDate )
Begin
return
end
merge into factRegistration fr
using (
select
od.OrderDetailID, od.eventID, od.pid
, case when isnull(sh.status, od.itemStatus) in ( @Registered, @Completed ) then 1 else 0 end isCurrentParticipant
, case when isnull(sh.status, od.itemStatus) = @Withdrawn then 1 else 0 end isWithdrawn
, case when isnull(sh.status, od.itemStatus) = @Transferred then 1 else 0 end isTransferred
, e.eventdate
, case when isnull(sh.status, od.itemStatus) in ( @Completed ) then 1 when isnull(sh.status, od.itemStatus) = @Registered and od.itemstatus = @Completed then 1 when isnull(sh.status, od.itemStatus) = @Registered then 0 else NULL end hasCompleted
, lastRegSH.firstRegistrationDate firstRegistrationDate
, isnull(sh.createdate, od.createdate) currentStatusDate
, lastRegSH.lastRegistrationDate
from dbo.orderdetails as od
left outer join ( select orderdetailid, min(createdate) firstRegistrationDate, max(createdate) lastRegistrationDate from dbo.orderdetailstatushistory where status = @Registered and orderdetailid in ( select distinct OrderDetailID from dbo.orderdetailstatushistory where createdate between @startdate and @endDate and eventid is not null and pid is not null ) group by orderdetailid ) as lastRegSH
on od.OrderDetailID = lastRegSH.orderdetailID
left outer join (
select * from (
select RANK() over( partition by sh.orderdetailid order by sh.orderdetailid, sh.createdate desc) lastStatus, sh.* from ( select * from dbo.orderdetailstatushistory where createdate between @startdate and @endDate and eventid is not null and pid is not null ) as sh join dbo.orderdetails od on sh.orderdetailid = od.orderdetailID
) as sh where lastStatus = 1
) as sh on od.OrderDetailID = sh.OrderDetailID
left outer join dbo.events e on od.eventID = e.eventid
left outer join dbo.centers c on e.centerid = c.centerid
left outer join dbo.products p on e.programid = p.productid
left outer join dbo.productMiscellaneousData pmo on p.productid = pmo.productID
and pmo.datatypeid in ( select cr.codeReferenceID from dbo.codeReferences cc join dbo.codeReferences cr on cc.codeReferenceid = cr.codeCategoryID and upper(cr.shortdescription) = 'INT' and cc.shortdescription = 'DATATYPE' and isnull(cc.codeCategoryID ,0 ) = 0 )
and pmo.codeID in ( select cr.codeReferenceID from dbo.codeReferences cc join dbo.codeReferences cr on cc.shortdescription = 'PROGMISCOF' and cc.codeReferenceID = cr.codeCategoryID and cr.shortdescription = 'STARTREPOR' )
left outer join dbo.events fe on sh.xferFromEventID = fe.eventid
left outer join dbo.events te on sh.xferToEventID = te.eventid
left outer join dbo.codeReferences crOutType on crOutType.codeReferenceID = sh.xferWithdrawReasonID
left outer join dbo.products rp on e.programid = rp.productid
left outer join dbo.productGroupCodes rpgc on rp.productGroupID = rpgc.productGroupCodeID
left outer join dbo.codeReferences cc on cc.shortdescription = 'REGSOURCE'
left outer join dbo.codeReferences crSource on crSource.CodeCategoryID = cc.codeReferenceID and crSource.shortdescription = od.registrationSourceType
left outer join
(
select orderdetailid, SUM(appliedAmount) appliedAmount, SUM(revenueCollected) revenueCollected from
(
select
ordf.orderdetailID
, ordf.Amount appliedAmount
, case when ordf.reconciled = 1 then 0 else
case when scheduledProcessingDate is not null then
case when ordf.processed = 1 then ordf.Amount else 0 end
else
case
when ftt.isRevenue = 1 and ftt.isFee = 1 then
abs(ordf.amount)
when ftt.isRevenue = 1 and fpm.isRevenue = 1 and isnull(ordf.orderdetailID,0) <> 0 then ordf.amount
else 0
end
end
end
revenueCollected
from (select distinct pid, eventid from dbo.orderdetailstatushistory where createdate between @startdate and @endDate ) as sh
join dbo.orderfinances ordf on sh.eventID = ordf.EventID and sh.pid = ordf.pid and ordf.active = 1
join dbo.financialPaymentMethods fpm on ordf.paymentMethodId = fpm.paymentMethodID
join dbo.financialTransactionTypes ftt on ordf.transactionTypeID = ftt.TransactionTypeID
) as ordf group by ordf.OrderDetailID
) as ordf on od.orderdetailID = ordf.orderdetailID
where od.pid is not null and od.eventid is not null
) as reg
when matched and reg.currentStatusDate > fr.currentStatusDate then
update set --fr.pid = reg.pid, fr.eventid = reg.eventid,
(/*update columns*/)
when not matched and reg.pid is not null and reg.eventid is not null then
insert (/*insert columns*/
)
values ( /*columns*/
)
OUTPUT $action, Inserted.registrationID, Inserted.eventid, Inserted.pid
;
February 7, 2013 at 12:29 pm
Please take a few minutes to read the first link in my signature. There is nowhere near enough information here to even begin thinking about an answer. You have an enormous query with at least a dozen tables that nobody here knows anything about.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 7, 2013 at 12:31 pm
I took the liberty of attempting to format your script but it has a number of syntax errors, variables missing etc...
DECLARE @startDate AS DATETIME
,@endDate AS DATETIME
,@lastRunDate AS DATETIME
SELECT @lastRunDate = lastrundate
FROM dbo.reportingJobs
WHERE JobCode = 'factRegistration'
AND active = 1
IF (@@rowcount = 0)
BEGIN
PRINT 'Job has not been defined or has been inactivated.'
RETURN
END
SELECT @lastRunDate = dateadd(ms, 3, isnull(@lastRunDate, '12/31/1989 23:59:59.997')) -- To start from begining and add a secon
SELECT @startDate = dateadd(d, - 1, convert(DATETIME, convert(VARCHAR, @lastRunDate, 101)))
,@endDate = dateadd(ms, - 3, convert(DATETIME, convert(VARCHAR, GETDATE(), 101)))
SET @startDate = '1/1/1900'
SET @endDate = '12/19/2012 23:59:59.997'
IF (@startDate >= @endDate)
BEGIN
RETURN
END
MERGE INTO factRegistration fr
USING (
SELECT od.OrderDetailID
,od.eventID
,od.pid
,CASE
WHEN isnull(sh.STATUS, od.itemStatus) IN (
@Registered
,@Completed
)
THEN 1
ELSE 0
END isCurrentParticipant
,CASE
WHEN isnull(sh.STATUS, od.itemStatus) = @Withdrawn
THEN 1
ELSE 0
END isWithdrawn
,CASE
WHEN isnull(sh.STATUS, od.itemStatus) = @Transferred
THEN 1
ELSE 0
END isTransferred
,e.eventdate
,CASE
WHEN isnull(sh.STATUS, od.itemStatus) IN (@Completed)
THEN 1
WHEN isnull(sh.STATUS, od.itemStatus) = @Registered
AND od.itemstatus = @Completed
THEN 1
WHEN isnull(sh.STATUS, od.itemStatus) = @Registered
THEN 0
ELSE NULL
END hasCompleted
,lastRegSH.firstRegistrationDate firstRegistrationDate
,isnull(sh.createdate, od.createdate) currentStatusDate
,lastRegSH.lastRegistrationDate
FROM dbo.orderdetails AS od
LEFT JOIN (
SELECT orderdetailid
,min(createdate) firstRegistrationDate
,max(createdate) lastRegistrationDate
FROM dbo.orderdetailstatushistory
WHERE STATUS = @Registered
AND orderdetailid IN (
SELECT DISTINCT OrderDetailID
FROM dbo.orderdetailstatushistory
WHERE createdate BETWEEN @startdate
AND @endDate
AND eventid IS NOT NULL
AND pid IS NOT NULL
)
GROUP BY orderdetailid
) AS lastRegSH ON od.OrderDetailID = lastRegSH.orderdetailID
LEFT JOIN (
SELECT *
FROM (
SELECT RANK() OVER (
PARTITION BY sh.orderdetailid ORDER BY sh.orderdetailid
,sh.createdate DESC
) lastStatus
,sh.*
FROM (
SELECT *
FROM dbo.orderdetailstatushistory
WHERE createdate BETWEEN @startdate
AND @endDate
AND eventid IS NOT NULL
AND pid IS NOT NULL
) AS sh
INNER JOIN dbo.orderdetails od ON sh.orderdetailid = od.orderdetailID
) AS sh
WHERE lastStatus = 1
) AS sh ON od.OrderDetailID = sh.OrderDetailID
LEFT JOIN dbo.events e ON od.eventID = e.eventid
LEFT JOIN dbo.centers c ON e.centerid = c.centerid
LEFT JOIN dbo.products p ON e.programid = p.productid
LEFT JOIN dbo.productMiscellaneousData pmo ON p.productid = pmo.productID
AND pmo.datatypeid IN (
SELECT cr.codeReferenceID
FROM dbo.codeReferences cc
INNER JOIN dbo.codeReferences cr ON cc.codeReferenceid = cr.codeCategoryID
AND upper(cr.shortdescription) = 'INT'
AND cc.shortdescription = 'DATATYPE'
AND isnull(cc.codeCategoryID, 0) = 0
)
AND pmo.codeID IN (
SELECT cr.codeReferenceID
FROM dbo.codeReferences cc
INNER JOIN dbo.codeReferences cr ON cc.shortdescription = 'PROGMISCOF'
AND cc.codeReferenceID = cr.codeCategoryID
AND cr.shortdescription = 'STARTREPOR'
)
LEFT JOIN dbo.events fe ON sh.xferFromEventID = fe.eventid
LEFT JOIN dbo.events te ON sh.xferToEventID = te.eventid
LEFT JOIN dbo.codeReferences crOutType ON crOutType.codeReferenceID = sh.xferWithdrawReasonID
LEFT JOIN dbo.products rp ON e.programid = rp.productid
LEFT JOIN dbo.productGroupCodes rpgc ON rp.productGroupID = rpgc.productGroupCodeID
LEFT JOIN dbo.codeReferences cc ON cc.shortdescription = 'REGSOURCE'
LEFT JOIN dbo.codeReferences crSource ON crSource.CodeCategoryID = cc.codeReferenceID
AND crSource.shortdescription = od.registrationSourceType
LEFT JOIN (
SELECT orderdetailid
,SUM(appliedAmount) appliedAmount
,SUM(revenueCollected) revenueCollected
FROM (
SELECT ordf.orderdetailID
,ordf.Amount appliedAmount
,CASE
WHEN ordf.reconciled = 1
THEN 0
ELSE CASE
WHEN scheduledProcessingDate IS NOT NULL
THEN CASE
WHEN ordf.processed = 1
THEN ordf.Amount
ELSE 0
END
ELSE CASE
WHEN ftt.isRevenue = 1
AND ftt.isFee = 1
THEN abs(ordf.amount)
WHEN ftt.isRevenue = 1
AND fpm.isRevenue = 1
AND isnull(ordf.orderdetailID, 0) <> 0
THEN ordf.amount
ELSE 0
END
END
END revenueCollected
FROM (
SELECT DISTINCT pid
,eventid
FROM dbo.orderdetailstatushistory
WHERE createdate BETWEEN @startdate
AND @endDate
) AS sh
INNER JOIN dbo.orderfinances ordf ON sh.eventID = ordf.EventID
AND sh.pid = ordf.pid
AND ordf.active = 1
INNER JOIN dbo.financialPaymentMethods fpm ON ordf.paymentMethodId = fpm.paymentMethodID
INNER JOIN dbo.financialTransactionTypes ftt ON ordf.transactionTypeID = ftt.TransactionTypeID
) AS ordf
GROUP BY ordf.OrderDetailID
) AS ordf ON od.orderdetailID = ordf.orderdetailID
WHERE od.pid IS NOT NULL
AND od.eventid IS NOT NULL
) AS reg when MATCHED
AND reg.currentStatusDate > fr.currentStatusDate then UPDATE SET --fr.pid = reg.pid, fr.eventid = reg.eventid,
(/*update columns*/) when NOT MATCHED
AND reg.pid IS NOT NULL
AND reg.eventid IS NOT NULL then INSERT (
/*insert columns*/
) VALUES (
/*columns*/
) OUTPUT $ACTION
,Inserted.registrationID
,Inserted.eventid
,Inserted.pid;
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 7, 2013 at 12:39 pm
thanks and apologies Sean, I will try to simplify my code and comeback here shortly.
February 7, 2013 at 1:12 pm
Apologies everybody, here is my refined version of my post.
I am new here and I will make sure I will abide by the etiquette from now. could do you guys tell me how to get the keywords in different color.
So here's what I need help with.
I want to automate the @asofdate so that I can increment @createdate by 1 day to get the historical data from that day until @endofDate in the source table. I need help designing a while loop but I am pragmatically handicapped in analyzing where to
put the while loop and what condition to use in the while loop and finally where to put the Select @asofdate = dateadd(d,1,@asofdate) in the while loop(at the beginning or at the end)
declare @asofdate as datetime
,@createdate as datetime
,@enddate as datetime
set @createdate = dateadd(d,-1,convert(datetime,Convert(varchar,@asofdate,101))) -- stripping off the time
set @enddate = dateadd(ms,-3,dateadd(d,1,@Createdate)) --setting it to 3 ms before end of day at midnight
Merge into dest
using (
Select
/* Columns */
From Tables
Join SubQuery (Select columns from Table3 where Table3.createdate between @Startend and @endDate)
Join more tables
) as source
on (dest.column1 = source.column1 and dest.column2 = source.column2)
When matched and source.column2> target.column2 Then update (/*columns*/)
When not matched Then Insert (/*columns*/) values(/*columns*/)
February 7, 2013 at 1:26 pm
justsidu (2/7/2013)
Apologies everybody, here is my refined version of my post.I am new here and I will make sure I will abide by the etiquette from now. could do you guys tell me how to get the keywords in different color.
No worries. Most people around here are pretty patient and forgiving. This type of thing is not easy to post on a forum. To get your code blocks to look nice you should the IFCode shortcuts. They are on the left side when posting.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 7, 2013 at 1:30 pm
justsidu (2/7/2013)
So here's what I need help with.I want to automate the @asofdate so that I can increment @createdate by 1 day to get the historical data from that day until @endofDate in the source table. I need help designing a while loop but I am pragmatically handicapped in analyzing where to
put the while loop and what condition to use in the while loop and finally where to put the Select @asofdate = dateadd(d,1,@asofdate) in the while loop(at the beginning or at the end)
declare @asofdate as datetime
,@createdate as datetime
,@enddate as datetime
set @createdate = dateadd(d,-1,convert(datetime,Convert(varchar,@asofdate,101))) -- stripping off the time
set @enddate = dateadd(ms,-3,dateadd(d,1,@Createdate)) --setting it to 3 ms before end of day at midnight
Merge into dest
using (
Select
/* Columns */
From Tables
Join SubQuery (Select columns from Table3 where Table3.createdate between @Startend and @endDate)
Join more tables
) as source
on (dest.column1 = source.column1 and dest.column2 = source.column2)
When matched and source.column2> target.column2 Then update (/*columns*/)
When not matched Then Insert (/*columns*/) values(/*columns*/)
The problem still is that we can't see your tables. This description is perfectly clear to you because you know the data and the business rules. I can't make enough sense of what you are trying to do to even try to point you in a direction. I can however say that I highly doubt you need a loop for this. Loops and cursors are horribly slow.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 7, 2013 at 2:18 pm
I hope I can explain it better in take 3 :p (This post will be epic when I look back at it 1yr from now, loll!)
Background:
There is a fact table called factRegistration. It tracks all the registrations for a program. The status of the registrations can be registered, withdrawn (from the program), completed. This is the destination
The source dataset for this fact table is a giant select statement with the columns coming from multiple joins and subqueries.
The primary subqueries which are affecting this merge are named SH which give the result of the latest status via the select statement with the Rank() function.
Business rules:
I want to insert data into destination for one all the transactions/entries everyday.
For this I am using a day(random day @asofDate for testing purposes) and passing this for @create date which is dateadd(d,-1,@asofDate) and @enddate which are used as a filter to get one day's worth of data in the subquery SH with the rank() function. Now that I have one day's worth of data, I will want to increment this @asoFDate to get next day's worth of day. For this I am thinking of looping it until I have the latest day's data. So the latest day would be max(createDate) from the orderdetailstatushistory table.
What I need help with:
I need help in designing a loop over this merge query. I considered using a stored preocedure and passing a date parameter which can used by the variables @createdate and @enddate but I am failed in acheiving it. Please help.
DECLARE @startDate AS DATETIME
,@endDate AS DATETIME
,@lastRunDate AS DATETIME
SELECT @lastRunDate = dateadd(ms, 3, isnull(@lastRunDate, '12/31/1989 23:59:59.997')) -- To start from begining and add a secon
SELECT @startDate = dateadd(d, - 1, convert(DATETIME, convert(VARCHAR, @lastRunDate, 101)))
,@endDate = dateadd(ms, - 3, convert(DATETIME, convert(VARCHAR, GETDATE(), 101)))
MERGE INTO factRegistration fr
USING (
SELECT od.OrderDetailID
,od.eventID
,od.pid
,CASE
WHEN isnull(sh.STATUS, od.itemStatus) IN (
@Registered
,@Completed
)
THEN 1
ELSE 0
END isCurrentParticipant
,CASE
WHEN isnull(sh.STATUS, od.itemStatus) = @Withdrawn
THEN 1
ELSE 0
END isWithdrawn
,CASE
WHEN isnull(sh.STATUS, od.itemStatus) = @Transferred
THEN 1
ELSE 0
END isTransferred
,e.eventdate
,CASE
WHEN isnull(sh.STATUS, od.itemStatus) IN (@Completed)
THEN 1
WHEN isnull(sh.STATUS, od.itemStatus) = @Registered
AND od.itemstatus = @Completed
THEN 1
WHEN isnull(sh.STATUS, od.itemStatus) = @Registered
THEN 0
ELSE NULL
END hasCompleted
,lastRegSH.firstRegistrationDate firstRegistrationDate
,isnull(sh.createdate, od.createdate) currentStatusDate
,lastRegSH.lastRegistrationDate
FROM dbo.orderdetails AS od
LEFT JOIN (
SELECT orderdetailid
,min(createdate) firstRegistrationDate
,max(createdate) lastRegistrationDate
FROM dbo.orderdetailstatushistory
WHERE STATUS = @Registered
AND orderdetailid IN (
SELECT DISTINCT OrderDetailID
FROM dbo.orderdetailstatushistory
WHERE createdate BETWEEN @startdate
AND @endDate
AND eventid IS NOT NULL
AND pid IS NOT NULL
)
GROUP BY orderdetailid
) AS lastRegSH ON od.OrderDetailID = lastRegSH.orderdetailID
LEFT JOIN (
SELECT *
FROM (
SELECT RANK() OVER (
PARTITION BY sh.orderdetailid ORDER BY sh.orderdetailid
,sh.createdate DESC
) lastStatus
,sh.*
FROM (
SELECT *
FROM dbo.orderdetailstatushistory
WHERE createdate BETWEEN @startdate
AND @endDate
AND eventid IS NOT NULL
AND pid IS NOT NULL
) AS sh
INNER JOIN dbo.orderdetails od ON sh.orderdetailid = od.orderdetailID
) AS sh
WHERE lastStatus = 1
) AS sh ON od.OrderDetailID = sh.OrderDetailID
LEFT JOIN dbo.events e ON od.eventID = e.eventid
LEFT JOIN dbo.centers c ON e.centerid = c.centerid
LEFT JOIN dbo.products p ON e.programid = p.productid
LEFT JOIN dbo.productMiscellaneousData pmo ON p.productid = pmo.productID
AND pmo.datatypeid IN (
SELECT cr.codeReferenceID
FROM dbo.codeReferences cc
INNER JOIN dbo.codeReferences cr ON cc.codeReferenceid = cr.codeCategoryID
AND upper(cr.shortdescription) = 'INT'
AND cc.shortdescription = 'DATATYPE'
AND isnull(cc.codeCategoryID, 0) = 0
)
AND pmo.codeID IN (
SELECT cr.codeReferenceID
FROM dbo.codeReferences cc
INNER JOIN dbo.codeReferences cr ON cc.shortdescription = 'PROGMISCOF'
AND cc.codeReferenceID = cr.codeCategoryID
AND cr.shortdescription = 'STARTREPOR'
)
LEFT JOIN dbo.events fe ON sh.xferFromEventID = fe.eventid
LEFT JOIN dbo.events te ON sh.xferToEventID = te.eventid
LEFT JOIN dbo.codeReferences crOutType ON crOutType.codeReferenceID = sh.xferWithdrawReasonID
LEFT JOIN dbo.products rp ON e.programid = rp.productid
LEFT JOIN dbo.productGroupCodes rpgc ON rp.productGroupID = rpgc.productGroupCodeID
LEFT JOIN dbo.codeReferences cc ON cc.shortdescription = 'REGSOURCE'
LEFT JOIN dbo.codeReferences crSource ON crSource.CodeCategoryID = cc.codeReferenceID
AND crSource.shortdescription = od.registrationSourceType
LEFT JOIN (
SELECT orderdetailid
,SUM(appliedAmount) appliedAmount
,SUM(revenueCollected) revenueCollected
FROM (
SELECT ordf.orderdetailID
,ordf.Amount appliedAmount
,CASE
WHEN ordf.reconciled = 1
THEN 0
ELSE CASE
WHEN scheduledProcessingDate IS NOT NULL
THEN CASE
WHEN ordf.processed = 1
THEN ordf.Amount
ELSE 0
END
ELSE CASE
WHEN ftt.isRevenue = 1
AND ftt.isFee = 1
THEN abs(ordf.amount)
WHEN ftt.isRevenue = 1
AND fpm.isRevenue = 1
AND isnull(ordf.orderdetailID, 0) <> 0
THEN ordf.amount
ELSE 0
END
END
END revenueCollected
FROM (
SELECT DISTINCT pid
,eventid
FROM dbo.orderdetailstatushistory
WHERE createdate BETWEEN @startdate
AND @endDate
) AS sh
INNER JOIN dbo.orderfinances ordf ON sh.eventID = ordf.EventID
AND sh.pid = ordf.pid
AND ordf.active = 1
INNER JOIN dbo.financialPaymentMethods fpm ON ordf.paymentMethodId = fpm.paymentMethodID
INNER JOIN dbo.financialTransactionTypes ftt ON ordf.transactionTypeID = ftt.TransactionTypeID
) AS ordf
GROUP BY ordf.OrderDetailID
) AS ordf ON od.orderdetailID = ordf.orderdetailID
WHERE od.pid IS NOT NULL
AND od.eventid IS NOT NULL
) AS reg when MATCHED
AND reg.currentStatusDate > fr.currentStatusDate then UPDATE SET --fr.pid = reg.pid, fr.eventid = reg.eventid,
(/*update columns*/) when NOT MATCHED
AND reg.pid IS NOT NULL
AND reg.eventid IS NOT NULL then INSERT (
/*insert columns*/
) VALUES (
/*columns*/
) OUTPUT $ACTION
,Inserted.registrationID
,Inserted.eventid
,Inserted.pid;
February 7, 2013 at 2:33 pm
OK last try. You think you need a loop but you don't. A loop is not the answer here. Sadly I can't help you with the answer because I still don't know what the question is. Perhaps it is because I am a visual person but this merge query is almost 200 lines dealing with tables I have never seen manipulating data I don't have any idea what it is like. Are you seriously trying to record an audit of every row in a table for every single day? I have read and reread and reread but I just can't figure out what you are trying to do here. No matter how I try I just can't visualize this data at all. There are some people around here who might be able to see this without some ddl and sample but I am not one of them.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 7, 2013 at 3:23 pm
Thanks for trying it out Sean. I appreciate your time.
February 7, 2013 at 3:30 pm
justsidu (2/7/2013)
Thanks for trying it out Sean. I appreciate your time.
I am willing to help you but you have to put in the effort to provide tables and data to work with. I doubt anybody will be able to provide much of answer without something to work with.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply