SELECT
COUNT(dbo.ConsumerPricing.finishProductId),
--dbo.ConsumerPricing.price,
--dbo.ConsumerPricing.date,
dbo.ConsumerPricing.finishProductId
FROM
dbo.ConsumerPricing
WHERE
DATE >= '20150321'
GROUP BY
--dbo.ConsumerPricing.date,
dbo.ConsumerPricing.finishProductId
SELECT
dbo.ConsumerPricing.price,
dbo.ConsumerPricing.finishProductId
FROM
dbo.ConsumerPricing
WHERE
DATE >= '20150321'
GROUP BY
dbo.ConsumerPricing.price,
dbo.ConsumerPricing.finishProductId
SELECT
dbo.ConsumerPricing.price,
dbo.ConsumerPricing.finishProductId,
COUNT(dbo.ConsumerPricing.finishProductId) cnt
FROM
dbo.ConsumerPricing
WHERE
DATE >= '20150321'
GROUP BY
dbo.ConsumerPricing.price,
dbo.ConsumerPricing.finishProductId
SELECT
a.finishProductId,
a.price,
b.cnt
FROM
dbo.ConsumerPricing a
inner join (
select b.finishProductId, count(distinct price) cnt
from dbo.ConsumerPricing b
group by b.finishProductId
) b
on a.finishProductId = b.finishProductId
WHERE
DATE >= '20150321'
Group By
a.finishProductId,
a.price,
b.cnt
SELECT
dbo.ConsumerPricing.price,
dbo.ConsumerPricing.finishProductId
FROM
dbo.ConsumerPricing
WHERE
DATE >= '20150321'
GROUP BY
dbo.ConsumerPricing.price,
dbo.ConsumerPricing.finishProductId
create table dbo.ConsumerPricing
(
finishProductId int,
price money,
"date" varchar(8)
);
insert into dbo.ConsumerPricing (finishProductId, price, "date") values (1, 32.22, '20150322');
insert into dbo.ConsumerPricing (finishProductId, price, "date") values (1, 32.22, '20150323');
insert into dbo.ConsumerPricing (finishProductId, price, "date") values (1, 40.15, '20150324');
insert into dbo.ConsumerPricing (finishProductId, price, "date") values (1, 56.32, '20150325');
insert into dbo.ConsumerPricing (finishProductId, price, "date") values (1, 32.22, '20150326');
insert into dbo.ConsumerPricing (finishProductId, price, "date") values (1, 32.22, '20150327');
insert into dbo.ConsumerPricing (finishProductId, price, "date") values (1, 32.22, '20150328');
insert into dbo.ConsumerPricing (finishProductId, price, "date") values (1, 14.22, '20150329');
insert into dbo.ConsumerPricing (finishProductId, price, "date") values (1, 32.22, '20150330');
insert into dbo.ConsumerPricing (finishProductId, price, "date") values (1, 14.22, '20150331');
insert into dbo.ConsumerPricing (finishProductId, price, "date") values (50, 18000, '20150330');
insert into dbo.ConsumerPricing (finishProductId, price, "date") values (50, 20000, '20150331');
SELECT
a.finishProductId,
a.price,
b.cnt
FROM
dbo.ConsumerPricing a
inner join (
select b.finishProductId, count(distinct price) cnt
from dbo.ConsumerPricing b
group by b.finishProductId
) b
on a.finishProductId = b.finishProductId
WHERE
DATE >= '20150321'
Group By
a.finishProductId,
a.price,
b.cnt
;with CTE as (
select *, row_number() over(partition by finishProductId, price
order by [date] DESC ) as rn
from dbo.ConsumerPricing
where [date] >= '20150321'
)
select
*
from CTE
cross apply (
select COUNT(DISTINCT ref2.price)
from CTE as ref2
where ref2.finishProductId = cte.finishProductId
) oa (num_prices)
where rn = 1
The date that is displayed is the "most recent" for each product/price combination, the count is the number of times the price has changed (not just for that date) e.g.
| finishProductId | price | date | rn | num_prices |
|-----------------|-------|----------|----|------------|
| 1 | 14.22 | 20150331 | 1 | 4 |
| 1 | 32.22 | 20150330 | 1 | 4 |
| 1 | 40.15 | 20150324 | 1 | 4 |
| 1 | 56.32 | 20150325 | 1 | 4 |
| 50 | 18000 | 20150330 | 1 | 2 |
| 50 | 20000 | 20150331 | 1 | 2 |
also see http://sqlfiddle.com/#!6/eab0d/7
Open in new window
?