SQL Query to find minimum of date

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

General SQL Server Forums

New to SQL Server Programming

SQL Query to find minimum of date

Author

Topic

s_anr
Yak Posting Veteran

81 Posts

Posted - 2009-05-08 : 05:36:55

There are four columns in a table "TEST" and the sample data is given below :

sl_no Initial_date Log_Time Description
245 4/9/2009 13:00 4/9/2009 15:51 This is a description
245 4/9/2009 13:00 4/11/2009 13:45 This is a description
245 4/9/2009 13:00 4/9/2009 15:38 This is a description
296 4/10/2009 23:00 4/10/2009 23:07 This is a description
296 4/10/2009 23:00 4/10/2009 23:09 This is a description
296 4/10/2009 23:00 4/10/2009 23:11 This is a description
296 4/10/2009 23:00 4/10/2009 23:17 This is a description
791 4/11/2009 23:02 4/10/2009 23:17 This should return null value
892 4/10/2009 23:11 4/10/2009 23:17 This should return null value
596 4/10/2009 23:17 4/10/2009 23:17 This should return null value

How do I create a query so that it returns the following OUTPUT :

sl_no Initial_date Log_Time Description
245 4/9/2009 13:00 4/9/2009 15:38 This is a description
296 4/10/2009 23:00 4/10/2009 23:07 This is a description
791 4/11/2009 23:02 NULL This should return null value
892 4/10/2009 23:11 NULL This should return null value
596 4/10/2009 23:17 NULL This should return null value

The condition to extract this data are :
1. When ever "description" contains the word DESCRIPTION, it should return the minimum LOG_TIME and if this word is not preset it should return NULL as LOG_TIME
2. For each sl_no there wil be only ione value finally (either NULL or the minimum of LOG TIME)
3. A data filter should be added so that the data based on INITIAL_TIME (i.e between 4/10/2009 and 4/11/2009) can be pulled.

I have tried two queries but it does not seem to work.

The queries are :
select sl_no,dateadd(hh,-7,dateAdd(ss, min()log_time, '19700101')), dateadd(hh,-7,dateAdd(ss, initial_time, '19700101')), description where description like '%description%'
and dateadd (hh,-7,dateAdd(ss, open_date, '19700101')) >= '2009-04-10' and dateadd (hh,-7,dateAdd(ss, open_date, '19700101')) < '2009-04-11'

select sl_no, log_time as NULL, dateadd(hh,-7,dateAdd(ss, initial_time, '19700101')), description where description not like '%description%'and dateadd (hh,-7,dateAdd(ss, open_date, '19700101')) >= '2009-04-10' and dateadd (hh,-7,dateAdd(ss, open_date, '19700101')) < '2009-04-11'

I guess these two queries have to be modified and combined to get the desired results

Would someone help please.......? I have been rocking my brains for a long time to get it done. I would run this query on SQL 2005

Regards
Black Mamba

s_anr
Yak Posting Veteran

81 Posts

Posted - 2009-05-08 : 05:40:06

JUST A NOTE : THE VALUES IN LOG_TIME AND INITIAL_TIME ARE IN UNIX FORMAT AND HENCE I"M USING THE DATEADD FORMAT
Regards
Black Mamba

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-08 : 06:18:34

What's the version of SQL Server are you using ?

quote:
2. For each sl_no there wil be only ione value finally (either NULL or the minimum of LOG TIME)

What if for a sl_no, there are 2 lines and one with "description" and one without ?

KH
[spoiler]Time is always against us[/spoiler]

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-05-08 : 06:18:36

[code]-- *** Test Data ***
-- Please supply this in future
DECLARE @t TABLE
(
sl_no int NOT NULL
,Initial_date datetime NOT NULL
,Log_Time datetime NOT NULL
,[Description] varchar(50) NOT NULL
)
INSERT INTO @t
SELECT 245, '20090409 13:00', '20090409 15:51', 'This is a description' UNION ALL
SELECT 245, '20090409 13:00', '20090411 13:45', 'This is a description' UNION ALL
SELECT 245, '20090409 13:00', '20090409 15:38', 'This is a description' UNION ALL
SELECT 296, '20090410 23:00', '20090410 23:07', 'This is a description' UNION ALL
SELECT 296, '20090410 23:00', '20090410 23:09', 'This is a description' UNION ALL
SELECT 296, '20090410 23:00', '20090410 23:11', 'This is a description' UNION ALL
SELECT 296, '20090410 23:00', '20090410 23:17', 'This is a description' UNION ALL
SELECT 791, '20090411 23:02', '20090410 23:17', 'This should return null value' UNION ALL
SELECT 892, '20090410 23:11', '20090410 23:17', 'This should return null value' UNION ALL
SELECT 596, '20090410 23:17', '20090410 23:17', 'This should return null value'
-- *** Test Data ***

-- The query
SELECT sl_no, Initial_date
,CASE
WHEN [Description] LIKE '%Description%'
THEN MIN(Log_Time)
END AS Log_Time
,[Description]
-- replace your table name here
FROM @t
-- WHERE Initial_date >= '20090410'
-- AND Initial_date < '20090412'
GROUP BY sl_no, Initial_date, [Description]
[/code]

s_anr
Yak Posting Veteran

81 Posts

Posted - 2009-05-08 : 06:20:59

KH - that is not possible as the interface for this database is created in this way.

mailsomani
Starting Member

4 Posts

Posted - 2009-05-08 : 06:38:24

Hi,
Try this query.

select * from (
select a.* from Test a
inner join
(select si_no,min(log_time) as minLog from Test where description like '%description%' group by si_no)b
on a.si_no=b.si_no and a.log_time=b.minLog
union
select si_no,initial_date,null,Description from Test where description not like '%description%'
)Test where Initial_date>'date' and Initial_date<'date'

Note: In query i am not formatting the date.

Regards
Pawan Somani
WINWIRE TECHNOLOGIES

Pawansomani

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-08 : 06:39:47

Hijacking Ifor's code


DECLARE @t TABLE
(
    sl_no int NOT NULL
    ,Initial_date datetime NOT NULL
    ,Log_Time datetime NOT NULL
    ,[Description] varchar(50) NOT NULL
)
INSERT INTO @t
SELECT 245, '20090409 13:00', '20090409 15:51', 'This is a description' UNION ALL
SELECT 245, '20090409 13:00', '20090411 13:45', 'This is a description' UNION ALL
SELECT 245, '20090409 13:00', '20090409 15:38', 'This is a description' UNION ALL
SELECT 296, '20090410 23:00', '20090410 23:07', 'This is a description' UNION ALL
SELECT 296, '20090410 23:00', '20090410 23:09', 'This is a description' UNION ALL
SELECT 296, '20090410 23:00', '20090410 23:11', 'This is a description' UNION ALL
SELECT 296, '20090410 23:00', '20090410 23:17', 'This is a description' UNION ALL
SELECT 791, '20090411 23:02', '20090410 23:17', 'This should return null value' UNION ALL
SELECT 892, '20090410 23:11', '20090410 23:17', 'This should return null value' UNION ALL
SELECT 596, '20090410 23:17', '20090410 23:17', 'This should return null value'

SELECT	t.sl_no, t.Initial_date,
	Log_Time	= CASE 	WHEN 	t.[Description] NOT LIKE '%description%'
				THEN	NULL
				ELSE	t.Log_Time
				END,
	t.[Description]
FROM	@t t
	INNER JOIN
	(
		SELECT	sl_no, Log_Time = MIN(Log_Time)
		FROM	@t
		GROUP BY sl_no
	) m	ON	t.sl_no		= m.sl_no
		AND	t.Log_Time	= m.Log_Time

/* RESULT
sl_no  Initial_date             Log_Time                  Description                   
------ ------------------------ ------------------------- ------------------------------
245    2009-04-09 13:00:00.000  2009-04-09 15:38:00.000   This IS a description
296    2009-04-10 23:00:00.000  2009-04-10 23:07:00.000   This IS a description
596    2009-04-10 23:17:00.000  NULL                      This should RETURN NULL value
791    2009-04-11 23:02:00.000  NULL                      This should RETURN NULL value
892    2009-04-10 23:11:00.000  NULL                      This should RETURN NULL value

(5 row(s) affected)
*/

KH
[spoiler]Time is always against us[/spoiler]

s_anr
Yak Posting Veteran

81 Posts

Posted - 2009-05-08 : 06:45:10

Hi IFOR, the major problem is that the timwe is stored in UNIX /EPOCH FORMAT (Integer value) and I would want the output and date inputs in normal TIME DATE format rather than EPOCH format. The Data types used are given below (Sorry I missed it in the initial post)

sl_no = nvarchar(30)
log_time = int
description = ntext
initial_date = int

s_anr
Yak Posting Veteran

81 Posts

Posted - 2009-05-08 : 06:46:08

KH, I missed the SQL server : Its SQL Server 2005.

s_anr
Yak Posting Veteran

81 Posts

Posted - 2009-05-08 : 08:19:49

My apologies everyone!

The data is stored in this format

sl_no initial_date log_time description
245 1239282000 1239292260 This is a description
245 1239282000 1239291900 This is a description
245 1239282000 1239291480 This is a description
296 1239404400 1239404820 This is a description
296 1239404400 1239404940 This is a description
296 1239404400 1239405060 This is a description
296 1239404400 1239405420 This is a description
791 1239404520 1239405420 This should return null value
892 1239405060 1239405420 This should return null value
596 1239405420 1239405420 This should return null value

The output should be as given below (initial_date and log_time should be in human readable form).

sl_no initial_date log_time description
245 1239282000 1239291480 This is a description
296 1239404400 1239404820 This is a description
791 1239404520 NULL This should return null value
892 1239405060 NULL This should return null value
596 1239405420 NULL This should return null value

But the query should accept date in human readable format and it should give the output as well in human readable form.

Does that make sense?

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-05-08 : 12:47:59

Just use the date functions.
For my simplistic query something like:

SELECT sl_no
    ,DATEADD(s, Initial_date, '19700101') AS Initial_date
    ,CASE
        WHEN [Description] LIKE '%Description%'
        THEN DATEADD(s, MIN(Log_Time), '19700101')
        ELSE CAST(NULL AS datetime)
    END AS Log_Time
    ,[Description]
FROM YourTable
-- WHERE Initial_date >= DATEDIFF(s, '19700101', '20090410')
--     AND Initial_date < DATEDIFF(s, '19700101', '20090412')
GROUP BY sl_no, Initial_date, [Description]

s_anr
Yak Posting Veteran

81 Posts

Posted - 2009-05-08 : 12:54:30

ok let me check

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-08 : 12:54:51

[code]
SELECT t.sl_no,t.initial_date,
CASE WHEN t1.Cnt>1 THEN t.log_time ELSE NULL END,
t.description
FROM YourTable t
INNER JOIN (SELECT sl_no,MIN(log_time) AS MinTime,COUNT(*) AS Cnt
FROM YourTable
GROUP BY sl_no) t1
ON t1.sl_no=t.sl_no
AND t1.MinTime=t.log_time
[/code]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-08 : 23:00:57

Datetime or Integer, the query i posted will still work

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources