Solved

Generating report out of some certain range of dates with SQL

Posted on 2013-06-10
6
398 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
Comment Utility
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
Comment Utility
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
Comment Utility
"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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 48

Expert Comment

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

Author Comment

by:hi4ppl
Comment Utility
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
Comment Utility
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now