Solved

SQL Query Syntax Difference

Posted on 2014-11-25
7
197 Views
Last Modified: 2014-11-26
Hello all,

I have the following.   I have a table called Customer that has the following data for example:

Customer
CustId        CustDate       CustOrdAmount      CustGrpAmount
1                 9/2/2014      1000.00                  900.00
1                 10/1/2014      800.00                  800.00
1                 11/2/2014      900.00                  100.00
2                  8/4/2014       990.00                  990.00
2                  9/5/2014       700.00                  800.00
I then have another temp table I will create that will have the following:

#TempCustomer
CustId         CustDate      CustOrdAmount      CustGrpAmount
1                 11/29/2014     800.00                     500.00
2                 10/30/2014     700.00                     800.00

What I need to do is check based on the CustId if the temp table CustOrdAmount delta is different OR the CustGrpAmount is different than the Max(CustDate) record in Customer for that CustId then Insert a new record into the Customer Table otherwise ignore the insert from the Temp table to the Customer table for that CustId.

So in this case above I would be comparing the 3rd record in Customer table CustId = 1 to the TempTable CustId = 1 and inserting a record into the Customer table.  For CustId = 2 I would be ignoring that insert they are the same both columns.
0
Comment
Question by:sbornstein2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40464349
This should returns the records that you need insert:
WITH Cust_CTE (CustId, CustDate) 
AS (
     SELECT CustId, MAX(CustDate)
     FROM Customer
)
SELECT c.CustId, c.CustDate
FROM Cust_CTE c
INNER JOIN #TempCustomer tc ON tc.CustId=c.CustId
WHERE tc.CustOrdAmount <> c.CustOrdAmount OR tc.CustGrpAmount=c.CustGrpAmount

Open in new window

0
 
LVL 34

Expert Comment

by:ste5an
ID: 40464350
Delta? What delta?
0
 
LVL 32

Expert Comment

by:awking00
ID: 40464975
If you're using sql server 2008 or later, you can use merge.
merge customer as c
using #tempCustomer as t
on c.custid = t.custid and c.custordamount = t.custordamount and c.custgrpamount = t.custgrpamount
when not matched then
insert values (t.custid, t.custdate, t.custordamount, t.custgrpamount);
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:sbornstein2
ID: 40465411
I need to look at every record in the #TempCustomer then compare it against the max(custdate) record in Customer where CustId = the same CustID.   So the records I would be comparing for the 1st temp table record is:

#TempCustomer
 CustId         CustDate      CustOrdAmount      CustGrpAmount
 1                 11/29/2014     800.00                     500.00

Then getting the Max cust date record in Customer for that same CustId
Customer
 CustId        CustDate       CustOrdAmount      CustGrpAmount
 1                 11/2/2014      900.00                  100.00

Then if CustOrdAmount is different OR CustGrpAmount is different I want to insert the #TempCustomer table record into the Customer table.   In this case they are different so my Customer table would now look like this:

Customer
 CustId        CustDate       CustOrdAmount      CustGrpAmount
 1                 9/2/2014      1000.00                  900.00
 1                 10/1/2014      800.00                  800.00
 1                 11/2/2014      900.00                  100.00
 1                 11/29/2014    800.00                  500.00
 2                  8/4/2014       990.00                  990.00
 2                  9/5/2014       700.00                  800.00

Then for the 2nd record in the temp table I would be comparing:
#TempCustomer
 CustId         CustDate      CustOrdAmount      CustGrpAmount
 2                 10/30/2014     700.00                     800.00

Customer
 CustId        CustDate       CustOrdAmount      CustGrpAmount
 2                  9/5/2014       700.00                  800.00

In this case they are the same so I would not insert anything into Customer.

Then last scenario if the Temp table record did not have a matching CustId record at all in the Customer table I would insert that into the Customer table.
0
 
LVL 13

Accepted Solution

by:
Koen Van Wielink earned 500 total points
ID: 40466235
This should do the trick:

With CTE
(	CustId
,	CustDate
,	CustOrdAmount
,	CustGrpAmount
)

as
(
Select		CustId
		,	CustDate
		,	CustOrdAmount
		,	CustGrpAmount
from	(select		ROW_NUMBER() over (partition by custID order by CustDate desc) as 'RowNum'
				,	CustId
				,	CustDate
				,	CustOrdAmount
				,	CustGrpAmount
		From	customer) as a
where	a.RowNum = 1
)

-- Insert records with different amount
Insert into customer
select		t.CustId
		,	t.CustDate
		,	t.CustOrdAmount
		,	t.CustGrpAmount
from	#Tempcustomer t
		inner join CTE
			on t.CustId = CTE.CustId
where	(CTE.CustId = t.CustId
and		(CTE.CustOrdAmount <> t.CustOrdAmount
or		CTE.CustGrpAmount <> t.CustGrpAmount))

-- Insert records without custId in customer table
Insert into customer
select		t.CustId
		,	t.CustDate
		,	t.CustOrdAmount
		,	t.CustGrpAmount
from	#Tempcustomer t
where	not exists
		(select 1
		from	customer c
		where	t.CustId = c.CustId)

Open in new window


The CTE is populated with the record of the customer table containing the maximum order date for each customer.
The first insert statement joins that CTE on the temp table and selects only the orders where the customer ID is the same, but either the Grpamount or OrdAmount is different, and inserts those into the customer table.
The second insert statement handles the records in the temp table for which no customer ID exists in the customer table.
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40466303
Did you test my script?
0
 

Author Closing Comment

by:sbornstein2
ID: 40466762
awesome thanks I ended up using the NOT EXISTS as well
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question