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)
 Grouping/Sum/and tables Help..

Author  Topic 

hbadministrator
Posting Yak Master

120 Posts

Posted - 2013-03-27 : 09:53:56
Hello again I have a question I am almost positive you guys can help me with. I am trying to take a view I created that has a union to get all the data together. This is working wonderful.



Now I am trying to take that view and refine the data. I need to do a case when that sums up balance due before a date and it sums all transactions. Then puts a balance due and the rest of the transactions would not be summed up. Below is the data then I will put the code and the end results I am trying to get.

Example.
Cust-No Date Credits Debit Baldue
10837 9/26/2012 NULL -306 -306
10837 10/31/2012 NULL -306 -306
10837 11/30/2012 NULL -306 -306
10837 12/24/2012 NULL -306 -306
10837 2/1/2013 NULL -306 -306
10837 2/21/2013 NULL -306 -306
10837 3/15/2013 NULL -306 -306
10837 8/1/2012 NULL -278.34 -278.34
10837 1/16/2013 NULL -3.09 -3.09
10837 1/16/2013 NULL -2.97 -2.97
10837 1/16/2013 NULL -2.07 -2.07
10837 1/16/2013 NULL -1.95 -1.95
10837 1/16/2013 NULL -0.93 -0.93
10837 1/11/2013 342 NULL 342

10837 1/15/2013 518 NULL 518

10837 2/19/2013 526.67 NULL 526.67
10837 11/16/2012 576.31 NULL 576.31
10837 1/14/2013 651.25 NULL 651.25

SELECT [Cust-no], CASE WHEN Transactions.date < '02/28/2013' THEN SUM(Baldue) END AS PreBalance
FROM dbo.Transactions
WHERE ([Cust-no] = '010837')
GROUP BY [Cust-no]

Customer Date Credit Debit Baldue PreBalance
10837 Null Null Null Null 488.88
10837 3/15/2013 NULL -306 -306 Null

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-03-27 : 10:48:06
You may use this as an idea to get it solved

SELECT CustNo,SUM(BalDue)
FROM (
SELECT *,CASE WHEN Tdate < '02/28/2013' THEN 1 ELSE 0 END AS PreBalance
FROM Table1
)A GROUP BY CustNo,PreBalance

Note: you'll need to change it as per your requirements

Cheers
MIK
Go to Top of Page
   

- Advertisement -