CODEWITHSUNDEEP
androidiossqlobjective-csqlite
Yash Vyas
Yash Vyas

Reputation: 556

How to get data for a month using sqlite ? If there is no data for a specific date then i want zero on place of that

I already know that How can I get data for a month ? using this query -

SELECT SLIDER_VALUE FROM TBL_ACTIVITY_TRACKS WHERE STRFTIME('%m', TRACK_DATE_TIME, 'localtime') = STRFTIME('%m', 'now', 'localtime');

Here TRACK_DATE_TIME is in yyyy-mm-dd date format.

But i want that if there is no data for a particular date then i want zero or a specific identifier on place of that.

I also know that I can do this by creating a TBL_DATES. which have all dates and then left join it with my table but I don't want to create a new table.

Can I do this ?

Upvotes: 1

Views: 1087

Answers (2)

CL.
CL.

Reputation: 180210

You have to get the individual date values from somewhere. If you do not want to create a temporary table, you can embed the values into a subquery directly in your statement:

SELECT IFNULL(Slider_Value, 'none')
FROM (SELECT '2013-10-01' AS Date
      UNION ALL
      SELECT '2013-10-02'
      UNION ALL
      SELECT '2013-10-03'
      UNION ALL
      ...
      SELECT '2013-10-31'
     ) AS ThisMonth
LEFT JOIN Tbl_Activity_Tracks ON ThisMonth.Date = date(Track_Date_Time)

If you do not want to enumerate the days in the code that generates the SQL statement, you can also do this in SQL itself, with enough contortions:

SELECT IFNULL(Slider_Value, 'none')
FROM (SELECT date('now', 'start of month') AS Date
      UNION ALL
      SELECT date('now', 'start of month', '+1 days')
      UNION ALL
      SELECT date('now', 'start of month', '+2 days')
      UNION ALL
      ...
      UNION ALL
      SELECT date('now', 'start of month', '+27 days')
      UNION ALL
      SELECT date('now', 'start of month', '+28 days') WHERE strftime('%m', 'now', 'start of month', '+28 days') = strftime('%m', 'now')
      UNION ALL
      SELECT date('now', 'start of month', '+29 days') WHERE strftime('%m', 'now', 'start of month', '+29 days') = strftime('%m', 'now')
      UNION ALL
      SELECT date('now', 'start of month', '+30 days') WHERE strftime('%m', 'now', 'start of month', '+30 days') = strftime('%m', 'now')
     ) AS ThisMonth
LEFT JOIN Tbl_Activity_Tracks ON ThisMonth.Date = date(Track_Date_Time)

(The last three WHERE clauses omit days that are no longer in the current month, for months with less than 31 days.)

Upvotes: 2

arshu
arshu

Reputation: 11915

You simply need to pass the database query return values in an ArrayList & then you get the size() of the ArrayList. If it is greater than zero you can do your operation on it, else if there is no data you can pass zero or do the respective operation.

Upvotes: 0

Related Questions