How to join two table with some specific condition using Microsoft SQL server 2008 ?

Question

1.00/5 (2 votes)

See more:

I am using Microsfot SQL SERVER 2008

i have two table..

customer table and customer_trasaction table

customer table has a primary key customer_id

customer_transaction table has a foreign key customer_id.

all the transaction done by customer , added in the customer transaction table.

customer table data

customer_id	Customer_Name	Address		        contact
32552		Mahesh pattn    New delhi, india	9090909090
32553		Raj kumar 	New delhi, india	9923992939

customer transaction table structure

Customer_Id	Ticket		Trasactionn_Name	Transaction_date	
1	32552		6140332552	ORDER CREATED		2016-03-14 18:04:57.027	
45	32552		6140332552	PART NOT AVAILABLE	2016-03-15 09:37:53.217	
1544	32552		6140332552	IN-TRANSIT TO FSL		2016-03-23 00:19:59.613	
2045	32552		6140332552	IN-TRANSIT TO CUSTOMER	2016-03-28 12:15:01.743	
3288	32552		6140332552	IN-TRANSIT TO CUSTOMER	2016-03-29 11:23:56.277

now my query is..

SQL

select cust.customer_id, cust.CustomerName , cust.TicketNo, trans.Transaction_name, trans.transaction_date
from mstCustomer cust inner join Transaction_details trans
where cust.Customer_id=trans.customer_id

i want to show the customer details in report with transaction info..
now in the transaction table, the trasaction may be repeatative.. but we have to find the current transaction and the previous transaction with date..

and the condition is current and previous transaction should not be same..

now i want to show the output like this..

Customer_id	Customer_Name	Current_Trasaction		Current_Transaction_date	Previous_Transaction	Previous_Transaction_Date
32552		Mahesh pattnayak	IN-TRANSIT TO CUSTOMER	2016-03-29 11:23:56.277	IN-TRANSIT TO FSL		2016-03-23 00:19:59.613

How to get this output ??

thanks in advance..

Posted 1-Aug-16 6:07am

Mahesh Pattnayak

Updated 1-Aug-16 15:14pm

Maciej Los

v3

Add a Solution

Comments

Richard Deeming 1-Aug-16 13:08pm

No, it's not urgent for the unpaid volunteers who answer questions here. Trying to add a sense of urgency to your question is very rude, and is likely to discourage people from answering.

You also haven't told us which DBMS you are using.

Click the green "Improve question" link at the bottom of your question. Remove the "it's urgent" line. Remove the "BROKEN CAPS-LOCK KEY" text. And add the name and version of the DBMS you are using.

Mahesh Pattnayak 1-Aug-16 13:56pm

sorry sir..

Mahesh Pattnayak 1-Aug-16 13:56pm

i am totally confused what to do for this query..

RossMW 1-Aug-16 19:03pm

It is not clear as to what defines the current and previous transactions. Is it just the top 2 ordered by transactiondate desc?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RossMW · Answer 1 · 2016-08-01T14:03:00

Its a bit complicated but the general principle is below. Note: This code is a sample only as I do not have your database to test against and it is missing additional information you need.

First off it is easier to create a view below to show how to get the data into different columns. it will need to be changed to include additional data and filters as needed.

eg

SQL

SELECT top 2 
case when row_number() over(order by Transactiondate) = 1 then Transaction else '' end as [Current],  
case when row_number() over(order by Transactiondate) = 2 then Transaction else '' end as [Previous],  
row_number() over(order by eventdate) as row
FROM customer_transaction

Then combine the text fields (one will be '' and one will have the data) into a single row.

eg

SQL

SELECT STUFF((SELECT ',' + [Current] FROM View1 FOR XML PATH('')) ,1,1,'') AS [Current], STUFF((SELECT ',' + [Previous] FROM View1 FOR XML PATH('')) ,1,1,'') AS [Previous]

Once you have sorted out these two they can be combined to create one large statement.

A good article on the second part is

Converting row values in a table to a single concatenated string - SQLMatters[^]

ProgrammingEnthusiast · Answer 2 · 2016-08-01T15:14:00

Solution 2

what do you mean with the current transaction? is it the transaction for today ? and previous transaction is all transactions from the start until yesterday?

Posted 1-Aug-16 15:14pm

ProgrammingEnthusiast

Comments

Richard Deeming 2-Aug-16 8:50am

Don't post comments as solutions. Click the "Have a Question or Comment?" button under the question and post a comment instead.