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,