|
Hi,
I have a sql server table with fields EmployeeID, Date, TimeIN, TimeOut, Remarks.
Now there is a unique constraint on both EmployeeID and Date such that only one record exists for a particular Employee on a particular date.
Now the main application can insert and update records. I need a trigger to track changes such that on insert or update, the values are copied onto a data_log table. Here is the problem, I need the trigger to check if the row exists in data_log for that EmployeeID and Date, then update, else insert. So there should be only one record of each employee per date in the data_log table as well. I just want the most current change and need to overwrite previous log entry.
thanks
|
|
|
|
|
Just a thought... why are you bothering if all you are doing is copying the values into another table? An audit or log table should contain complete history, I've always thought and no unique keys from the parent table - duplicates are fine - create a compound index or a surrogate key if you need one. Prepared to be called out on this - just seems a bit pointless.
In the meantime you could check with:
if exists(select top 1 1 from [table] where [key] = [parameter])
begin
end
else
begin
end
or similar: adjust to suit.
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
|
|
|
|
|
I have to run extended operations on bulk records in the destination table.
I thought Delete if exists, then Insert would be a possible solution, but the trigger is working some times and failing other times, and when it does fail, it cancels out the transaction on the original table resulting in data loss.
|
|
|
|
|
I do not know which version of SQL server you have but if 2008 try MERGE in your trigger.
|
|
|
|
|
Hi,
I have two mysql tables with large amount of data.
Table_1 has fields PropID, FullStreetAddress, City, State, Zipcode, Remarks, Date which I want to pick. Table_2 has PropID,URL.
Now I want to pick top 60 rows from table_1 order by date desc. Each PropID has several URL records in Table_2. Out of those records, I need to pick only 4 for each PropID in table_1.
So in effect I would get a result set of 60 x 4 = 240 rows.
Selecting from table_1 and inner joining on PropID in table_2, I am getting a complete result set, but I need to limit that somehow to get 60 rows from table_1 and 4 rows for each of the 60 ids from table_2 in a single query.
thanks.
|
|
|
|
|
use
LIMIT 60 to get 60 rows from table_1
I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
I know that part. But I need to create a View of some sort with 60 x 4 = 240 rows, I cant use Limit for both tables in a single query now, can i?
|
|
|
|
|
Only the first part of the solution: how to get the 60 rows from table_1:
SELECT table_1.PropID, FullStreetAddress, City, State, Zipcode, Remarks, Date, table_2.URL
FROM table_1 LEFT JOIN table_2 ON table_1.PropID=table_2.PropID
WHERE table_1.PropID IN
(SELECT PropID
FROM table_1
WHERE <your selection criteria>
LIMIT 60)
That's still with all releated rows from table_2.
|
|
|
|
|
ALTER PROCEDURE [dbo].[insert_task_details11]
@taskname varchar(150),
@completiondate varchar(30)
AS
BEGIN
DECLARE @DataID int
select DataID=MAX(taskid) from tblTaskAssign;
insert into tblJobs (taskid,taskname,completiondate) values (DataID,@taskname,@completiondate)
END
I want to insert maximum taskid into tblJobs.
It gives Error The name "DataID" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
and if I use @DataId instedat od DataID, null value goes in taskid in tblJobs.
How to solve this problem
thanks
|
|
|
|
|
Uma Shankar Patel wrote: select @DataID=MAX(taskid) from tblTaskAssign;
insert into tblJobs (taskid,taskname,completiondate) values (@DataID,@taskname,@completiondate)
Should work assuming that there are TaskID's in tblTaskAssign.
|
|
|
|
|
values are inserted but in TaskId column Null value inserted in tblJobs table.
i.e.
DataId is not inserted or not fetched properly from tblTaskAssign to be inserted into tblJobs
|
|
|
|
|
try this
DECLARE @DataID int
set @DataID = (select isnull(max(taskid),0) from tblTaskAssign)
insert into tblJobs (taskid,taskname,completiondate) values (@DataID ,@taskname,@completiondate)
I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
|
hi friends
i created a database in sqlserver2008 throught management studio now i edited one of my table ie inserted a new column in it and want to save it. but it says that the changes cannot be saved so how to do it plz let me know.
K.Gayathri
|
|
|
|
|
What else does it say about it? Is it because you have the table open in another window?
|
|
|
|
|
|
Did you define the new column to not allow NULL ? If so, then you need to define a default value for that column.
|
|
|
|
|
Today I wanted a function (in SQL Server 2008) to return the lesser of two integers, but it didn't seem to exist, so I wrote one, then decided that it might be somewhat more useful sometime in the future if it wasn't coded for integers, so I changed it to use numeric instead:
CREATE FUNCTION dbo.LesserOf
(
@Op1 NUMERIC
,
@Op2 NUMERIC
)
RETURNS NUMERIC
AS
BEGIN
DECLARE @result NUMERIC
IF ( @Op1 < @Op2 )
SET @result = @Op1
ELSE
SET @result = @Op2
RETURN @result
END
Is there some odd limitation involved with numeric that I don't know about (I never use them)?
Is there a better way to implement this simple function (not a CASE)?
Is there a built-in LesserOf function?
|
|
|
|
|
Try this as an exercise....
DECLARE @N NUMERIC
SET @N = 12.345
PRINT @N
DECLARE @N2 NUMERIC(5,3)
SET @N2 = 12.345
PRINT @N2
DECLARE @N3 NUMERIC(4,3)
SET @N3 = 12.345
PRINT @N3
The precision and scale parameters are important!
I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
|
|
|
|
|
Ah, I thought that might be the case, thanks.
So what do you suggest? real?
|
|
|
|
|
You could do this...
CREATE FUNCTION dbo.LesserOf
(
@Op1 NUMERIC(18,7)
,
@Op2 NUMERIC(18,7)
)
RETURNS NUMERIC(18,7)
AS
BEGIN
DECLARE @result NUMERIC(18,7)
IF ( @Op1 < @Op2 )
SET @result = @Op1
ELSE
SET @result = @Op2
RETURN @result
END
...or set the precision and scale to something bigger (20,7)...will give 13 significant numbers and 7 decimal places.
(I don't usually use real.)
I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
|
|
|
|
|
I think T-SQL needs the Least function from PL-SQL.
It's more generic.
|
|
|
|
|
I'm trying to sum up a column twice, each with a separate condition
For example, if we have the following table:
Visitors
--------
MeetingID LocationID NumVisitors
1 1 3
1 1 5
1 3 7
2 1 9
2 5 3
2 5 4
4 3 10
I want to specify 2 Meeting IDs (lets say 1 & 2) and have the query return the following:
LocationID TotalNumVisitors1 TotalNumVisitors2
1 8 9
3 7 0
5 0 7
I tried using a full outer join on the table with itself, but that accumulates everything (both ids) together.
Select MeetingID, Sum(Visitors.NumVisitors) as NumVisitors1, Sum(Visitors1.NumVisitors) NumVisitors1
from Visitors
full outer join ...
where Visitors.MeetingId = 1 or Visitors1.MeetingId = 2
Group By ...
I then tried using sub-queries - that results in dis-joined return set - half of the columns are populated, the other half are null, then it switches
Select Visitors.MeetingId, TotalNumVisitors = (select Sum(Visitors.NumVisitors) from Visitors where Visitors.MeetingId = 1),
TotalNumVisitors1 = (select Sum(Visitors.NumVisitors) from Visitors where Visitors.MeetingId = 2)
where ...
LocationID TotalNumVisitors1 TotalNumVisitors2
1 8 NULL
1 NULL 9
3 7 NULL
5 NULL 7
Any ideas?
|
|
|
|
|
What database are you using?
Some have Pivot builtin.
Otherwise you can do it as a variation of this query:
Select LocationID
,Sum(Case when MeetingID = 1 then Numvisitors else 0) as TotalNumVisitors1
,Sum(Case when MeetingID = 2 then Numvisitors else 0) as TotalNumVisitors2
From Visitors
Group By LocationID
|
|
|
|
|
It's actually a query used to populate an rdl report.
Would that work if the calculation is more complex? It's actually adding the rows together, then multiplying by a number from a different table. Something like this maybe:
Select LocationID, Groups.GroupSize
,Sum(Case when MeetingID = 1 then (Group)*GroupSize + Numvisitors else 0) as TotalNumVisitors1
,Sum(Case when MeetingID = 2 then (Group)*GroupSize + Numvisitors else 0) as TotalNumVisitors2
From Visitors
join Groups on Visitors.SomeID = Groups.SomeID
Group By LocationID, GroupSize
I could probably work around this by adding some logic in the report, but it seems a bit cleaner to do my data handling in the query and have the report only display the data. And I figured there got to be an easy way to do that 
I'll give it a try later, but it should probably work right?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
