SQL Server select data from same column multiple times

Question

I have 2 tables with following kind of data and structure:

Companies:                                          Contacts:  

CompanyID | UserDefine4                             CompanyID  | Comment       
-------      --------                                ------    | ------
   1      |  Register 20.03.2014                      1        | Newsletter   
   2      |  Subscribe 21.03.2014                     2        | Newsletter   
   3      |  Register 01.04.2014                      3        | Newsletter
   4      |  Unsubscribe 02.04.2014                   4        | Newsletter
   5      |  Unsubscribe 03.04.2014                   5        | Newsletter
   6      |  Subscribe 05.04.2014                     6        | Newsletter

From these two tables I want to select UserDefined4 according to text(Register,Subscribe,Unsubscribe) in UserDefine4 groupby date part in this field.

I am running following queries to select records individually: But I don't know how I can join the below mentioned 3 queries.

Step 1:

Select Right(Companies.UserDefined4, 10) As DateSubscribe, Count(Companies.ID) As NumberofSubscribe
    From Companies, Contacts
    Where Companies.ID = Contacts.CompanyID And 
    (Contacts.Comment Like 'Newsletter' And Companies.UserDefined4 Like '%Subscribe%' And
      (Right(Companies.UserDefined4, 10) Like '%.03.2014' Or Right(Companies.UserDefined4, 10) Like '%.04.2014'))
    Group By Right(Companies.UserDefined4, 10)

Step 2:

Select Right(Companies.UserDefined4, 10) As DateRegister, Count(Companies.ID) As NumberofRegister
    From Companies, Contacts
    Where Companies.ID = Contacts.CompanyID And 
    (Contacts.Comment Like 'Newsletter' And Companies.UserDefined4 Like '%Register%' And
      (Right(Companies.UserDefined4, 10) Like '%.03.2014' Or Right(Companies.UserDefined4, 10) Like '%.04.2014'))
    Group By Right(Companies.UserDefined4, 10)

Step 3:

Select Right(Companies.UserDefined4, 10) As DateUnsubscribe, Count(Companies.ID) As NumberofUnsubscribe
    From Companies, Contacts
    Where Companies.ID = Contacts.CompanyID And 
    (Contacts.Comment Like 'Newsletter' And Companies.UserDefined4 Like '%Unsubscribe%' And
      (Right(Companies.UserDefined4, 10) Like '%.03.2014' Or Right(Companies.UserDefined4, 10) Like '%.04.2014'))
    Group By Right(Companies.UserDefined4, 10)

Answer 1

By separating the UserDefined4 column into two, then converting it into a number (1 or 0) we can perform a SUM on the end results for a by date count:

 SELECT SubscriptionDate,
        SUM(IsRegistration) AS NoOfRegistrations,
        SUM(IsSubscription) AS NoOfSubscriptions,
        SUM(IsUnsubscription) AS NoOfUnsubscriptions
   FROM (
        -- Separate UserDefined4 into SubscriptionType and SubscriptionDate
         SELECT CompanyId,
                CASE
                     WHEN SUBSTRING(UserDefined4, 1, LEN(UserDefined4) - 8) = 'Register' THEN 1
                     ELSE 0
                END AS IsRegistration,
                CASE
                     WHEN SUBSTRING(UserDefined4, 1, LEN(UserDefined4) - 9) = 'Subscribe' THEN 1
                     ELSE 0
                END AS IsSubscription,
                CASE
                     WHEN SUBSTRING(UserDefined4, 1, LEN(UserDefined4) - 11) = 'Unsubscribe' THEN 1
                     ELSE 0
                END AS IsUnsubscription,
                RIGHT(UserDefined4, 10) AS SubscriptionDate
           FROM Companies
         ) AS t
  GROUP BY SubscriptionDate;

Edit 14-Apr-2014: Corrected SUBSTRING lengths to match lengths of actual words

Answer 2

There are alot of possible way achieve your goal however still depend on your decision, how you want to design your output

Option 1

SELECT 
CASE WHEN SUBSTRING(C.UserDefined4,1,1) = 'R' THEN RIGHT(C.UserDefined4,10) 
ELSE ' ' END 'DateRegister',
CASE WHEN SUBSTRING(C.UserDefined4,1,1) = 'R' THEN COUNT(T.CompanyID) 
ELSE ' ' END 'NumberofSubscribe',
CASE WHEN SUBSTRING(C.UserDefined4,1,1) = 'U' THEN RIGHT(C.UserDefined4,10) 
ELSE ' ' END 'DateSubscribe',
CASE WHEN SUBSTRING(C.UserDefined4,1,1) = 'U' THEN COUNT(T.CompanyID) 
ELSE ' ' END 'NumberofSubscribe',
CASE WHEN SUBSTRING(C.UserDefined4,1,1) = 'S' THEN RIGHT(C.UserDefined4,10) 
ELSE ' ' END 'DateUnsubscribe',
CASE WHEN SUBSTRING(C.UserDefined4,1,1) = 'S' THEN COUNT(T.CompanyID) 
ELSE ' ' END 'NumberofSubscribe'
FROM COMPANIES C
INNER JOIN Contacts T ON T.CompanyID = C.ID
WHERE (T.Comment  = 'Newsletter' OR T.Comment LIKE '%Newsletter%')
AND (RIGHT(C.UserDefined4,10) LIKE '%.03.2014' 
OR RIGHT(C.UserDefined4,10) LIKE'%.04.2014')
GROUP BY  SUBSTRING(C.UserDefined4,1,1),RIGHT(C.UserDefined4,10)

Option 2

SELECT RIGHT(C.UserDefined4,10) 'Date',
CASE WHEN SUBSTRING(C.UserDefined4,1,1) = 'R' THEN 'Register'
WHEN SUBSTRING(C.UserDefined4,1,1) = 'S' THEN 'Subscribe'
WHEN SUBSTRING(C.UserDefined4,1,1) = 'U' THEN 'Unsubscribe' END 'Action',
COUNT(SUBSTRING(C.UserDefined4,1,1)) 'NumberOfSubscriber'
FROM COMPANIES C
INNER JOIN Contacts T ON T.CompanyID = C.ID
WHERE (T.Comment  = 'Newsletter' OR T.Comment LIKE '%Newsletter%')
AND (RIGHT(C.UserDefined4,10) LIKE '%.03.2014' 
OR RIGHT(C.UserDefined4,10) LIKE'%.04.2014')
GROUP BY  SUBSTRING(C.UserDefined4,1,1),RIGHT(C.UserDefined4,10)

Answer 3

Using PIVOT could help here, assuming that you are OK with output as the number of registrations, subscriptions and unsubscriptions for every date in your source table. Query would be something like this:

SELECT Right(UserDefined4, 10) as 'Date', 
[R] as 'Number of Register', 
[S] as 'Number of Subscribe', 
[U] as 'Number of Unsubscribe'
FROM
(
 SELECT 
 CompanyID, 
 userdefined4, 
 LEFT(userdefined4, 1) typ 
 FROM companies join 
 contacts on companies.companyid = contacts.companyid
 WHERE 
 (Right(Companies.UserDefined4, 10) Like '%.03.2014' 
  Or Right(Companies.UserDefined4, 10) Like '%.04.2014'
 )
 AND Contacts.Comment Like 'Newsletter') AS SourceTable
PIVOT
(
 COUNT(CompanyID)
 FOR typ IN ([R], [S], [U])
) AS PivotTable;