Geek Answers Handbook

Sql Server - combine multiple queries into one

I have two tables Customerand Contacts.

Table

Companiescontains information about customers and when an email is sent to customers, a contact is created in a table Contactscontaining information about what 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’m looking for a request so that on a certain day I can see how many letters were sent and how many customers are registered, not signed or ordered.

For example, when I run the following query, I can get how many letters were sent in March by 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 request:

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)

The same is why I can get users that are UnSubscried or Ordered in March.

, , , , . . , :

    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
+4
2

companies :

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);

:

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) ;

union all 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;

:

  • , , .
  • . , 104 (. ).
  • join, on, where.
  • .
+2

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)

---------------------------------------------- -------------------------------------

.. , Null 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;
+2

More articles:


All Articles