CODEWITHSUNDEEP
sqlsql-server
abramlimpin
abramlimpin

Reputation: 5077

Get the first and last record of each item for the month

Product ID     Quantity     DateAdded
1              100          4/1/14
2              200          4/2/14
3              300          4/2/14
1              80           4/3/14
3              40           4/5/14
2              5            4/6/14
1              10           4/7/14

I am using this SQL statement to display the first and last record of each item:

SELECT 
    ProductID, MIN(Quantity) AS Starting, MAX(Quantity) AS Ending 
FROM 
    Records
WHERE 
    DateAdded BETWEEN '2014-04-01' AND '2014-04-30'
GROUP BY 
    ProductID, Quantity

but I am getting the same values for the Starting and Ending columns. I want to achieve something like this:

Product ID     Starting     Ending
1              100          10
2              200          5
3              300          40

Upvotes: 0

Views: 1123

Answers (6)

dean
dean

Reputation: 10098

This works in 2012 and newer:

with x as (
select distinct productid
,first_value(quantity) over(partition by productid order by dateadded
    range between unbounded preceding  and current row) as starting
,last_value(quantity) over(partition by productid order by dateadded
    range between current row and unbounded following) as ending
from #t
)
select productid, starting, ending
from x

Single pass through the table.

Upvotes: 0

kwelsan
kwelsan

Reputation: 1219

I did not try it on SQL Server but on MySQL this SQL is working:

SELECT 
    ProductID, MAX(Quantity) AS Starting, MIN(Quantity) AS Ending 
FROM 
    Records
WHERE 
    DateAdded BETWEEN '2014-04-01' AND '2014-04-30'
GROUP BY 
    ProductID;

For DateAdded field:

SELECT 
    ProductID, MAX(Quantity) AS Starting, MIN(Quantity) AS Ending 
FROM 
    Records
WHERE 
    convert(datetime, DateAdded) BETWEEN '2014-04-01' AND '2014-04-30'
GROUP BY 
    ProductID;

P.S. Missing sqlfiddle badly.. :(

Upvotes: -1

Gordon Linoff
Gordon Linoff

Reputation: 1270391

You are getting the same quantities because you are aggregating by quantity in the group by as well as product. Your version of the query, properly written would be:

SELECT ProductID, MIN(Quantity) AS Starting, MAX(Quantity) AS Ending 
FROM Records
WHERE DateAdded BETWEEN '2014-04-01' AND '2014-04-30'
GROUP BY ProductID;

However, this doesn't give you the first and last values. It only gives you the minimum and maximum ones. To get those values, use row_number() and conditional aggregation:

SELECT ProductID,
       MAX(CASE WHEN seqnum_asc = 1 THEN Quantity END) as Starting,
       MAX(CASE WHEN seqnum_desc = 1 THEN Quantity END) as Ending
FROM (SELECT r.*,
             row_number() over (partition by product order by dateadded asc) as seqnum_asc,
             row_number() over (partition by product order by dateadded desc) as seqnum_desc
      FROM Records r
     ) r
WHERE DateAdded BETWEEN '2014-04-01' AND '2014-04-30'
GROUP BY ProductID;

If you are using SQL Server 2012, then you can also use this with FIRST_VALUE() and LAST_VALUE() instead of row_number().

Upvotes: 2

podiluska
podiluska

Reputation: 51504

Use the row_number() ranking function

select starting.*, ending.ending
from
    (select ProductID, quantity as starting from 
            (select * , ROW_NUMBER() over (partition by productid order by dateadded) rn 
             from yourtable   
             where DateAdded BETWEEN '2014-04-01' AND '2014-04-30'
     ) first 
     where rn = 1) starting
    inner join 
    (select ProductID, quantity as ending from 
            (select * , ROW_NUMBER() over (partition by productid order by dateadded desc) rn 
             from yourtable   
             where DateAdded BETWEEN '2014-04-01' AND '2014-04-30'
    ) last 
    where rn = 1) ending
    on starting.productid=ending.productid

The first subquery gets the first entry for the time period, the second gets the last entry

Upvotes: 3

mohan111
mohan111

Reputation: 8865

DECLARE @test Table 
(ID INT, Name INT)
INSERT INTO @test 
VALUES
(1,  100),
(2, 200),
(3,  300 ),
(1,  5),
(2, 10),
    (3, 15);

    select ID,MIN(name),MAX(name) from @test
    group by ID

Upvotes: 0

Reza
Reza

Reputation: 19903

Change your query to this

SELECT 
    ProductID, MIN(Quantity) AS Starting, MAX(Quantity) AS Ending 
FROM 
    Records
WHERE 
    DateAdded BETWEEN '2014-04-01' AND '2014-04-30'
GROUP BY 
    ProductID

you don't need to make a group for quantity

Upvotes: 0

Related Questions