SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Grouping/Sum/and tables Help..
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

hbadministrator
Posting Yak Master

120 Posts

Posted - 03/27/2013 :  09:53:56  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 03/27/2013 :  10:48:06  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000