quote:

---

Originally posted by AdamWest

I have to make a change to this sp. Instead of passing all rows on the department level, I have codes a new table "account.UserDepartments'
UserID (PK,FK, int, not null)
DepartmentID (PK, FK, int, not null)
CustomerID (PK, FK, int, not null)

And we want to include the rows by user department, for example
say the customer has 4 departments> Medical, Dietary, Housekeeping, and Nutrition. THe user has in that table a row for Medical and Nutrition, then the SP should return only those rows for Medical and nutrition departments.
but the only caveat is, if the customer has only ONE department, not 4 for example, then we do want to process the 1 department. as it would be very tedious to enter rights to a one department customer.
Here you have the sp, On our DB, the highest level is department, then category, then item. I have to integrate this new table to this sp: while there are other sp's i think this is the main one, getting the transactions rows in the first place.

USE [SpendDashb]
GO
/****** Object: StoredProcedure [dbo].[GetTransactions] Script Date: 07/24/2011 11:48:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- drop procedure GetTransactions
ALTER PROCEDURE [dbo].[GetTransactions]

@CustomerId INT = NULL

AS

BEGIN


INSERT INTO App_Log
(Info, Start, EndDate)
VALUES
('GetTransactions', getDate(), null )
Declare @logId int
SET @logId = @@IDENTITY

SET NOCOUNT ON;

SELECT LTRIM(RTRIM(ISNULL(d.DepartmentName, ''))) AS DepartmentName
,d.DepartmentId AS DepartmentId
,ISNULL(t.Cost, 0) AS Expense
,ISNULL(t.Quantity , 0)AS Quantity
,LTRIM(RTRIM(ISNULL(c.CustomerName, ''))) AS CUSTNAME
,LTRIM(RTRIM(ISNULL(i.ItemName, ''))) AS Description
,t.DateCreated AS DateCreated
,LTRIM(RTRIM(ISNULL(ct.CategoryLongName, ''))) AS CategoryName
,LTRIM(RTRIM(ISNULL(ct.CategoryName, ''))) AS USCATVAL
--,'00000' AS Budget
,LTRIM(RTRIM(ISNULL(t.InvoiceNumber,''))) as InvoiceNum
FROM Transactions t
LEFT JOIN Departments d ON t.DepartmentId = d.DepartmentId
LEFT JOIN Customers c ON t.CustomerId = c.CustomerId
LEFT JOIN Categories ct ON t.CategoryId = ct.CategoryId
LEFT JOIN account.UserDepartments ud ON ud.DepartmentID = d.DepartmentId AND ud.CustomerId = c.CustomerId
LEFT JOIN Items i ON t.ItemId = i.ItemId
WHERE DateCreated >= DATEADD(YYYY, - 2, GETDATE())
AND DateCreated <= GETDATE()
AND t.CustomerId = @CustomerId
ORDER BY DateCreated DESC



UPDATE App_Log
SET EndDate = GETDATE()
where ID = @logId

END

---

USE [SpendDashb]
GO
/****** Object: StoredProcedure [dbo].[GetTransactions] Script Date: 07/24/2011 11:48:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- drop procedure GetTransactions
ALTER PROCEDURE [dbo].[GetTransactions] 

@CustomerId INT = NULL 

AS 

BEGIN 


INSERT INTO App_Log
(Info, Start, EndDate)
VALUES
('GetTransactions', getDate(), null )
Declare @logId int
SET @logId = @@IDENTITY

SET NOCOUNT ON; 

SELECT LTRIM(RTRIM(ISNULL(d.DepartmentName, ''))) AS DepartmentName
,d.DepartmentId AS DepartmentId
,ISNULL(t.Cost, 0) AS Expense
,ISNULL(t.Quantity , 0)AS Quantity
,LTRIM(RTRIM(ISNULL(c.CustomerName, ''))) AS CUSTNAME
,LTRIM(RTRIM(ISNULL(i.ItemName, ''))) AS Description
,t.DateCreated AS DateCreated
,LTRIM(RTRIM(ISNULL(ct.CategoryLongName, ''))) AS CategoryName
,LTRIM(RTRIM(ISNULL(ct.CategoryName, ''))) AS USCATVAL
--,'00000' AS Budget
,LTRIM(RTRIM(ISNULL(t.InvoiceNumber,''))) as InvoiceNum
FROM Transactions t
LEFT JOIN Departments d ON t.DepartmentId = d.DepartmentId
LEFT JOIN Customers c ON t.CustomerId = c.CustomerId
LEFT JOIN Categories ct ON t.CategoryId = ct.CategoryId
LEFT JOIN account.UserDepartments ud ON ud.DepartmentID = d.DepartmentId AND ud.CustomerId = c.CustomerId
LEFT JOIN (SELECT UserID,COUNT(DepartmentID) AS Dept_Cnt FROM account.UserDepartments GROUP BY UserID)ud1 ON ud1.UserID=ud.UserID
LEFT JOIN Items i ON t.ItemId = i.ItemId
WHERE DateCreated >= DATEADD(YYYY, - 2, GETDATE()) 
AND DateCreated <= GETDATE()
AND t.CustomerId = @CustomerId
AND (ud1.Dept_Cnt <=1 OR ud.UserID IS NOT NULL)
ORDER BY DateCreated DESC 



UPDATE App_Log
SET EndDate = GETDATE()
where ID = @logId

END