CODEWITHSUNDEEP
sqlsql-server
Kevin Kilford
Kevin Kilford

Reputation: 13

SQL query to return rows in multiple groups

I have a SQL table with data in the following format:

REF  FIRSTMONTH  NoMONTHS  VALUE
--------------------------------
1    2           1         100
2    4           2         240
3    5           4         200

This shows a quoted value which should be delivered starting on the FIRSTMONTH and split over NoMONTHS

I want to calculate the SUM for each month of the potential deliveries from the quoted values. As such I need to return the following result from a SQL server query:

MONTH  TOTAL
------------
2      100   <- should be all of REF=1
4      120   <- should be half of REF=2
5      170   <- should be half of REF=2 and quarter of REF=3
6      50    <- should be quarter of REF=3
7      50    <- should be quarter of REF=3
8      50    <- should be quarter of REF=3

How can I do this?

Upvotes: 1

Views: 81

Answers (2)

podiluska
podiluska

Reputation: 51494

The first part of this query gets a set of numbers between the start and the end of the valid values

The second part takes each month value, and divides it into the monthly amount

Then it is simply a case of grouping each month, and adding up all of the monthly amounts.

select 
    number as month, sum(amount)    
from 
(
    select number 
    from master..spt_values 
    where type='p' 
      and number between (select min(firstmonth) from yourtable) 
          and (select max(firstmonth+nomonths-1) from yourtable)
) numbers
    inner join
(select 
     firstmonth, 
     firstmonth+nomonths-1 as lastmonth, 
     value / nomonths as amount 
 from yourtable) monthly
    on numbers.number between firstmonth and lastmonth
group by number

Upvotes: 0

Soturi
Soturi

Reputation: 1496

You are trying extract data from what should be a many to many relationship. You need 3 tables. You should be able to write a JOIN or GROUP BY select statement from there. The tables below don't use the same data values as yours, and are merely intended for a structural example.

**Month**
REF    Month    Value
---------------------
1      2        100
2      3        120
etc.

**MonthGroup**
REF
---
1
2

**MonthsToMonthGroups**
MonthREF    MonthGroupREF    
------------------
1           1
2           2
2           3

Upvotes: 1

Related Questions