March 24, 2008 at 8:43 am
well I have a table with few records and looks like this
START_DT END_DT
----------------------- -----------------------
2008-03-01 00:00:00.000 2008-03-15 00:00:00.000
2008-02-16 00:00:00.000 2008-02-29 00:00:00.000
2008-02-01 00:00:00.000 2008-02-15 00:00:00.000
start_dt= start date of a period
end_dt = end date of a period
I would like to calculate the start date and end date of last two periods less than getdate().
For example my latest end date is march 15th. and the start date two periods ago was feb 16th.
my question is how can I pick these two values using a sql?
March 24, 2008 at 8:49 am
Use TOP with ORDER BY ....
SELECT TOP 2
START_DT, END_DT
FROM
dbo.yourTable
WHERE
END_DT <= GETDATE()
ORDER BY
END_DT DESC
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMarch 24, 2008 at 9:12 am
Jason.. thanks for replying.
the query you provided:
SELECT TOP 2
START_DT, END_DT
FROM
ods32.dbo.ts_pd_sch
WHERE
END_DT <= GETDATE()
ORDER BY
END_DT DESC
gives me following records:
START_DT END_DT
----------------------- -----------------------
2008-03-01 00:00:00.000 2008-03-15 00:00:00.000
2008-03-01 00:00:00.000 2008-03-15 00:00:00.000
whereas what I want is a single row with start date (two periods ago) and max end_dt before getdate():
START_DT END_DT
----------------------- -----------------------
2008-02-16 00:00:00.000 2008-03-15 00:00:00.000
any ideas?
March 24, 2008 at 9:33 am
ok, that was written with the assumption that there would be only one row per range.
-- create some test data
DECLARE @t TABLE (START_DT DATETIME, END_DT DATETIME)
INSERT @t
SELECT '2008-03-01 00:00:00.000','2008-03-15 00:00:00.000' UNION ALL
SELECT '2008-02-16 00:00:00.000','2008-02-29 00:00:00.000' UNION ALL
SELECT '2008-03-01 00:00:00.000','2008-03-15 00:00:00.000' UNION ALL
SELECT '2008-02-16 00:00:00.000','2008-02-29 00:00:00.000' UNION ALL
SELECT '2008-03-01 00:00:00.000','2008-03-15 00:00:00.000' UNION ALL
SELECT '2008-02-16 00:00:00.000','2008-02-29 00:00:00.000' UNION ALL
SELECT '2008-02-01 00:00:00.000','2008-02-15 00:00:00.000'
-- the actual query
SELECT TOP 2
START_DT
,END_DT
FROM (SELECT
ROW_NUMBER() OVER (PARTITION BY START_DT, END_DT ORDER BY START_DT, END_DT) AS rn
,START_DT, END_DT
FROM
@t
WHERE
END_DT <= GETDATE()) AS x
WHERE
rn = 1
ORDER BY
END_DT DESC
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMarch 24, 2008 at 9:40 am
same result:
START_DT END_DT
----------------------- -----------------------
2008-03-01 00:00:00.000 2008-03-15 00:00:00.000
2008-02-16 00:00:00.000 2008-02-29 00:00:00.000
March 24, 2008 at 9:50 am
I missed that "one row" requirement. ARGH!!!
SELECT
MIN(START_DT) AS START_DT
,MAX(END_DT) AS END_DT
FROM (SELECT
DENSE_RANK() OVER (ORDER BY END_DT DESC) AS rec
,ROW_NUMBER() OVER (PARTITION BY START_DT, END_DT ORDER BY START_DT, END_DT) AS rn
,START_DT, END_DT
FROM
@t
WHERE
END_DT <= GETDATE()) AS x
WHERE
rec <= 2 AND rn = 1
And there is probably an easier way to write this... 😀
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMarch 24, 2008 at 11:39 am
SELECT MIN(a.Start_dt), MAX(a.End_dt)
FROM(
SELECT
ROW_NUMBER() OVER(ORDER BY End_Dt DESC) AS [RowNbr],
Start_Dt [Start_dt],
End_Dt
FROM @t
WHERE End_Dt < GETDATE()
) AS a
WHERE a.RowNbr < 3
March 24, 2008 at 11:42 am
Thanks Jason. It worked.
🙂
March 25, 2008 at 7:36 am
Jason Selburg (3/24/2008)
And there is probably an easier way to write this... 😀
That would be true, ol' friend... and, in this case, simpler means faster and easier to read to boot...
First, the obligitory million rows of test data... 😉--===== Create and populate a 1,000,000 row test table.
-- Column "Start_Dt" has a range of >=01/01/2000 and <01/01/2010 non-unique date/times
-- Column "End_Date" has a range of >=01/01/2000 and <01/01/2010 non-unique date/times
-- Jeff Moden
SELECT TOP 1000000
Start_Dt = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME),
End_Dt = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME)
INTO dbo.JBMTest
FROM Master.dbo.SysColumns t1,
Master.dbo.SysColumns t2
... notice, no indexes or primary key because the schema of the real table is unknown...
Here's the test of 3 different methods... "Old School" methods will many times beat out the
fancy "new" methods... sometimes by a long shot. Run these against the test data above.
PRINT '========== "Old School" method =========='
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT MIN(Start_Dt) AS Start_Dt,
MAX(End_Dt) AS End_Dt
FROM (
SELECT TOP 2
Start_Dt,End_Dt
FROM dbo.jbmTest
WHERE End_Dt < GETDATE()
ORDER BY End_Dt DESC, Start_Dt DESC) d
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
PRINT REPLICATE('=',100)
GO
PRINT '========== "Old School" method using CTE =========='
SET STATISTICS IO ON
SET STATISTICS TIME ON
;WITH cteTop2 AS
(
SELECT TOP 2
Start_Dt,End_Dt
FROM dbo.jbmTest
WHERE End_Dt < GETDATE()
ORDER BY End_Dt DESC, Start_Dt DESC
)
SELECT MIN(Start_Dt) AS Start_Dt,
MAX(End_Dt) AS End_Dt
FROM cteTop2
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
PRINT REPLICATE('=',100)
PRINT '========== Jason''s ROW_NUMBER method =========='
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT
MIN(START_DT) AS START_DT
,MAX(END_DT) AS END_DT
FROM
(SELECT
DENSE_RANK() OVER (ORDER BY END_DT DESC) AS rec
,ROW_NUMBER() OVER (PARTITION BY START_DT, END_DT ORDER BY START_DT, END_DT) AS rn
,START_DT, END_DT
FROM
dbo.jbmTest
WHERE
END_DT <= GETDATE()) AS x
WHERE
rec <= 2 AND rn = 1
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
PRINT REPLICATE('=',100)
GO
PRINT '===== Just show the top 2 rows < GETDATE()'
SELECT TOP 2
Start_Dt,End_Dt
FROM dbo.jbmTest
WHERE End_Dt < GETDATE()
ORDER BY End_Dt DESC, Start_Dt DESC
Here's the results on my box (answer will change because of the randow test data)...
========== "Old School" method ==========
Start_Dt End_Dt
----------------------- -----------------------
2005-12-15 10:52:12.217 2008-03-25 09:27:14.923
(1 row(s) affected)
Table 'JBMTest'. Scan count 1, logical reads 3097, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 937 ms, elapsed time = 1030 ms.
====================================================================================================
========== "Old School" method using CTE ==========
Start_Dt End_Dt
----------------------- -----------------------
2005-12-15 10:52:12.217 2008-03-25 09:27:14.923
(1 row(s) affected)
Table 'JBMTest'. Scan count 1, logical reads 3097, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 1016 ms, elapsed time = 1047 ms.
====================================================================================================
========== Jason's ROW_NUMBER method ==========
START_DT END_DT
----------------------- -----------------------
2005-12-15 10:52:12.217 2008-03-25 09:27:14.923
(1 row(s) affected)
Table 'JBMTest'. Scan count 1, logical reads 3097, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 8562 ms, elapsed time = 9400 ms.
====================================================================================================
===== Just show the top 2 rows < GETDATE()
Start_Dt End_Dt
----------------------- -----------------------
2009-12-26 16:06:03.187 2008-03-25 09:27:14.923
2005-12-15 10:52:12.217 2008-03-25 09:21:37.477
(2 row(s) affected)
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply