CASE Statement

bobdragon12 · August 22, 2018, 11:18am

OK I've been working on a this year /last year query and the data stacks up but looks a bit messy and I'm not sure how or if i can alter the layout

Current Query is

DECLARE @year date
DECLARE @yearB date
SET @year = GETDATE()
SET @yearB = GETDATE()

SET @year = DATEADD(YEAR, DATEDIFF(YEAR, 0, @year), 0)
SET @yearB = DATEADD(YEAR, DATEDIFF(YEAR, 0, @yearB)-1, 0)


SELECT     --   MAX (Customers.CustomerId) CustomerId, 
 --SUM (SalesInvoiceItems.CurItemValue) AS ItemValue ,
 --MAX (SalesInvoices.EffectiveDate) AS EffectiveDate,
  dbo.wfn_GetUDFNvarchar ('Category','Products',Products.Product)AS 'Category',

  SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 00, @year) AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 01, @year) 
                       THEN SalesInvoiceItems.ItemValue ELSE 0 END) AS Jan,
 SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 00, @yearB) AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 01, @yearB) 
                       THEN SalesInvoiceItems.ItemValue ELSE 0 END) AS Jan1,

              SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 01, @year) AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 02, @year) 
                       THEN SalesInvoiceItems.ItemValue ELSE 0 END) AS Feb,
					   SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 01, @yearB) AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 02, @yearB) 
                       THEN SalesInvoiceItems.ItemValue ELSE 0 END) AS Feb1,
              SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 02, @year) AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 03, @year) 
                       THEN SalesInvoiceItems.ItemValue ELSE 0 END) AS Mar ,
SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 02, @yearB) AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 03, @yearB) 
                       THEN SalesInvoiceItems.ItemValue ELSE 0 END) AS Mar1 ,
                 SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 03, @year) AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 04, @year) 
                       THEN SalesInvoiceItems.ItemValue ELSE 0 END) AS Apr,
SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 03, @yearB) AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 04, @yearB) 
                       THEN SalesInvoiceItems.ItemValue ELSE 0 END) AS Apr1,

              SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 04, @year) AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 05, @year) 
                       THEN SalesInvoiceItems.ItemValue ELSE 0 END) AS May,
SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 04, @yearB) AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 05, @yearB) 
                       THEN SalesInvoiceItems.ItemValue ELSE 0 END) AS May1,

              SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 05, @year) AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 06, @year) 
                       THEN SalesInvoiceItems.ItemValue ELSE 0 END) AS June ,
					   SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 05, @yearB) AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 06, @yearB) 
                       THEN SalesInvoiceItems.ItemValue ELSE 0 END) AS June1 ,


   SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 06, @year) AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 07, @year) 
                       THEN SalesInvoiceItems.ItemValue ELSE 0 END) AS July,
  SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 06, @yearB) AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 07, @yearB) 
                       THEN SalesInvoiceItems.ItemValue ELSE 0 END) AS July1,

              SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 07, @year) AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 08, @year) 
                       THEN SalesInvoiceItems.ItemValue ELSE 0 END) AS Aug,
					   SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 07, @yearB) AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 08, @yearB) 
                       THEN SalesInvoiceItems.ItemValue ELSE 0 END) AS Aug1,


              SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 08, @year) AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 09, @year) 
                       THEN SalesInvoiceItems.ItemValue ELSE 0 END) AS Sep ,
SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 08, @yearB) AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 09, @yearB) 
                       THEN SalesInvoiceItems.ItemValue ELSE 0 END) AS Sep1 ,

              SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 09, @year) AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 10, @year) 
                       THEN SalesInvoiceItems.ItemValue ELSE 0 END) AS Oct ,
					    SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 09, @yearB) AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 10, @yearB) 
                       THEN SalesInvoiceItems.ItemValue ELSE 0 END) AS Oct1 ,

              SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 10, @year) AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 11, @year) 
                       THEN SalesInvoiceItems.ItemValue ELSE 0 END) AS Nov,
					    SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 10, @yearB) AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 11, @yearB) 
                       THEN SalesInvoiceItems.ItemValue ELSE 0 END) AS Nov1,

              SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 11, @year) AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 12, @year) 
                       THEN SalesInvoiceItems.ItemValue ELSE 0 END) AS Dec,
					     SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 11, @yearB) AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 12, @yearB) 
                       THEN SalesInvoiceItems.ItemValue ELSE 0 END) AS Dec1,

SUM(CASE WHEN SalesInvoices.EffectiveDate >= @year AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 12, @year) 
                       THEN SalesInvoiceItems.Quantity ELSE 0 END) AS TotalUnits,
SUM(CASE WHEN SalesInvoices.EffectiveDate >= @year AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 12, @yearB) 
                       THEN SalesInvoiceItems.Quantity ELSE 0 END) AS TotalUnitsLastYear,
SUM(CASE WHEN SalesInvoices.EffectiveDate >= @yearB AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 12, @year) 
                       THEN SalesInvoiceItems.ItemValue ELSE 0 END) AS TotalValueCurrentYear,

SUM(CASE WHEN SalesInvoices.EffectiveDate >= @yearB AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 12, @yearB) 
                       THEN SalesInvoiceItems.ItemValue ELSE 0 END) AS TotalValueLastYear




FROM            SalesInvoices INNER JOIN
                         SalesInvoiceItems ON SalesInvoices.SalesInvoice = SalesInvoiceItems.SalesInvoice INNER JOIN
                         Products ON SalesInvoiceItems.Product = Products.Product INNER JOIN
                         Customers ON SalesInvoices.Customer = Customers.Customer INNER JOIN
                         CommissionClasses ON Customers.CommissionClass = CommissionClasses.CommissionClass

WHERE 
 Customers.CustomerId IN  ('WILLHIGH','KLONKEL_','WILLBYRK','WILLCAR_','KLONINV_','WILLSTO_','WILLGARF') 
 
GROUP BY
 dbo.wfn_GetUDFNvarchar ('Category','Products',Products.Product)
 ORDER BY dbo.wfn_GetUDFNvarchar ('Category','Products',Products.Product) ASC

and the results look like this

Category	Jan	Jan1	Mar	Apr1	July	Aug	Sep1	Oct1	Dec1	TotalUnits	TotalValueCurrentYear	TotalValueLastYear
ARTBLOCKS	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00000	0.00	0.00
CANVAS	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00000	0.00	0.00
CARD	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00000	0.00	0.00
CHRISTMAS CARDS	-56.76	0.00	0.00	0.00	0.00	261.38	990.72	0.00	0.00	31.00000	1195.34	990.72
CLOCK	-81.21	-13.87	0.00	-9.03	388.82	-90.16	0.00	881.46	57.38	22.00000	1133.39	915.94
DISPLAYS	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	1.00000	0.00	0.00
FRAMED	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00000	0.00	0.00
MIRROR	0.00	0.00	0.00	0.00	0.00	0.00	0.00	248.64	0.00	0.00000	248.64	248.64
PHOTOFRAME	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00000	0.00	0.00
PICTURE GALLERY	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00000	0.00	0.00
PICTURE SMALL	0.00	0.00	-24.31	0.00	445.32	0.00	0.00	2552.50	0.00	44.00000	2973.51	2552.50
PLAQUES	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00000	0.00	0.00
WOODEN PLAQUES	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00000	0.00	0.00

What I would like is Single months and split category's like the below

Category	Jan	Feb	Mar	Apr	May
ARTBLOCKS	0	0	0	0	0
ARTBLOCKS - 1
CANVAS	0	0	0	0	0
CANVAS - 1
CARD	0	0	0	0	0
CARD - 1
CHRISTMAS CARDS	-56.76	0	0	0	0
CHRISTMAS CARDS - 1
CLOCK	-81.21	0	0	0	0

Hope that make sense and many thanks for any assistance

Cheers Jon

bitsmed · August 22, 2018, 4:30pm

Use "union all"

jotorre_riversidedpss.org · August 22, 2018, 4:45pm

Try using this function joining using between or >= and <=.

/****** Object:  UserDefinedFunction [dbo].[MonthlyCalendar]    Script Date: 8/22/2018 9:39:39 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/* RCIT Database Support
   By:   Joe Torre
   On:   Aug 8th 2018
   For:  This funtion returns monthly inerval values for the range passed in
   DECLARE @d date = GetDate();
   SELECT
      BegDt
   ,  EndDt
   FROM dbo.MonthlyCalendar ('20150101', @d);
*/
CREATE FUNCTION [dbo].[MonthlyCalendar] (@BegDate datetime, @EndDate datetime) RETURNS TABLE
AS RETURN
(

	WITH n1 AS (SELECT n FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0))t(n))--10
      , n2 AS (SELECT n.n FROM n1 AS n CROSS JOIN n1)--100
      , n3 AS (SELECT n.n FROM n2 AS n CROSS JOIN n2)--10000
      , nums AS (SELECT 0 num UNION ALL SELECT Row_Number() OVER (ORDER BY(SELECT NULL)) num FROM n3)
      , Calendar (BegDt, EndDt) AS (
                  SELECT 
                        DateAdd(m, nums.num, @BegDate) 
                      , DateAdd(MILLISECOND, -3, DateAdd(m, 1, DateAdd(m, nums.num,@BegDate)))
             
                  FROM nums
                  )
   SELECT
        c.BegDt
      , c.EndDt
   FROM 
      Calendar c
   WHERE
      c.BegDt < @EndDate
)

bobdragon12 · August 22, 2018, 5:09pm

That looks great , sadly my SQL is not on par with yours. Will have to study that to work it out . Will give it a shot

bobdragon12 · August 30, 2018, 1:35pm

Sorry for the late reply but that worked perfectly

bobdragon12 · August 30, 2018, 1:39pm

based on the Union all I now have the report looking good but I want to alternate the row colour but none of the things i have researched seem to work and I can only assume its because of the double up on Category Year and Year - 1 how can I get SSRS to tell that the row is from a specific year