CODEWITHSUNDEEP
sqlsql-servergroup-bysql-server-2000
Korjavin Ivan
Korjavin Ivan

Reputation: 449

sql : summary by random period

I have two tables:

first one (amount): 

  amountid      date         amount
    1         01.01.2011      100
    2         02.01.2011       50
    3         04.01.2011      200
    4         10.01.2011      20
    5         11.01.2011      5

second one (period): 

  periodid      date
     1          01.01.2011
     2          03.01.2011
     3          05.01.2011

I need group first table by times from second table, and sum (amount):

for this example answer is:

      01.01.2011    150
      03.01.2011    200  
      05.01.2011    25

help me with sql query please.

Upvotes: 1

Views: 134

Answers (4)

Mikael Eriksson
Mikael Eriksson

Reputation: 138990

Sample data

declare @Amount table
(
  amountid int,
  [date] datetime,
  amount int
)

declare @Period table
(
  periodid int,
  [date] datetime
)

insert into @Amount
select 1, '20110101', 100 union all
select 2, '20110102', 50  union all
select 3, '20110104', 200 union all
select 4, '20110110', 20  union all
select 5, '20110111', 5

insert into @Period
select 1, '20110101' union all
select 2, '20110103' union all
select 3, '20110105'

The query

select P.StartDate,
       sum(A.amount) as amount
from (
      select P1.[date] as StartDate,
             coalesce((select min(P2.[date])
                       from @Period as P2
                       where P1.[date] < P2.[date]), '99991231') as endDate
      from @Period as P1
     ) as P      
  inner join @Amount as A
    on A.[date] >= P.StartDate and 
       A.[date] < P.EndDate
group by P.StartDate

Upvotes: 3

Korjavin Ivan
Korjavin Ivan

Reputation: 449

For now i have this sql-s from other forum:

 declare @t table
 ( amountid int,date smalldatetime,amount int)

 declare @p table (periodid int,date smalldatetime)

 Insert into  @t
   Select 1,'2011-01-01', 100
 union all
  Select 2,'2011-01-02', 50
 union all
  Select 3,'2011-01-04', 200
   union all
  Select 4,'2011-01-10', 20
  union all
  Select 5,'2011-01-11', 5

 Insert into @p
  Select 1, '2011-01-01'
   union all
  Select 2, '2011-01-03'
   union all
    Select 3, '2011-01-05'

Select p.date, sum(t.amount) from 
@P p
inner join
@t t on
    p.date <= t.Date
where
    not exists
        ( select top 1 1 from @p pp where pp.Date > p.Date and pp.Date < t.Date)
group by p.Date

if i am right, this have best plan when i do "show execution plan" on my server.

Am i right?

Upvotes: 0

boes
boes

Reputation: 2855

The query to compute the enddates for the periods is

select P1.periodid, P1.date as startdate, min(P2.date) as enddate
    from period P1 
    left outer join period P2 on P2.date > P1.date
    group by P1.periodid, P1.date

results

PERIODID STARTDATE  ENDDATE
        1 2011-01-01 2011-01-03
        2 2011-01-03 2011-01-05
        3 2011-01-05 NULL

With the use of that you can sum all the amounts for all periods like this

select P.periodid, P.startdate, P.enddate, sum(A.amount) as sum
    from (
        select P1.periodid, P1.date as startdate, min(P2.date) as enddate
            from period P1 
            left outer join period P2 on P2.date > P1.date
            group by P1.periodid, P1.date
        ) P 
        left outer join amount A
            on A.date >= P.startdate and (P.enddate is null or A.date < P.enddate)
    group by P.periodid, P.startdate, P.enddate

results

PERIODID STARTDATE  ENDDATE    SUM
        1 2011-01-01 2011-01-03 150
        2 2011-01-03 2011-01-05 200
        3 2011-01-05 NULL        25

Upvotes: 1

John N
John N

Reputation: 1815

create table #amount (
    amountid    int,
    [date]      datetime,
    amount      int
)

create table #period (
    periodid    int,
    [date]      datetime
)

delete from #amount
delete from #period

insert into #amount
values (1, {d '2011-01-01'}, 100),
    (2, {d '2011-01-02'}, 50),
    (3, {d '2011-01-04'}, 200),
    (4, {d '2011-01-10'}, 20),
    (5, {d '2011-01-11'}, 5)

insert into #period
values (1, {d '2011-01-01'}),
    (2, {d '2011-01-03'}),
    (3, {d '2011-01-05'})


select p.date, SUM(a.amount) as amount
from #period p inner join
    (
        select a.amountid, MAX(p.periodid) as periodid
        from #amount a inner join
            #period p on a.date >= p.date   
        group by a.amountid
    ) mp on p.periodid = mp.periodid inner join
    #amount a on mp.amountid = a.amountid
group by p.date

It would certainly be easier if your period table could have an end date.

Upvotes: 0

Related Questions