|
Posted by KingofGing on 01/09/08 11:53
On Jan 9, 11:13 am, KingofGing <kingofg...@gmail.com> wrote:
> I should clarify, I'm looking for any subscriptions that have a break
> of at least 1 issue before returning with the current transaction,
> i.e. in the examples above, any subscription whose last transaction
> ended on or before issue 17 and are coming back with a new transaction
> starting with the current issue of 19.
OK, rather surprisingly I think I've got it.
It's all the same except the final couple of select statements - I
took one out and re-jigged the other a bit:
SELECT
COUNT(tblSubscription.SubscriptionID) AS NoSubs,
SUM(tblTransaction.Copies) AS NoCopies
FROM
tblSubscription INNER JOIN tblTransaction ON
tblSubscription.SubscriptionID = tblTransaction.SubscriptionID
WHERE
(tblSubscription.JournalID = @JournalID) AND
(tblTransaction.IssueStart =
(SELECT
MAX(JournalSequence) AS Expr1
FROM
tblIssue AS tblIssue_1
WHERE
(JournalID = @JournalID))) AND
(tblTransaction.TransactionTypeID = 11) AND
((SELECT
MAX(Transactions.IssueStart) AS RestartIssue
FROM
tblSubscription AS Subscriptions INNER JOIN tblTransaction AS
Transactions ON Subscriptions.SubscriptionID =
Transactions.SubscriptionID
WHERE
(Subscriptions.JournalID = @JournalID) AND
(Subscriptions.SubscriptionID = tblSubscription.SubscriptionID)) >
1 +
(SELECT
MIN(IssueEnd) AS ExpiredIssue
FROM
(SELECT
TOP (2) IssueEnd
FROM
tblSubscription AS Subscriptions INNER JOIN tblTransaction AS
Transactions ON Subscriptions.SubscriptionID =
Transactions.SubscriptionID
WHERE
(Subscriptions.JournalID = @JournalID) AND
(Subscriptions.SubscriptionID = tblSubscription.SubscriptionID)
ORDER BY IssueEnd DESC)
AS derivedtbl_1))
There might be a more simple solution - I'd be happy to hear it if
anyone comes up with one.
Other than that, thanks for your time!
Navigation:
[Reply to this message]
|