CODEWITHSUNDEEP
sql-servert-sql
OM Asphyxiate
OM Asphyxiate

Reputation: 329

Using previous year's data within the same query

Below is my current query, I have a CTE that calculates the revenue for each of my fiscal periods grouped by the clinic and their respective transactions. Followed by a query that looks at the individual clinic's worked days and finding the daily average (calculating the current days) and projected revenue based on the expected # of days.

This query works fine as is; however I need to add a few more columns. I would like to also show the Revenue of the previous year, so in this case I have limited the query to only showing 2016 and period 9 and the previous year's revenue should show for 2015's period 9 and also will need to see the previous year's days as well (which I imagine will be a similar process).

WITH CTE_Revenue AS(
SELECT  c.Clinic,
        c.ClinicID,
        SUM(td.Amount)*-1 AS Revenue,
        p.PeriodID,
        p.FiscalYear
FROM Trans.TransactionHeader th
JOIN Clinic.[Master] c
    ON (th.ClinicID = c.ClinicID)
JOIN Trans.TransactionDetail td
    ON (th.ClinicID = td.ClinicID AND th.TranNum = td.TranNum)
JOIN Clinic.EOD e
    ON (th.ClinicID = e.ClinicID AND th.TranNum BETWEEN e.StartTran AND e.EndTran)
JOIN Clinic.Period p
    ON (CAST(e.TimeRan AS date) BETWEEN p.PeriodStart AND p.PeriodEnd)
AND th.Impacts='C'
GROUP BY c.Clinic, c.ClinicID, p.PeriodID, p.FiscalYear)

SELECT  w.Clinic,
        w.Revenue,
        (w.Revenue / days.CurrentDays) AS DailyAverage,
        (w.Revenue / days.CurrentDays)*d.PeriodDays AS ProjectedRevenue
FROM CTE_Revenue w
JOIN Clinic.Dates d
    ON (w.ClinicID = d.ClinicID AND w.PeriodID = d.PeriodID AND w.FiscalYear = d.FiscalYear)
JOIN (  SELECT DISTINCT
        td.ClinicID,
        COUNT(DISTINCT td.DateEntered) AS CurrentDays,
        p.PeriodID,
        p.FiscalYear
    FROM Trans.TransactionDetail td
    JOIN Clinic.Period p
        ON td.DateEntered BETWEEN p.PeriodStart AND p.PeriodEnd
    GROUP BY td.ClinicID, p.PeriodID, p.FiscalYear) AS days
    ON (w.ClinicID = days.ClinicID AND w.PeriodID=days.PeriodID AND w.FiscalYear = days.FiscalYear)
WHERE w.FiscalYear = 2016
AND w.PeriodID = 9

The end resulting SELECT statement, I imagine, will look something like this:

SELECT  w.Clinic,
        w.Revenue,
        (w.Revenue / days.CurrentDays) AS DailyAverage,
        (w.Revenue / days.CurrentDays)*d.PeriodDays AS ProjectedRevenue,
        PrevYear.Revenue,
        (PrevYear.Revenue / PrevYear.CurrentDays) AS PYDailyAverage,
        (PrevYear.Revenue / PrevYear.CurrentDays)*d.PeriodDays AS PYCalculated

This query might not be fully optimized, I'm still fairly new to SQL. Thanks for any advice and help ahead of time!

EDIT: Here's an image of the table structures and relations using the SQL Server Diagrams: Table Diagram

Upvotes: 1

Views: 63

Answers (1)

SqlZim
SqlZim

Reputation: 38023

You can use multiple ctes

Like this:

with cte_Revenue as(
  select  
      c.Clinic
    , c.Clinicid
    , Revenue = sum(td.Amount)*-1
    , p.Periodid
    , p.FiscalYear
  from Trans.TransactionHeader th
  inner join Clinic.[Master] c 
     on (th.Clinicid = c.Clinicid)
  inner join Trans.TransactionDetail td 
     on th.Clinicid = td.Clinicid 
    and th.TranNum = td.TranNum
  inner join Clinic.eod e
     on th.Clinicid = e.Clinicid 
    and th.TranNum between e.StartTran and e.EndTran
  inner join Clinic.Period p
      on (cast(e.TimeRan as date) between p.PeriodStart and p.PeriodEnd)
    and th.Impacts='C'
  group by 
      c.Clinic
    , c.Clinicid
    , p.Periodid
    , p.FiscalYear
)
, include_py as (
  select 
        w.Clinic
      , w.Revenue
      , w.Periodid
      , w.FiscalYear
      , DailyAverage = (w.Revenue / days.CurrentDays)
      , ProjectedRevenue = (w.Revenue / days.CurrentDays)*d.PeriodDays 
    from cte_Revenue w
      inner join Clinic.Dates d
         on w.Clinicid = d.Clinicid 
        and w.Periodid = d.Periodid 
        and w.FiscalYear = d.FiscalYear
      inner join (
        select distinct
              td.Clinicid
            , CurrentDays = count(distinct td.DateEntered)
            , p.Periodid
            , p.FiscalYear
          from Trans.TransactionDetail td
            inner join Clinic.Period p
              on td.DateEntered between p.PeriodStart and p.PeriodEnd
          group by 
              td.Clinicid
            , p.Periodid
            , p.FiscalYear
      ) as days
            on w.Clinicid = days.Clinicid 
          and w.Periodid=days.Periodid 
          and w.FiscalYear = days.FiscalYear
    where w.FiscalYear in (2015,2016)
      and w.Periodid = 9
)
select 
    c.Clinic
  , c.FiscalYear
  , c.PeriodId
  , c.Revenue
  , c.DailyAverage
  , c.ProjectedRevenue
  , pyRevenue = p.Revenue
  , pyDailyAverage = p.DailyAverage
  , pyProjectedRevenue = p.ProjectedRevenue
from (select * from include_py where fiscalyear = 2016) c
  left join (select * from include_py where fiscalyear = 2015) p
    on c.Clinic = p.Clinic

Bad Habits to Kick : Using AS instead of = for column aliases - Aaron Bertrand

Upvotes: 1

Related Questions