December 11, 2014 at 9:05 am
Hi,
Iām trying to write a query that will extract cost based on closest date (<= date passed by user), product and terminal from example below. Table has over million records. I was trying to utilize ROW_NUMBER()OVER(ORDER BY MarketDate) functionality but no luck because table can have multiple identical dates for same products Please help.
SELECT PK, Terminal, MarketDate = CAST(d.MarketDate AS DATETIME)
,d.Prod, d.Cost
INTO #TestTable
FROM (
SELECT 1, 1, '2014-12-01 15:04:00.000','A', 2.23 UNION ALL
SELECT 2, 1, '2014-12-04 00:04:00.000','A', 2.27 UNION ALL
SELECT 3, 1, '2014-12-05 11:04:00.000','A', 2.34 UNION ALL
SELECT 4, 2, '2014-12-01 15:04:00.000','A', 2.20 UNION ALL
SELECT 5, 2, '2014-12-02 18:08:00.000','A', 2.31 UNION ALL
SELECT 6, 1, '2014-12-01 15:04:00.000','B', 5.54 UNION ALL
SELECT 7, 1, '2014-12-02 14:04:00.000','B', 5.64 UNION ALL
SELECT 8, 2, '2014-12-03 12:12:00.000','B', 5.51 UNION ALL
SELECT 9, 1, '2014-12-01 00:04:00.000','C', 7.23 UNION ALL
SELECT 10,1, '2014-12-04 00:04:00.000','C', 6.22 UNION ALL
SELECT 11,1, '2014-12-05 00:04:00.000','C', 6.23 UNION ALL
SELECT 12,1, '2014-12-06 00:04:00.000','C', 6.24 UNION ALL
SELECT 13,1, '2014-12-12 10:04:00.000','C', 3.99
) d ( PK,Terminal,MarketDate, Prod, Cost)
;
Expected result if user passes date MarketDate='2014-12-05 00:01:00.000'
PKTerminalMarketDateProdCost
212014-12-04 00:04:00.000A2.27
522014-12-02 18:08:00.000A2.31
712014-12-02 14:04:00.000B5.64
822014-12-03 12:12:00.000B5.51
1012014-12-04 00:04:00.000C6.22
December 11, 2014 at 12:04 pm
Is this what you're looking for?
set @uservariable = '2014-12-03'
select marketdate
, cost
from #TestTable
where MarketDate<=@uservariable
December 11, 2014 at 12:37 pm
There's also this
declare @uservariable datetime
set @uservariable = '2014-12-03'
select prod
, cost
, MarketDate
, row_number( ) over (order by marketdate desc)
from #TestTable
where MarketDate<=@uservariable
What exactly do you want to display when there are two costs for the same date/prod?
December 11, 2014 at 12:42 pm
vvinarov (12/11/2014)
Hi,Iām trying to write a query that will extract cost based on closest date (<= date passed by user), product and terminal from example below. Table has over million records. I was trying to utilize ROW_NUMBER()OVER(ORDER BY MarketDate) functionality but no luck because table can have multiple identical dates for same products Please help.
SELECT PK, Terminal, MarketDate = CAST(d.MarketDate AS DATETIME)
,d.Prod, d.Cost
INTO #TestTable
FROM (
SELECT 1, 1, '2014-12-01 15:04:00.000','A', 2.23 UNION ALL
SELECT 2, 1, '2014-12-04 00:04:00.000','A', 2.27 UNION ALL
SELECT 3, 1, '2014-12-05 11:04:00.000','A', 2.34 UNION ALL
SELECT 4, 2, '2014-12-01 15:04:00.000','A', 2.20 UNION ALL
SELECT 5, 2, '2014-12-02 18:08:00.000','A', 2.31 UNION ALL
SELECT 6, 1, '2014-12-01 15:04:00.000','B', 5.54 UNION ALL
SELECT 7, 1, '2014-12-02 14:04:00.000','B', 5.64 UNION ALL
SELECT 8, 2, '2014-12-03 12:12:00.000','B', 5.51 UNION ALL
SELECT 9, 1, '2014-12-01 00:04:00.000','C', 7.23 UNION ALL
SELECT 10,1, '2014-12-04 00:04:00.000','C', 6.22 UNION ALL
SELECT 11,1, '2014-12-05 00:04:00.000','C', 6.23 UNION ALL
SELECT 12,1, '2014-12-06 00:04:00.000','C', 6.24 UNION ALL
SELECT 13,1, '2014-12-12 10:04:00.000','C', 3.99
) d ( PK,Terminal,MarketDate, Prod, Cost)
;
Expected result if user passes date MarketDate='2014-12-05 00:01:00.000'
PKTerminalMarketDateProdCost
212014-12-04 00:04:00.000A2.27
522014-12-02 18:08:00.000A2.31
712014-12-02 14:04:00.000B5.64
822014-12-03 12:12:00.000B5.51
1012014-12-04 00:04:00.000C6.22
Excellent job posting ddl and sample data!!!
This returns the rows as posted in your example.
with SortedValues as
(
select *
, ROW_NUMBER() over (partition by Terminal, Prod order by ABS(Datediff(minute, @UserDate, MarketDate))) as RowNum
from #TestTable
where MarketDate < @UserDate
)
select *
from SortedValues
where RowNum = 1
order by PK;
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 ā Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 11, 2014 at 12:44 pm
cost for product and terminal based on the date requested by user. If user requested date 12/01/2014 I want to see max date that is equal or less then 12/01/2014
December 11, 2014 at 12:48 pm
Sorry if I'm not understanding but what logic do you want if the dates are exactly the same?
December 11, 2014 at 12:49 pm
Quick and simple row_number solution
š
USE tempdb;
GO
SET NOCOUNT ON;
IF OBJECT_ID('tempdb..#TestTable') IS NOT NULL DROP TABLE #TestTable;
SELECT PK, Terminal, MarketDate = CAST(d.MarketDate AS DATETIME)
,d.Prod, d.Cost
INTO #TestTable
FROM (
SELECT 1, 1, '2014-12-01 15:04:00.000','A', 2.23 UNION ALL ---
SELECT 2, 1, '2014-12-04 00:04:00.000','A', 2.27 UNION ALL --- x
SELECT 3, 1, '2014-12-05 11:04:00.000','A', 2.34 UNION ALL ---
SELECT 4, 2, '2014-12-01 15:04:00.000','A', 2.20 UNION ALL ---
SELECT 5, 2, '2014-12-02 18:08:00.000','A', 2.31 UNION ALL --- x
SELECT 6, 1, '2014-12-01 15:04:00.000','B', 5.54 UNION ALL ---
SELECT 7, 1, '2014-12-02 14:04:00.000','B', 5.64 UNION ALL --- x
SELECT 8, 2, '2014-12-03 12:12:00.000','B', 5.51 UNION ALL --- X
SELECT 9, 1, '2014-12-01 00:04:00.000','C', 7.23 UNION ALL ---
SELECT 10,1, '2014-12-04 00:04:00.000','C', 6.22 UNION ALL --- x
SELECT 11,1, '2014-12-05 00:04:00.000','C', 6.23 UNION ALL ---
SELECT 12,1, '2014-12-06 00:04:00.000','C', 6.24 UNION ALL ---
SELECT 13,1, '2014-12-12 10:04:00.000','C', 3.99 ---
) d ( PK,Terminal,MarketDate, Prod, Cost);
DECLARE @TODATE DATETIME = '2014-12-05 00:01:00.000';
;WITH BASE_DATA AS
(
SELECT
TT.PK
,ROW_NUMBER() OVER
(
PARTITION BY TT.Prod, TT.Terminal
ORDER BY TT.MarketDate DESC
) AS MDPR_RID
,TT.Terminal
,TT.MarketDate
,TT.Prod
,TT.Cost
FROM #TestTable TT
WHERE TT.MarketDate <= @TODATE
)
SELECT
BD.PK
,BD.Terminal
,BD.MarketDate
,BD.Prod
,BD.Cost
FROM BASE_DATA BD
WHERE BD.MDPR_RID = 1;
Results
PK Terminal MarketDate Prod Cost
--- --------- ----------------------- ---- ------
2 1 2014-12-04 00:04:00.000 A 2.27
5 2 2014-12-02 18:08:00.000 A 2.31
7 1 2014-12-02 14:04:00.000 B 5.64
8 2 2014-12-03 12:12:00.000 B 5.51
10 1 2014-12-04 00:04:00.000 C 6.22
December 11, 2014 at 12:59 pm
Eirikur Eiriksson (12/11/2014)
Quick and simple row_number solutionš
This looks pretty close to mine. Just before I posted my solution I saw that the OP wanted the closest date that was earlier than the parameter. I included the ABS around DATEDIFF to get the "closest" date on either side. š
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 ā Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 11, 2014 at 1:08 pm
Thank you SSCEnt and SSCChamp. Both solutions will work.
December 11, 2014 at 1:08 pm
Sean Lange (12/11/2014)
Eirikur Eiriksson (12/11/2014)
Quick and simple row_number solutionš
This looks pretty close to mine. Just before I posted my solution I saw that the OP wanted the closest date that was earlier than the parameter. I included the ABS around DATEDIFF to get the "closest" date on either side. š
Almost identical:-D, I only read the initial post but did not get around to post the reply until now.
š
December 12, 2014 at 3:00 am
On 2012+ you may also want to use analytics with windowing.
declare @prm datetime = '2014-12-04 12:00';
select distinct
PK = LAST_VALUE(PK) OVER ( PARTITION BY Prod, Terminal ORDER BY MarketDate ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
, Prod, Terminal
, cost = LAST_VALUE(Cost) OVER ( PARTITION BY Prod, Terminal ORDER BY MarketDate ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
, marketdate = LAST_VALUE(MarketDate) OVER ( PARTITION BY Prod, Terminal ORDER BY MarketDate ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
from #TestTable
where MarketDate <= @prm;
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply