CODEWITHSUNDEEP
sqlsql-serversql-server-2008
last10seconds
last10seconds

Reputation: 333

Create weighted average in SQL using dates

I have a SQL query that lists details about a certain item. Everything works as should except for the last column. I want the weight of transaction column to report back a difference in days.

So for example the 4th row in the txdate column is 05/21/2014 and the 3rd row is 05/12/20014. The weight of transaction column in the 4th row should say 9.

I read about the Lag and Lead functions, but I'm not sure how to implement those with dates (if it's even possible). If it isn't possible is there a way to accomplish this?

SQL results

Select t.txNumber, 
   t.item, 
   t.txCode, 
   t.txdate, 
   (t.onhandlocold + t.stockQty) as 'Ending Quantity',
   tmax.maxtnumber 'Latest Transaction Code',
   tmax.maxdate 'Latest Transaction Date', 
   tmin.mindate 'First Transaction Date', 
   (t.txdate - tmin.mindate) 'weight of transaction'

From tbliminvtxhistory t
 Left outer join
     (Select t.item, max(t.txnumber) as maxtnumber, max(t.txdate) as maxdate
      From tbliminvtxHistory t
      Where t.txCode != 'PAWAY'
      Group By Item) tmax
  on t.item = tmax.item
Left Outer Join
     (Select t.item, min(t.txdate) as mindate
      From tbliminvtxHistory t
      WHere  t.txCode != 'PAWAY'
         and t.txdate > DateAdd(Year, -1, GetDate())
      Group By Item) tmin
  on t.item = tmin.item

where t.item = 'LR50M' 
and t.txCode != 'PAWAY'
and t.txdate > DateAdd(Year, -1, GetDate())

Upvotes: 0

Views: 1050

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I think you are looking for this expression:

Select . . . ,
       datediff(day, lag(txdate) over (order by xnumber), txdate)

This assumes that the rows are ordered by the first column, which seems reasonable given your explanation and the sample data.

EDIT:

Without lag() you can use outer apply. For simplicity, let me assume that your query is defined as a CTE:

with cte as (<your query here>)
select . . . ,
       datediff(day, prev.txdate , cte.txdate)
from cte cross apply
     (select top 1 cte2.*
      from cte cte2
      where cte2.xnumber < cte.xnumber
      order by cte2.xnumber desc
     ) prev

Upvotes: 1

Adam Scharp
Adam Scharp

Reputation: 632

Check out the DATEDIFF function, which will return the difference between two dates.

I think this is what you are looking for:

DATEDIFF(dd,tmin.mindate,t.txdate)

UPDATE:

Now that I understand your question a little better, here is an update. As mentioned in a comment on the above post, the LAG function is only supported in SQL 2012 and up. An alternative is to use ROW_NUMBER and store the results into a temp table. Then you can left join back to the same table on the next ROW_NUMBER in the results. Then you would use your DATEDIFF to compare the dates. This will do the exact same thing as the the LAG function.

Example:

SELECT ROW_NUMBER() OVER (ORDER BY txdate) AS RowNumber,*
INTO #Rows
FROM tbliminvtxhistory 

SELECT DATEDIFF(dd,r2.txdate,r.txdate),* 
FROM #Rows r
LEFT JOIN #Rows r2 ON r.RowNumber=r2.RowNumber+1

DROP TABLE #Rows

Upvotes: 2

Related Questions