gettrans

USE [SpendAl]
GO
/****** Object:  StoredProcedure [dbo].[GetTransactions]    Script Date: 03/19/2011 22:48:41 ******/
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 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