SQL Server 2008 join on condition

Question

I have table where I have some sent sms'es by operaters and some by users, in both cases in column PhoneNumber is users phone

{PhoneNumber Date Type SMS Response}

Type is 1 or 2, when value is 1, message is sent by operater, when value is 2, message is sent by user. In first case, there is response filled, in second, there is SMS.

I need to show messages sent by operaters and replies by users.

If this is example (sorted by time):

user message 1
user message 2
operators message 1
operators message 2
operators message 3
users message 3
users message 4

it should be sorted like this:

operators message 1   null
operators message 2   null
operators message 3  users message 3 users message 4

(I "selected just" row with messages)

so this is my idea, to separate users and operaters message in two temp tables, and left join them on PhoneNumber = PhoneNumber, but I dont know to do those time conditions.

Basically any solutions that will list operaters messages and answer (that should be determined by times, if three operators messages in row and then users message, its answerd on third, and for first two answer should be null, and users messages before first operators message are not important).

I did my best to explain, if you have questions, please ask, and thanks in advance for any ideas.

So all subsequent user messages are concatenated and put into one column in the same row with the operator message that directly precedes them. Is that right? If yes, should they be separated by some character(s) (`';'` or `CHAR(13)+CHAR(10)`, for instance)? — Andriy M, Nov 01 '11 at 14:51
That's right,and that's one solution, it's not necessarily that way, just in some usable way for report. So, in one row, or listed after operater messages in new row(s), that's not important, this was just my idea. — el ninho, Nov 01 '11 at 14:54

score 0 · Answer 1 · answered Nov 01 '11 at 14:54

0

Why don't you add a column to your table which can be used as a reference column like below example:

CREATE TABLE #temp
(
    SmsId           INT,
    PhoneNumber     VARCHAR(100),
    SMS             VARCHAR(200),
    IsUserResponse  BIT,
    DateReceived    DATETIME,
    LinkTo          INT 
)
INSERT INTO #temp VALUES(1,'0300','sms from operator', 0, GETDATE(), NULL)
INSERT INTO #temp VALUES(2,'0300','sms from operator', 0, GETDATE(), NULL)
INSERT INTO #temp VALUES(3,'0301','sms from operator', 0, GETDATE(), NULL)
INSERT INTO #temp VALUES(4,'0300','sms from Customerr', 1, GETDATE(), 2)
INSERT INTO #temp VALUES(3,'0301','sms from Customer', 1, GETDATE(), 3)

SELECT  * INTO #CustomerSms FROM    #temp WHERE IsUserResponse = 0
SELECT  * INTO #OperatorSms FROM    #temp WHERE IsUserResponse = 1


SELECT  * 
FROM    #CustomerSms c
        FULL JOIN #OperatorSms o ON c.SmsId = o.LinkTo

DROP TABLE #temp
DROP TABLE #CustomerSms
DROP TABLE #OperatorSms

I hope its clear.

answered Nov 01 '11 at 14:54

Yaqub Ahmad

27,569
23
102
149

This looks hardcoded, I need idea for large (unknown) number of rows. – el ninho Nov 01 '11 at 15:37
@el ninho This was just to show you that how you can achieve your desired functionality. What is your table structure exactly? – Yaqub Ahmad Nov 01 '11 at 16:00
I can't manually do this for 100.000 or more rows, that is the problem. – el ninho Nov 01 '11 at 16:03
Your table structure please? If you can provide your table structure then i can write an sql. Table structure; the columns of the table. – Yaqub Ahmad Nov 01 '11 at 16:05

score -1 · Accepted Answer · edited Jan 18 '21 at 12:30

This should work in t-SQL:

CREATE table #msg(id integer, type integer, msg nvarchar(max), ts datetime)

INSERT INTO #msg VALUES (1, 2, 'user msg 1',     '2011-11-01 16:00')
INSERT INTO #msg VALUES (2, 2, 'user msg 2',     '2011-11-01 16:01')
INSERT INTO #msg VALUES (3, 1, 'operator msg 1', '2011-11-01 16:02')
INSERT INTO #msg VALUES (4, 1, 'operator msg 2', '2011-11-01 16:03')
INSERT INTO #msg VALUES (5, 1, 'operator msg 3', '2011-11-01 16:04')
INSERT INTO #msg VALUES (6, 2, 'user msg 3',     '2011-11-01 16:05')
INSERT INTO #msg VALUES (7, 2, 'user msg 4',     '2011-11-01 16:06')

;WITH
   o AS (
  SELECT id, ts, msg
  FROM   #msg
  WHERE  type = 1
  )
  ,u AS (
  SELECT id, ts, msg, (SELECT TOP 1 id FROM o
                       WHERE  o.ts <= x.ts ORDER BY ts DESC) AS o_id
  FROM   #msg x
  WHERE  type = 2
  )
SELECT o.msg, (SELECT u.msg + ',' FROM u WHERE  u.o_id = o.id
               ORDER  BY u.ts, u.id FOR XML PATH('')) AS u_msg
FROM   o
ORDER  BY o.ts

See a demo at data.stackexchange.com

Major points:

Use CTE to separate user messages and assign the preceding operator message to each.
GROUP BY operator message in the final step.
Use FOR XML to substitute for the missing aggregate concatenation function.
This related answer was helful: Concatenate row values T-SQL

SQL Server 2008 join on condition

2 Answers2

Major points: