David David - 3 months ago 21
SQL Question

Embedded Case statement

I have a query (which works) however, I want to be able to make it so I don't need to rerun the query to change the date ranges.

What I have here is it is pulling customer name and the amount and summing it. I need it broken down monthly by fiscal year, so what I would just do is change the year and rerun it, but I want to make the query more efficient so it doesn't need to be changed in the future (except the end date for a new FY) So after it takes the sum of each month (July to June), I want it to show the FY. So the date ranges from 2014 to 2015 should show a column called FY and have 2015 in the column. I am pretty certain I am on the right track but not there. I only showed what I was working on for the first case (which will be replicated for the other cases)

Could this just be an IF statement loop instead of another case statement? Any help with direction would be great to help me learn. I got this far, but just want to make the code more efficient for someone to use

Declare @StartDate datetime
Declare @EndDate datetime
set @StartDate = convert(datetime, '07/01/2014')
set @EndDate = convert(datetime, '06/30/2017')

select
d.CUSTNMBR Customer_ID,
d.CUSTNAME Customer_Name,
'A' as Company,
sum(case when month(d.DOCDATE) = 7
then d.SALES else 0 end) July,
case when datetime between '07/01/2016' and '06/30/2017' as '2017'
when datetime between '07/01/2015' and '06/30/2016' as '2016'
when datetime between '07/01/2014' and '06/30/2015' as '2015'
END
sum(case when month(d.DOCDATE) = 8
then d.SALES else 0 end) August,
sum(case when month(d.DOCDATE) = 9
then d.SALES else 0 end) September,
sum(case when month(d.DOCDATE) = 10
then d.SALES else 0 end) October,
sum(case when month(d.DOCDATE) = 11
then d.SALES else 0 end) November,
sum(case when month(d.DOCDATE) = 12
then d.SALES else 0 end) December,
sum(case when month(d.DOCDATE) = 1
then d.SALES else 0 end) January,
sum(case when month(d.DOCDATE) = 2
then d.SALES else 0 end) February,
sum(case when month(d.DOCDATE) = 3
then d.SALES else 0 end) March,
sum(case when month(d.DOCDATE) = 4
then d.SALES else 0 end) April,
sum(case when month(d.DOCDATE) = 5
then d.SALES else 0 end) May,
sum(case when month(d.DOCDATE) = 6
then d.SALES else 0 end) June,
sum(d.SALES) 'Year to Date'

from
(select s.DOCDATE, s.CUSTNMBR, c.CUSTNAME,
case s.SOPTYPE
when 3 then s.DOCAMNT
when 4 then s.DOCAMNT*-1
end SALES
from a1.dbo.S200 s
left outer join a1.dbo.RM1 c
on s.CUSTNMBR = c.CUSTNMBR
where s.VOIDSTTS = 0
and s.SOPTYPE in (3,4) and
s.DOCDATE between @StartDate and @EndDate -- Fiscal Year
) d

group by d.CUSTNMBR, d.CUSTNAME

Answer

Consider a recursive CTE that iterates through each fiscal year and then run a CROSS JOIN...WHERE (CROSS APPLY can work too) on your existing derived table. The WHERE conditions DOCDate to fiscal year start and end ranges. And going forward, simply change the declaration lines for each fiscal year. In fact, you can set up @end INT = 2020 to anticipate future data!

DECLARE @start INT = 2014;
DECLARE @end INT = 2016;

WITH fiscalyears AS (
    SELECT @start + 1 AS FY, 
           CONVERT(datetime, cast(1 as varchar)+'/'+cast(7 as varchar)+'/'+cast(@start as varchar), 103) As StartRng,
           CONVERT(datetime, cast(30 as varchar)+'/'+cast(6 as varchar)+'/'+cast(@start + 1 as varchar), 103) As EndRng
    UNION ALL
    SELECT FY + 1, 
           CONVERT(datetime, cast(1 as varchar)+'/'+cast(7 as varchar)+'/'+cast(FY + 1 as varchar), 103),
           CONVERT(datetime, cast(30 as varchar)+'/'+cast(6 as varchar)+'/'+cast(FY + 2 as varchar), 103) 
    FROM  fiscalyears
    WHERE FY < @end
)

-- FY     StartRng                  EndRng
-- 2015   2014-07-01 00:00:00.000   2015-06-30 00:00:00.000
-- 2016   2015-07-01 00:00:00.000   2016-06-30 00:00:00.000
---2017   2016-07-01 00:00:00.000   2017-06-30 00:00:00.000

select
     d.CUSTNMBR Customer_ID,
     d.CUSTNAME Customer_Name,
     'A' as Company,
     d.FY As FY,
     sum(case when month(d.DOCDATE) = 7 then d.SALES else 0 end) AS July,    
     sum(case when month(d.DOCDATE) = 8 then d.SALES else 0 end) AS August,
     sum(case when month(d.DOCDATE) = 9 then d.SALES else 0 end) AS September,
     sum(case when month(d.DOCDATE) = 10 then d.SALES else 0 end) AS October,
     sum(case when month(d.DOCDATE) = 11 then d.SALES else 0 end) AS November,
     sum(case when month(d.DOCDATE) = 12 then d.SALES else 0 end) AS December,
     sum(case when month(d.DOCDATE) = 1 then d.SALES else 0 end) AS January,
     sum(case when month(d.DOCDATE) = 2 then d.SALES else 0 end) AS February,
     sum(case when month(d.DOCDATE) = 3 then d.SALES else 0 end) AS March,
     sum(case when month(d.DOCDATE) = 4 then d.SALES else 0 end) AS April,
     sum(case when month(d.DOCDATE) = 5 then d.SALES else 0 end) AS May,
     sum(case when month(d.DOCDATE) = 6 then d.SALES else 0 end) AS June,
     sum(d.SALES) 'Year to Date'

from
     (select f.FY, s.DOCDATE, s.CUSTNMBR, c.CUSTNAME,
             case s.SOPTYPE
                when 3 then s.DOCAMNT
                when 4 then s.DOCAMNT*-1
             end SALES
     from a1.dbo.S200 s
     left outer join a1.dbo.RM1 c
             on s.CUSTNMBR = c.CUSTNMBR
     cross join fiscalyears f  
     where s.VOIDSTTS = 0 
       and s.SOPTYPE in (3,4) 
       and s.DOCDATE between f.StartRng and f.EndRng) d

group by d.CUSTNMBR, 
         d.CUSTNAME, 
         d.FY
Comments