|
|
Hi All,
Iam new to database. Here iam using sql server 2000. i want to sum the counts from a table. I need this to calculate the employees who have come late in a week/month.
Example: I have table such as
Table1:tbl_attandence(it contains the employee id and the intimings and outtime)
Fields:empcode,intime,outime
Eg: EMP001,11/1/2011 09:30:45 AM,11/30/2011 06:30:15 PM
I am attaching my code such that it return the count but iam unable to sum the values because it is showing the result as shown below
DECLARE @actualTime DATETIME
DECLARE @lateTime DATETIME
SELECT @actualTime = '9:41'
Select @lateTime ='10:00'
select count(convert(varchar,intime,105)) as dates,CONVERT(VARCHAR(5),intime,108) AS Hours from tbl_attendance
where empcode='EMP001';
and intime between '2011-11-01' and '2011-11-30'
and CONVERT(VARCHAR(5),intime,108) >=@actualTime and CONVERT(VARCHAR(5),intime,108) <= @lateTime
group by CONVERT(VARCHAR(5),intime,108)
OytPut:
days intime
1 09:41
1 09:44
1 09:46
2 09:53
1 09:54
Now i need to sum all the days and get the sum as 7
Please suggest that how can i do this task any help will be appreciated
|
|
|
|
|
Elizabeth Rani wrote: Now i need to sum all the days and get the sum as 7
Seems to add up to 6!
You can used compute after you query in the proc
compute sum(Days)
or put the existing query into another outer query
Select sum(days) from (put your query here)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi everyone! I'm currently learning Database Administration and trying to play with database backup and restore. I have created a database that I am using to try what I've learnt so far.
I'm trying to restore a backup database but I always get an error that '... Restore failed on server ...' when I still have the database on the server. But if I delete the database from the server and perform the restore, everything works fine.
However, when I create a backup of 'AdventureWorks' database and perform the restore, I do not encounter any problem whether 'AdventureWorks' database still exists on the server or not. I'm thinking the problem be occuring from some database properties I do not know. I need help on how to solve this problem of restoring the backup when the database still exists on the server as it works with 'AdventureWorks'. Thanks in advance.
|
|
|
|
|
Hi,
When you are doing a SQL database restore, are you over riding the current database?
1.In the restore window, select the database source for backup.
2.select options from the left pane.
3.check 'over write the existing database' check box.
4.Restore.
Hope this helps.
-Manognya
__________________________________________________
$ God gives what is best.Not what all you wish 
|
|
|
|
|
Does the service have read/write privileges on the mdf-file and read-privileges the location where your backup is located?
Bastard Programmer from Hell 
|
|
|
|
|
hello guys... I have this numeric column whose '<b>Identity Specification</b>' I have set to <b>Yes </b>. But sometimes problem occurs when I use this technique due to the fact that, when I delete all the records from this table, this column's ID does not reset to 1. Instead, it retains the last ID (the ID before deleting all the records).
SO the question is: how can I reset this column to 1, after I delete all the records from this table? thnx for any help.
|
|
|
|
|
You need to Reseed the Column using the DBCC command of MS SQL Server.
dbcc CHECKIDENT("TableName1",RESEED,0)
DBCC CHECKIDENT for Microsoft SQL Server
I hope this help with your Problem
_____________________________________________________________________
Hey guys. Just to defend my answer from the birds and stones .
The Reason for me using the Delete/DBCC CHECKIDENT Function is that you can still use Auditing on the tables if needed
(The Triggers are still called). With a truncate table call the triggers will not be called Truncate Table
Because TRUNCATE TABLE is not logged, it cannot activate a trigger.
Cheers
modified 10-Jan-12 0:39am.
|
|
|
|
|
Thnx for your answer. It perfectly solves my problem
|
|
|
|
|
If there are no foreign keys on the table I guess you can always use:
truncate table <table_name>
to kill 2 birds with one stone...
|
|
|
|
|
Don't let the animal welfare people hear you talk of "killing two birds with one stone". 
|
|
|
|
|
Yes, not very PC of me, obviously if I wanted two birds killed I wouldn't use something as inelegant as a stone, I'd simply put a contract out on them... 
|
|
|
|
|
Nah this is so obvious that Ellen's answer had to be the relevant one surely!
Besides I'd use a shotgun and get the whole flock (or at least more than 2).
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hmm, I guess it all depends where you need to use it. I prefer my code to be able to be used in as many situations as possible, therefore to me issuing DML is _always_ preferable to DDL. Besides which, the obvious solution to deleting all rows in a table is to use truncate, the fact that it resets the seed is an added benefit, why make life more difficult for yourself than you need to ? 
|
|
|
|
|
unclejimbob wrote: I prefer my code to be able to be used in as many situations as possible
I don't ever recall actually coding a truncate/reseed into a stored proc, in 20+ years. These live under the snippets/template folder in my IDE!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
"I don't ever recall actually coding a truncate/reseed into a stored proc, in 20+ years."
Hey, good for you, I use truncate in sps for several - admittedly specialist - purposes. I too have a large number of tsql patterns/idioms as templates.
I can't ever recall anyone suggesting that 2 lines of code (delete/reseed) is somehow better than 1 (truncate) and yes, I've been at this game at least as long as you have.
You will note that I never suggested my solution was any better or worse than Ellen's I just looked at it from a slightly different angle, taking into account the full content of the question - an approach you might like to try and emulate rather than turning a useful discussion into a point-scoring exercise with you as the self-anointed games master.
At 20+ years experience I shouldn't have to be pointing out to you self-evident things such as the fact that the first/most obvious solution isn't necessarily the most appropriate.
Oh yeah the bird thing ? Leave it, it started out as witty repartee and you've come in late in the piece and successfully tortured it to death.
___
Updated/final:
A few observations on this thread before I go.
I have deliberately not read either the most recent post or my email as I assume it contains the same pointless drivel I've been exposed to up to now.
1) Anyone who includes the clause 'I've never needed to do this in x years' is, of course, demeaning your contribution because they mistakenly equate length of service with quality of work; you rarely see this from published, world-class contributors, they tend to be pretty diffident - you don't need to put up with it
2) Indidivuals who come in after the fact and attempt to rate your contribution based upon their own close-enough-is-good-enough two-second evaluation are, of course, putting you down - don't accept it
3) Argumentative postings which don't add value to the discussion are a sign of insecurity over one's position - don't accept it
4) Zero-value postings which are used to simply boost an already over-inflated points-based rating system are, of course to be accepted for what they are
5) Crass, infantile, witless and off-colour postings unrelated to an original throw-away post are a sign that the person is humour-challenged, accept the fact that these people cannot be helped
Read the posts in this thread, there are 2 valid answers, 2 witty (albeit off-topic) observations and a host of pointless, unsubstantiated nitpicking - I leave you to decide which of these posts has actually assisted in answering the question
The main point of all this unseemly bickering is of course that it is NOT acceptable to rate another contributor's post without substantiating it with some solid evidence.
I don't care who you are out in the world, the same rules of posting etiquette should apply to both first-time and veteran contributors.
Bye.
-- modified 9-Jan-12 11:45am.
|
|
|
|
|
unclejimbob wrote: I use truncate in sps for several - admittedly specialist - purposes.
As you say , specialised requirements, thankfully I have never actually had to use them.
unclejimbob wrote: Taking into account the full content of the question - an approach you might like to try and emulate
What and actually give a balanced and reasonable response - Nah.
I actually was not denigrating your response as it was a good one (and got an upvote from me) more to the point that the OP probably was not aware of the limitation of the truncate solution.
unclejimbob wrote: Oh yeah the bird thing
Never, I say never, have I come in late to an obscure discussion and got the wrong handle on on the discussion while trying to be amusing!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
unclejimbob wrote: I can't ever recall anyone suggesting that 2 lines of code (delete/reseed) is
somehow better than 1 (truncate) and yes, I've been at this game at least as
long as you have.
Hopefully that statement is flippant.
There are in fact differences between using delete and truncate, especially in SQL Server, which have nothing to do with this discussion. And those differences are the reasons is should be used with care and why it is seldom needed.
Choosing the correct one has nothing to do with the number of lines. And there is very little in programmng that should be based solely on line count. (It isn't even a good developer productivity metric.)
unclejimbob wrote: Indidivuals who come in after the fact and attempt to rate your contribution
based upon their own close-enough-is-good-enough two-second evaluation are, of
course, putting you down - don't accept it
This site is set up specifically to allow rating answers. Especially on technical questions. That is what the "Rate this message" is for.
And one need not even explain why they give it a good or bad rating.
Moreover I think that any technical site that doesn't allow and expect discourse on the correctness of answers is probably worthless.
|
|
|
|
|
Hi all
I have encountered a stored procedure template that we are supposed to use for our daily development work. The strange thing about this template (to me, at least) is that it has a RETURN statement just before the final END statement. Obviously if the proc is returning a value, this statement is necessary, but we are talking about a stored proc that returns no value. I do not understand why this convention exists, much less why someone would care to enforce it. It looks like it clutters the readability of the stored proc by appending code that does nothing. I don't recall seeing any C code like this: "void fx(void) { return; }", at least not on purpose.
But I figure that this convention must have come from somewhere, and maybe at some point there were a perfectly valid and good reason for it.
So my question is this: What good reason is there (if any) to have a RETURN; statement just prior to the final END statement in a stored proc that does not return a value?
Here is an example:
CREATE PROC fx
AS
BEGIN
RETURN;
END
go
|
|
|
|
|
No reason.
For TSQL the only thing I find odd with return is the need to put one after raiserror. Seems like raiserror itself would be sufficient to say stop.
|
|
|
|
|
Depends on the errorlevel; I use 'em also to send some intermediate messages to the layer above.
Bastard Programmer from Hell
|
|
|
|
|
Because that's where it belongs.
|
|
|
|
|
I think I recognize a Fortran voice...
|
|
|
|
|
I am building a reporting database that is pulling data from several source and different databases.
The plan so far is to turn on change data capture on the source databases and process the log generated using SSIS.
I can find a few papers and sample online on how to process the ubiquitous INSERT/UPDATE/DELETE log and it is helpful but I fail to find any good sample or tool with multiple tables.
I can't see how I can process the items in the right order and keep integrity.
Let's say we have a classic database with Product, Order and OrderLine.
And this scenario:
8:00 - Create a product P1
8:01 - Create an Order O1
8:02 - Create an Order Line L1
8:03 - Delete the Order Line L1
8:04 - Delete the Order O1
8:05 - Create a new Order O1
8:06 - Create a new Order Line L1
The cdc tables will contain
For Product
8:00 INSERT P1
For Order
8:01 INSERT O1
8:04 DELETE O1
8:05 INSERT O1
For Order Line
8:02 INSERT L1 Order:O1 Product:P1
8:03 DELETE L1
8:06 CREATE L1 Order:O1 Product:P1
To keep the referential integrity of the reporting database, I would need to:
- insert order lines after orders
- delete order lines before orders
Because of this I can't really treat a CDC at a time.
As I see it the only way would be to process all CDC simulataneously in datetime order.
Is there ways to do that?
The source databases are Oracle and MS SQL and possibly text files.
The reporting database is MS SQL
|
|
|
|
|
Wow in 20+ year of writing reporting systems I have never had to consider this for of replication, imagine reading the logs!
Why do normal replication tools (something else I know little about) not meet your requirements.
Never underestimate the power of human stupidity
RAH
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
