How do I sum and reference two subtotals in computing the difference

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

You might want to add WHERE PriorMonth <> 0 to the outer query to keep from getting a Divide by zero error. Also, you can use code to determine the first day of the prior month and last day of the current month, or pass them as parameters.

ASKER

Current Month and Prior Month are both from the same field called BillingDate and is a column group. How do you refer to those subtotals when computing percentage?

ASKER

Oh wait, I see. PriorMonth is sum(case when month(invDate) = month(dateadd, month, -1, getdate()) then invMonth else 0 end)

ASKER

I'm trying to rewrite it with my fields but getting an error near 'AS'. I'm using parameters ThisMonth and PreviousMonth to filter.

select CAccount,
PriorMonth,
CurrMonth,
((((CurrMonth - PriorMonth) / PriorMonth) * 100) AS PercentChange))
from (
select ClaimAccount,
sum(case when month(BillingDate) = PreviousMonth then Quantity else 0 end) as PriorMonth,
sum(case when month(BillingDate) = ThisMonth then Quantity else 0 end) as CurrMonth
from PReporting.dbo.LficheBkup

)

ASKER

Correction ClaimAccount should be CAccount

you need an alias on the FROM:

FROM (
...
) a

The 'a' can be anything.

ASKER

Using this

select ClaimAccount,
PriorMonth,
CurrMonth,
((CurrMonth - PriorMonth) / PriorMonth * 100 AS PercentChange
from (
select ClaimAccount,
sum(case when month(BillingDate) = month(dateadd, month, -1, getdate()) then Quantity else 0 end) as PriorMonth,
sum(case when month(BillingDate) = month(getdate() then Quantity else 0 end) as CurrMonth
from PlatoReporting.dbo.LaserficheBackupReport
where invDate between '2018-10-01' and '2018-11-30'
group by ClaimAccount
) as Intermed

I get this error 'Incorrect syntax near the keyword 'AS'.
The month function requires 1 argument(s).'

You are missing a closing ) on the 4th line.
Try: ((CurrMonth - PriorMonth) / PriorMonth) * 100 AS PercentChange

Also on this line:
sum(case when month(BillingDate) = month(getdate() then Quantity else 0 end) as CurrMonth
use:
sum(case when month(BillingDate) = month(getdate()) then Quantity else 0 end) as CurrMonth

Sorry, they were missing in my original post. The first sum is also incorrect:
Here is the corrected query:

select ClaimAccount,
           PriorMonth,
           CurrMonth,
           ((CurrMonth - PriorMonth) / PriorMonth) * 100 AS PercentChange
from (
          select ClaimAccount, 
                      sum(case when month(BillingDate) = month(dateadd(month, -1, getdate())) then Quantity else 0 end) as PriorMonth,
                      sum(case when month(BillingDate) = month(getdate()) then Quantity else 0 end) as CurrMonth
          from   PlatoReporting.dbo.LaserficheBackupReport
          where invDate between '2018-10-01' and '2018-11-30'
          group by ClaimAccount
          ) as Intermed

ASKER

So to filter by values in a parameter, I add the parameter to the report but also to the dataset, right? Then I substitute the parameter name for dateadd(month,-1,getdate()) in the query?

ASKER

And if I want to list the BillingDate next the Quantity in the report, I just add BillingDate to both the inner and outer query, right?

ASKER

Or do I need to save the Billing dates by creating new fields called CurrentBillingDate and PreviousBillingDate?

It gets a little more complex then. You are basically creating a detail report whereas this is a summary report.
I'm not sure the report would make much sense in that regard.
To keep monthly totals, you would need to carry them into each row, which, in my opinion would create a somewhat meaningless report.
I think to get anything with meaning, we are getting into using a UNION and window functions.
I can try to come up with something tomorrow if you want to continue does that route.

One issue is, how do you match up dates (current vs. prior). I would assume on the day of month. You might have a quantity on the 5th of the current month but not the 5th of the previous month (weekend, holiday, lack of activity, etc.) Then you get into running totals vs. an actual monthly total (both can be accommodated using the proper window functions. Realize that for ach account, instead of a single line, you now have a line for each day of the month, so you are creating a lot more data.

I would start with this, and then, either create another report that shows activity by date, or slowly expand on this query. Once you have this one down, you are venturing into more advanced T-SQL code (window functions, JOIN or UNION statements) and possibly even more complex CASE statements. Possibly potential for another question once you have the actual layout you want and the criteria.

Just to give you an idea of the 'noise' you'd see in such a report, this is a small sample comparing day over day and running totals for the month. The zeroes in the Qty column are, in this case, assuming a weekend and no activity. Your scenario might be different. The last two columns represent both a variance for current day over last month's day and based on a running total for the month. The -- in the Daily variance column are just place holders because the formula would result in a division by zero error.

Obviously if one month had 30 days and the other 31, there would be one row with a 0 Qty, and the other would have a value. Also, what is unclear in this output is whether the 0 Qty was a result of no activity for the account, or, for example, was the entity closed for business so no account had activity.

                Last Month              Current Month        Percent Change  
Account   Day   Qty   Monthly Qty     Qty   Monthly Qty     Daily    Monthly
--------  ---   ---   -----------     ---   -----------     -----    -------  
ABC-123    1     10            10      15            15        50         50
ABC-123    2      8            18       7            22       -12         22
ABC-123    3      0            18      11            33        --         83
ABC-123    4      0            18      13            46        --        156
ABC-123    5     12            30       0            46        --         53
ABC-123    6     16            46       0            46      -100          0

ASKER

All the records for a month will have the same date. So instead of Current Month and Previous Month I want to display the date.

ASKER

I also tried to Interactive Sort based on Percent Changed but its not working. Sorry I know this seems like the question that goes on forever but I'm learning a great deal from it.

I missed your comment about parameterizing the query. Will you always be running the report for the current and previous month, or can it be run for past months (e.g. run today for a comparison of May and June 2018?. If always the current and previous, you do not need parameters. Use this:

DECLARE @PREVMO DATE =  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0)
DECLARE @CURRMO DATE = DATEADD (dd, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 0))
-- NOTE: If you have SQL Server 2012 or above, you can use DECLARE @CURRMO DATE = EOMONTH(GETDATE()) instead

select ClaimAccount,
           PriorMonth,
           CurrMonth,
           ((CurrMonth - PriorMonth) / PriorMonth) * 100 AS PercentChange
from (
          select ClaimAccount, 
                      sum(case when month(BillingDate) = month(@PREVMO) then Quantity else 0 end) as PriorMonth,
                      sum(case when month(BillingDate) = month(@CURRMO) then Quantity else 0 end) as CurrMonth
          from   PlatoReporting.dbo.LaserficheBackupReport
          where invDate between @PREVMO and @CURRMO
          group by ClaimAccount
          ) as Intermed

If it CAN be run for previous periods, you have three options. Pass either a date you want to use for the current month and calculate the previous month using the above code, pass the date you want to use as the previous month and calculate the current month using the above code, or pass the date range as a start and end date (in your example, '2018-10-01' and '2018-11-30'). Whichever you do, adjust the DECLARE statement accordingly; if you pass both, you don't need either one, if you pass the current month you don't need to DECLARE @CURRMO because it will be a parameter and, if you pass @PREVMO, you don't need the DECLARE for it.
Whichever route you take, the code in the SELECT statement will remain the same, assuming you use the same variable/parameter names.

Regarding sorting, you can use the ordinal column. For this query, you would add ORDER BY 4

I'll address this comment in a separate post.

Note, if there is no previous date data (or current date) the Billing Date will be NULL.

select ClaimAccount,
           PriorMonthBillDate,
           PriorMonthQty,
           CurrMonthBillDate,
           CurrMonthQty,
           ((CurrMonth - PriorMonth) / PriorMonth) * 100 AS PercentChange
from (
          select ClaimAccount, 
                      max(case when month(BillingDate) = month(@PREVMO) then BillingDate) end) as PriorMonthBillDate,
                      sum(case when month(BillingDate) = month(@PREVMO) then Quantity else 0 end) as PriorMonthQty,
                      max(case when month(BillingDate) = month(@CURRMO) then BillingDate) end) as CurrMonthBillDate,
                      sum(case when month(BillingDate) = month(@CURRMO) then Quantity else 0 end) as CurrMonthQty
          from   PlatoReporting.dbo.LaserficheBackupReport
          where invDate between @PREVMO and @CURRMO
          group by ClaimAccount
          ) as Intermed

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Excellent service and extremely patient!