Different Product groups in a department

Version 4
1

Hi Guys.

Ill try and Explain my problem.

I need to split my report down to show Food and Alcohol Separate in only one department.

I have Three Separate Departments. 2 Serve Alcohol Only and 1 Serves Alcohol and Food.

Now each department has a different ticket type so that I can see how much each department has taken.

the non food bars item sales are exactly correct as they are only adding up the item group sales for alcohol.

the bar that serves food aswell, the ticket type reports the total sales but I need to pull how much of that was food and how much of that was alcohol.

Hope I’m making sense.

Cheers

2

Unless there is an easier way of having one product sold in 3 different departments and reported seperatly for each department???

3

What are your departments? Sorry not following. Alcohol and food?

4

I created a sample pivot report that displays item quantity summary by department.

Pasted image2103×1338 151 KB

It fetches department names dynamically and sorts them alphabetically in report but you need change table header to match it your department names.

This how you need to configure SQL Script

Pasted image2103×1338 151 KB

… and the script.

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
DECLARE @FieldName AS NVARCHAR(MAX)
 
--Get distinct values of the PIVOT Column 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
       +'FORMAT(ISNULL('+ QUOTENAME(Name) +',0),''0.##;-0.##;-'') as '+Name+''
FROM (SELECT TOP 99 Name FROM Departments order by Name) AS DepartmentNames

SELECT @FieldName= ISNULL(@FieldName + ',','') 
       +QUOTENAME(Name)
FROM (SELECT TOP 99 Name FROM Departments order by Name) AS DepartmentNames
 
--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
 N'Select MenuItemName,' + @ColumnName + ' from
(select 
    O.MenuItemName as MenuItemName,
    O.Quantity as Quantity,
	D.Name as DepartmentName
  from Orders as O
  inner join Tickets as T on T.Id = O.TicketId
  inner join Departments as D on D.Id = T.DepartmentId
  where O.DecreaseInventory = 1) as DataTable
  PIVOT(
    Sum(Quantity)
	For DepartmentName 
	IN (' + @FieldName + ')
  ) as PivotTable'

--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery
5 Likes