want Single table result not in multiple tables in sql server 2000
-
Hi.
I want to gets records from sql2000 database and i am not able to get the rignt resule.
here is the querry.
DECLARE @NodeAccountCode as varchar(128)
DECLARE @JobID as varchar(128)
DECLARE @BetweenLower AS INT
DECLARE @BetweenHigher AS INT
DECLARE @Count AS INT
DECLARE @Time AS varchar(128)
DECLARE @LowerTime AS varchar(128)
DECLARE @HigherTime AS varchar(128)
DECLARE @MaximumDuration AS INT
SET @NodeAccountCode='Datcom.computers'
SET @JobID='060727191600'
SET @BetweenLower = 0
SET @BetweenHigher = @BetweenLower + 10
SET @Count = 0
SET @LowerTime = @BetweenLower
SET @HigherTime = @BetweenHigher
SET @Time = @LowerTime + '-' + @HigherTime
SELECT @MaximumDuration = MAX(duration) FROM MyTableName
WHERE TS_Last_MDA_Note = @JobID AND originator = @NodeAccountCode
--SELECT @MaximumDuration as [Maximum Duration]
--TRUNCATE TABLE MAY
--SELECT * FROM MAY
WHILE (@Count < @MaximumDuration + 10)
BEGIN
IF EXISTS (
SELECT @Time AS [Time], COUNT(duration)AS [Count],cost_center AS [JobID],Originator AS [NodeAccountCode], cast(replace(Normalized_Addr,'PUSHIVR:','')as varchar(30)) as Address
FROM MyTableName
WHERE duration BETWEEN @BetweenLower AND @BetweenHigher AND cost_center = @JobID
AND originator = @NodeAccountCode AND INT_STATE_MASKED = 650 AND DURATION > 0GROUP BY cost_center,Originator,Normalized_Addr
)
BEGIN
--INSERT INTO MAY SELECT @Time AS [Time], COUNT(duration)AS [Count],cost_center AS [JobID],Originator AS [NodeAccountCode],cast(replace(Normalized_Addr,'PUSHIVR:','')as varchar(30)) as Address
select @Time AS [Time], COUNT(duration)AS [Count],cost_center AS [JobID],Originator AS [NodeAccountCode],cast(replace(Normalized_Addr,'PUSHIVR:','')as varchar(30)) as Address
FROM MyTablename
WHERE duration BETWEEN @BetweenLower AND @BetweenHigher AND cost_center = @JobID
AND originator = @NodeAccountCode AND INT_STATE_MASKED = 650 AND DURATION > 0
GROUP BY cost_center ,Originator,Normalized_addr
CONCAT
SET @BetweenLower = @BetweenHigher + 1
SET @BetweenHigher = @BetweenLower + 9
SET @LowerTime = @BetweenLower
SET @HigherTime = @BetweenHigher
SET @Time = @LowerTime + '-' + @HigherTime
SET @Count = @Count + 10
END
ELSE
BEGIN
SET @BetweenLower = @BetweenHigher + 1
SET @BetweenHigher = @BetweenLower + 9
SET @LowerTime = @BetweenLower
SET @HigherTime = @BetweenHigher
SET @Time = @LowerTime + '-' + @HigherTime
SET @Count = @Count + 10
END
--SELECT @BetweenLower,@BetweenHigher,@Time
END
-----------------------------------------------------------------
If i select the data i get different tables but i want the query to return all the data in one table.
so i tried inserting values to a temp table and using them but when there are two people requesting same data one has to wait. and is not efficient.
so if anybody has a solution for this. please help me out.
Regards,
Forforums.
-
What is the CONCAT function?
N 56°04'39.16"
E 12°55'05.25" -
This code does the same thing...
DECLARE @NodeAccountCode as varchar(128),
@JobID as varchar(128)
DECLARE @BetweenLower AS INT,
@BetweenHigher AS INT,
@Count AS INT,
@Time AS varchar(128),
@LowerTime AS varchar(128),
@HigherTime AS varchar(128),
@MaximumDuration AS INT
SELECT @NodeAccountCode='Datcom.computers',
@JobID='060727191600',
@BetweenLower = 0,
@BetweenHigher = @BetweenLower + 10,
@Count = 0,
@LowerTime = @BetweenLower,
@HigherTime = @BetweenHigher,
@Time = @LowerTime + '-' + @HigherTime,
SELECT @MaximumDuration = MAX(duration)
FROM MyTableName
WHERE TS_Last_MDA_Note = @JobID
AND originator = @NodeAccountCode
WHILE (@Count < @MaximumDuration + 10)
BEGIN
select @Time AS [Time],
COUNT(duration) AS [Count],
cost_center AS [JobID],
Originator AS [NodeAccountCode],
cast(replace(Normalized_Addr, 'PUSHIVR:', '') as varchar(30)) as Address
FROM MyTablename
WHERE duration BETWEEN @BetweenLower AND @BetweenHigher
AND cost_center = @JobID
AND originator = @NodeAccountCode
AND INT_STATE_MASKED = 650
AND DURATION > 0
GROUP BY cost_center,
Originator,
Normalized_addr
SELECT @BetweenLower = @BetweenHigher + 1,
@BetweenHigher = @BetweenLower + 9,
@LowerTime = @BetweenLower,
@HigherTime = @BetweenHigher,
@Time = @LowerTime + '-' + @HigherTime,
@Count = @Count + 10
END
N 56°04'39.16"
E 12°55'05.25" -
Hi Peter Larsson.
Thanks for your reply.
The query you have submitted does the same thing as of i had submitted, but the probles is that i want the result in a single table. when i run this querry it returns 1 table for the first while loop execution. then a another table for second loop and on and on.
Result.
and i want that it should display the result but in one result set.
not in two different tables.
if you could help me out it would be of great help.
Regards,
forforums
-
Then put an INSERT statement just before my SELECT statement and the case should be closed.
N 56°04'39.16"
E 12°55'05.25" -
Hi Peter Larsson.
Thanks for your reply.
I have already tried this but the problen that i face is that. when there are more than 1 clients accessing the database.
for example.
1st person requested the data the insert clause will insert the data related to the 1st person. now if a 2nd person comes and requests data then the 1st persons data is replaced with the 2nd persons data and if the first person hits a refresh button then he will be getting the 2nd persons data. and the other option is that i should recall the database and re insert the values on every refresh. this can be a temperory solution but is not effective. It reduces the sql server performance and also the response time is increased. and in peak access time it creates lot of problems.
Regards,
forforums
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply