Query!!!! - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

Query!!!!

Author

Topic

m.sofia.hidalgo
Starting Member

3 Posts

Posted - 2011-06-23 : 11:29:29

Hi everyone, I need to display the Available credit of my clients for the week. This is what i have:

Table 1 - Clients
Code
Name
CreditLimit

Table 2 - Receipts
Code
Name
Date
TotalBuy

What i have till now is the following query:

Select T0.CardCode, T0.CardName,T0.CreditLine - Sum(T1.DocTotal) AS Available
From Clients T0 left outer Join Receipts T1 ON T0.CardCode = T1.CardCode
Where DATEPART(WEEK, T1.DocDate) = DATEPART(Week, SYSDATETIME())
Group by T0.CardCode, T0.CardName,T0.CreditLine

This query displays the available credit for the clients who HAVE BOUGHT during the week, but i need to display ALL my clients, even if they havent bought. If they havent bought credit available should be the same as CreditLine in table Clients.

Thanks guys!!

I hope someone can help me!!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-23 : 11:41:47

When you do a left join, and if you have the right table in the WHERE clause, effectively ends up being an inner join. So you need to move the filtering condition to the join. Something like this, where I have also added some additional things I thought you may need:


SELECT
	T0.CardCode,
	T0.CardName,
	T0.CreditLine - ISNULL( SUM(T1.DocTotal),0) AS Available
FROM
	Clients T0
	LEFT OUTER JOIN Receipts T1
		ON  T0.CardCode = T1.CardCode
		and DATEPART(WEEK, T1.DocDate) = DATEPART(Week, SYSDATETIME())
		AND YEAR(T1.DocDate) = YEAR(SYSDATETIME())
GROUP BY
	T0.CardCode,
	T0.CardName,
	T0.CreditLine

m.sofia.hidalgo
Starting Member

3 Posts

Posted - 2011-06-23 : 12:22:24

Hey, thanks a lot. That worked nicely, the only problem is is the column Available some clients have Null. How can i validate that if he/she hasnt bought that available = CreditLine??

Thanks!!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-23 : 12:43:31

Not sure why it is showing NULL. I thought the ISNULL would make Available equal to CreditLine:


SELECT
	T0.CardCode,
	T0.CardName,
	T0.CreditLine - ISNULL( SUM(T1.DocTotal),0) AS Available
FROM

Is CreditLine null for those CardCodes?

m.sofia.hidalgo
Starting Member

3 Posts

Posted - 2011-06-23 : 13:07:31

You are right. I didnt include the ISNULL, now it works PERFECTLY!

thanks a lot!!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-23 : 13:44:27

yay! and, you are quite welcome!!

If your data would span more than one year, you may also want to add that line I added for the year, if you haven't already:


...
AND YEAR(T1.DocDate) = YEAR(SYSDATETIME())
...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources