Database

SELECT t1.name, t3.countryname
FROM       [table 1] as t1
INNER JOIN [table 2] as t2 ON t2.id = t1.id
INNER JOIN [table 3] as t3 ON t3.countrycode = t2.countrycode

DECLARE @s NVARCHAR(4000);
SET @s = '(SVDB, N, Y; VGBY, Y, B; SCFV, Y, S; SRVD, Y, N; SRVD, Y, N; )';

/* START PROCESSING */;
SET @s = REPLACE(REPLACE(REPLACE( @s ,' ','') ,'(','') ,')','');

CREATE TABLE #S (pk INT IDENTITY(1,1), line NVARCHAR(100)); 
CREATE TABLE #T (col1 NVARCHAR(4), col2 NVARCHAR(1), col3 NVARCHAR(1)); 

DECLARE @p1 INT;
DECLARE @p2 INT;
SET @p1 = 1; 
SET @p2 = CHARINDEX(';' , @s); 

WHILE @p1 < @p2 
BEGIN 
	INSERT INTO #S (line) SELECT SUBSTRING(@s, @p1, @p2-@p1); 
	SET @s = SUBSTRING(@s, @p2+1, LEN(@s)); 
	SET @p1 = 1; 
	SET @p2 = CHARINDEX(';' , @s); 
END 

DECLARE @k INT;SET @k = 1; 
DECLARE @max INT; 
SELECT @max = MAX(pk) FROM #S; 


WHILE @k < @max 
BEGIN 
	SELECT @s = 
          'INSERT INTO #T (col1 , col2 , col3) SELECT  ''' 
          + REPLACE( line , ',', ''',''') + '''' FROM #S WHERE pk = @k; 
	EXEC(@s); 
	SET @k = @k + 1; 
END 

DROP TABLE #S; 

SELECT * FROM #T; 

/* ;
...... 
Do what ever you have to do with data in #T table 
..... 
*/ ;

DROP TABLE #T; 
/* END PROCESSING */

obarahmeh wrote:

First: Is the previous query have the correct functions to convert the Decimal values into date, If yes, where is the syntax error??

Ashfield wrote:

Your STUFF is wrong.

stuff(stuff(CAST(DayTransTime AS char(8)),5,0,':'),3,0,':')

should do it.

obarahmeh wrote:

I used your query, but it did not work

CREATE TABLE  #MyTable (   DayTransDate Decimal(8,0) ,    DayTransTime  Decimal(6,0) );  
INSERT INTO #MyTable (DayTransDate, DayTransTime) SELECT 20080709, 120844 ; 
INSERT INTO #MyTable (DayTransDate, DayTransTime) SELECT 20090709, 120844 ;   
INSERT INTO #MyTable (DayTransDate, DayTransTime) SELECT 20080710, 150844 ;
INSERT INTO #MyTable (DayTransDate, DayTransTime) SELECT 20080710, 200844 ;

SELECT *,DATEADD(minute, -30, getdate()) as comparedate  FROM #MyTable
WHERE convert(datetime, cast(DayTransDate AS char(8)) 
+ ' ' + stuff(stuff(CAST(DayTransTime AS char(8)),5,0,':'),3,0,':'))
> DATEADD(minute, -30, getdate());

CREATE TABLE  #MyTable ( 
  DayTransDate Decimal(8,0) ,  
  DayTransTime  Decimal(6,0) 
); 
 
INSERT INTO #MyTable (DayTransDate, DayTransTime) SELECT 20080709, 120844 ; 
INSERT INTO #MyTable (DayTransDate, DayTransTime) SELECT 20090709, 120844 ; 
 
 
SELECT *  
, DayTransDate * 1000000 + DayTransTime  
, convert(datetime, cast(DayTransDate AS char(8))  
  + ' '  
  + STUFF(STUFF(CAST(DayTransTime AS char(6)), 5, 0, ':'), 3, 0, ':'))  
, CONVERT(NVARCHAR, DATEADD(minute, -30, GETDATE()), 112) +  
  REPLACE(CONVERT(NVARCHAR, DATEADD(minute, -30, GETDATE()), 108) , ':', '')  
, REPLACE(REPLACE(REPLACE( 
  CONVERT(NVARCHAR(19), DATEADD(minute, -30, GETDATE()), 120)  
  , '-', '') , ':', '') , ' ', '')  
 
FROM #MyTable  
WHERE  
  DayTransDate * 1000000+DayTransTime  
  >  
  REPLACE(REPLACE(REPLACE( 
    CONVERT(NVARCHAR(19), DATEADD(minute, -30, GETDATE()), 120)  
    , '-', '') , ':', '') , ' ', '' 
  )  
; 
 
 
SELECT * FROM #MyTable 
WHERE  
  CONVERT( 
    NVARCHAR(19),  
    convert(datetime,  
      cast(DayTransDate AS char(8))  
      + ' '  
      + STUFF(STUFF(CAST(DayTransTime AS char(6)), 5, 0, ':'), 3, 0, ':') 
      )  
  , 120)  
  >   
  CONVERT(NVARCHAR(19), DATEADD(minute, -30, GETDATE()) , 120) 
; 

SELECT * FROM #MyTable  
WHERE  
   DayTransDate * 1000000 + DayTransTime  
   >  
   CONVERT(NVARCHAR,  
     DATEADD(minute, -30, GETDATE()), 112 
   )  
   +  
   REPLACE( 
     CONVERT(NVARCHAR, DATEADD(minute, -30, GETDATE()), 108)  
     , ':', '' 
   )  
; 
 
SELECT * FROM #MyTable 
WHERE  
  DayTransDate * 1000000 + DayTransTime  
  > 
  REPLACE(REPLACE(REPLACE( 
    CONVERT(NVARCHAR(19), DATEADD(minute, -30, GETDATE()), 120)  
    , '-', '') , ':', '') , ' ', '' 
  )  
; 
 
 
DROP TABLE #MyTable 

SELECT * FROM MyTable 
WHERE 
  CONVERT( 
    NVARCHAR(19) 
    , convert( 
       datetime, 
       cast( 
         DayTransDate AS char(8))  
         + ' '  
         + STUFF(STUFF(CAST(DayTransTime AS char(6)), 5, 0, ':'), 3, 0, ':')) ,  
       120 
    )  
  >   
  CONVERT(NVARCHAR(19), DATEADD(minute, -30, GETDATE()) , 120) 
; 
 
SELECT * FROM MyTable 
WHERE DayTransDate * 1000000 + DayTransTime  
> CONVERT( 
   NVARCHAR,  
    DATEADD(minute, -30, GETDATE()),  
    112 
  )  
  + REPLACE(CONVERT(NVARCHAR, DATEADD(minute, -30, GETDATE()), 108) , ':', '')  
;  

SELECT * FROM MyTable
WHERE 
  DayTransDate * 1000000 + DayTransTime 
  > 
  REPLACE(REPLACE(REPLACE(
      CONVERT(NVARCHAR(19), DATEADD(minute, -30, GETDATE()), 120) 
      , '-', '') , ':', '') , ' ', ''
  ) 
;

haneef wrote:

i know therotic about index for retrieve data fast.

how it work ?.