two separate years in same query? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server Development (2000)

two separate years in same query?

Author

Topic

brendita
Starting Member

38 Posts

Posted - 2005-07-18 : 16:00:37

Currently I have this query that gives me three columns. I need to also pull the CASE column, that I am suming, but for the year before. Is there anyway to do that using the same query so I can use it in my application?


SELECT act.AcctType,a.AccountName, 
CASE WHEN act.DebitIncrease = 1 THEN SUM(j.Debit - j.Credit) 
ELSE SUM(j.Credit - j.Debit) END AS Balance 
FROM GLjournalentry j INNER JOIN GLaccounts a 
ON j.AccountID = a.AccountID INNER JOIN AcctTypes act 
ON a.AccountType = act.AcctType 
WHERE act.ParentAcctType = 'Current Earnings' 
AND j.BranchID = '1' 
AND j.JournalDate >= CAST(CONVERT(varChar(2),6)+ '/1/'+ CONVERT(varChar(4),2005) AS DateTime) 
AND j.JournalDate < CAST(CONVERT(varChar(2),7)+ '/1/'+ CONVERT(varChar(4),2005) AS DateTime) 
AND act.AcctType = 'Income' 
GROUP BY a.AccountName, act.DebitIncrease, act.AcctType 
ORDER BY a.AccountName ASC

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-07-18 : 16:17:51

See if this works:

SELECT act.AcctType,a.AccountName,
CASE WHEN j.JournalDate >= CONVERT(datetime, '06/01/2005') AND j.JournalDate < CONVERT(datetime, '07/01/2005')
THEN CASE WHEN act.DebitIncrease = 1 THEN SUM(j.Debit - j.Credit)
ELSE SUM(j.Credit - j.Debit) END
END AS Balance,
CASE WHEN j.JournalDate >= CONVERT(datetime, '06/01/2004') AND j.JournalDate < CONVERT(datetime, '07/01/2004')
THEN CASE WHEN act.DebitIncrease = 1 THEN SUM(j.Debit - j.Credit)
ELSE SUM(j.Credit - j.Debit) END
END AS PrevYrBalance
FROM GLjournalentry j INNER JOIN GLaccounts a
ON j.AccountID = a.AccountID INNER JOIN AcctTypes act
ON a.AccountType = act.AcctType
WHERE act.ParentAcctType = 'Current Earnings'
AND j.BranchID = '1'
AND act.AcctType = 'Income'
GROUP BY a.AccountName, act.DebitIncrease, act.AcctType
ORDER BY a.AccountName ASC

brendita
Starting Member

38 Posts

Posted - 2005-07-18 : 16:42:07

Ummmm, it doesn't really work how I need it to. It is pulling multiple instances of AccountName. This is the output I currently have with my code:

Income Delivery 30.0
Income Hardware Distrib -1.0
Income Installation 3888.0
Income Sales 498849.35750000115
Income Sales Discounts -0.02
Income Scrap Metal 1624.5
Income Shipping Income 5196.8999999999924

But I just need a 4th column with amounts from the previous year. Any ideas?

brendita
Starting Member

38 Posts

Posted - 2005-07-18 : 19:07:23

I figured it out:


SELECT act.AcctType,a.AccountName, 
CASE WHEN act.DebitIncrease = 1 THEN (SELECT SUM(j2.Debit - j2.Credit) from GLjournalentry j2 WHERE j.accountID = j2.accountID
AND j2.branchID=j.branchID AND j2.JournalDate >= CAST(CONVERT(varChar(2),6)+ '/1/'+ CONVERT(varChar(4),2005) AS DateTime) 
AND j2.JournalDate < CAST(CONVERT(varChar(2),7)+ '/1/'+ CONVERT(varChar(4),2005) AS DateTime))
ELSE (SELECT SUM(j2.Credit - j2.Debit) from GLjournalentry j2 WHERE j.accountID = j2.accountID
AND j2.branchID=j.branchID AND j2.JournalDate >= CAST(CONVERT(varChar(2),6)+ '/1/'+ CONVERT(varChar(4),2005) AS DateTime) 
AND j2.JournalDate < CAST(CONVERT(varChar(2),7)+ '/1/'+ CONVERT(varChar(4),2005) AS DateTime)) END AS Balance,
CASE WHEN act.DebitIncrease = 1 THEN (SELECT SUM(j2.Debit - j2.Credit) from GLjournalentry j2 WHERE j.accountID = j2.accountID
AND j2.branchID=j.branchID AND j2.JournalDate >= CAST(CONVERT(varChar(2),6)+ '/1/'+ CONVERT(varChar(4),2004) AS DateTime) 
AND j2.JournalDate < CAST(CONVERT(varChar(2),7)+ '/1/'+ CONVERT(varChar(4),2004) AS DateTime))
ELSE (SELECT SUM(j2.Credit - j2.Debit) from GLjournalentry j2 WHERE j.accountID = j2.accountID
AND j2.branchID=j.branchID AND j2.JournalDate >= CAST(CONVERT(varChar(2),6)+ '/1/'+ CONVERT(varChar(4),2004) AS DateTime) 
AND j2.JournalDate < CAST(CONVERT(varChar(2),7)+ '/1/'+ CONVERT(varChar(4),2004) AS DateTime)) END AS Balance
FROM GLjournalentry j INNER JOIN GLaccounts a 
ON j.AccountID = a.AccountID INNER JOIN AcctTypes act 
ON a.AccountType = act.AcctType 
WHERE act.ParentAcctType = 'Current Earnings' 
AND j.BranchID = '1' 
AND act.AcctType = 'Income' 
GROUP BY a.AccountName, act.DebitIncrease, act.AcctType, j.AccountID, j.BranchID
ORDER BY a.AccountName ASC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources