Click here to Skip to main content
12,069,536 members (65,380 online)
Rate this:
 
Please Sign up or sign in to vote.
I have database with filled records like :

Sr    ExpName       Date       Camt  Tamt
---   --------- -----------   ----- -----
1     Pen        10-10-2012      10    10
2     abc        10-10-2012      30    45
3     xyz        11-10-2012      15    55
4     ggg        11-10-2012      20    75
5     aaa        12-10-2012      10    85
6     nnn        13-10-2012      10    95
But along with i want to only same date records in one row and related Camt
will be added for same date Camt and output will be appears like :
DATE             AMOUNT
------------     ------
10-10-2012          40
11-10-2012          35
12-10-2012          10
13-10-2012          10
I have coded for regarding requirement using cursor and SimpleCursorAdapter but still i can't get appropriate result.

this output will be get in simple Sql query like :
SELECT CAST(Date AS DATE),SUM(Camt) AS AMOUNT from tb_expense GROUP BY CAST(Date AS DATE);

Cursor c = db.query("tb_expense", new String[] {"Date", "SUM(Camt) AS Amount" }, null, null, "Date", null, null, null);
adapter = new SimpleCursorAdapter(this, R.layout.myrow1, c, new String[]{"Date","Camt"},new int[]{R.id.textView3,R.id.textView5} );
startManagingCursor(c);
lstExpense.setAdapter(adapter);
Posted 15-Oct-12 0:34am
Edited 15-Oct-12 0:36am
v2
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

this problem solved by myself :
Cursor c = db.query("tb_expense", new String[] 
       {DbHelper.C_Expno,"Group_Concat(ExpName) AS 
       ExpName","Date","SUM(Camt) AS Camt"}, null, null, "Date", null, null);
 
adapter = new SimpleCursorAdapter(this, R.layout.myrow1, c, new String[]
       {DbHelper.C_Expno,"ExpName","Date","Camt"},new  
       int[]{R.id.lblexpno,R.id.lblexpnm,R.id.lbldt,R.id.lblcamt} );
 
startManagingCursor(c);
lstExpense.setAdapter(adapter);
  Permalink  
v2
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

select * from(
select row_number() over(partition by dateadd(d,0,datediff(d,0,Date)) order by dateadd(d,0,datediff(d,0,Date)))as newSrNo, ExpName, Camt,Tamt from tbltrading where tradeid =11 group by InvoiceNo,PDate
)as q where q.SrNo=1


please necessary changes
hope this work
  Permalink  
Comments
Anil Dalsaniya 15-Oct-12 6:09am
   
But how to write this code in android.........
Anil Dalsaniya 15-Oct-12 6:11am
   
This query successfully works in sql but problem with android............
bhargavpp 15-Oct-12 6:12am
   
please vote for that
Anil Dalsaniya 16-Oct-12 3:46am
   
but it not works in android, how can i vote for it?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web04 | 2.8.160208.1 | Last Updated 18 Oct 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100