Help with SQL query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with SQL query

Author

Topic

pkovarik
Starting Member

6 Posts

Posted - 2014-01-26 : 03:05:09

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

52326 Posts

Posted - 2014-01-27 : 07:09:09

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

pkovarik
Starting Member

6 Posts

Posted - 2014-01-28 : 17:38:37

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.

pkovarik
Starting Member

6 Posts

Posted - 2014-01-28 : 18:17:13

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?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-29 : 04:12:26

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources