none
Combining 2 select statements with different WHERE conditions

    Question

  • hello you all,

    I guess that there are alot of questions about joining select statements but I didn't find the one that squites me...

    I have the following 2 select statement with 2 different WHERE conditions:

    1. 

    select Name,dbinfodate,FORMAT(AVG(convert(float,DiskPercentFree)),'N2') as "Disk % Free last 2 days"
    	from tblDBColInfoDate
    where DBInfoDate=CONVERT(varchar(10), GETDATE() -2, 101) group by name,DBInfoDate

    2.

    select Name,dbinfodate,FORMAT(AVG(convert(float,DiskPercentFree)),'N2') as "Disk % Free last 1 day"
    	from tblDBColInfoDate
    where DBInfoDate=CONVERT(varchar(10), GETDATE() -1, 101) group by name,DBInfoDate

    how do I combine them together that I will have one table with 4 columns:

    name,DBInfoDate,[Disk % Free last 2 days],[Disk % Free last 1 day]

    Regards,

    Sunday, November 17, 2013 3:10 PM

Answers

  • try,

    select A.Name,A.dbinfodate,A.[Disk % Free last 2 days],B.[Disk % Free last 1 day] from (
    select Name,dbinfodate,FORMAT(AVG(convert(float,DiskPercentFree)),'N2') as "Disk % Free last 2 days"
    	from tblDBColInfoDate
    where DBInfoDate=CONVERT(varchar(10), GETDATE() -2, 101) group by name,DBInfoDate) A
    LEFT JOIN (
    select Name,dbinfodate,FORMAT(AVG(convert(float,DiskPercentFree)),'N2') as "Disk % Free last 1 day"
    	from tblDBColInfoDate
    where DBInfoDate=CONVERT(varchar(10), GETDATE() -1, 101) group by name,DBInfoDate)B
    ON A.Name=B.Name and A.dbinfodate=B.dbinfodate


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Proposed as answer by RSingh() Monday, November 18, 2013 6:56 AM
    • Marked as answer by ITForums Monday, November 18, 2013 7:19 AM
    Sunday, November 17, 2013 5:47 PM

All replies

  • I don't has the table so I cannot test it

    but I've got something like that

    select Name,dbinfodate,FORMAT(AVG(convert(float,DiskPercentFree)),'N2') as "Disk % Free last 2 days",
    zzz.name,zzz.dbinfodate,zzz.fld as as "Disk % Free last 1 day"
    	from tblDBColInfoDate
    	outer APPLY 
    	(
    	select Name,dbinfodate,FORMAT(AVG(convert(float,DiskPercentFree)),'N2') as fld
    	from tblDBColInfoDate
    where DBInfoDate=CONVERT(varchar(10), GETDATE() -1, 101) group by name,DBInfoDate
    	
    	) as zzz
    where DBInfoDate=CONVERT(varchar(10), GETDATE() -2, 101) group by name,DBInfoDate

    Sunday, November 17, 2013 3:28 PM
  • Hi,

    10x for the reply.

    unfortunately it won't work:

    Msg 209, Level 16, State 1, Line 11
    Ambiguous column name 'DBInfoDate'.
    Msg 209, Level 16, State 1, Line 11
    Ambiguous column name 'name'.
    Msg 209, Level 16, State 1, Line 11
    Ambiguous column name 'DBInfoDate'.
    Msg 209, Level 16, State 1, Line 1
    Ambiguous column name 'Name'.
    Msg 209, Level 16, State 1, Line 1
    Ambiguous column name 'dbinfodate'.

    Sunday, November 17, 2013 4:28 PM
  • You need to put aliases for column names to identify from which table you want them to be included.
    Sunday, November 17, 2013 5:36 PM
  • try,

    select A.Name,A.dbinfodate,A.[Disk % Free last 2 days],B.[Disk % Free last 1 day] from (
    select Name,dbinfodate,FORMAT(AVG(convert(float,DiskPercentFree)),'N2') as "Disk % Free last 2 days"
    	from tblDBColInfoDate
    where DBInfoDate=CONVERT(varchar(10), GETDATE() -2, 101) group by name,DBInfoDate) A
    LEFT JOIN (
    select Name,dbinfodate,FORMAT(AVG(convert(float,DiskPercentFree)),'N2') as "Disk % Free last 1 day"
    	from tblDBColInfoDate
    where DBInfoDate=CONVERT(varchar(10), GETDATE() -1, 101) group by name,DBInfoDate)B
    ON A.Name=B.Name and A.dbinfodate=B.dbinfodate


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Proposed as answer by RSingh() Monday, November 18, 2013 6:56 AM
    • Marked as answer by ITForums Monday, November 18, 2013 7:19 AM
    Sunday, November 17, 2013 5:47 PM
  • just like visakh16 say add alias

    select a.Name,a.dbinfodate,FORMAT(AVG(convert(float,a.DiskPercentFree)),'N2') as "Disk % Free last 2 days",
    
    zzz.name,zzz.dbinfodate,zzz.fld as as "Disk % Free last 1 day"
    	from tblDBColInfoDate a
    	outer APPLY 
    	(
    	select b.Name,b.dbinfodate,FORMAT(AVG(convert(float,b.DiskPercentFree)),'N2') as fld
    	from tblDBColInfoDate b
    where b.DBInfoDate=CONVERT(varchar(10), GETDATE() -1, 101) group by b.name,b.DBInfoDate
    	
    	) as zzz
    
    where a.DBInfoDate=CONVERT(varchar(10), GETDATE() -2, 101) 
    group by a.name,a.DBInfoDate

    Monday, November 18, 2013 6:23 AM
  • Thank you Sarat Babu, your solution helped me...

    can you please tell me how can I add another 2/3/4 etc columns that will show me different info of days?

    for example, I want another 3 columns that each of them will show me info of last 7 days, 3 months and 6 months...

    10x ahead

    Monday, November 18, 2013 11:24 AM
  • OK,

    Found the answer...

    select t1.Name,t1.dbinfodate,t1.[Disk % Free last 1 day],t2.[Disk % Free last 2 days],t3.[Disk % Free last 3 days],t4.[Disk % Free last 4 days]
    from
    (
    	select Name,dbinfodate,FORMAT(AVG(convert(float,DiskPercentFree)),'N2') as "Disk % Free last 1 day"
    		from tblDBColInfoDate
    	where DBInfoDate=CONVERT(varchar(10), GETDATE() -1, 101) group by name,DBInfoDate
    ) t1
    	
    LEFT JOIN
    (
    	select Name,dbinfodate,FORMAT(AVG(convert(float,DiskPercentFree)),'N2') as "Disk % Free last 2 days"
    		from tblDBColInfoDate
    	where DBInfoDate=CONVERT(varchar(10), GETDATE() -2, 101) group by name,DBInfoDate
    ) t2
    ON t1.Name=t2.Name
    
    LEFT JOIN
    (
    	select Name,dbinfodate,FORMAT(AVG(convert(float,DiskPercentFree)),'N2') as "Disk % Free last 3 days"
    		from tblDBColInfoDate
    	where DBInfoDate=CONVERT(varchar(10), GETDATE() -3, 101) group by name,DBInfoDate
    ) t3
    ON t1.Name=t3.Name
    LEFT JOIN
    (
    	select Name,dbinfodate,FORMAT(AVG(convert(float,DiskPercentFree)),'N2') as "Disk % Free last 4 days"
    		from tblDBColInfoDate
    	where DBInfoDate=CONVERT(varchar(10), GETDATE() -4, 101) group by name,DBInfoDate
    ) t4

    and this way to each and every table(column) I would like to add.
    thank you all...

    Monday, November 18, 2013 12:50 PM