CODEWITHSUNDEEP
androidsqlite
Gunaseelan
Gunaseelan

Reputation: 15515

select by month from sqlite database

Friends. I am confused with the following queries.

c = db.rawQuery("SELECT strftime('%W', tm.txn_date) AS week, 
               sum(case when cm.master_id = 1 then (tm.amount - tm.pre_amount) else 0 end) AS Income, 
               sum(case when cm.master_id = 2 then (tm.pre_amount - tm.amount) else 0 end) AS Expense 
               FROM transmaster tm 
               INNER JOIN subcatmasters scm ON tm.scmaster_id = scm.id 
               INNER JOIN catmasters cm ON scm.catmaster_id = cm.id 
               WHERE strftime('%m', tm.txn_date) = '05' 
               GROUP BY week ORDER BY week DESC", null);

This one works perfectly but.

c = db.rawQuery("SELECT strftime('%W', tm.txn_date) AS week, 
               sum(case when cm.master_id = 1 then (tm.amount - tm.pre_amount) else 0 end) AS Income, 
               sum(case when cm.master_id = 2 then (tm.pre_amount - tm.amount) else 0 end) AS Expense 
               FROM transmaster tm 
               INNER JOIN subcatmasters scm ON tm.scmaster_id = scm.id 
               INNER JOIN catmasters cm ON scm.catmaster_id = cm.id 
               WHERE strftime('%m', tm.txn_date) = '"+05+"' 
               GROUP BY week ORDER BY week DESC", null);

This one didn't work. What is the problem friends.

I am just change WHERE strftime('%m', tm.txn_date) = '05' this line to WHERE strftime('%m', tm.txn_date) = '"+05+"' Why because I want to give the month number explicitly.

Please help me. Thanks in advance.

Upvotes: 0

Views: 444

Answers (2)

Yoann Hercouet
Yoann Hercouet

Reputation: 17976

You should do something cleaner and declare a variable for your month number:

String monthNumber = "05";
String request = "SELECT strftime('%W', tm.txn_date) AS week, " +
               "sum(case when cm.master_id = 1 then (tm.amount - tm.pre_amount) else 0 end) AS Income, " +
               "sum(case when cm.master_id = 2 then (tm.pre_amount - tm.amount) else 0 end) AS Expense " +
               "FROM transmaster tm " +
               "INNER JOIN subcatmasters scm ON tm.scmaster_id = scm.id " +
               "INNER JOIN catmasters cm ON scm.catmaster_id = cm.id " +
               "WHERE strftime('%m', tm.txn_date) = '" + monthNumber + "' " +
               "GROUP BY week ORDER BY week DESC";
c = db.rawQuery(request, null);

Upvotes: 1

user2358253
user2358253

Reputation: 26

Try this : 

c = db.rawQuery("SELECT strftime('%W', tm.txn_date) AS week, 
               sum(case when cm.master_id = 1 then (tm.amount - tm.pre_amount) else 0 end) AS Income, 
               sum(case when cm.master_id = 2 then (tm.pre_amount - tm.amount) else 0 end) AS Expense 
               FROM transmaster tm 
               INNER JOIN subcatmasters scm ON tm.scmaster_id = scm.id 
               INNER JOIN catmasters cm ON scm.catmaster_id = cm.id 
               WHERE strftime('%m', tm.txn_date) = '0"+5+"' 
               GROUP BY week ORDER BY week DESC", null);

What about ?

Upvotes: 1

Related Questions