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