Querying min, max and between - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Querying min, max and between

Author

Topic

workindan
Starting Member

21 Posts

Posted - 2012-06-25 : 17:36:58

I'm trying to summarize an activity table with a history table perID and between 2 dates.

Table Activity looks like:
ID / ACTIVITY_DATETIME / RESULT
id1 / '2012-01-18' / 'Unavailable'
id1 / '2012-01-21' / 'Confirmed'
id2 / '2012-01-19' / 'Unavailable'
id3 / '2012-01-19' / 'Unavailable'

Table History looks like:
ID / HISTORY_DATETIME / API
id1 / '2012-01-15' / 'web'
id1 / '2012-01-22' / 'phone'
id2 / '2012-02-01' / 'web'
id3 / '2012-02-09' / 'web'

I'm looking to find the summary of activity between each history date for each ID.

Current code:


Select ID
sum(case when result IN ('...','...') then 1 else 0 end) as Confirmed,
sum(case when result IN ('...','...') then 1 else 0 end) as Denied,
sum(case when result IN ('...','...') then 1 else 0 end) as Unavail
FROM tblActivity tblA
WHERE tblA.Activity_Datetime > (SELECT dateA from History WHERE ActivityID = HistoryID) 
AND tblA.Activity_Datetime <= (SELECT dateB from History WHERE ActivityID = HistoryID)

If history has dates A,B,C,D,E,F how do I query unique rows only select between dates A and B, then dates B and C, then dates C and D (etc) for each ID?

I'd like the select to produce:
ID / sum(ResultA) / sum(ResultB) / sum(ResultC) / HistoryAPI
id1 / 2 / 0 / 0 / 'web'
id1 / 0 / 0 / 1 / 'web'
id2 / 0 / 1 / 0 / 'phone'
...

Thanks for reading,
Dan

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-06-25 : 18:03:16

I'm not following your logic for you determine which result should be in the result set. Is there any matching between the tables on ID or is it just the DATES?

Here is a query that might help get you going, but feel free to clarif your business rules and we can help further:

DECLARE @Activity TABLE (ID VARCHAR(10), ACTIVITY_DATETIME DATE, RESULT VARCHAR(20))
INSERT @Activity VALUES
('id1', '2012-01-18', 'Unavailable'),
('id1', '2012-01-21', 'Confirmed'),
('id2', '2012-01-19', 'Unavailable'),
('id3', '2012-01-19', 'Unavailable')

DECLARE @History TABLE (ID VARCHAR(10), HISTORY_DATETIME DATE, API VARCHAR(10))
INSERT @History VALUES
('id1', '2012-01-15', 'web'),
('id1', '2012-01-22', 'phone'),
('id2', '2012-02-01', 'web'),
('id3', '2012-02-09', 'web')

SELECT
	A.ID,
	sum(case when A.result IN ('Confirmed') then 1 else 0 end) as Confirmed,
	sum(case when A.result IN ('Denied') then 1 else 0 end) as Denied,
	sum(case when A.result IN ('Unavailable') then 1 else 0 end) as Unavail,
	B.API
FROM
	@Activity AS A
INNER JOIN
	(
		SELECT 
			H1.ID, 
			H1.API,
			H1.HISTORY_DATETIME AS HISTORY_DATETIME_Start, 
			COALESCE(H2.HISTORY_DATETIME, '99991231') AS HISTORY_DATETIME_Next
		FROM 
			@History AS H1
		OUTER APPLY
			(
				SELECT MIN(HISTORY_DATETIME) AS HISTORY_DATETIME
				FROM @History
				WHERE H1.HISTORY_DATETIME < HISTORY_DATETIME
			) AS H2
	) AS B
	ON A.ACTIVITY_DATETIME BETWEEN B.HISTORY_DATETIME_Start AND B.HISTORY_DATETIME_Next
GROUP BY
	A.ID,
	B.HISTORY_DATETIME_Start,
	B.API

EDIT: It looks like the OP corrected the expected results. I'm still not understanding the logic.
EDIT2: Did a SQL 2012 solution, editing to a 2008 solution.

workindan
Starting Member

21 Posts

Posted - 2012-06-25 : 18:42:25

I hope this clarifies:

Consider each history record as an END result with the activity table containing multiple transactions leading up to that particular result.

So, for each history end result (History DateA, DateB,...) I'd want to sum up the activity that occured before it for each ID. What activity happened for ID1 before DateA? What activity occured for ID1 between DateA and DateB?

I hope this helps. Using SQL 2008, version 10.0.2531.0

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-06-25 : 19:37:16

I still don't see how you get the sample output your shown above but, perhaps, it is not correct. Try this:

SELECT 
	A.ID, 
	B.Confirmed,
	B.Denied,
	B.Unavail,
	A.API AS HistoryAPI

FROM 
	@History AS A
OUTER APPLY
	(
		SELECT
			sum(case when result IN ('Confirmed') then 1 else 0 end) as Confirmed,
			sum(case when result IN ('Denied') then 1 else 0 end) as Denied,
			sum(case when result IN ('Unavailable') then 1 else 0 end) as Unavail
		FROM 
			@Activity
		WHERE 
			ACTIVITY_DATETIME < A.HISTORY_DATETIME
			AND A.ID = ID
	) AS B

If that is not correct, please break down the logic for each row in your sample otuput to help make it more clear.

workindan
Starting Member

21 Posts

Posted - 2012-06-27 : 23:19:26

I figured it out using two user defined functions and an OUTER APPLY:

SELECT
t.id,
t.historydatetime,
t.API,
a.*
FROM
HistoryTable t
OUTER APPLY
fn_getActivity(t.id,t.historydatetime) AS a
ORDER BY
t.id,t.activitydatetime asc

..with these functions:

 fn_getActivity (@id,@hdate)
RETURNS TABLE
AS
RETURN
( SELECT sum(case when....), etc....
FROM ActivityTable
WHERE id = @id
AND activitydatetime > (select * from fn_previousHistory(@id,@hdate)
AND activitydatetime <= @hdate
)
GO

fn_previousHistory(@id,@hdate)
RETURNS TABLE
AS
RETURN
( SELECT (case when PrevHist is null then '1900-01-01' else PrevHist end) as PrevHist FROM
  ( SELECT max(historydatetime) as PrevHist
    FROM HistoryTable
    WHERE historydatetime < @hdate
    AND id = @id) as t
)
GO

woo hoo! It isn't the most optimized query, but it gets the job done.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources