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)

Kevin Hogg · Accepted Answer · 2014-04-14 08:29:52Z

2

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

edited Apr 14, 2014 at 8:29

answered Apr 9, 2014 at 12:45

Kevin Hogg

1,78125 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Kamran Over a year ago

I tried your query and it gives me an error, "An invalid length parameter passed to the LEFT or SUBSTRING function.". I tried to change the number in Substring funciton but still getting the same error

Clockwork-Muse Over a year ago

@Kami - That's because 'Register' (for example) only has 8 characters, making the total -2 for the length parameter for SUBSTRING (which must be positive). In the long run you'd be better off changing your table designs somewhat so you don't need to substring, but finding the first space may work here.

Kevin Hogg Over a year ago

I agree with Clockwork-Muse; the table designs should be changed rather than this approach, but at least this way you have something to work with.

shree.pat18 · Accepted Answer · 2014-04-09 12:26:39Z

1

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;

answered Apr 9, 2014 at 12:26

shree.pat18

21.8k3 gold badges45 silver badges65 bronze badges

2 Comments

Kamran Over a year ago

I tried your query but its returning me alot more rows then I am supposed to get. Its returning me 96 rows and I am supposed to get 38 rows in the result.

shree.pat18 Over a year ago

Hmm. Not really sure why without knowing more. Have you checked if my initial filters are the same as what you used in your original queries?

Low Chee Mun · Accepted Answer · 2014-04-09 13:23:01Z

0

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)

answered Apr 9, 2014 at 13:23

Low Chee Mun

6101 gold badge4 silver badges9 bronze badges

2 Comments

Kamran Over a year ago

Thanks, I tried your query in Option1, I am getting this error, "The data types ntext and varchar are to-operator in equal incompatible."

Low Chee Mun Over a year ago

hi brother, try to convert the all column above to varchar type then should be fine =)

Collectives™ on Stack Overflow

SQL Server select data from same column multiple times

3 Answers 3

3 Comments

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related