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 10 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply