Switching query depending on date

Transact-SQL
#1

OK as I'm beginning to realize there is not mush you cant do in SQL , the biggest problem I have is asking the write questions to get the end result . So I will try and be as detalied as I can , I have 2 queries both are identical except one is Sales Invoiced (Money in the bank ) and Sales orders by Due date . what I would like to achieve is that when the report is run it changes the data dependent on the month of the year its being run . so in January it shows Salesinvoiced in January and Sales Due for the next 11 months and say in June it shows Jan- June as invoiced and July - Dec as Sales Due

Is this possible or am i asking the wrong questions . Many thanks for any help provide

Invoiced
Category Jan Feb Mar Apr May June July Aug Sep Oct Nov Dec TotalUnits TotalValue
CLOCK 10.502 3.4158 10.8695 9.8699 12.1716 17.8415 8.3943 15.6929 10.5234 11.8547 0 0 8996 111137.57
Due Date
Category Jan Feb Mar Apr May June July Aug Sep Oct Nov Dec TotalUnits TotalValue
CLOCK 11.441 1.2744 10.6043 10.0274 6.1484 24.2835 11.4676 13.8853 13.6476 14.8613 10.3439 7.7551 11060 135742.478
Combined
Category Jan Feb Mar Apr May June July Aug Sep Oct Nov Dec TotalUnits TotalValue
CLOCK 10.502 3.4158 10.8695 9.8699 12.1716 17.8415 8.3943 15.6929 10.5234 11.8547 10.3439 7.7551 8996 111137.57 
DECLARE @year date
SET @year = GETDATE()

--guarantee @year is Jan 01 00:00:00.
SET @year = DATEADD(YEAR, DATEDIFF(YEAR, 0, @year), 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/1000 ELSE 0 END) AS Jan,
              SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 01, @year) AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 02, @year) 
                       THEN SalesInvoiceItems.ItemValue/1000 ELSE 0 END) AS Feb,
              SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 02, @year) AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 03, @year) 
                       THEN SalesInvoiceItems.ItemValue/1000 ELSE 0 END) AS Mar ,
                 SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 03, @year) AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 04, @year) 
                       THEN SalesInvoiceItems.ItemValue/1000 ELSE 0 END) AS Apr,
              SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 04, @year) AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 05, @year) 
                       THEN SalesInvoiceItems.ItemValue/1000 ELSE 0 END) AS May,
              SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 05, @year) AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 06, @year) 
                       THEN SalesInvoiceItems.ItemValue/1000 ELSE 0 END) AS June ,
   SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 06, @year) AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 07, @year) 
                       THEN SalesInvoiceItems.ItemValue/1000 ELSE 0 END) AS July,
              SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 07, @year) AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 08, @year) 
                       THEN SalesInvoiceItems.ItemValue/1000 ELSE 0 END) AS Aug,
              SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 08, @year) AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 09, @year) 
                       THEN SalesInvoiceItems.ItemValue/1000 ELSE 0 END) AS Sep ,
              SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 09, @year) AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 10, @year) 
                       THEN SalesInvoiceItems.ItemValue/1000 ELSE 0 END) AS Oct ,
              SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 10, @year) AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 11, @year) 
                       THEN SalesInvoiceItems.ItemValue/1000 ELSE 0 END) AS Nov,
              SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 11, @year) AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 12, @year) 
                       THEN SalesInvoiceItems.ItemValue/1000 ELSE 0 END) AS Dec,


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, @year) 
                       THEN SalesInvoiceItems.ItemValue ELSE 0 END) AS TotalValue

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


FROM            Classifications INNER JOIN
                         Products ON Classifications.Classification = Products.Classification INNER JOIN
                         SalesInvoiceItems ON Products.Product = SalesInvoiceItems.Product INNER JOIN
                         SalesInvoices ON SalesInvoiceItems.SalesInvoice = SalesInvoices.SalesInvoice INNER JOIN
                         Customers ON SalesInvoices.Customer = Customers.Customer


WHERE  --SalesInvoices.EffectiveDate >= @StartDate
 -- AND SalesInvoices.EffectiveDate < @EndDate
 Customers.CustomerID IN ('JOHNSTE_','JOHNSTE_1','JOHNSTE3','JOHNSTE4','JOHNSTE5','JOHNSTE8')

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

DECLARE @year date
SET @year = GETDATE()

--guarantee @year is Jan 01 00:00:00.
SET @year = DATEADD(YEAR, DATEDIFF(YEAR, 0, @year), 0)

SELECT    

  dbo.wfn_GetUDFNvarchar ('Category','Products',Products.Product)AS 'Category',

  SUM(CASE WHEN SalesOrders.DueDate >= DATEADD(MONTH, 00, @year) AND 
                        SalesOrders.DueDate <  DATEADD(MONTH, 01, @year) 
                   THEN SalesOrderItems.ItemValue/1000 ELSE 0 END) AS Jan,
          SUM(CASE WHEN SalesOrders.DueDate >= DATEADD(MONTH, 01, @year) AND 
                        SalesOrders.DueDate <  DATEADD(MONTH, 02, @year) 
                   THEN SalesOrderItems.ItemValue/1000 ELSE 0 END) AS Feb,
          SUM(CASE WHEN SalesOrders.DueDate >= DATEADD(MONTH, 02, @year) AND 
                        SalesOrders.DueDate <  DATEADD(MONTH, 03, @year) 
                   THEN SalesOrderItems.ItemValue/1000 ELSE 0 END) AS Mar ,
             SUM(CASE WHEN SalesOrders.DueDate >= DATEADD(MONTH, 03, @year) AND 
                        SalesOrders.DueDate <  DATEADD(MONTH, 04, @year) 
                   THEN SalesOrderItems.ItemValue/1000 ELSE 0 END) AS Apr,
          SUM(CASE WHEN SalesOrders.DueDate >= DATEADD(MONTH, 04, @year) AND 
                        SalesOrders.DueDate <  DATEADD(MONTH, 05, @year) 
                   THEN SalesOrderItems.ItemValue/1000 ELSE 0 END) AS May,
          SUM(CASE WHEN SalesOrders.DueDate >= DATEADD(MONTH, 05, @year) AND 
                        SalesOrders.DueDate <  DATEADD(MONTH, 06, @year) 
                   THEN SalesOrderItems.ItemValue/1000 ELSE 0 END) AS June ,
   SUM(CASE WHEN SalesOrders.DueDate >= DATEADD(MONTH, 06, @year) AND 
                        SalesOrders.DueDate <  DATEADD(MONTH, 07, @year) 
                   THEN SalesOrderItems.ItemValue/1000 ELSE 0 END) AS July,
          SUM(CASE WHEN SalesOrders.DueDate >= DATEADD(MONTH, 07, @year) AND 
                        SalesOrders.DueDate <  DATEADD(MONTH, 08, @year) 
                   THEN SalesOrderItems.ItemValue/1000 ELSE 0 END) AS Aug,
          SUM(CASE WHEN SalesOrders.DueDate >= DATEADD(MONTH, 08, @year) AND 
                        SalesOrders.DueDate <  DATEADD(MONTH, 09, @year) 
                   THEN SalesOrderItems.ItemValue/1000 ELSE 0 END) AS Sep ,
          SUM(CASE WHEN SalesOrders.DueDate >= DATEADD(MONTH, 09, @year) AND 
                        SalesOrders.DueDate <  DATEADD(MONTH, 10, @year) 
                   THEN SalesOrderItems.ItemValue/1000 ELSE 0 END) AS Oct ,
          SUM(CASE WHEN SalesOrders.DueDate >= DATEADD(MONTH, 10, @year) AND 
                        SalesOrders.DueDate <  DATEADD(MONTH, 11, @year) 
                   THEN SalesOrderItems.ItemValue/1000 ELSE 0 END) AS Nov,
          SUM(CASE WHEN SalesOrders.DueDate >= DATEADD(MONTH, 11, @year) AND 
                        SalesOrders.DueDate <  DATEADD(MONTH, 12, @year) 
                   THEN SalesOrderItems.ItemValue/1000 ELSE 0 END) AS Dec,


SUM(CASE WHEN SalesOrders.DueDate >= @year AND 
                        SalesOrders.DueDate <  DATEADD(MONTH, 12, @year) 
                   THEN SalesOrderItems.Quantity ELSE 0 END) AS TotalUnits,

SUM(CASE WHEN SalesOrders.DueDate >= @year AND 
                        SalesOrders.DueDate <  DATEADD(MONTH, 12, @year) 
                   THEN SalesOrderItems.ItemValue ELSE 0 END) AS TotalValue

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


FROM            Classifications INNER JOIN
                     Products ON Classifications.Classification = Products.Classification INNER JOIN
                     SalesOrderItems ON Products.Product = SalesorderItems.Product INNER JOIN
                     Salesorders ON SalesorderItems.Salesorder = Salesorders.Salesorder INNER JOIN
                     Customers ON Salesorders.Customer = Customers.Customer


WHERE  --SalesInvoices.EffectiveDate >= @StartDate
 -- AND SalesInvoices.EffectiveDate < @EndDate
 Customers.CustomerID IN ('JOHNSTE_','JOHNSTE_1','JOHNSTE3','JOHNSTE4','JOHNSTE5','JOHNSTE8')

GROUP BY
 dbo.wfn_GetUDFNvarchar ('Category','Products',Products.Product)
 ORDER BY dbo.wfn_GetUDFNvarchar ('Category','Products',Products.Product) ASC
#5

HI , sorry for the amount of code , in an Ideal world I would like to keep it looking like this ,not sure how to get the tally into the CASE

Category Jan Feb Mar Apr May June July TotalValue Aug Sep Oct Nov Dec TotalUnits
CANVAS 18.9745 0.00 3.9554 4.2176 6.1185 11.7495 5.5413 58427.6399 0.00 4.2317 0.00 3.6375 0.00 1993.00000
CARD 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00000
CHRISTMAS CARDS 0.00 0.00 0.00 0.00 0.00 0.00 0.00 65321.28 16.5161 0.00 24.5076 24.2953 0.00 7356.00000
CLOCK 11.441 1.2744 10.6043 10.0274 6.1484 24.2835 11.4676 137912.218 13.8853 13.6476 14.8613 12.5136 7.7551 11275.00000
FRAMED 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00000
MIRROR 34.8164 7.2016 4.3016 75.0457 30.9545 38.8188 52.6743 300702.4548 31.3663 17.0598 6.1728 0.00 2.2845 9349.00000
NONE 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00000
ORIGINAL 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00000
PHOTOFRAME 0.00 0.00 0.00 8.1773 2.5492 2.482 4.5591 20478.4882 0.00 0.00 0.00 0.00 2.7098 8112.00000
PICTURE GALLERY 6.5857 0.00 2.6576 1.5885 3.5846 1.0691 3.0824 24682.2068 0.00 1.1334 0.00 4.9802 0.00 977.00000
PICTURE SMALL 27.724 0.00 8.2821 3.5586 15.8449 8.815 31.68 134335.1834 0.00 17.0148 2.9195 18.4896 0.00 13304.00000
#7

please take a look at this

#10

hi

i tried to do the pivot

i deleted some of my posts

SQL ... 
SELECT category, 
       [1] AS jan, 
       [2] AS feb, 
       [3] AS march, 
       [4] AS apr, 
       [5] AS may, 
       [6] AS jun 
FROM   (SELECT a.category, 
               Datepart(m, a.effectivedate) AS month1, 
               ( a.itemvalue / 1000 )       AS itemvalue 
        FROM   (SELECT * 
                FROM   classifications a 
                       INNER JOIN products b 
                               ON a.classification = b.classification 
                       INNER JOIN salesinvoiceitems c 
                               ON b.product = c.product 
                       INNER JOIN salesinvoices d 
                               ON c.salesinvoice = d.salesinvoice 
                       INNER JOIN customers f 
                               ON d.customer = f.customer) a) source 
       PIVOT ( Sum(itemvalue) 
             FOR month1 IN ( [1], 
                             [2], 
                             [3], 
                             [4], 
                             [5], 
                             [6] ) ) AS pvtmonth
#11

Hi Bob

Does the pivot work ???

I don't have any data to work with

Very curious :grinning::grinning::grinning:

#12

Hi , Pivot works fine , what I’m trying to achieve is something like the below. My thought is that if the invoice value is greater than 0 then we must be in or past the invoiced month . Just not sure how to structure it correctly . Hope that makes sense

  SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 00, @year) AND 
                            SalesInvoices.EffectiveDate <  DATEADD(MONTH, 01, @year) 
                       THEN SalesInvoiceItems.ItemValue/1000 ,IF  0 THEN SalesOrders.DueDate >= DATEADD(MONTH, 00, @year) AND 
                        SalesOrders.DueDate <  DATEADD(MONTH, 01, @year) 
                   THEN SalesOrderItems.ItemValue/1000 ELSE 0 END) AS Jan,
#13

Hi

I tried to work on it and came up with this .. Hope it helps :slight_smile::slight_smile:

Question is HOW do we test this ????

My Rewording 
Sum(CASE 
             WHEN Datepart(m, salesinvoices.effectivedate) = 1 
                  AND salesinvoiceitems.itemvalue / 1000 = 0 
                  AND Datepart(m, salesorders.duedate) = 1 THEN 
             salesorderitems.itemvalue / 1000 
             ELSE 0 
           END) AS Jan
#14

HI , Thanks for your help on this , I put this in but its returned 0 values , I've tried to rewrite to work with the Year info i'm currently using but i'm clearly missing something

     SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 00, @year) AND 
                           SalesInvoices.EffectiveDate <  DATEADD(MONTH, 01, @year) 
					AND	salesinvoiceitems.itemvalue / 1000  =0
					AND		SalesOrders.DueDate >= DATEADD(MONTH, 00, @year) AND 
                       SalesOrder.DueDate <  DATEADD(MONTH, 01, @year) 
              THEN salesorderitems.itemvalue / 1000 ELSE 0 END ) AS Jan
#15

hi

is it possible to give some sample data to work with ??
Possibly I could help you out

drop table #sampledata
go

create table #sampledate
(
name varchar(100),
age int
)
go

insert into #sampledata select 'pam',40
insert into #sampledata select 'adam',15
insert into #sampledata select 'bob',25
go