• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 422
  • Last Modified:

Generating report out of some certain range of dates with SQL

Hi I have following one view which contains 3 month data and from bellow data what I want is to separate those account that fall under bellow category

8-15 days / 16-30 days / 31-60 days / 61-90 days

account:dateofused:amout
456:2013-03-01:10
456:2013-03-08:30
457:2013-03-01:10
457:2013-03-15:30
458:2013-03-01:10
458:2013-03-30:30
459:2013-03-01:10
459:2013-04-29:30
460:2013-03-01:10
460:2013-06-29:30

above data example is also sample that full above criteria that I have given i.e 01-08 March which gives first category and that way the rest is also give that example.

My objective is: to grab those accounts that have not used from 8-15 and so on for all other criteria with SQL my database is Postgres

thanks for time and help regards
0
hi4ppl
Asked:
hi4ppl
  • 4
  • 2
1 Solution
 
PortletPaulCommented:
here are some ideas on this:
--8-15 days / 16-30 days / 31-60 days / 61-90 days 
select
*
, '8 - 15 days'
from accountdata
where dateofused  < (current_date - interval '8 days') 
  and dateofused >= (current_date - interval '15 days') 
;

select
*
, '16 - 30 days'
from accountdata
where dateofused  < (current_date - interval '15 days') 
  and dateofused >= (current_date - interval '30 days') 
;

select
*
, '31 - 60 days'
from accountdata
where dateofused  < (current_date - interval '30 days') 
  and dateofused >= (current_date - interval '60 days') 
;

select
*
, '61 - 90 days'
from accountdata
where dateofused  < (current_date - interval '60 days') 
  and dateofused >= (current_date - interval '90 days') 
;

select
*
, case when dateofused >= (current_date - interval '8 days') 
       then amout 
       else null
  end gt_8_days 
, case when dateofused  < (current_date - interval '8 days') 
        and dateofused >= (current_date - interval '15 days') 
       then amout 
       else null
  end gt_15_days
, case when dateofused  < (current_date - interval '15 days') 
        and dateofused >= (current_date - interval '30 days') 
       then amout 
       else null
  end gt_30_days
, case when dateofused  < (current_date - interval '30 days') 
        and dateofused >= (current_date - interval '60 days') 
       then amout 
       else null
  end gt_60_days
, case when dateofused  < (current_date - interval '60 days') 
        and dateofused >= (current_date - interval '90 days') 
       then amout 
       else null
  end gt_90_days
from accountdata
;

Open in new window

see these working at: http://sqlfiddle.com/#!1/e6696/10
also see:
http://www.postgresql.org/docs/8.0/static/functions-datetime.html
http://www.postgresql.org/docs/9.1/static/functions-datetime.html
0
 
hi4pplAuthor Commented:
Hi, thanks for help, one question would be following that I have.

how to insure that numbers are not coming in multiple place  i.e 8-15 and 16-30 so I want to give higher priority as of 16-30 days not 8-16 the higher the usage the higher priority it will get.

and one more point I want to clear is if I used 8-15 means for this means number of days im in active which is total 8 days I have never used interval and not sure how it works.

thanks for help.
0
 
PortletPaulCommented:
"interval" is the way to provide a number of time units to be used, in the above  it is ' n days'

>>"how to insure that numbers are not coming in multiple places"
first you need to ensure that the "buckets" are "mutually exclusive" (do not have overlaps) and also that they do not have "gaps", that is why you see I use >= with < in my suggestions - this ensures there are no overlaps and provided you take care of defining logical intervals you won't get gaps either. e.g.

'a' ... when dateofused >= (current_date - interval '8 days')  ...
'b' ... when dateofused  < (current_date - interval '8 days')  ... and >= ... '15 days'
'c' ... when dateofused  < (current_date - interval '15 days')  ... and >= ... '30 days'
'd' ... when dateofused  < (current_date - interval '30 days') ... and >= ... '60 days'

'b' has no gap or overlap with 'a'
'c' has no gap or overlap with 'd' etc

any row that meets 'a' cannot also meet any of 'b' 'c' or 'd' etc
any row that meets 'b' cannot also meet any of 'a' 'c' or 'd' etc
any row that meets 'c' cannot also meet any of 'a' 'b' or 'd' etc
any row that meets 'd' cannot also meet any of 'a' 'b' or 'b' etc

A DIFFERENT question is:
"How do I ensure that an account is only listed in only one of these columns?"
This would require a subquery providing MAX(dateofused) for each account and then apply the date range logic on top of that subquery.

Is this what you mean?
"How do I ensure that an account is only listed in only one of these columns?"
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
PortletPaulCommented:
as an example. see:
http://sqlfiddle.com/#!12/54159/5
0
 
hi4pplAuthor Commented:
a- Hi, thanks for the help appreciate it however the table that I have, have many field if I say Select* it will select all those fields that is on table account which I don't want I only want the result of these cases along with max data used and account..

b- and my main goal out of this is to find those people that have no activity based on number of days specified like those that comes under

8-15 = 8 days / 16-30 = 15 days / 31-60 = 30 days / 61-90  = 30 days

which means the person that have no activity on first category is 8 days, this guy which is idle for 8 days

if this query gives me above scenario result then it's okay for me, to double check this is there a way to add a column extra to this query to count number of days and put it beside the account.

thanks for help again
0
 
PortletPaulCommented:
please use 'sample data' (from the source) and 'expected results' to specify what you are seeking

>> if I say Select* it will select all those fields
yes, of course, so don't do that

>> I only want the result of these cases along with max data used and account.
specify the 'expected result'
I cannot guess (although I have provided examples)
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now