CODEWITHSUNDEEP
sqlsql-server-2008-r2
Laxman
Laxman

Reputation: 1481

SQL query to fetch data based on date range

i want to fetch data for below scenario

input: (Let say today is: 1-Mar-2015)

LicenseNo    LicenseEndDate   LicenseType   Amount
1                 1-Apr-2015             AB                 100
2                5-Apr-2015             AB                 150
3                7-Apr-2015             BC                 200
4                10-July-2015             AB                 120
5                10-july-2015             BC                 140

Expected O/P

                  AB          BC

Between 0-3 months 250           200
Between 3-6 months 120           140
this may increase

Upvotes: 0

Views: 165

Answers (2)

G B
G B

Reputation: 1462

Having the derived table in this solution just makes grouping easier in the outer select, it saves us from repeating ourselves too much. The SUM(case...end) structure is one way of pivoting our results, you could look at the pivot operator but I think it is overkill for this scenario. I also added a few other cases even though your supplied data doesn't use them because I figure they are likely. you can always add a where clause if you are looking at specific groups and this is facilitated by the derived table as well.

I have used GETDATE() but you can substitute a date variable for that if it suits better.

declare @t as table 
(
    LicenseNo int, 
    LicenseEndDate datetime, 
    LicenseType varchar(2), 
    Amount numeric(10,2)
)

insert into @t
values
    (1,'1-Apr-2015','AB',100),
    (2,'5-Apr-2015','AB',150),
    (3,'7-Apr-2015','BC',200),
    (4,'10-July-2015','AB',120),
    (5,'10-july-2015','BC',140)

declare @comparison_date as datetime = getdate()

select 
    case ExpGrp
        when 0 then 'Expired'
        when 1 then 'Expires today'
        when 2 then 'Expires in 0-3 months'
        when 3 then 'Expires in 3-6 months'
        when 4 then 'Not due to expire'
        else 'Something went wrong'
    end as Descrip,
    sum(case when LicenseType = 'AB'
            then Amount
            else 0
        end) as AB,
    sum(case when LicenseType = 'BC'
            then Amount
            else 0
        end) as BC
from
    (select *,
        case 
            when LicenseEndDate < @comparison_date
                then 0
            when LicenseEndDate = @comparison_date
                then 1
            when LicenseEndDate > @comparison_date and LicenseEndDate <= dateadd(MONTH,3,@comparison_date)
                then 2
            when LicenseEndDate > dateadd(MONTH,3,@comparison_date) and LicenseEndDate <= dateadd(MONTH,6,@comparison_date)
                then 3
            else 4
        end as ExpGrp
    from @t) t
group by t.ExpGrp

Upvotes: 0

Javaluca
Javaluca

Reputation: 857

SELECT 'Between 0-3 months', 
SUM(Case when l.LicenseType='AB' then l.Amount End), 
SUM(Case when l.LicenseType='BC' then l.Amount End)
FROM licence l
WHERE l.LicenceEndDate BETWEEN @inputDate AND DATEADD (month , 3 , @inputDate)

UNION

SELECT 'Between 3-6 months', 
SUM(Case when l.LicenseType='AB' then l.Amount End), 
SUM(Case when l.LicenseType='BC' then l.Amount End)
FROM licence l
WHERE l.LicenceEndDate BETWEEN DATEADD (month , 3 , @inputDate) AND DATEADD (month , 6 , @inputDate)

Union of two queries for the two interval.

Or you can create a temporary table based on your input date like this

|     ID | DESCRIPTION        | DATA_MIN | DATA_MAX  |
|      1 | Between 0-3 months | @input   | @input + 3|
|      2 | Between 3-6 months | @input +3| @input + 6|

And use that for your join

Upvotes: 1

Related Questions