|
try this
<br />
select CAST(DATEDIFF(minute, 0, '01:00:00') AS int) / 60
Hope it will help.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Hi
My Stored Procedure output is as below given.
Interval Avg1 Avg2
NULL NULL NULL
04/08 NULL NULL
05/08 2000 NULL
NULL 1000 NULL
NULL NULL NULL
NULL NULL 4000
NULL NULL 3000
I want result in two rows as below given.
Interval Avg1 Avg2
04/2008 2000 4000
05/2008 1000 3000
Please let me know how i can do this using temm table ?
Thanks
Care Career
|
|
|
|
|
Based on the info you posted, the only thing I can imagine is that you should modify the output from sp to give you what want.
So, you need to include to the post, how the result is made in sp and what do you want to change
or
if you cannot change the sp, what is the logic you want to achieve with temp tables (why 04/2008 has 4000 in avg2, since there is nothing in the data combining the row "NULL NULL 4000" to the row "04/08 NULL NULL"
Mika
The need to optimize rises from a bad design
|
|
|
|
|
Hi
I do not want to change my existing SP I just want output as below given.I want to remove NULL Please help..
Sr No Interval Avg1 Avg2
1 04/2008 2000 4000
2 05/2008 1000 3000
|
|
|
|
|
This simply isn't possible unless there is some kind of logic why the result set should look like you described.
If your result set in the start is:
Interval Avg1 Avg2
NULL NULL NULL
04/08 NULL NULL
05/08 2000 NULL
NULL 1000 NULL
NULL NULL NULL
NULL NULL 4000
NULL NULL 3000 and you want:
Interval Avg1 Avg2
04/2008 2000 4000
05/2008 1000 3000
Please explain for example why the last row having 3000 in Avg2 should be combined with the row having 05/08 as interval. Also why the value 2000 on the same row is moved to the row 04/08 and so on...
One crucial thing is that you think this as result sets having rows with conditions since this is the way the DBMS does it.
The need to optimize rises from a bad design
|
|
|
|
|
I want to remove NULL from below given resultset.If I will remove NULL then data will come in two rows.SO how Can i do this using temp table?
please help its urgent.
Interval Avg1 Avg2
NULL NULL NULL
04/08 NULL NULL
05/08 2000 NULL
NULL 1000 NULL
NULL NULL NULL
NULL NULL 4000
NULL NULL 3000
Thanks
Care Career
|
|
|
|
|
listen,
first i assume columns are varchar,int,int type
if column is varchar type you can use isnull() funtion like this:
select,...,isnull(avg1,'') FROM mytable
that way you will get an empty slot.
if you want to erase all row with null values type
select * from mytable where (interval <> null) and (avg1 <> null) and (avg2 <> null)
what you type is wrong, you can´t get that, columns not from the same row!!!
about temp table forget it, you don´t need that ... for now 
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
Is it possible that two different SQL servers generate the same
UniqueIdentifier
thanks
Mohammad Al Hoss
Development To Me Is A Pleasure more than a Job
|
|
|
|
|
Jeopardy answer: What is the easiest question in the world to google?
"While each generated GUID is not guaranteed to be unique, the total number of unique keys (2128 or 3.4×1038) is so large that the probability of the same number being generated twice is very small. For example, consider the observable universe, which contains about 5×1022 stars; every star could then have 6.8×1015 universally unique GUIDs."
from the first google result: http://en.wikipedia.org/wiki/Globally_Unique_Identifier[^]
|
|
|
|
|
hi
how can i backup and restore a table?
thanks in advance
|
|
|
|
|
Restore the database as a copy with another name and get the data needed from the desired table.
|
|
|
|
|
You can use export/import or bcp utility for a specific table.
Mika
The need to optimize rises from a bad design
|
|
|
|
|
hi
how can i connect from an application to sql server 2005
i build up an application_role in sql server
but still cannot create trusted login and username
|
|
|
|
|
reza assar wrote: trusted login and username
Trusted Connection uses WINDOWS AUTHENTICATION (i.e. your windows credentials), otherwise you need to create a login in SQL Server.
Have a look at www.connectionstrings.com to learn more about connecting to sql server.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi all
SELECT @vSQL = 'INSERT INTO #rapor(Id) VALUES('' '+ @X + ' '')'
i want below
FOR EX
INSERT INTO #rapor(Id) VALUES('5')
BUT SQL SAYS
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ' VALUES('.
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string ')'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ' VALUES('.
like that pls help
Thanks ...
Sampiyon FENERBAHCE
|
|
|
|
|
Odd, it works for me - although you get null in @vSQL if @X is null.
You will get a space each side of you value though.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
ITS THE CODE BELOW
DECLARE ankcev_cursor SCROLL CURSOR FOR
SELECT KULLANICINO FROM AnketCevap WHERE ANKETID='7'
OPEN ankcev_cursor
declare @vSQL varchar(1000), @numrows int
DECLARE @SNO INT;
DECLARE @KNO INT;
DECLARE @SONUC VARCHAR(10);
SET @SNO = 10;
WHILE @SNO < 83
BEGIN
FETCH NEXT FROM ankcev_cursor INTO @KNO
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SONUC=(SELECT CEVAPSIK FROM ANKETCEVAP WHERE ANKETID='7' AND
KULLANICINO = @KNO AND SORUNO=@SNO)
SELECT @vSQL = 'INSERT INTO #rapor(S'+ convert(varchar, @SNO) + ')'' VALUES('' '+ @sonuc+ ' '')'
Execute (@vSQL)
FETCH NEXT FROM ankcev_cursor INTO @KNO
END
SET @SNO=@SNO+1;
END
CLOSE ankcev_cursor
DEALLOCATE ankcev_cursor
Sampiyon FENERBAHCE
|
|
|
|
|
It wasn't complaining about the select, it was the resuklt when you tried to exec it. You had too many quotes
try this
SELECT @vSQL = 'INSERT INTO #rapor(S'+ convert(varchar, @SNO) + ') VALUES('' '+ @sonuc+ ' '')'
Your original selct gave this (I set @sonuc to f) :
INSERT INTO #rapor(S10)' VALUES(' f ')
rather than
INSERT INTO #rapor(S10) VALUES(' f ')
Bob
Ashfield Consultants Ltd
|
|
|
|
|
thanx for helping bob
i saw now that i had a mistake
this is working below,
SELECT @vSQL = 'INSERT INTO #rapor(S'+ convert(varchar, @SNO) + ') VALUES('''+convert(varchar, @SONUC) +''')'
an by the way how can i see what value in my variable
i mean like c# Console.WriteLine(@vSQL) and it writes
INSERT INTO #rapor(S10) VALUES(' f ')
how can do with tsql
thanks again
Sampiyon FENERBAHCE
|
|
|
|
|
TALHAKOSEN wrote: thanx for helping bob
No problem, always happy to help.
TALHAKOSEN wrote: how can i see what value in my variable
If running in query analyser
print @vSQL
or simply
select @vSQL
Bob
Ashfield Consultants Ltd
|
|
|
|
|
declare @vSQL varchar(1000)
DECLARE @SNO INT;
DECLARE @SNO2 INT;
DECLARE @KNO INT;
DECLARE @SONUC VARCHAR(10);
SET @SNO = 10;
SET @SNO2 = 10;
WHILE @SNO < 83
BEGIN
DECLARE ankcev_cursor SCROLL CURSOR FOR
SELECT KULLANICINO FROM AnketCevap WHERE ANKETID='7'
OPEN ankcev_cursor
FETCH NEXT FROM ankcev_cursor INTO @KNO
WHILE @@FETCH_STATUS = 0
BEGIN
--SELECT CEVAPSIK FROM ANKETCEVAP WHERE ANKETID='7' AND KULLANICINO = @KNO AND SORUNO=@SNO
SET @SONUC=(SELECT CEVAPSIK FROM ANKETCEVAP WHERE ANKETID='7' AND KULLANICINO = @KNO AND SORUNO=@SNO)
SELECT @vSQL = 'UPDATE #rapor SET S'+ convert(varchar, @SNO)+'='''+ convert(varchar, @SONUC)+''' where ID='''+ convert(varchar, @KNO) + ''''
Execute (@vSQL)
FETCH NEXT FROM ankcev_cursor INTO @KNO
END
CLOSE ankcev_cursor
DEALLOCATE ankcev_cursor
SET @SNO = @SNO+1;
END
PRINT @vSQL
PRINT @SNO ------ALWAYS WRITE S10 why stay same value ????
Sampiyon FENERBAHCE
|
|
|
|
|
Don't know. I took your code and commented out all the bits that wouldn't run and it printed 83.
declare @vSQL varchar(1000)
DECLARE @SNO INT;
DECLARE @SNO2 INT;
DECLARE @KNO INT;
DECLARE @SONUC VARCHAR(10);
SET @SNO = 10;
SET @SNO2 = 10;
WHILE @SNO < 83
BEGIN
-- DECLARE ankcev_cursor SCROLL CURSOR FOR
-- SELECT KULLANICINO FROM AnketCevap WHERE ANKETID='7'
-- OPEN ankcev_cursor
--
-- FETCH NEXT FROM ankcev_cursor INTO @KNO
--
-- WHILE @@FETCH_STATUS = 0
-- BEGIN
-- --SELECT CEVAPSIK FROM ANKETCEVAP WHERE ANKETID='7' AND KULLANICINO = @KNO AND SORUNO=@SNO
-- SET @SONUC=(SELECT CEVAPSIK FROM ANKETCEVAP WHERE ANKETID='7' AND KULLANICINO = @KNO AND SORUNO=@SNO)
-- SELECT @vSQL = 'UPDATE #rapor SET S'+ convert(varchar, @SNO)+'='''+ convert(varchar, @SONUC)+''' where ID='''+ convert(varchar, @KNO) + ''''
-- Execute (@vSQL)
-- FETCH NEXT FROM ankcev_cursor INTO @KNO
-- END
--
-- CLOSE ankcev_cursor
-- DEALLOCATE ankcev_cursor
SET @SNO = @SNO+1;
END
PRINT @vSQL
PRINT @SNO ------ALWAYS WRITE S10 why stay same value ????
Bob
Ashfield Consultants Ltd
|
|
|
|
|
ok ok i see now my mistake
SELECT KULLANICINO FROM AnketCevap WHERE ANKETID='7' and <soruno> i forget to add this
so it takes to long times to return back ))
anyway thanks for your patience on me )) thank a lot...
Sampiyon FENERBAHCE
|
|
|
|
|
Thats OK
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi
QUESTION:
Why is the code running so slow and what can I do to improve it?
THE CODE:
$currentTable = "SS_products";
$q1 = db_query("SELECT FLOOR(RAND() * COUNT(*)) AS rand_row FROM ".
$currentTable) or die (db_error());
$row1 = db_fetch_row($q1);
$q2 = db_query("SELECT categoryID, name, default_picture FROM ".
$currentTable." WHERE categoryID=7 Limit $row1[0], 1") or die (db_error());
Background Infomation on the Code:
I have a website where I use a shopping cart system called ShopScript Premium. This system works more then fine with my current webhost BlueHost. But a problem arises when I add customized the code.
I have added to the website home page that every time the browser is refreshed 10 new random products will be the customers. The ShopScript system only offers products which are to be changed by the administrator manually. When the code for the random product is executed the website runs considerably slower then without the random products, although sometimes and does run with no problems and fast speed for a minute or so. Even if the user is using the other pages the website is still slow. I suspect this has to do with the fact that the ShopScript system uses a smarty engine, although that does not fully explain why.
In my database the table called has 1373 rows. I use the code above to randomly select one of the rows to be viewed on the website. According to my log on mySQL slow queries, the above mentioned code, is the source of the problems. The log states that the code uses 2 to 4 seconds query time. My webhost does not allow any single process taking more than 30 CPU seconds to run or if the total processes take more than 40 CPU seconds in any 60 second window to avoid server lockups. Such an event triggers a CPU Quota/Suspension Error, where my website is accessable for 5 to 10 minutes. This is a big problem as I lose customers this way.
On before hand
Thank you for your help
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
