Solved

Generating report out of some certain range of dates with SQL

Posted on 2013-06-10
6
407 Views
Last Modified: 2013-06-19
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
Comment
Question by:hi4ppl
  • 4
  • 2
6 Comments
 
LVL 48

Accepted Solution

by:
PortletPaul earned 300 total points
ID: 39236565
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
 
LVL 1

Author Comment

by:hi4ppl
ID: 39249633
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39249663
"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
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 
LVL 48

Expert Comment

by:PortletPaul
ID: 39249668
as an example. see:
http://sqlfiddle.com/#!12/54159/5
0
 
LVL 1

Author Comment

by:hi4ppl
ID: 39249908
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39250099
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question