SQL Sub-Query using Dates

swaggrK
swaggrK used Ask the Experts™
on
I need to edit my query so that when two date parameters are passed to it, the query will accurately pull the correct data.
I would like to use a BETWEEN statement that looks at the dateBegin and dateEnd.


[EditHistoryPlacement] table
placementID	dateAdded	columnName
64291	2010-08-25 17:21:00.000	customText10
64291	2010-09-20 12:50:00.000	customText11
64291	2018-07-23 16:01:32.260	customText32
64291	2018-07-23 16:01:32.260	customText33
64291	2010-08-25 17:21:00.000	customText6
64291	2010-08-25 17:21:00.000	dateBegin
64291	2010-08-25 17:21:00.000	dateEnd
64291	2010-08-25 17:38:00.000	dateEnd
64291	2010-08-25 17:21:00.000	daysGuaranteed
64291	2017-12-13 16:31:01.653	employeeType
64291	2010-08-25 17:21:00.000	otExemption
64291	2010-08-25 17:21:00.000	reportTo
64291	2010-09-16 15:20:00.000	status
64291	2016-12-16 12:13:00.000	taxState
64291	2015-05-05 21:44:00.000	terminationReason
64291	2015-05-14 22:13:00.000	terminationReason

Open in new window



My  query:
SELECT DISTINCT
ehp.[placementID]
,(SELECT TOP 1 min([newValue]) as dateBegin FROM [AddisonDataMirror].[dbo].[EditHistoryPlacement] e1
		WHERE columnName = 'dateBegin' 
		and e1.placementID in (ehp.placementID) 
		GROUP BY [dateAdded]) as dateBegin

,(SELECT TOP 1 max([newValue]) FROM [AddisonDataMirror].[dbo].[EditHistoryPlacement] e2
		WHERE columnName = 'dateEnd' 
		and e2.placementID in (ehp.placementID)
		GROUP BY [dateAdded]) as dateEnd


FROM [EditHistoryPlacement] ehp

WHERE 
 ehp.placementID in (64291) 
and (columnName = 'dateBegin' or columnName = 'dateEnd') 
ORDER BY [placementID]

Open in new window


The results from my query

placementID	dateBegin	dateEnd
64291	2010-09-14 00:00:00.0	2013-09-14 00:00:00.0

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
Need directly usable sample data.  That is, CREATE TABLE and INSERT statements for [EditHistoryPlacement], not just a splat of data on the screen.
I'm guessing the two tables are in different databases:
SELECT	a.[placementID]
,		dateBegin = MIN(CASE WHEN b.columnName = 'dateBegin' THEN b.[newValue] END)
,		dateEnd   = MAX(CASE WHEN b.columnName = 'dateEnd'   THEN b.[newValue] END)
FROM	[EditHistoryPlacement]					a
LEFT JOIN	[AddisonDataMirror].[dbo].[EditHistoryPlacement]	b	ON	a.placementID = b.placementID
										AND	a.columnName IN ('dateBegin', 'dateEnd') 
										AND	b.columnName IN ('dateBegin', 'dateEnd') 
										AND	a.placementID = 64291
GROUP BY	a.placementID
ORDER BY	a.placementID

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial