so what results would you expect from here.....(earlier post was not the sample data I intended posting...sorry)

CREATE TABLE #BuyData(

[Party_Code] [varchar](10) NULL,

[Item] [varchar](20) NULL,

[TradeDate] [smalldatetime] NULL,

[PurchaseQty] [int] NULL,

[Tsrno] [bigint] IDENTITY(1,1) NOT...

I am confused and I think your sample data may be over simplified.

kudos for the set up data though...it helps a lot.

what would you expect in this sample data?

CREATE TABLE...

Sergiy (5/9/2016)

---

R.P.Rozema (5/9/2016)

---

I wonder who can see more of them - me or you? 🙂

I...

some sample data and expected results would help me thinks

drew.allen (5/16/2016)

---

J Livingston SQL (5/15/2016)

---

maybe....use following and then do a crosstab

WITH cteprices as (

SELECT itmId,

price,

ROW_NUMBER() OVER(PARTITION...

drew.allen (5/16/2016)

---

J Livingston SQL (5/15/2016)

---

maybe....use following and then do a crosstab

WITH cteprices as (

SELECT itmId,

price,

ROW_NUMBER() OVER(PARTITION...

just to remind you again....without any columns to order your prices by, you are leaving yourself open to report the wrong results.

making an assumption today. that prices will for now,...

maybe...

DECLARE @prices TABLE (

itmId int,

price money

)

INSERT INTO @prices VALUES (1, 1000)

INSERT INTO @prices VALUES (1, 1500)

INSERT INTO @prices VALUES (1, 1800)

INSERT INTO @prices VALUES (2, 2000)

INSERT INTO @prices VALUES (2,...

maybe....

SELECT FromDate,

ToDate,

MAX(CASE

...

randyetheridge (5/16/2016)

---

I have a table that has daily rates for various currencies in multiple rows. i.e. eur to USD for 1/1/2016, Eur to CAD for 1/1/2016 etc. I am...

maybe....use following and then do a crosstab

WITH cteprices as (

SELECT itmId,

price,

ROW_NUMBER() OVER(PARTITION BY itmId ORDER BY...

well you could use ROW_NUMBER to do that....but what happens when a price drops?

what you suggest will only work if proces are continualy increasing.

I find it very hard to believe...

do you have anything in your prices table that indicates order....eg datetime / Id ?

I think you need that to determine which price is Price1/Price2/ etc

does seem a little like homework.....hmmm

anyways and only because I have been playing around with converting screen grabs into usuable create table statements.....

----http://www.sqlservercentral.com/Forums/Topic1784995-391-1.aspx

CREATE TABLE #Finance(

PID ...

zahid.techk (5/14/2016)

---

Set DateFormat DMY

Select DATENAME(MM,TransHdr.TransDate)As Month_Name,--(It Shows Only Months Name)

Count(TransHdr.TransNum) As Total_Test,

LAG(TransHdr.TransNum,1,0)As Prvs_transaction-- Over (Order by Month(TransHdr.TransDate)) As Prvs_transaction)

...