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.
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 Baldue10837 9/26/2012 NULL -306 -30610837 10/31/2012 NULL -306 -30610837 11/30/2012 NULL -306 -30610837 12/24/2012 NULL -306 -30610837 2/1/2013 NULL -306 -30610837 2/21/2013 NULL -306 -30610837 3/15/2013 NULL -306 -30610837 8/1/2012 NULL -278.34 -278.3410837 1/16/2013 NULL -3.09 -3.0910837 1/16/2013 NULL -2.97 -2.9710837 1/16/2013 NULL -2.07 -2.0710837 1/16/2013 NULL -1.95 -1.9510837 1/16/2013 NULL -0.93 -0.9310837 1/11/2013 342 NULL 342 10837 1/15/2013 518 NULL 518 10837 2/19/2013 526.67 NULL 526.6710837 11/16/2012 576.31 NULL 576.3110837 1/14/2013 651.25 NULL 651.25SELECT [Cust-no], CASE WHEN Transactions.date < '02/28/2013' THEN SUM(Baldue) END AS PreBalanceFROM dbo.TransactionsWHERE ([Cust-no] = '010837')GROUP BY [Cust-no]Customer Date Credit Debit Baldue PreBalance10837 Null Null Null Null 488.8810837 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,PreBalanceNote: you'll need to change it as per your requirementsCheersMIK |
|
|
|
|
|