Solved

Generating report out of some certain range of dates with SQL

Posted on 2013-06-10
6
408 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 49

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 49

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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 49

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 49

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

The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
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.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

688 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