I've looked through several previous questions but I am struggling to apply the solutions to my specific example.
I am having trouble combining query 1 and query 2.
My query originally returned (amongst other details) the values "SpentTotal" and "UnderSpent" for all members/users for the current month.
My issue has been adding two additional columns to this original quert that will return JUST these two columns (Spent and Overspent) but for the previous months data
Original Query #1:
set @BPlanKey = '##CURRENTMONTH##'
EXECUTE @RC = Minimum_UpdateForPeriod @BPlanKey
SELECT cm.clubaccountnumber, bp.Description , msh.PeriodMinObligation, msh.SpentTotal, msh.UnderSpent, msh.OverSpent, msh.BilledDate, msh.PeriodStartDate, msh.PeriodEndDate, msh.OverSpent
FROM MinimumSpendHistory msh
INNER JOIN BillPlanMinimums bpm ON msh.BillingPeriodKey = @BPlanKey and bpm.BillPlanMinimumKey = msh.BillPlanMinimumKey
INNER JOIN BillPlans bp ON bp.BillPlanKey = bpm.BillPlanKey
INNER JOIN ClubMembers cm ON cm.parentmemberkey is null and cm.ClubMemberKey = msh.ClubMemberKey
order by cm.clubaccountnumber asc, msh.BilledDate asc
Query #2, query of all columns for PREVIOUS month, but I only need two (spent and over spent), added to the query from above, joined on the customer number:
set @BPlanKeyLastMo = '##PREVMONTH##'
EXECUTE @RCLastMo = Minimum_UpdateForPeriod @BPlanKeyLastMo
SELECT cm.clubaccountnumber, bp.Description , msh.PeriodMinObligation, msh.SpentTotal, msh.UnderSpent, msh.OverSpent, msh.BilledDate, msh.PeriodStartDate, msh.PeriodEndDate, msh.OverSpent
FROM MinimumSpendHistory msh
INNER JOIN BillPlanMinimums bpm ON msh.BillingPeriodKey = @BPlanKeyLastMo and bpm.BillPlanMinimumKey = msh.BillPlanMinimumKey
INNER JOIN BillPlans bp ON bp.BillPlanKey = bpm.BillPlanKey
INNER JOIN ClubMembers cm ON cm.parentmemberkey is null and cm.ClubMemberKey = msh.ClubMemberKey
order by cm.clubaccountnumber asc, msh.BilledDate asc
Big thank you to any and all that are willing to lend their help and time.
Cheers!
AJ
CREATE TABLE MinimumSpendHistory(
[MinimumSpendHistoryKey] [uniqueidentifier] NOT NULL,
[BillPlanMinimumKey] [uniqueidentifier] NOT NULL,
[ClubMemberKey] [uniqueidentifier] NOT NULL,
[BillingPeriodKey] [uniqueidentifier] NOT NULL,
[PeriodStartDate] [datetime] NOT NULL,
[PeriodEndDate] [datetime] NOT NULL,
[PeriodMinObligation] [money] NOT NULL,
[SpentTotal] [money] NOT NULL,
[CurrentSpent] [money] NOT NULL,
[OverSpent] [money] NULL,
[UnderSpent] [money] NULL,
[BilledAmount] [money] NOT NULL,
[BilledDate] [datetime] NOT NULL,
[PriorPeriodMinimum] [money] NULL,
[IsCommitted] [bit] NOT NULL,
[IsCalculated] [bit] NOT NULL,
[BillPeriodMinimumKey] [uniqueidentifier] NOT NULL,
[CarryForwardCounter] [smallint] NULL,
[YTDSpent] [money] NOT NULL,
[PeriodToAccumulateCounter] [int] NULL,
[StartDate] [datetime] NOT NULL,
