Selecting SUM and COUNT, but not straightforward ..

[KingofGing]

Hi All,

I'm having a problem writing an SQL statement that I can't quite wrap
my head around.
First, the background:

I have a journal subscription system including 3 tables,
tblSubscription , tblTransaction and tblIssue, detailed below.
tblSubscription :
CREATE TABLE [dbo].[tblSubscription](
[SubscriptionID] [int] NOT NULL,
[SubscriberID] [int] NOT NULL,
[Status] [int] NOT NULL,
[JournalID] [int] NOT NULL,
[Created] [datetime] NOT NULL,
CONSTRAINT [PK_tblSubscript ion] PRIMARY KEY CLUSTERED
(
[SubscriptionID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
tblTransaction:
CREATE TABLE [dbo].[tblTransaction](
[TransactionID] [bigint] NOT NULL,
[SubscriptionID] [int] NOT NULL,
[Copies] [int] NOT NULL,
[IssueStart] [int] NOT NULL,
[IssueEnd] [int] NOT NULL,
[LastUpdated] [datetime] NOT NULL,
CONSTRAINT [PK_tblTransacti on] PRIMARY KEY CLUSTERED
(
[TransactionID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[tblTransaction] WITH NOCHECK ADD CONSTRAINT
[FK_tblTransacti on_tblSubscript ion] FOREIGN KEY([SubscriptionID])
REFERENCES [dbo].[tblSubscription] ([SubscriptionID])
GO
ALTER TABLE [dbo].[tblTransaction] CHECK CONSTRAINT
[FK_tblTransacti on_tblSubscript ion]
GO
tblIssue
CREATE TABLE [dbo].[tblIssue](
[IssueID] [int] NOT NULL,
[JournalID] [int] NOT NULL,
[JournalSequence] [int] NOT NULL,
[Status] [int] NOT NULL,
[DispatchDate] [datetime] NULL,
CONSTRAINT [PK_tblIssue] PRIMARY KEY CLUSTERED
(
[IssueID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[tblIssue] WITH NOCHECK ADD CONSTRAINT
[FK_tblIssue_tbl Journal] FOREIGN KEY([JournalID])
REFERENCES [dbo].[tblJournal] ([JournalID])
GO
ALTER TABLE [dbo].[tblIssue] CHECK CONSTRAINT [FK_tblIssue_tbl Journal]
A subscription is to one individual journal and consists of one or
more transactions, and a transaction covers a period of time, say a
year.
If there are 6 issues of this journal per year then a 2 year
subscription might consist of 2 transactions for 1 year each, so for
example
Year 1: Issue13 - Issue18
Year 2: Issue19 - Issue24

However it is possible for a subscription to pause, or lapse, for a
period of time between two transactions and miss some issues, for
example
Year 1: Issue11 - Issue16
Year 2: Issue19 - Issue24

tblIssue is not linked to tblTransaction by any foreign keys, and
issues are referenced by JournalSequence number not IssueID, i.e. for
Year 2 in the second example above, tblTransaction. IssueStart contains
'19' and tblTransaction. IssueEnd contains '24'. Issues are not added
to tblIssue until they are current, so the Issue in tblIssue with the
highest JournalSequence number is the current one (i.e. SELECT
MAX(JournalSequ ence) FROM tblIssue will select the current issue)
Journal ID is an integer and will be passed into the SQL statement as
a parameter, i.e. @JournalID = 1013

What I need is to be able to determine the number of subscriptions
(and also the total number of copies for those subscriptions) that are
returning with the current issue (e.g. Issue19 in the examples above)
after a lapsed period (the second example), EXCLUDING any that haven't
lapsed, i.e. that have continued straight on (the first example) for
any particular journal.

I currently have (this returns no results, although there should be
some):
(Apologies for the tabs, they appear to have gone a bit crazy)

SELECT
COUNT(tblSubscr iption.Subscrip tionID) AS NoSubs,
SUM(tblTransact ion.Copies) AS NoCopies
FROM
tblSubscription INNER JOIN tblTransaction ON
tblSubscription .SubscriptionID = tblTransaction. SubscriptionID
WHERE
(tblSubscriptio n.JournalID = @JournalID) AND
(tblTransaction .IssueStart =
(SELECT
MAX(JournalSequ ence) AS Expr1
FROM
tblIssue AS tblIssue_1
WHERE
(JournalID = @JournalID))) AND
(tblTransaction .TransactionTyp eID = 11) AND
((SELECT
MAX(Transaction s.IssueStart) AS RestartIssue
FROM
tblSubscription AS Subscriptions INNER JOIN tblTransaction AS
Transactions ON Subscriptions.S ubscriptionID =
Transactions.Su bscriptionID
WHERE
(Subscriptions. JournalID = @JournalID) AND
(Subscriptions. SubscriptionID = tblSubscription .SubscriptionID )) >
1 +
(SELECT
MIN(IssueEnd) AS ExpiredIssue
FROM
SELECT
TOP (2) IssueEnd
FROM
(SELECT
Transactions.Is sueEnd
FROM
tblSubscription AS Subscriptions INNER JOIN tblTransaction AS
Transactions ON Subscriptions.S ubscriptionID =
Transactions.Su bscriptionID
WHERE
(Subscriptions. JournalID = @JournalID) AND
(Subscriptions. SubscriptionID =
tblSubscription .SubscriptionID ))
AS derivedtbl_2
- AS derivedtbl_1))

[KingofGing]

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.