Solved

SQL Query Syntax Difference

Posted on 2014-11-25
7
198 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
Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

 

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

688 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