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_tblSubscription] 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_tblTransaction] PRIMARY KEY CLUSTERED
(
[TransactionID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [dbo].[tblTransaction] WITH NOCHECK ADD CONSTRAINT
[FK_tblTransaction_tblSubscription] FOREIGN KEY([SubscriptionID])
REFERENCES [dbo].[tblSubscription] ([SubscriptionID])
GO
ALTER TABLE [dbo].[tblTransaction] CHECK CONSTRAINT
[FK_tblTransaction_tblSubscription]
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_tblJournal] FOREIGN KEY([JournalID])
REFERENCES [dbo].[tblJournal] ([JournalID])
GO
ALTER TABLE [dbo].[tblIssue] CHECK CONSTRAINT [FK_tblIssue_tblJournal]
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(JournalSequence) 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(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
(SELECT
Transactions.IssueEnd
FROM
tblSubscription AS Subscriptions INNER JOIN tblTransaction AS
Transactions ON Subscriptions.SubscriptionID =
Transactions.SubscriptionID
WHERE
(Subscriptions.JournalID = @JournalID) AND
(Subscriptions.SubscriptionID =
tblSubscription.SubscriptionID))
AS derivedtbl_2
- AS derivedtbl_1))
