May 8, 2009 at 4:04 am
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
NOTE : THE VALUES IN LOG_TIME AND INITIAL_TIME ARE IN UNIX FORMAT AND HENCE I"M USING THE DATEADD FORMAT
Regards
Black Mamba
May 8, 2009 at 4:35 am
Hi
ansharma (5/8/2009)
NOTE : THE VALUES IN LOG_TIME AND INITIAL_TIME ARE IN UNIX FORMAT AND HENCE I"M USING THE DATEADD FORMAT
I don't know what you want to say with this. The dates are VARCHAR?
If not try this:
DECLARE @t TABLE (sl_no INT, Initial_date DATETIME, Log_Time DATETIME, Description VARCHAR(100))
INSERT INTO @t
SELECT 245, '4/9/2009 13:00', '4/9/2009 15:51', 'This is a description'
UNION ALL SELECT 245, '4/9/2009 13:00', '4/11/2009 13:45', 'This is a description'
UNION ALL SELECT 245, '4/9/2009 13:00', '4/9/2009 15:38', 'This is a description'
UNION ALL SELECT 296, '4/10/2009 23:00', '4/10/2009 23:07', 'This is a description'
UNION ALL SELECT 296, '4/10/2009 23:00', '4/10/2009 23:09', 'This is a description'
UNION ALL SELECT 296, '4/10/2009 23:00', '4/10/2009 23:11', 'This is a description'
UNION ALL SELECT 296, '4/10/2009 23:00', '4/10/2009 23:17', 'This is a description'
UNION ALL SELECT 791, '4/11/2009 23:02', '4/10/2009 23:17', 'This should return null value'
UNION ALL SELECT 892, '4/10/2009 23:11', '4/10/2009 23:17', 'This should return null value'
UNION ALL SELECT 596, '4/10/2009 23:17', '4/10/2009 23:17', 'This should return null value'
; WITH
cte (sl_no, Initial_date, Log_Time, Description, RowNum) AS
(
SELECT
sl_no,
Initial_date,
CASE WHEN CHARINDEX('Description', Description) != 0 THEN Log_Time ELSE NULL END,
Description,
ROW_NUMBER() OVER (PARTITION BY sl_no ORDER BY Log_Time)
FROM @t
)
SELECT *
FROM cte
WHERE RowNum = 1
Greets
Flo
May 8, 2009 at 4:48 am
Hi FLO, the major problem is that the time 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
May 8, 2009 at 5:00 am
What would be the expected output for the following data?
401 4/10/2009 23:00 4/10/2009 23:07 This should return null value
401 4/10/2009 23:00 4/10/2009 23:09 This is a description
--Ramesh
May 8, 2009 at 5:44 am
Hi
ansharma (5/8/2009)
sl_no = nvarchar(30)log_time = int
description = ntext
initial_date = int
Your initial sample data:
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
Well... Either you are working with a very special kind of an extended "int" or the sample date do not match the table definition 😉
Please provide your DDL and some sample data. You can find a link in my signature which should help with this.
Greets
Flo
May 8, 2009 at 6:18 am
My apologies everyone!
The data is stored in this format
sl_noinitial_datelog_timedescription
24512392820001239292260This is a description
24512392820001239291900This is a description
24512392820001239291480This is a description
29612394044001239404820This is a description
29612394044001239404940This is a description
29612394044001239405060This is a description
29612394044001239405420This is a description
79112394045201239405420This should return null value
89212394050601239405420This should return null value
59612394054201239405420This should return null value
The output should be as given below (initial_date and log_time should be in human readable form).
sl_noinitial_date log_time description
24512392820001239291480This is a description
29612394044001239404820This is a description
7911239404520NULL This should return null value
8921239405060NULL This should return null value
5961239405420NULL 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?
May 8, 2009 at 6:22 am
You still didn't answered my question?
Edit:
I have the solution but it depends on what you answer to my question.
--Ramesh
May 8, 2009 at 6:35 am
Hi Ramesh
This should be the output (Please note that teh data is stored in UNIX format i.e int data type :
sl_noinitial_datelog_timedescription
2454/9/2009 13:00 4/9/2009 15:38 This is a description
2964/10/2009 23:00:00 PM4/10/2009 23:07:00 PMThis is a description
7914/10/2009 23:02:00 PMNULL This should return null value
8924/10/2009 23:11:00 PMNULL This should return null value
5964/10/2009 23:17:00 PMNULL This should return null value
May 8, 2009 at 7:02 am
ansharma (5/8/2009)
Hi RameshThis should be the output (Please note that teh data is stored in UNIX format i.e int data type :
sl_noinitial_datelog_timedescription
2454/9/2009 13:00 4/9/2009 15:38 This is a description
2964/10/2009 23:00:00 PM4/10/2009 23:07:00 PMThis is a description
7914/10/2009 23:02:00 PMNULL This should return null value
8924/10/2009 23:11:00 PMNULL This should return null value
5964/10/2009 23:17:00 PMNULL This should return null value
What would be the expected output for the following data?
401 4/10/2009 23:00 4/10/2009 23:07 This should return null value
401 4/10/2009 23:00 4/10/2009 23:09 This is a description
--Ramesh
May 8, 2009 at 7:32 am
Ramesh,
This is an invalid condition in the current context. In case sl_no is same, it will have similar description.
So the valid input data would be
either
401 4/10/2009 23:00 4/10/2009 23:07 This is a description
401 4/10/2009 23:00 4/10/2009 23:09 This is a description
with the output 401 4/10/2009 23:00 4/10/2009 23:07 This is a description
OR
with teh input :
401 4/10/2009 23:00 4/10/2009 23:07 This is a description
401 4/10/2009 23:00 4/10/2009 23:09 This is a description
402 4/10/2009 23:00 4/10/2009 23:09 This should return NULL value
output should be ;
401 4/10/2009 23:00 4/10/2009 23:07 This is a description
402 4/10/2009 23:00 NULL This should return NULL value
May 8, 2009 at 7:51 am
Good...
Thanks Flo for the script:-)
So here is the solution same as Flo's solution except that considers UNIX-EPOCH date formats.
DECLARE @t TABLE( sl_no INT, Initial_date INT, Log_Time INT, Description VARCHAR(100) )
INSERT INTO @t
SELECT 245, DATEDIFF( SECOND, '19700101', '4/9/2009 13:00' ), DATEDIFF( SECOND, '19700101', '4/9/2009 15:51' ), 'This is a description'
UNION ALL SELECT 245, DATEDIFF( SECOND, '19700101', '4/9/2009 13:00' ), DATEDIFF( SECOND, '19700101', '4/11/2009 13:45' ), 'This is a description'
UNION ALL SELECT 245, DATEDIFF( SECOND, '19700101', '4/9/2009 13:00' ), DATEDIFF( SECOND, '19700101', '4/9/2009 15:38' ), 'This is a description'
UNION ALL SELECT 296, DATEDIFF( SECOND, '19700101', '4/10/2009 23:00' ), DATEDIFF( SECOND, '19700101', '4/10/2009 23:07' ), 'This is a description'
UNION ALL SELECT 296, DATEDIFF( SECOND, '19700101', '4/10/2009 23:00' ), DATEDIFF( SECOND, '19700101', '4/10/2009 23:09' ), 'This is a description'
UNION ALL SELECT 296, DATEDIFF( SECOND, '19700101', '4/10/2009 23:00' ), DATEDIFF( SECOND, '19700101', '4/10/2009 23:11' ), 'This is a description'
UNION ALL SELECT 296, DATEDIFF( SECOND, '19700101', '4/10/2009 23:00' ), DATEDIFF( SECOND, '19700101', '4/10/2009 23:17' ), 'This is a description'
UNION ALL SELECT 791, DATEDIFF( SECOND, '19700101', '4/11/2009 23:02' ), DATEDIFF( SECOND, '19700101', '4/10/2009 23:17' ), 'This should return null value'
UNION ALL SELECT 892, DATEDIFF( SECOND, '19700101', '4/10/2009 23:11' ), DATEDIFF( SECOND, '19700101', '4/10/2009 23:17' ), 'This should return null value'
UNION ALL SELECT 596, DATEDIFF( SECOND, '19700101', '4/10/2009 23:17' ), DATEDIFF( SECOND, '19700101', '4/10/2009 23:17' ), 'This should return null value'
; WITH ConvertedTimes
AS
(
SELECT sl_no,
DATEADD( SECOND, Initial_date, '19700101' ) AS Initial_date,
DATEADD( SECOND, Log_Time, '19700101' ) AS Log_Time, [Description]
FROM @t
),
TestCTE
AS
(
SELECT ROW_NUMBER() OVER( PARTITION BY sl_no ORDER BY Log_Time ) AS RowNumber,
sl_no, Initial_date, [Description],
( CASE WHEN CHARINDEX( 'Description', [Description] ) > 0 THEN Log_Time ELSE NULL END ) AS Log_Time
FROM ConvertedTimes
)
SELECT sl_no, Initial_date, Log_Time, [Description]
FROM TestCTE
WHERE RowNumber = 1
--Ramesh
May 8, 2009 at 8:26 am
Ramesh (5/8/2009)
Good...Thanks Flo for the script:-)
Always welcome! 🙂
May 8, 2009 at 8:34 am
Thank you FLo and Ramesh!
Ramesh your code works beautifully. But you'll have to bear with a new user : ME. You have created a table with the selected inputs and given me the output as well. But Here is the situation
I already have a database and it has a table "TEST". some of the entries as shown in the table are:
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
I would need the query to pull the It should return the following output :
2452009-04-09 13:002009-04-09 15:38:00.000This is a description
2962009-04-10 23:002009-04-10 23:07:00.000This is a description
5962009-04-10 23:17NULL This should return null value
7912009-04-11 23:02NULL This should return null value
8922009-04-10 23:11NULL This should return null value
I should be able to pull this report by specifying the dates in where clause of the query.
(the log time / initial date is stored as : 1239405420 which translates to : 2009-04-10 23:17)
May 8, 2009 at 8:47 am
Never mind the newbies...:-D
DECLARE @FromDate DATETIME, @ToDate DATETIME
DECLARE @FromDateConverted INT, @ToDateConverted INT
SELECT @FromDate = '2009-04-09',
@ToDate = '2009-04-10',
@FromDateConverted = DATEDIFF( SECOND, '19700101', @FromDate ),
@ToDateConverted = DATEDIFF( SECOND, '19700101', @ToDate )
; WITH ConvertedTimes
AS
(
SELECT sl_no,
DATEADD( SECOND, Initial_date, '19700101' ) AS Initial_date,
DATEADD( SECOND, Log_Time, '19700101' ) AS Log_Time, [Description]
FROM TEST
-- Filter the records by the selected period
WHERE Initial_date >= @FromDate AND Initial_date 0 THEN Log_Time ELSE NULL END ) AS Log_Time
FROM ConvertedTimes
)
SELECT sl_no, Initial_date, Log_Time, [Description]
FROM TestCTE
WHERE RowNumber = 1
--Ramesh
May 8, 2009 at 9:29 am
Ramesh 🙁 . It gives teh following error :
Arithmetic overflow error converting expression to data type datetime.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply