|
|
|
I have a table on an SQLServer 2000 box with a DateTime field called CallTime.
I want to select all the records between 19:00 and 06:59 over a period of days
Is there a more efficent way of doing the select in a stored procedure than this,
WHERE (DATEPART(hh, dbo.JobData.calltime) * 60 + DATEPART(mi, dbo.JobData.calltime) BETWEEN
DATEPART(hh, '19:00') * 60 + DATEPART(mi, '19:00') AND DATEPART(hh, '23:59') * 60 + DATEPART(mi,
'23:59')) OR (DATEPART(hh, dbo.JobData.calltime) * 60 + DATEPART(mi, dbo.JobData.calltime) BETWEEN
DATEPART(hh, '00:00') * 60 + DATEPART(mi, '00:00') AND DATEPART(hh, '06:59') * 60 + DATEPART(mi,
'06:59'))
Michael
CP Blog [^] Development Blog [^]
|
|
|
|
|
|
Thanks Colin.
Having to split the WHERE up into before midnight and after midnight seemed to a bit clunky, but it'll do the job. I really hate T-SQL somedays, feels so primitive. Here's hoping that SQLServer2005 has a few useful enhancements.
Michael
CP Blog [^] Development Blog [^]
|
|
|
|
|
|
Colin Angus Mackay wrote:
Another thought occurs to me...
You could create an extra column to store the minutes (or seconds) since midnight. It is updated by a trigger on the table. Your query would be less complex and probably run faster too.
Thanks. I hadn't thought of that one. I may look into implementing that for the next release of the software. I'll stick with the query for now as I'm only using it to build a View to produce a report and I don't want to make too many changes.
Michael
CP Blog [^] Development Blog [^]
|
|
|
|
|
Could you subtract out 7 hours from each DateTime value, then convert the result to a string formatting the time in AM/PM format and just keep the ones that have a PM as their time component by using a LIKE comparison? Not sure just what this SQL would look like, but I think it work.
where to_char((DateTime - 10584000),'YYYYMMDDHHMISSAM') LIKE '_____________PM'
The value of 1058400 is how many seconds are in 7 hours.
Chris Meech
I am Canadian. [heard in a local bar]
Remember that in Texas, Gun Control is hitting what you aim at. [Richard Stringer]
Nice sig! [Tim Deveaux on Matt Newman's sig with a quote from me]
|
|
|
|
|
Hi all,
I m desiging the database first time, so how can i design the database,how i set the datatype of Datafield.(means which creteria i use for giving data type).
how can i manage the Relationship.....
plz discuss in detail.....
thanx in advance
Sajjad Rizvi
|
|
|
|
|
"E. F. Codd"...put that in Google (or even better, Amazon) and read the results.
|
|
|
|
|
|
Hi
does ne1 know how to put an if statement with in sql query ??
im looking on doing something like the below
Select school_id,class_id from student
if school_id = 1
where left(class_id,1) = @class_val
else
where left(class_id,2) = @class_val
Any ideas
Thanks
Si
|
|
|
|
|
Try the CASE statement.
e.g.
select school_id,class_id,
CASE school_id
WHEN 1 THEN left(class_id,1)
ELSE left(class_id,2)
as exp1
from student
where
exp1 = @class_val
I think this will work. Sorry but I can't test it now on my machine but try and let me know.
|
|
|
|
|
Only to show you more examples with CASE:
select nf_item.nf_number,
case nf_item.nf_unit_abbr
when 'KGS' then nf_item.wgt_net/1000
when 'TON' then nf_item.wgt_net
end as wgt_net,
case nf_item.currency_id
when '0795' then nf_item.price_per_unit_c
else nf_item.price_unit_conv nf_master.exchange_rate
from nf_item with(nolock) where nf_item.ts_print >= 2345345345
|
|
|
|
|
Hello all.
I'm almost getting my head to the wall  because I can't get to the solution for a query I need to do.
Is anyone here wanting to help me ?
Let me explain the problem:
I have a table named T1 with the following structure:
ID | Subject | ParentID
This table represents info in a form of tree and when I'm at the top branch the ID and ParentID fields have the same value.
If I have the following data
ID Subject ParentID
1 Sub1 1
2 Sub2 2
3 Sub1_1 1
4 Sub1_2 1
5 Sub1_1_1 3
I want to make a query with the following result:
ID|Subject|NumberOfChildRecords
e.g.:
1 | Sub1 | 2
2 | Sub2 | 0
3 | Sub1_1 | 1
4 | Sub1_2 | 0
5 | Sub1_1_1 | 0
Is this simple to do ?
Thanks in advance for the attention you are having reading this.
|
|
|
|
|
I dont think it is simple... but you could certainly try something like:
Here is how you would do it using NW:
select a.employeeid, count(b.employeeid) from employees a, employees b
where a.employeeid = b.reportsto group by a.employeeid
What we have is a self referencing table. What we need to do is essentially add the table twice in the from clause with two seperate aliases and then correlate the two in the where. I should be using ANSI standard joins here, but it was quick.
so for you we need something like
select a.id, count(b.id) from T1 a, T1 b where
a.id = b.parentid group by a.id;
Let me know if this works.
http://www.jasncab.com/huberblog :: Jason Huber
|
|
|
|
|
I migrate the microsoft class : SqlHelper to OracleHelper, i used in this class ODP .NET 10..
I need some person to do the test. if there is someone who is interested by this this request ,please send me an email
at nizar@myway.com and i will response him.
thnx
ENSI TUNISIA
|
|
|
|
|
we are developing an ASP.NET application with SqlServer at backend..
there are an supplier and about 3000 customer, and each customer has about 3-4 users. they are selling mobile phone counters.
while a sale occurs, we are selecting customers stock amount, if it is bigger then sale amount, we are updating its stock, and we increment suppliers stock. The query is like that :
DECLARE @StockAmount int
SELECT @StockAmount = Amont FROM Stocks WHERE CustomerId = @BuyerCustomerId
IF @StockAmount > @SaleAmount
UPDATE Stocks SET amount = @StockAmount - @SaleAmount WHERE CustomerId = @BuyerCustomerId
SELECT @SuppliersStockAmount = Amont FROM Stocks WHERE CustomerId = @SellerCustomerId
UPDATE Stocks SET amount = @StockAmount + @SaleAmount WHERE CustomerId = @SellerCustomerId
i know, each customer has got not so many users and a concurrency problem seems to be not a big possibility. but there is just a 1 supplier record and i think conlicts are possible. how can i alter this problem. after a research, i found
SELECT @SuppliersStockAmount = Amont FROM Stocks WITH (XLOCK ROWLOCK) WHERE CustomerId = @SellerCustomerId
seems to work fine for us, but it will block the row untill the transaction finishes. any approaches are appreciated. thanks.
|
|
|
|
|
Hi,
I want to execute a transact SQL like below
Declare @local as varchar(30)
Declare @LocalStr as varchar(50)
Set @LocalStr ='Select @local =ColumnName, * from tablename where Condition'
Exec(@LocalStr)
But it gives @local not declare error
or how can I assign the value of columnName to local variable, It should be Transact (Dynamic) as conditions are dynamic
Thanks in advance
|
|
|
|
|
|
|
Hi all,
In my .NET application i want to read the sql server database schema(Table names,triggers attached to tables etc).
Once user selects the database (sql server) to connect to, i will open the connection and show to user list of tables available in the database.
Is there any way of reading the database schema after opening connection to the database.
Thanks in advance,
Tushar Mahajan.
|
|
|
|
|
|
Thanks Colin it is returning all Table Names.
For triggers i am executing 'sp_helptrigger' stored procedure with table Name as
a parameter.It returns all the triggers and their types for that table.
Thanks,
Tushar Mahajan.
|
|
|
|
|
Hi,
i would like to know how it's possible to work with a output parameter.
i already worked this way for input:
"insert into ... (test, test2) values(@test, @test2)"
and then i add those parameters to the parameterscollection of my odbccommand object.
now i want to do a simple select of one column:
"select test from testtable" but how can i retrieve that value , the result of my query i mean, without working with a dataset or a reader. is it possible to also work with parameters for this?
grtz & thx
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
