Solved

SQL Query Syntax Difference

Posted on 2014-11-25
7
180 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
7 Comments
 
LVL 45

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 32

Expert Comment

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

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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 12

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 45

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 Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now