|
try this
select t.id,t.name,d.name,g.name,c.*
from team t
inner join division d on d.id = t.divisionid
inner join [group] g on g.id = d.groupid
inner join Company_Group cg on cg.groupid = g.id
inner join company c on c.id = cg.companyid
Hope it will help you
I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Sorry Blu_Boy, not quite the expected results. I may not have explained correctly,
I want to get only the Teams from the supplied Company (@CompanyId) and where the supplied DivisionId (and thus referenced GroupId) maps to the GroupId in Company_Group table.
This is where I'm stuck. This really should be super easy, but I'm not seeing it tonight.
I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
|
|
|
|
|
I may be wrong again with query.
select c.company,g.name,d.name,t.name
from Company_Group cg
inner join company c on c.id=cg.companyid
inner join [group] g on g.id=cg.groupid
inner join division d on d.id=g.id and d.groupid = cg.groupid
inner join team t on t.id = d.id
I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Thanks for your time Blue_Boy, I have found that the design is flawed and there will have to be a work-around outside of the database.
I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
|
|
|
|
|
Andy_L_J wrote: hanks for your time Blue_Boy,
My pleasure to help others 
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
I am busy working on a windows application to log working times of staff. The application is being developed using C# with SQL Server 2008 as the backend.
I have a [NetTime] column (Time data type) in my table in the database. I get the TimeSpan value by subtracting the official working hours and the actual working hours. This TimeSpan value can be positive or negative. I would like to store this value in the [NetTime] column, but the Time data type does not accept negative values.
What is the usual way of dealing with this problem? I will need to do calculations with the values in this column, for example to calculate the net or total time a staff member worked overtime or not.
Thank you.
Kobus
|
|
|
|
|
My advice would be to use a Long data type to store the Timespan. You can easily store this in the db using TimeSpan.Ticks() and convert back to a TimeSpan in your app using TimeSpan.FromTicks(). That way there is no problem storing positive or negative values.
Hope this helps
When I was a coder, we worked on algorithms. Today, we memorize APIs for countless libraries — those libraries have the algorithms - Eric Allman
|
|
|
|
|
Most timekeeping systems have a minimum timespan for calculations eg a minute, timespan.tick has no place in a timekeeping system. Therefore I would store the values as integers.
Sometimes you need to look at the real world, not the theoretical or technical capabilities of the system.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
A Time represents a point in time. A TimeSpan is a duration of time. They are fundamentally different things. 11 o'clock is a point in time, and so is 12 o'clock. The time span between them is 1 hour.
An analogy: the location of Paris is at 48°48'N 2°20'E. London is at 51°32'N 0°5'W. The distance between Paris and London is 211 miles. Location and distance are fundamentally different things and are not interchangeable.
Similarly, Time and TimeSpan are different things and are not interchangeable.
|
|
|
|
|
A (pretty bad) solution is to store the Abs(Timespan) value in one column and Sign(Timespan) in another.
The others have given you better answers .
|
|
|
|
|
I almost posted a "bad" solution with a disclaimer like yours. (store it as a character).
|
|
|
|
|
You could even use the '+' character for positive values and the '-' character for negative ones. I'm not sure what a NULL value would mean though. Might have to make it a non-nullable column. 
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
The problem for me is that I'm often enough set on the wrong track by how the question is formulated.
|
|
|
|
|
kbalias wrote: I get the TimeSpan value by subtracting the official working hours and the actual working hours.
Which would seem to me that you would need to store the "official working hours" as well. Given that then there is no point in storing the difference for the second value. Instead you just store the "actual working hours". If you need the difference then the caller subtracts the two.
If you want the difference anyways then store it as seconds. That insures enough precision and is probably a more business friendly value than Ticks.
|
|
|
|
|
<Getting my brain back into gear>
The standard way is to store the login and logout times in a table. The nettime is easily enough calculated from those two values. It's a part of normalization to not store values you can calculate from other columns in the database.
|
|
|
|
|
Agree with this. Answer upvoted.
|
|
|
|
|
I agree with Jörgen. It isn't advisable to store calculated values in the database. If any of the values used in a calculation changes you have to do a recalculation to keep the data up-to-date.
Consider the following:
create table timetest (
starttime datetime,
endtime datetime
);
insert into timetest values ( getdate(), getdate() - 1);
insert into timetest values ( getdate(), getdate() + 1);
select starttime, endtime, DATEDIFF(n, starttime, endtime) from timetest;
if either one of the datetime columns change, the result is still always correct.
However, what you can do is that you can specify a computed column if you like. To extend the previous example:
alter table timetest add timediff as DATEDIFF(n, starttime, endtime)
Now if you run a select * query to the table you will see the same results as in the previous example because the calculation is now part of the table structure (and always up-to-date).
|
|
|
|
|
hi,
I'm using an asp.net application with some sql SELECT statements, I want to ask you when is it worthwhile to execute statements with SQL query?
I guess that if I use the paging to get 10-rows pages from a 25-rows table, it's not profitable, and I have to do the paging at the server side code. But when my table has 10000 rows, it becomes worthwhile enough.
Am I right?
|
|
|
|
|
In my opinion you're right on track.
However, things could get complicated if the amount of rows in the result set is changing (because the amount of rows changes, criteria changes etc). So a straightforward solution would be using paging nevertheless. If the connection between the application and the database isn't slow and the database isn't over utilized, this shouldn't cause too much wait time.
Of course if you have tables that you know are always small, fetching all one time and then paging locally would be an easy thing to do.
|
|
|
|
|
At some point you are presenting too much data to a user.
If you are looking at a 25 row limit per page then probably the limit is well below 500 total rows.
At that point you know that the user knows what they are looking for. They are not just randomly scanning records. So make them tell you what they are looking for. Use that to create a query that restricts the total rows returned.
When creating servers I usually have a configurable maximum and user queries are run with a count(...) first and if the return is more than a maximum then I return an error. The GUI screens are responsible for providing sufficient input specific to one query to allow the user to provide enough data to get below the limit.
|
|
|
|
|
I you are returning 10k rows to a web browser you are sacked on the spot! One of the issues we have is that the users are always after data dumps to analyse in excel, the continious whine of just give me a data dump becomes really irritating. Now that we have volume policies it is easier to limit these.
As others have said, you need to filter the results to minimise the volume returned. I never use paging although I do use a top 1000 in procs with a potential of high volume and then inform the user they have exceeded the volume policy. By the time you chuck 1000 records into a grid with local filtering and sorting I see no requirement for paging.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: One of the issues we have is that the users are always after data dumps to analyse in excel
Does that not imply that something is missing from the application? I'm guessing because I don't know the business requirements, but if they are constantly extracting data into another tool to analyse, that suggests that they need some sort of information they can't get from the source application.
|
|
|
|
|
I have encountered this with several server, the cpu utilization with SQL server on multi core systems is very low i.e. one core does all the work and the others are idle, what is worse is that the queries are queued serially.
Anyone encountered this, is there a solution.
Its the man, not the machine - Chuck Yeager
If at first you don't succeed... get a better publicist
If the final destination is death, then we should enjoy every second of the journey.
|
|
|
|
|
Have you checked if processor affinity is defined? It could be that only one core is allowed. Also it could be that the disk subsystem is actually the bottleneck so CPU has to wait for the answers.
And what comes to the serial execution, I thing there are at least few possibilities:
- again waiting for disk subsystem
- serial executions is forced because of locking issues
- transactions are ran in serializable isolation level.
|
|
|
|
|
Thanks Mika, I will look into it...
In the meantime more information:
- We have a windows application server service connecting to sql server via integrated security on an internal threadpool of 10 threads. Each thread creates a connection when needed, and closes when finished.
- The server is an HP with 2 quad core xeon hyperthreaded (16 processors in windows) with 10k sata raid 5.
- Clients connect to the application server via tcp (no direct connection to sql).
Its the man, not the machine - Chuck Yeager
If at first you don't succeed... get a better publicist
If the final destination is death, then we should enjoy every second of the journey.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
