SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help with SQL query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pkovarik
Starting Member

Czech Republic
6 Posts

Posted - 01/26/2014 :  03:05:09  Show Profile  Reply with Quote
I have a table T with columns a1, a2, ..., a9, date.
At one point, I get the new values ??X1, X2, ..., X9, NewDate. I need to perform a SQL query, which determines whether the table contains a record that matches the month and year of the "date" with the month and year "NewDate".
- If such a record does not exist in the table then inserts a new row INSERT INTO T (a1, a2, ..., a5, date) VALUES (X1, X2, ..., X5, NewDate), and overwrites the value of an items a6, ..., a9 with the items a2, ... ,a5 of such row in the table T, which has the highest "date" (ie values of a2, ... ,a5 of the result of the query SELECT TOP 1 ... ORDER BY date DESC).
- If such a record exists, then overwrites values of items a2, .., a5 and date with values X2, ..., X5 and NewDate.
Does anyone know how to construct such a SQL query? Thank you.

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/27/2014 :  07:09:09  Show Profile  Reply with Quote
something like this

IF NOT EXISTS (SELECT 1 FROM T WHERE MONTH([date]) = MONTH(@NewDate) AND YEAR([date] = YEAR(@NewDate))
INSERT T (a1,a2,..,a5,a6,a7,a8,a9,[date])
SELECT TOP 1 @X1,@X2,...@X5,a2,a3,a4,a5,@NewDate
FROM T
ORDER BY [date] DESC
ELSE
UPDATE t
SET a2=@X2,
a3=@X3,
a4=@X4,
a5=@X5,
[date]=@NewDate
FROM T t
WHERE DATEDIFF(mm,0,[date]) = DATEDIFF(mm,0,@NewDate)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

pkovarik
Starting Member

Czech Republic
6 Posts

Posted - 01/28/2014 :  17:38:37  Show Profile  Reply with Quote
Thank you, it's perfect, but ... if T is empty it doesn't INSERT into T because SELECT TOP 1 @X1,@X2,...@X5,a2,a3,a4,a5,@NewDate
FROM T is empty. Could you rewrite query to match this situation? (In fact, it is not the case of empty table, the example is simplified - in fact, SELECT TOP 1 is completed with a WHERE condition, so I'm talking about when no record meets the WHERE criteria.)
Thank you.
Go to Top of Page

pkovarik
Starting Member

Czech Republic
6 Posts

Posted - 01/28/2014 :  18:17:13  Show Profile  Reply with Quote
I see only one solution - to append second query

IF NOT EXISTS (SELECT 1 FROM T WHERE MONTH([date]) = MONTH(@NewDate) AND YEAR([date] = YEAR(@NewDate))
INSERT INTO T (a1,a2,..,a5,a6,a7,a8,a9,[date])
VALUES (@X1,@X2,...@X5,0,0,0,0,@NewDate);

But is there any way how to do it in one query?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/29/2014 :  04:12:26  Show Profile  Reply with Quote
this is another way

DECLARE a2 int,@a3 int,@a4 int,@a5 int

SELECT TOP 1 @a2=a2,@a3=a3,@a4=a4,@a5=a5
FROM T
ORDER BY [date] DESC


IF NOT EXISTS (SELECT 1 FROM T WHERE MONTH([date]) = MONTH(@NewDate) AND YEAR([date] = YEAR(@NewDate))
INSERT T (a1,a2,..,a5,a6,a7,a8,a9,[date])
SELECT @X1,@X2,...@X5,COALESCE(@a2,0),COALESCE(@a3,0),COALESCE(@a4,0),COALESCE(@a5,0),@NewDate
ELSE
UPDATE t
SET a2=@X2,
a3=@X3,
a4=@X4,
a5=@X5,
[date]=@NewDate
FROM T t
WHERE DATEDIFF(mm,0,[date]) = DATEDIFF(mm,0,@NewDate)




------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000