Inner join and case 1 or 2 then add 1 to sum

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 2005 Forums

Transact-SQL (2005)

Inner join and case 1 or 2 then add 1 to sum

Author

Topic

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2009-07-14 : 09:31:30

After many attempts, need some input.

Here is an inner join:


INNER JOIN (SELECT ImportAlertID, COUNT(*) AS numInvoices, SUM(invorigamt) AS sumInvoices,
CreditStatus AS numCredit 			
FROM dbo.invoicenotadvanced GROUP BY ImportAlertID, CreditStatus) inv ON a.ImportAlertID = inv.ImportAlertID

Now, the CreditStatus column may be 0, 1, 2, or 3.
If it's 0 or 1 then need to have a running sum to add 1 to it.

Been trying to get a case statement working but with no luck.
So, I stripped it out for an easier read for suggestions.

But here it is and it does not work but can give a better idea...

INNER JOIN (SELECT ImportAlertID, COUNT(*) AS numInvoices, SUM(invorigamt) AS sumInvoices,
(CASE WHEN creditstatus = 1 THEN (SET @creditCnt = @creditCnt + 1) END)
AS numCredit

Thanks,

Zath

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-14 : 10:27:39

SELECT ImportAlertID, COUNT(*) AS numInvoices, SUM(invorigamt) AS sumInvoices,
SUM(CASE WHEN creditstatus = 1 THEN 1 else 0 END) AS numCredit
GROUP BY ImportAlertID

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2009-07-14 : 10:43:36

That will not increment my variable as I need.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-14 : 11:48:31

show the whole SP

also, your going to end up with multiple records, so a variable can't be assigned the value

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2009-07-14 : 11:57:28

Yes, discovered can't increment in the case statement.
So, thought about doing another join, but that won't work...

DECLARE @type INT
SET @type = 3

DECLARE @creditCnt INT
SET @creditCnt = 0
		
SELECT a.ImportAlertID, a.ClientID, b.ImportType, a.ProcessedBy, c.Name, a.ProcessDate, 
inv.numInvoices,
inv.sumInvoices,
inv0.numCredit0,
inv1.numCredit1,
c.CustomerCreditReqd

FROM ImportAlert a
INNER JOIN ImportType b ON a.ImportTypeID = b.ImportTypeID
INNER JOIN Client c ON a.ClientID = c.pk_id

INNER JOIN (SELECT  ImportAlertID, COUNT(*) AS numCredit0						
FROM dbo.invoicenotadvanced WHERE creditstatus = 0 GROUP BY ImportAlertID) inv0
ON a.ImportAlertID = inv0.ImportAlertID

INNER JOIN (SELECT  ImportAlertID, COUNT(*) AS numCredit1						
FROM dbo.invoicenotadvanced WHERE creditstatus = 1 GROUP BY ImportAlertID) inv1 
ON a.ImportAlertID = inv1.ImportAlertID

INNER JOIN (SELECT ImportAlertID, COUNT(*) AS numInvoices, SUM(invorigamt) AS sumInvoices	
					
FROM dbo.invoicenotadvanced GROUP BY ImportAlertID) inv 
ON a.ImportAlertID = inv.ImportAlertID
WHERE a.CompleteStatus = 0 AND a.ImportTypeID = @type
	AND DATEDIFF(day, ProcessDate, GETDATE()) <= 
	(SELECT Days FROM ImportMaxDays WHERE ImportTypeID = @type)
		ORDER BY c.Name

The above won't work either.
It won't bring back any rows of course if creditstatus = 2 in all rows.

Need a count when 0 or 1.

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2009-07-14 : 12:11:00

Ok, think I got it working.

SELECT a.ImportAlertID, a.ClientID, b.ImportType, a.ProcessedBy, c.Name, a.ProcessDate, 
inv.numInvoices,
inv.sumInvoices,

(SELECT COUNT(*) 
	FROM invoicenotadvanced inv0 WHERE creditstatus = 0 
	AND a.ImportAlertID = inv0.ImportAlertID) AS numCredit0,
(SELECT COUNT(*) 
	FROM invoicenotadvanced inv1 WHERE creditstatus = 1 
	AND a.ImportAlertID = inv1.ImportAlertID) AS numCredit1
FROM ImportAlert a
.
.
.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-14 : 14:35:25

use cross apply instead of subquery


SELECT a.ImportAlertID, a.ClientID, b.ImportType, a.ProcessedBy, c.Name, a.ProcessDate, 
inv.numInvoices,
inv.sumInvoices,
inv0.numCredit0,
inv0.numCredit1
FROM ImportAlert a
.
.
.
CROSS APPLY (SELECT COUNT(CASE WHEN creditstatus = 0 THEN 1 ELSE NULL END) AS numCredit0, COUNT(CASE WHEN creditstatus = 1 THEN 1 ELSE NULL END) AS numCredit1
	FROM invoicenotadvanced  WHERE  
	AND a.ImportAlertID = ImportAlertID) inv0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources