August 9, 2009 at 1:23 am
hi firends
how can i mix up multi result set to one ?
i mean :
declare @first int,
@second int
set @first = 0
set @second = 1
while @first < 23
begin
select
Id,name,family
FROM record Where Time BETWEEN @first AND @second
set @first= @first + 1
set @second = @second + 1
end
[/code]
above TSQL command return 10 seprated result,how can i have all in one result (with 10 rows)?
August 9, 2009 at 3:20 am
dr_csharp (8/9/2009)
while @first < 23
begin
select Id,name,family
FROM record Where Time BETWEEN @first AND @second
set @first= @first + 1
set @second = @second + 1
end
Reading between the lines to see what it is you want as an end result, how about:
SELECT Id, Name, Family FROM record ORDER BY DATEPART(HOUR, Time) ASC;
The first challenge in writing good SQL is to unlearn procedural programming 🙂
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 9, 2009 at 3:34 am
no,it was just as an example..i mean just i have some result with same columns but in seprate resultset(because each one belong to another select ) and i wanna mix these up !
now,i create a temp table and insert each select to that temp table and at the end i select * from temptable..
i was looking for a better way..
the real code come below :
IF Exists(select * FROM tempdb.sys.tables WHERE Type = 'U' and name like '#temp%')
drop table #temp
create table #temp
(
Incomming varchar(5),
Outgoing varchar(5),
Intercome varchar(5),
Total varchar(5),
Period varchar(10)
)
declare @first int,
@second int,
@period varchar(10)
set @first = 0
set @second = 1
while @first < 23
begin
set @period=CAST(@first AS varchar)+' to '+CAST(@second AS varchar)
Insert into #temp
select
sum(case when IsIncomming=1 then duration Else 0 END)AS Incomming,
sum(case when IsIncomming=0 then duration Else 0 END)AS Outgoing,
sum(case when IsIntercome=1 then duration Else 0 END)AS Intercome,
sum(CAST(duration AS INT))AS Total,
@period AS period
FROM RECORD
Where CAST(SUBSTRING([Time],1,2) AS INT) BETWEEN @first AND @second
set @first= @first + 1
set @second = @second + 1
end
select * from #temp
August 9, 2009 at 4:38 am
Try this then. I took the liberty of creating some example data.
USE tempdb;
SET NOCOUNT ON;
GO
IF OBJECT_ID(N'tempdb.dbo.Record', N'U') IS NOT NULL DROP TABLE dbo.Record;
IF OBJECT_ID(N'tempdb.dbo.#Temp', N'U') IS NOT NULL DROP TABLE #Temp;
GO
-- Test tables
CREATE TABLE dbo.Record
(
[Time] DATETIME NOT NULL,
Duration INTEGER NOT NULL,
IsIncoming BIT NOT NULL,
IsIntercome BIT NOT NULL,
HourPart AS (DATEPART(HOUR, [Time])) PERSISTED NOT NULL,
);
GO
CREATE TABLE #Temp
(
Period VARCHAR(8) NOT NULL,
Incoming INTEGER NOT NULL,
Outgoing INTEGER NOT NULL,
Intercome INTEGER NOT NULL,
Total INTEGER NOT NULL,
);
GO
-- Some sample data...
INSERT dbo.Record ([Time], Duration, IsIncoming, IsIntercome) VALUES ('00:01:34', 78, 1, 1);
INSERT dbo.Record ([Time], Duration, IsIncoming, IsIntercome) VALUES ('00:32:17', 54, 0, 1);
INSERT dbo.Record ([Time], Duration, IsIncoming, IsIntercome) VALUES ('01:16:21', 23, 1, 1);
INSERT dbo.Record ([Time], Duration, IsIncoming, IsIntercome) VALUES ('01:34:19', 18, 0, 1);
INSERT dbo.Record ([Time], Duration, IsIncoming, IsIntercome) VALUES ('02:15:23', 91, 1, 1);
INSERT dbo.Record ([Time], Duration, IsIncoming, IsIntercome) VALUES ('02:37:07', 27, 1, 1);
INSERT dbo.Record ([Time], Duration, IsIncoming, IsIntercome) VALUES ('02:44:54', 42, 0, 1);
INSERT dbo.Record ([Time], Duration, IsIncoming, IsIntercome) VALUES ('03:01:13', 47, 0, 1);
INSERT dbo.Record ([Time], Duration, IsIncoming, IsIntercome) VALUES ('03:26:48', 16, 1, 1);
INSERT dbo.Record ([Time], Duration, IsIncoming, IsIntercome) VALUES ('04:52:01', 33, 1, 1);
INSERT dbo.Record ([Time], Duration, IsIncoming, IsIntercome) VALUES ('04:54:14', 90, 0, 1);
INSERT dbo.Record ([Time], Duration, IsIncoming, IsIntercome) VALUES ('05:22:00', 74, 1, 1);
INSERT dbo.Record ([Time], Duration, IsIncoming, IsIntercome) VALUES ('05:39:42', 56, 0, 1);
INSERT dbo.Record ([Time], Duration, IsIncoming, IsIntercome) VALUES ('06:07:14', 19, 0, 1);
INSERT dbo.Record ([Time], Duration, IsIncoming, IsIntercome) VALUES ('06:11:24', 87, 1, 1);
INSERT dbo.Record ([Time], Duration, IsIncoming, IsIntercome) VALUES ('06:14:48', 63, 1, 1);
INSERT dbo.Record ([Time], Duration, IsIncoming, IsIntercome) VALUES ('06:22:09', 07, 1, 1);
INSERT dbo.Record ([Time], Duration, IsIncoming, IsIntercome) VALUES ('06:37:31', 44, 0, 1);
INSERT dbo.Record ([Time], Duration, IsIncoming, IsIntercome) VALUES ('07:01:57', 68, 1, 1);
INSERT dbo.Record ([Time], Duration, IsIncoming, IsIntercome) VALUES ('07:02:01', 39, 0, 1);
GO
-- The insert
WITH Period (N)
AS (
SELECT TOP (24) ROW_NUMBER() OVER (ORDER BY number) - 1
FROM master.dbo.spt_values
)
INSERT #Temp (Period, Incoming, Outgoing, Intercome, Total)
SELECT Period = CONVERT(VARCHAR(2), P.N) + ' to ' + CONVERT(VARCHAR(2), P.N + 1),
Incoming = SUM(CASE WHEN R.IsIncoming = 1 THEN R.Duration ELSE 0 END),
Outgoing = SUM(CASE WHEN R.IsIncoming = 0 THEN R.Duration ELSE 0 END),
Intercome = SUM(CASE WHEN R.IsIntercome = 1 THEN R.Duration ELSE 0 END),
Total = SUM(R.Duration)
FROM Period P
JOIN dbo.Record R
ON (DATEPART(HOUR, R.[Time]) = P.N)
GROUP BY
P.N, DATEPART(HOUR, R.[Time]);
GO
-- Results
SELECT Period, Incoming, Outgoing, Intercome, Total
FROM #Temp;
GO
-- Tidy up
IF OBJECT_ID(N'tempdb.dbo.Record', N'U') IS NOT NULL DROP TABLE dbo.Record;
IF OBJECT_ID(N'tempdb.dbo.#Temp', N'U') IS NOT NULL DROP TABLE #Temp;
GO
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 9, 2009 at 11:10 am
Paul White (8/9/2009)
Try this then. I took the liberty of creating some example data.
You're a generous man. You must be bored today. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
August 9, 2009 at 5:10 pm
Jeff Moden (8/9/2009)
You're a generous man. You must be bored today. 😉
:laugh:
Yes I surprised myself. I think it was the momentum from my first guess-answer that carried me!
I will not be making a habit of it 😎
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 9, 2009 at 9:54 pm
Paul White (8/9/2009)
Try this then. I took the liberty of creating some example data.
USE tempdb;
SET NOCOUNT ON;
GO
IF OBJECT_ID(N'tempdb.dbo.Record', N'U') IS NOT NULL DROP TABLE dbo.Record;
IF OBJECT_ID(N'tempdb.dbo.#Temp', N'U') IS NOT NULL DROP TABLE #Temp;
GO
-- Test tables
CREATE TABLE dbo.Record
(
[Time] DATETIME NOT NULL,
Duration INTEGER NOT NULL,
IsIncoming BIT NOT NULL,
IsIntercome BIT NOT NULL,
HourPart AS (DATEPART(HOUR, [Time])) PERSISTED NOT NULL,
);
GO
CREATE TABLE #Temp
(
Period VARCHAR(8) NOT NULL,
Incoming INTEGER NOT NULL,
Outgoing INTEGER NOT NULL,
Intercome INTEGER NOT NULL,
Total INTEGER NOT NULL,
);
GO
-- Some sample data...
INSERT dbo.Record ([Time], Duration, IsIncoming, IsIntercome) VALUES ('00:01:34', 78, 1, 1);
INSERT dbo.Record ([Time], Duration, IsIncoming, IsIntercome) VALUES ('00:32:17', 54, 0, 1);
-- The insert
WITH Period (N)
AS (
SELECT TOP (24) ROW_NUMBER() OVER (ORDER BY number) - 1
FROM master.dbo.spt_values
)INSERT #Temp (Period, Incoming, Outgoing, Intercome, Total)
SELECT Period = CONVERT(VARCHAR(2), P.N) + ' to ' + CONVERT(VARCHAR(2), P.N + 1),
Incoming = SUM(CASE WHEN R.IsIncoming = 1 THEN R.Duration ELSE 0 END),
Outgoing = SUM(CASE WHEN R.IsIncoming = 0 THEN R.Duration ELSE 0 END),
Intercome = SUM(CASE WHEN R.IsIntercome = 1 THEN R.Duration ELSE 0 END),
Total = SUM(R.Duration)
FROM Period P
JOIN dbo.Record R
ON (DATEPART(HOUR, R.[Time]) = P.N)
GROUP BY
P.N, DATEPART(HOUR, R.[Time]);
GO
-- Results
SELECT Period, Incoming, Outgoing, Intercome, Total
FROM #Temp;
GO
-- Tidy up
IF OBJECT_ID(N'tempdb.dbo.Record', N'U') IS NOT NULL DROP TABLE dbo.Record;
IF OBJECT_ID(N'tempdb.dbo.#Temp', N'U') IS NOT NULL DROP TABLE #Temp;
GO
Paul
you use with instead while loop,why ?
August 9, 2009 at 10:09 pm
dr_csharp (8/9/2009)
you use with instead while loop,why?
I use a common table expression (the WITH clause) to dynamically generate a table with the 24 values required. That allows the query to be written as a set-based operation, rather than a WHILE 'cursor'.
Many professionals keep a table in the model database which just contains a sequence of integers. This is variously known as an Iterator, Sequence, or Tally table. Being in model, it is created in tempdb every time the server starts up, and comes in handy for all kinds of operations.
Rather than assume that you have such a table to hand, I generate one on the fly.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 10, 2009 at 5:25 am
For more information on how such a table as Paul speaks of works to replace a While Loop, please see the following article...
http://www.sqlservercentral.com/articles/T-SQL/62867/
--Jeff Moden
Change is inevitable... Change for the better is not.
August 10, 2009 at 5:28 am
Jeff Moden (8/10/2009)
For more information on how such a table as Paul speaks of works to replace a While Loop, please see the following article...
Yes, absolutely! Sorry Jeff, I had a perfect excuse to quote your awesome article and failed! 🙁
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 10, 2009 at 5:48 am
Heh... no problem, Paul. The OP's question of "you use with instead while loop,why?" reminded me of the very first time I saw such a thing except my question was "Now WHAT the heck is THAT?" 🙂 It didn't take me long to figure out. Based on the op's question, I figured it was taking him a bit longer and didn't want him to get away without a clear understanding of the tool.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 10, 2009 at 5:55 am
Jeff Moden (8/10/2009)
Heh... no problem, Paul. The OP's question of "you use with instead while loop,why?" reminded me of the very first time I saw such a thing except my question was "Now WHAT the heck is THAT?" 🙂 It didn't take me long to figure out. Based on the op's question, I figured it was taking him a bit longer and didn't want him to get away without a clear understanding of the tool.
Thanks friends,Jeff and Paul :O)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply