SQLTeam.com | Weblogs | Forums

Colour change rows


#1

Ok so i thought I cracked this until I passed to a colleague to check the Math and I have realized its the colour of the rows that's the issue . Is there away to control the colour by the declared @year in this Query? . The report data adds up its just defining the years that is problematic.
Query

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     
  dbo.wfn_GetUDFNvarchar ('Category','Products',Products.Product)AS 'Category',
CommissionClasses.CommissionClassID,
  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, 01, @year) AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 02, @year) 
                       THEN SalesInvoiceItems.ItemValue ELSE 0 END) AS Feb,
				
              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, 03, @year) AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 04, @year) 
                       THEN SalesInvoiceItems.ItemValue ELSE 0 END) AS Apr,


              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, 05, @year) AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 06, @year) 
                       THEN SalesInvoiceItems.ItemValue ELSE 0 END) AS June ,


   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, 07, @year) AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 08, @year) 
                       THEN SalesInvoiceItems.ItemValue ELSE 0 END) AS Aug,

              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, 09, @year) AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 10, @year) 
                       THEN SalesInvoiceItems.ItemValue ELSE 0 END) AS Oct ,

              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, 11, @year) AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 12, @year) 
                       THEN SalesInvoiceItems.ItemValue ELSE 0 END) AS Dec,



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


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  


 CommissionClasses.CommissionClassId IN (@CommClass)

 
 
 
GROUP BY
 dbo.wfn_GetUDFNvarchar ('Category','Products',Products.Product),
CommissionClasses.CommissionClassID
 


UNION ALL 


SELECT     
  dbo.wfn_GetUDFNvarchar ('Category','Products',Products.Product)AS 'Category',
CommissionClasses.CommissionClassID,
 
 SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 00, @yearB) AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 01, @yearB) 
                       THEN SalesInvoiceItems.ItemValue ELSE 0 END) AS Jan,

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

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

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


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

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


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

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

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

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


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





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 


 CommissionClasses.CommissionClassId IN (@CommClass)

GROUP BY
 dbo.wfn_GetUDFNvarchar ('Category','Products',Products.Product),
CommissionClasses.CommissionClassID
 ORDER BY dbo.wfn_GetUDFNvarchar ('Category','Products',Products.Product) ASC

Results

Code being used currently to change the colour

'*************************************************************************
' -- Display alternate color banding (defined below) in detail rows
' -- Call from BackgroundColor property of all detail row textboxes
'*************************************************************************
Function AlternateColor(Byval rowNumber as integer) As String
    Dim OddColor As String = "AntiqueWhite"
    Dim EvenColor As String = "IndianRed"

    If rowNumber mod 2 = 0 then 
        Return EvenColor
    Else
        Return OddColor
    End If
End Function