Sql Server - Combine multiple queries into one

Question

I have two tables Customer and Contacts.

Companies table contains information about customers and when an email is sent to the customers a contact is created in Contacts table containing information about which type of email is sent and at what time.

Here is an example:

Companies:                               Contacts:
------------------------              ----------------------------------------------    
  ID  | Action                           ID   |  CompanyID  | CreatedON  | EmailType
----- | ----------------------          ----- |  ---------- | ---------  | ---------
 Cus1 | 07.03.2014 Registered            Con1 |   Cus1      | 07.03.2014 | NewsLetter
 Cus2 | 08.03.2014 UnSubscribe           Con2 |   Cus2      | 07.03.2014 | NewsLetter
 Cus3 | 10.03.2014 Order                 Con3 |   Cus3      | 10.03.2014 | NewsLetter                  
 Cus4 | 10.03.2014 Registered            Con4 |   Cus4      | 10.03.2014 | NewsLetter   
 Cus5 | NULL                             Con5 |   Cus5      | 14.03.2014 | NewsLetter
 Cus6 | 15.03.2014 UnSubscribe           Con6 |   Cus6      | 14.03.2014 | NewsLetter   
 Cus7 | NULL                             Con7 |   Cus7      | 17.03.2014 | NewsLetter        
 Cus8 | NULL                             Con8 |   Cus8      | 17.03.2014 | NewsLetter
 Cus9 | NULL                             Con9 |   Cus9      | 17.03.2014 | NewsLetter

I am looking for a query so that I can see on a particular date how many emails were send and how many customers Registed, UnSubscribed or booked an Order.

e.g.When I run following query I can get how many emails were sent in march according to date:

Select Left(convert(date, CreatedON, 105),12) AS DATE_Sent, 
count ( CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, CreatedON))) ) as Alle 
From Contacts 
Where Contacts.Comment Like 'Newsletter' and  
        ( CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, CreatedON))) >= '01.03.2014' and 
CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, CreatedON))) <= '30.03.2014')
Group by Left(convert(date, CreatedON, 105),12)
Order by DATE_Sent

I can get How many users registered in March with this query:

SELECT Count(Companies.ID) as NumOfRegistered 
FROM Companies, Contacts 
Where Companies.ID = Contacts.CompanyID and Contacts.Comment Like 'Newsletter' And
      ( Companies.Action Like '%Registered%') And
      (Right(Companies.Action, 10) Like '%03.2014'
Group By Right(Companies.Action, 10)

In the same why I can get the users who UnSubscried or Ordered in March.

I am getting individual results, I don't know how I can combine these small queries so that I can see all the results together date wise. Any help will be really appreciated. I am expecting the query to give following results:

    Date     | Alle |  Registered |   Unsubscribe |  Order |
  ----------   ----   ----------      ----------     -----
  07.03.2014 |  2   |     1       |    NULL       |   NULL
  10.03.2014 |  2   |     1       |    NULL       |    1    
  14.03.2014 |  2   |     NULL    |    NULL       |   NULL
  17.03.2014 |  3   |     NULL    |    NULL       |   NULL

Answer 1

try this

Select DATE_Sent,Alle,NumOfRegistered 
from (
(Select CompanyID as id, Left(convert(date, CreatedON, 105),12) AS DATE_Sent, count ( CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, CreatedON))) ) as Alle 
From Contacts 
Where Contacts.Comment Like 'Newsletter' and  
        ( CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, CreatedON))) >= '01.03.2014' and CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, CreatedON))) <= '30.03.2014')
Group by Left(convert(date, CreatedON, 105),12),CompanyID
Order by DATE_Sent) As query1

Left outer Join

(SELECT Companies.ID as id1,Count(Companies.ID) as NumOfRegistered 
FROM Companies, Contacts 
Where Companies.ID = Contacts.CompanyID and Contacts.Comment Like 'Newsletter' And
      ( Companies.Action Like '%Registered%') And
      (Right(Companies.Action, 10) Like '%03.2014'
Group By Right(Companies.Action, 10),Companies.ID) as query2
on query1.id=query2.id1)

EDIT-----------------------------------------------------------------------------------

From Gordon reply.. changing the query to avoid Null and replacing with 0

select thedate, 
Case when Alle=NULL then 0 else sum(Alle) as col1, 
Case when NumOfRegistered=NULL then 0 else sum(NumOfRegistered) as col2, 
Case when NumOfUnsubscribed=NULL then 0 else sum(NumOfUnsubscribed) as col3, 
Case when NumOfOrdered=NULL then 0 else sum(NumOfOrdered) as col4
from ((SELECT convert(date, left(co.Action 10), 104) as thedate, NULL as Alle
              SUM(case when co.Action Like '%Registered%' then 1 else 0 end) as NumOfRegistered,
              SUM(case when co.Action Like '%Unsubscribed%' then 1 else 0 end) as NumOfUnsubscribed,
              SUM(case when co.Action Like '%Ordered%' then 1 else 0 end) as NumOfOrdered, 
       FROM Companies co
       GROUP BY convert(date, left(co.Action 10), 104)
      ) union all
      (select convert(date, CreatedON, 105) as DATE_Sent, count(*) as Alle, NULL, NULL, NULL
       from Contacts c
       where c.Comment Like 'Newsletter' and  
             substring(c.CreatedOn, 4, 7) = '03.2014'
       group by convert(date, CreatedON, 105) 
      )
     ) t
group by thedate
order by thedate;

Answer 2

Getting the numbers from the companies table would use conditional aggregation:

SELECT convert(date, left(co.Action 10), 104) as date
       SUM(case when co.Action Like '%Registered%' then 1 else 0 end) as NumOfRegistered,
       SUM(case when co.Action Like '%Unsubscribed%' then 1 else 0 end) as NumOfUnsubscribed,
       SUM(case when co.Action Like '%Ordered%' then 1 else 0 end) as NumOfOrdered, 
FROM Companies co
GROUP BY convert(date, left(co.Action 10), 104);

You an get the numbers from the contacts tables as:

select convert(date, CreatedON, 105) as DATE_Sent, count(*) as Alle 
from Contacts c
where c.Comment Like 'Newsletter' and  
      substring(c.CreatedOn, 4, 7) = '03.2014'
group by convert(date, CreatedON, 105) ;

You can combine these using union all and aggregation:

select thedate, sum(Alle), sum(NumOfRegistered), sum(NumOfUnsubscribed), sum(NumOfOrdered)
from ((SELECT convert(date, left(co.Action 10), 104) as thedate, NULL as Alle
              SUM(case when co.Action Like '%Registered%' then 1 else 0 end) as NumOfRegistered,
              SUM(case when co.Action Like '%Unsubscribed%' then 1 else 0 end) as NumOfUnsubscribed,
              SUM(case when co.Action Like '%Ordered%' then 1 else 0 end) as NumOfOrdered, 
       FROM Companies co
       GROUP BY convert(date, left(co.Action 10), 104)
      ) union all
      (select convert(date, CreatedON, 105) as DATE_Sent, count(*) as Alle, NULL, NULL, NULL
       from Contacts c
       where c.Comment Like 'Newsletter' and  
             substring(c.CreatedOn, 4, 7) = '03.2014'
       group by convert(date, CreatedON, 105) 
      )
     ) t
group by thedate
order by thedate;

Some lessons:

1. Fix you data model so dates are stored as dates and not strings.
2. Use correct conversions. According to the data in your question, the date conversion format is 104 (see the documentation).
3. Learn to use proper explicit join syntax, where the condition goes in the on clause rather than the where clause.
4. Learn to use conditional aggregation.