How can i merge the query? RSS

• • Reply
  

  SudhaRubini

  Member

  391 Points

  383 Posts

  How can i merge the query?

  Aug 18, 2012 06:38 AM|LINK

  Hi evey one,

  i have a  two separate query. i want to merge those two query. i post my query below

  select datest,datediff(day,getdate(),datest) a from datetb
  where datediff(day,datest,getdate()) <=0 AND (NOT dateend is null )
  ORDER BY A

  select dateend,datediff(day,getdate(),dateend) a from datetb
  where datediff(day,dateend,getdate()) > 0 AND (NOT datest is null )
  ORDER BY A DESC

  after merge the those query i want a result like that

  2012-08-18 00:00:00.000

  2012-08-30 00:00:00.000

  2012-09-25 00:00:00.000

  2012-11-13 00:00:00.000

  2012-08-17 00:00:00.000

   how can i do that please anu one help me.......................

• • Reply
  

  spapim

  Contributor

  2444 Points

  367 Posts

  Re: How can i merge the query?

  Aug 18, 2012 06:56 AM|LINK

  Hi,

  Try:

  select
      datest,
      datediff(day,getdate(),datest) a,
      datediff(day,getdate(),datest) b,
      1 as seq
  from datetb
  where datediff(day,datest,getdate()) <= 0 AND (NOT dateend is null )
  
  union all
  
  select
      dateend,
      datediff(day,getdate(),dateend) a,
      (datediff(day,getdate(),dateend) * -1) b,
      2 as seq
  from datetb
  where datediff(day,dateend,getdate()) > 0 AND (NOT datest is null )
  
  ORDER BY seq, b

  
  Hope this helps. 

  www.imoveisemexposicao.com.br/imoveis-venda-são_paulo-residencial
  www.imobiliariasemsuzano.com.br
• • Reply
  

  VSejpal

  Member

  190 Points

  49 Posts

  Re: How can i merge the query?

  Aug 18, 2012 07:00 AM|LINK

  Try doing a union like this..

  select * from
  (
  select datest,datediff(day,getdate(),datest) a from datetb
  where datediff(day,datest,getdate()) <=0 AND (NOT dateend is null )
  ORDER BY A
  ) FirstResult
  UNION
  select * from
  (
  select dateend,datediff(day,getdate(),dateend) a from datetb
  where datediff(day,dateend,getdate()) > 0 AND (NOT datest is null )
  ORDER BY A DESC
  ) SecondResult

  
  
  

  Please "Mark as Answer" if this post answered your question.
  -Vivek Sejpal
  Coder at Heart | MCTS: .NET Framework 4, Service Communication Application, Data Access | MCTS: .NET Framework 3.5, ASP.NET Applications
• • Reply
  

  SudhaRubini

  Member

  391 Points

  383 Posts

  Re: How can i merge the query?

  Aug 18, 2012 07:09 AM|LINK

  Sorry i getting error for below

  The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

• • Reply
  

  SudhaRubini

  Member

  391 Points

  383 Posts

  Re: How can i merge the query?

  Aug 18, 2012 07:19 AM|LINK

  Hi 

  Iam using sql 2008 r2. now i got  following error

  Conversion failed when converting date and/or time from character string.

  both field are varchar(max) datatype. how can i do that?

  please help me

• • Reply
  

  VSejpal

  Member

  190 Points

  49 Posts

  Re: How can i merge the query?

  Aug 18, 2012 09:24 AM|LINK

  Figured a wokaround .. "Table Variable" :)

  1) Declare a table variable.

  2) Insert the results of your individual queries into that variable

  3) Select * from the table variable

  Full Code..

  declare @tbl as table
  (
  DateCol date,
  Diff int
  )
  insert into @tbl
  select datest,datediff(day,getdate(),datest) a from datetb
  where datediff(day,datest,getdate()) <=0 AND (NOT dateend is null )
  ORDER BY A
  
  insert into @tbl
  select dateend,datediff(day,getdate(),dateend) a from datetb
  where datediff(day,dateend,getdate()) > 0 AND (NOT datest is null )
  ORDER BY A DESC
  
  select * from @tbl
  GO

  
  
  

  Please "Mark as Answer" if this post answered your question.
  -Vivek Sejpal
  Coder at Heart | MCTS: .NET Framework 4, Service Communication Application, Data Access | MCTS: .NET Framework 3.5, ASP.NET Applications
  • Edited by vsejpal on Aug 18, 2012 09:25 AM
  • Edited by vsejpal on Aug 18, 2012 09:27 AM
  • Marked as answer by Mark - MSFT on Aug 22, 2012 05:40 AM
• • Reply
  

  gaurabchatte...

  Participant

  1655 Points

  325 Posts

  Re: How can i merge the query?

  Aug 18, 2012 09:47 AM|LINK

  try the following

  Select datest as Date,datediff(day,getdate(),datest) a from datetb
  where datediff(day,datest,getdate()) <=0 AND (NOT dateend is null )
  ORDER BY A
  Union ALL
  select dateend as date,datediff(day,getdate(),dateend) a from datetb
  where datediff(day,dateend,getdate()) > 0 AND (NOT datest is null )
  ORDER BY A DESC

  
  
  

  Gaurab Chatterjee
  Microsoft Technologies
  
  • Marked as answer by Mark - MSFT on Aug 22, 2012 05:40 AM