something wrong here data not returning on all

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

something wrong here data not returning on all

Author

Topic

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2011-01-02 : 13:57:01

HI Perhaps you see some reason here why I am not getting
data back for all customers?

GO
/****** Object: StoredProcedure [dbo].[GetTransactions] Script Date: 01/02/2011 13:54:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

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

@CustomerId INT = NULL

AS

BEGIN

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

END

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-02 : 17:22:35

will oly give data for customers with transactions (and only for @CustomerId).
(I assume that datecreated is on the transaction table.
Maybe you want

FROM Customers c
LEFT JOIN Transactions t
ON t.CustomerId = c.CustomerId
AND DateCreated >= DATEADD(YYYY, - 2, GETDATE())
AND DateCreated <= GETDATE()
LEFT JOIN Departments d ON t.DepartmentId = d.DepartmentId
LEFT JOIN Categories ct ON t.CategoryId = ct.CategoryId
LEFT JOIN Items i ON t.ItemId = i.ItemId
WHERE c.CustomerId = @CustomerId
ORDER BY DateCreated DESC

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2011-01-02 : 18:26:45

"LEFT JOIN Customers c ON t.CustomerId = c.CustomerId"

Not the answer to your question, but can you really have transactions for customers that don't exist? or, perhaps more likely, transactions for NO customer?

If I was doing the design I'd be a bit anxious about Transactions that have no matching Category too ...

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2011-01-02 : 18:53:28

Thank you both, I didn't write this, it was modified a while back so
I can pass this along to the DBA.

quote:
Originally posted by Kristen

"LEFT JOIN Customers c ON t.CustomerId = c.CustomerId"

Not the answer to your question, but can you really have transactions for customers that don't exist? or, perhaps more likely, transactions for NO customer?

If I was doing the design I'd be a bit anxious about Transactions that have no matching Category too ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources