Help look at this SQL statement, how to write it? There are more than 5W pieces of data in the table, how to query can b

月夜雾影

Product name Time Quantity Unit price
---------- --------------- --------- ----------
   A 2016-11-01 20 15
   A 2016-11-08 40 15
   A 2016-11-28 70 14.5
   A 2016-12-01 10 14.5
   A 2016-12-15 80 14.8
   A 2016-12-25 55 14.8

Query data for the period from 2016-12-01 to 2016-12-31.
What is the quantity of different prices of a product in a certain period of time? And you need to find out what the last price was before the query start time.

result
 Product Name Time Quantity Unit Price Unit Price 2 Spread
------- --------- ------- --------- ---------- -------
   A 2016-12-01 10 14.5 14.5 0
   A 2016-12-25 135 14.8 14.5 0.3

dream88

I don’t see what it means. How do the quantities 10 and 135 come from?

fanfan1980

select ID=identity(int, 1, 1), product name, time=max(time), quantity=sum(quantity), unit price into #T from T
where time between '2016-12-01' and '2016-12-31'
group by product name, unit price
order by time

select *, unit price 2=isnull( (select unit price from #T where ID=A.ID-1), unit price), difference=unit price-isnull( (select unit price from #T where ID=A.ID-1), unit price ) from #TA

fanfan1980

create table T(product name char(1), time datetime, quantity int, unit price decimal(10,1))
insert T select'A', '2016-11-01', 20, 15
union all select'A', '2016-11-08', 40, 15
union all select'A', '2016-11-28', 70, 14.5
union all select'A', '2016-12-01', 10, 14.5
union all select'A', '2016-12-15', 80, 14.8
union all select'A', '2016-12-25', 55, 14.8

select ID=identity(int, 1, 1), product name, time=max(time), quantity=sum(quantity), unit price into #T from T
where time between '2016-12-01' and '2016-12-31'
group by product name, unit price
order by time

select *, unit price 2=isnull( (select unit price from #T where ID=A.ID-1), unit price), difference=unit price-isnull( (select unit price from #T where ID=A.ID-1), unit price ) from #TA

--result
ID Product name Time Quantity Unit price Unit price 2 Difference
----------- ---- ----------------------------------- ------------------- ----------- ------------ -------- ---- -------------
1 A 2016-12-01 00:00:00.000 10 14.5 14.5 .0
2 A 2016-12-25 00:00:00.000 135 14.8 14.5 .3

(2 row(s) affected)

月夜雾影

Quantity 10: The date is between 2016-12-01 and 2016-12-31, and the price is the sum of the quantities of 14.5.
Quantity 135: The date is between 2016-12-01 and 2016-12-31, and the price is the sum of the quantities of 14.8.
The date is the date of the last price execution.

wgfxman

select A. product name, MAX (A. time) AS time, SUM (A. quantity) AS quantity, A. unit price AS unit price 1, B. unit price AS unit price 2, A. unit price-B. unit price AS difference
FROM testtb A LEFT JOIN testtb B ON A. Product name=B. Product name
AND B. time=(SELECT MAX(time) FROM testtb WHERE product name=A. product name AND time<A. time AND (unit price<>A. unit price OR time<'2016-12-01'))
WHERE A. time>='2016-12-01' AND A. time<='2016-12-31'
GROUP BY A. Product name, A. Unit price, B. Unit price

wgfxman

create table testtb(
Product name char(1),
Time char(10),
Quantity int,
Unit price numeric(12,2)
)
go

insert into testtb (product name, time, quantity, unit price)
SELECT'A','2016-11-01', 20,15
UNION ALL SELECT'A','2016-11-08',40,5
UNION ALL SELECT'A','2016-11-28',70,14.5
UNION ALL SELECT'A','2016-12-01',10,14.5
UNION ALL SELECT'A','2016-12-15',80,14.8
UNION ALL SELECT'A','2016-12-25',55,14.8
GO

select A. product name, MAX (A. time) AS time, SUM (A. quantity) AS quantity, A. unit price AS unit price 1, B. unit price AS unit price 2, A. unit price-B. unit price AS difference
FROM testtb A LEFT JOIN testtb B ON A. Product name=B. Product name
AND B. time=(SELECT MAX(time) FROM testtb WHERE product name=A. product name AND time<A. time AND (unit price<>A. unit price OR time<'2016-12-01'))
WHERE A. time>='2016-12-01' AND A. time<='2016-12-31'
GROUP BY A. Product name, A. Unit price, B. Unit price

月夜雾影

Unit price 2: It shows that the price corresponding to the date 2016-11-28 is 14.5

wgfxman

Product name Time Quantity Unit price 1 Unit price 2 Difference
--------------------------------------- ----------- ----------------------------
A 2016-12-01 10 14.50 14.50 0.00
A 2016-12-25 135 14.80 14.50 0.30

michealhenry

--surroundings
create table tab
(
Product name varchar(2),
Time varchar(10),
Quantity int,
Unit price decimal(8,2)
)

insert into tab select'A', '2016-11-01', 20, 15
insert into tab select'A', '2016-11-08', 40, 15
insert into tab select'A', '2016-11-28', 70, 14.5
insert into tab select'A', '2016-12-01', 10, 14.5
insert into tab select'A', '2016-12-15', 80, 14.8
insert into tab select'A', '2016-12-25', 55, 14.8

--Statement
select *, spread = unit price-unit price 2 from
(
select product name, max (time) as time, sum (quantity) as quantity, unit price,
Unit price 2 = (select top 1 unit price from tab where product name = a. product name and time <'2016-12-01' order by time desc)
from tab a
where time between '2016-12-01' and '2016-12-31'
group by product name, unit price
)tt

--result
Product name Time Quantity Unit price Unit price 2 Difference
---- ---------- ----------- ---------- ---------- ----- ------
A 2016-12-01 10 14.50 14.50 .00
A 2016-12-25 135 14.80 14.50 .30

(The number of rows affected is 2)