Query Help - 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)

Query Help

Author

Topic

skippy59
Starting Member

2 Posts

Posted - 2012-10-30 : 06:22:03

Hi,

I search for a few days with this case,

I have this table

Activity Campaign DateTimeValue
FID-1-SEM-2012 UNADEV 2012-10-23 10:48:44.187
FID-1-SEM-2012 UNADEV 2012-10-23 10:54:47.780
FID-1-SEM-2012 UNADEV 2012-10-23 11:31:21.423
FID-1-SEM-2012 UNADEV 2012-10-23 11:41:17.857
FID-1-SEM-2012 UNADEV 2012-10-23 11:45:43.947
FID-1-SEM-2012 UNADEV 2012-10-23 11:54:36.297
FID-1-SEM-2012 UNADEV 2012-10-23 11:55:43.757
FID-1-SEM-2012 UNADEV 2012-10-23 11:56:37.527
NVX 01-05 2012 UNADEV 2012-10-23 12:04:25.937
NVX 01-05 2012 UNADEV 2012-10-23 12:06:39.930
NVX 01-05 2012 UNADEV 2012-10-23 12:11:53.123
NVX 01-05 2012 UNADEV 2012-10-23 12:12:53.580
NVX 01-05 2012 UNADEV 2012-10-23 12:13:46.173
NVX 01-05 2012 UNADEV 2012-10-23 12:15:42.657
FID-1-SEM-2012 UNADEV 2012-10-23 14:39:12.750
FID-1-SEM-2012 UNADEV 2012-10-23 14:39:57.577
FID-1-SEM-2012 UNADEV 2012-10-23 14:41:06.530
FID-1-SEM-2012 UNADEV 2012-10-23 14:43:10.053
FID-1-SEM-2012 UNADEV 2012-10-23 14:43:44.830
FID-1-SEM-2012 UNADEV 2012-10-23 14:45:13.047
FID-1-SEM-2012 UNADEV 2012-10-23 14:46:14.590

I have to get for each Campaign,Activity the min and the max DateTimeValue. I can have severals same Campaign,Activity

So for this sample, i want to get:

Activity Campaign MinDateTimeValue MaxDateTimeValue
FID-1-SEM-2012 UNADEV 2012-10-23 10:48:44.187 2012-10-23 11:56:37.527
NVX 01-05 2012 UNADEV 2012-10-23 12:04:25.937 2012-10-23 12:15:42.657
FID-1-SEM-2012 UNADEV 2012-10-23 14:39:12.750 2012-10-23 14:46:14.590

Thanks

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-30 : 06:30:57


SELECT Activity, Campaign, MIN(DateTimeValue) AS MinDateTimeValue, MAX(DateTimeValue) AS MaxDateTimeValue 
FROM YourTable
GROUP BY Activity, Campaign

--
Chandu

skippy59
Starting Member

2 Posts

Posted - 2012-10-30 : 06:32:50

Sorry but this query doesn't work, i'll get :

Activity Campaign MinDateTimeValue MaxDateTimeValue
FID-1-SEM-2012 UNADEV 2012-10-23 10:48:44.187 2012-10-23 14:46:14.590
NVX 01-05 2012 UNADEV 2012-10-23 12:04:25.937 2012-10-23 12:15:42.657

and this is not, i want

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-30 : 09:28:02

[code]
SELECT t.Activity, t.Campaign, MIN(t.DateTimeValue) AS MinDateTimeValue, MAX(t.DateTimeValue) AS MaxDateTimeValue
FROM YourTable t
OUTER APPLY (SELECT MIN(DateTimeValue) AS NextDate
FROM YourTable
WHERE DateTimeValue > t.DateTimeValue
AND (Activity <> t.Activity
OR Campaign <> t.Campaign)
)t1
GROUP BY t.Activity, t.Campaign,t1.NextDate
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources