quote:

---

Originally posted by visakh16

SELECT tmp.year,tmp.month,t.Calcprice FROM Table t
INNER JOIN (SELECT YEAR(Date) AS year,MONTH(Date) as month,Max(Date) as maxdate
            FROM Table
            GROUP BY YEAR(Date),MONTH(Date))tmp
ON tmp.maxdate=t.Date

Note that if you have multiple records on last date of any month then calaclprice from all of them will be retrieved seperately

---

quote:

---

Originally posted by khtan

SELECT *
FROM   (
          SELECT Transnum, Date, Calcprice,
                 row_no = row_number() OVER (PARTITION BY DATEADD(MONTH, DATEDIFF(MONTH, 0, [Date]), 0)
                                             ORDER BY [Date] DESC)
          FROM   TABLE
       ) t
WHERE  row_no = 1

---

KH
[spoiler]Time is always against us[/spoiler]

---

quote:

---

Originally posted by michaelb

quote:

---

Originally posted by visakh16

SELECT tmp.year,tmp.month,t.Calcprice FROM Table t
INNER JOIN (SELECT YEAR(Date) AS year,MONTH(Date) as month,Max(Date) as maxdate
            FROM Table
            GROUP BY YEAR(Date),MONTH(Date))tmp
ON tmp.maxdate=t.Date

Note that if you have multiple records on last date of any month then calaclprice from all of them will be retrieved seperately

---

Thanks. But how do I only retrieve a single result where there are multiples on the last date?

---

quote:

---

Originally posted by visakh16

quote:

---

Originally posted by michaelb

quote:

---

Originally posted by visakh16

SELECT tmp.year,tmp.month,t.Calcprice FROM Table t
INNER JOIN (SELECT YEAR(Date) AS year,MONTH(Date) as month,Max(Date) as maxdate
            FROM Table
            GROUP BY YEAR(Date),MONTH(Date))tmp
ON tmp.maxdate=t.Date

Note that if you have multiple records on last date of any month then calaclprice from all of them will be retrieved seperately

---

Thanks. But how do I only retrieve a single result where there are multiples on the last date?

---

In that case what should be your result? Should it be any value of calcprice (min,maxor random) or you want their sum?

---

quote:

---

Originally posted by michaelb

quote:

---

Originally posted by visakh16

quote:

---

Originally posted by michaelb

quote:

---

Originally posted by visakh16

SELECT tmp.year,tmp.month,t.Calcprice FROM Table t
INNER JOIN (SELECT YEAR(Date) AS year,MONTH(Date) as month,Max(Date) as maxdate
            FROM Table
            GROUP BY YEAR(Date),MONTH(Date))tmp
ON tmp.maxdate=t.Date

Note that if you have multiple records on last date of any month then calaclprice from all of them will be retrieved seperately

---

Thanks. But how do I only retrieve a single result where there are multiples on the last date?

---

In that case what should be your result? Should it be any value of calcprice (min,maxor random) or you want their sum?

---

I probably should have included that I also have an itemcode field. I will have a calcprice from the last date in the month for each itemcode.

Calcprice is the moving average cost of the item at the time of the transaction. What I'm trying to do is get the moving cost of each item at the last day of the month, so retrieving this from the last transaction of the month per item will give me this.

Hope I'm making sense.

There is also a doctime field which maybe will be useful, it is a 4 digit number like a 24hr clock... ie 1510 = 3:10pm... so if we got the last date and last time? Also the transnum field is a unique identifier, the higher the transnum the later the transaction occurred.

---

SELECT tmp.year,tmp.month,t.Itemcode,t.Calcprice 
FROM Table t
INNER JOIN (SELECT YEAR(Date) AS year,MONTH(Date) as month,Itemcode,Max(Date) as maxdate
            FROM Table
            GROUP BY YEAR(Date),MONTH(Date),Itemcode)tmp
ON tmp.maxdate=t.Date
AND tmp.Itemcode=t.Itemcode

SELECT Itemcode, Date, Calcprice
FROM   (
          SELECT Transnum,Itemcode, Date, Calcprice,
                 row_no = row_number() OVER (PARTITION BY YEAR(Date) AS year,MONTH(Date),Itemcode                                     ORDER BY [Date] DESC)
          FROM   TABLE
       ) t
WHERE  row_no = 1

SELECT *
FROM   (
          SELECT Transnum, Itemcode, Date, Calcprice,
                 row_no = row_number() OVER (PARTITION BY Itemcode, DATEADD(MONTH, DATEDIFF(MONTH, 0, [Date]), 0)
                                             ORDER BY [Date] DESC)
          FROM   TABLE
       ) t
WHERE  row_no = 1

quote:

---

Originally posted by michaelb

Is there any way of getting each month's price in a seperate column rather than a seperate row?

I have the qty of each item on hand at the end of the month in a seperate table which I need to multiply by the price relevant to that month.

Something like the following, although I don't think this will work:
q.Jan * t.calcprice where tmp.month = 1
q.Feb * t.calcprice where tmp.month = 2
and so on

---

quote:

---

Originally posted by Peso

Please, don't make us guess any more. Relieve our pain by reading and following the instructions in this blog post
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

---

SELECT m.Itemcode,m.Months,m.Value * n.Calcprice
(SELECT *
FROM
(SELECT ItemCode, Jan, Feb, Mar FROM QtyTable)t
UNPIVOT (Value FOR Months IN ([Jan],[Feb],...,[Dec]))p
)m
INNER JOIN (SELECT Itemcode, Date, Calcprice
FROM   (
          SELECT Transnum,Itemcode, Date, LEFT(DATENAME(mm,Date),3) AS MonthValue,
Calcprice,
                 row_no = row_number() OVER (PARTITION BY YEAR(Date) AS year,MONTH(Date),Itemcode                                     ORDER BY [Date] DESC)
          FROM   TABLE
       ) t
WHERE  row_no = 1
)n
ON m.Itemcode=n.Itemcode
AND m.MonthValue=n.Months

quote:

---

Originally posted by michaelb

Hi...

I've realised a gap in my theory.

If I have an item with no transactions in a particular month, there won't be a calcprice for the query to get. So I'm thinking I need to do the following somehow:
January will be last calcprice for transaction with date less than 31/01/2008
February will be last calcprice for transaction with date less than 28/02/2008
and so on

Any idea?

Cheers.

---