|
if you have a column set as identity you can use SCOPE_IDENTITY()
DECLARE @MyNewId INT
SET @MyNewId = SCOPE_IDENTITY()
|
|
|
|
|
So store the @@Identity or Scope_Identity() in a variable directly after inserting the activity record and use it in the second insert for todo, there you have your relationship
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Even you can go ahead with OUTPUT.INSERTED
e.g.
insert into Activitity
output inserted.ActivitityId into TODo(ActivitityId)
values(1,'somerecord')
select * from TODo
Output:
TODOId ActivitityId
1 1
Niladri Biswas
|
|
|
|
|
Now that's cool, I was not aware INSERTED worked for an ordinary insert statement, I thought it was only for triggers. Now to go play, I wonder is UPDATED works as well
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
Hi guys,
I have created a trigger for auditing ..
I want to keep track of changes made to each field by using a counter.
can you let me I go about achieving this task....
modified on Wednesday, January 13, 2010 5:34 AM
|
|
|
|
|
If I understand your questions correctly, why dont you create an audit table and always add a record before each update.. so you want an update triger[^]
|
|
|
|
|
I am developing an ecommerce website, aimed at selling electronic products.
So for storing the product details I use a product table in which I have the fields for storing the product specifications. My problem is how to include the specifications of different products like motherboard, mouse, Monitor in a single table (product table).
How should I design the product table to hold the specifications of different products. Suggest me a good database design for this problem.
|
|
|
|
|
You can have two tables like this:
In product table:
ProductId
ProductName
ProductType
and in ProductTypeCodes:
ProductTypeCode
ProductType
50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!
|
|
|
|
|
You need to think about the data - I'd get plenty of examples and see how they look - what attributes define a particular product for example.
A possible suggestion is two tables: one to hold products and the other to hold attributes. This is a simplified example of this approach.
Product Table
-------------
ProductID
ProductName
ProductType
Attribute Table
---------------
AttributeID
ProductID
Attribute
Value
Examples:
Products
1, LG XYZ, TFT Monitor
2, AX-999, Motherboard
Attributes
1, 1, Screen Size, 22
2, 1, Colour, Black
3, 2, CPU Type, AMD
4, 2, PCI Sockets, 3
You need to link the tables. Also this is probably too simplistic - think about the units for attributes. Some will numeric others strings.
Regards
David R
---------------------------------------------------------------
"Every program eventually becomes rococo, and then rubble." - Alan Perlis
|
|
|
|
|
|
DECLARE @Current_SQL varchar(500)
DECLARE @QueryResult int
....
SET @QueryResult = EXEC(@Current_SQL)
I get error: "Incorrect syntax near the keyword 'EXEC'."
My @Current_SQL query like "Select Count(*) From MyTable Where ID = 1"
Haw can I get return value from EXEC metod.
|
|
|
|
|
You could use an output-parameter, somewhat similar to this;
declare @Current_SQL nvarchar(500)
declare @result bigint
set @Current_SQL = 'select @result = count(*) from [MyTable] where [ID] = 1'
execute sp_executesql @Current_SQL, N'@result int output', @result output Good luck 
I are Troll 
|
|
|
|
|
|
Hi to All Forum members
I am stuck in finding duration and Net duration in the following procedure
I also adding image Url for better under standing of my requirement.
Please go to this Url ..
http://picasaweb.google.com/lh/photo/203UnHt9l8EnouLOtIKxBg?authkey=Gv1sRgCM6puf-sutfJ2QE&feat=directlink[^]
Following is my T-Sql Query...
Select F.Id as [FleetID], Convert(varchar(30), F.AssignedOn, 113) as [Date], D.DepotName as [Depot], DT.DutyID as [Duty Number],
IsNull(Convert(varchar(30), F.ActualOutSheddingTime, 113),'Not Assigned') as [Actual OutShedding Time],
IsNull(Convert(varchar(30), F.ActualInsheddingTime, 113) ,'Not Assigned') as [Actual Inshedding Time],
IsNull(CONVERT(varchar(5), DATEADD(ms, DATEDIFF(ms, F.ActualOutSheddingTime, F.ActualInsheddingTime), 0), 114) ,'00:00')as [Duration],
IsNull(DT.RestHours, 00) as [ShiftChangeoverTime],
IsNull(CONVERT(varchar(5), DATEADD(ms, DATEDIFF(ms, CONVERT(varchar(5), DATEADD(ms, DATEDIFF(ms, F.ActualOutSheddingTime, F.ActualInsheddingTime), 0), 114), Convert(Datetime, '00:'+ IsNull(Convert(varchar(30), DT.RestHours, 114), 30))), 0), 114),'00:00') as [Net Duration]
From Fleet F
Left Outer Join Duties DT on F.DutyID = DT.ID
Inner Join Depot D on DT.DepotId = D.ID
Inner Join Vehicles V on V.ID = F.VehicleID
Welcome for your valuable suggestions.
Vishnu
Vishnu Narayan Mishra
Software Engineer
|
|
|
|
|
Hi
Actually Duration is coming right in this query and the issue is Net Duration is not coming right.
Vishnu Narayan Mishra
Software Engineer
|
|
|
|
|
hi all,
i want to build a query but not succesful
i have two table named A and B
A table has field id(N), title(Varchar)
B table has field mid(N), id(N)
so i want to id, title from A table and count of id from B table
let me explain with data
Table A data
1 title1
2 title2
3 title3
Table B data
1 1
2 1
3 1
4 1
5 2
6 2
7 2
8 3
9 3
so i want to following result
id title count
1 title1 4
2 title2 3
3 title3 2
Please suggest how can build this query?
|
|
|
|
|
Here's the idea how to do this:
SELECT A.id, A.title, COUNT(*) as cnt
FROM A left join B on A.id = B.id
GROUP BY A.id, A.title
|
|
|
|
|
SELECT A.id, A.title, S.total
FROM A
LEFT OUTER JOIN
(
SELECT id, COUNT(id) as total
FROM B
GROUP BY id
) S on S.id = A.id
ORDER BY A.id ASC
|
|
|
|
|
Hi
I have a select query.The test team told me that the query is weak.So how can I make a query stronger in SQL Server.
Thanks
Denny
|
|
|
|
|
Let it lift weights...
Wout Louwers
|
|
|
|
|
They probably refer to strong and weak typing.
Here's[^] an explanation, the rest you can google.
|
|
|
|
|
Go back to the Test team and ask them to explain in detail why they believe that your query is 'weak'.
|
|
|
|
|
Hi,
Can someone please advise me on what to do with this issue.
I have a table that has a DATE, GROSSHOURS, GROSSMINUTES and TOTAL. I have a select query that takes the GROSSHOURS and GROSSMINUTES and add them together in seconds e.g: 1H 30M = 90M
My issue is this:
I have 2 lines on the same day.
2010/01/05 1HOUR 30MINUTES 90MINUTES
2010/01/05 2HOURS 20MINUTES 140MINUTES
How will I write a select statement where I can only display 1 line for 2010/01/05 but with both totals?
2010/01/05 230MINUTES
My statement looks like this at the moment:
Select Id, CONVERT(VARCHAR,EntryDate,111) AS EntryDate, GrossHours, GrossMinutes, DATEDIFF(minute, StartJob, EndJob) / 60 * 60 + GrossMinutes AS Total FROM [Timesheet] GROUP BY Id, EntryDate, GrossHours, GrossMinutes, StartJob, EndJob
Thank you!
Illegal Operation
|
|
|
|
|
You need to remove GrossHours and GrossMinutes from your select statement and group by.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
