April 30, 2012 at 1:38 pm
I should have also included that you need to
close FlightClosedList
deallocate FlightClosedList
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 30, 2012 at 1:43 pm
wmaa (4/30/2012)
why not do if the rows exists then copy to temp table then send email for all rows in temp table and exit when done? that was my thought but it doesn't like the variable declarations in the set rowcount variable.
You could do that but all you are doing is making another copy of the data, you still have to loop through it.
Keep in mind that all the code I have posted is totally blind as I have no ddl or sample data to work with.
Syntax wise this is perfectly fine.
declare @flightId int
declare @flightTime DECIMAL(10,2)
declare @blockTime DECIMAL(10,2)
declare @ttEngine1 DECIMAL(10,2)
declare @tcEngine1 DECIMAL(10,2)
declare @ttEngine2 DECIMAL(10,2)
declare @tcEngine2 DECIMAL(10,2)
declare @ttAirframe DECIMAL(10,2)
declare @tcAirframe DECIMAL(10,2)
declare @profileName nvarchar(128)= 'I'
declare @To nvarchar(255) = 'to'
declare @From nvarchar(255) ='from'
declare @Subject nvarchar(128)= 'Flight Closed'
declare @Message varchar(2000)
declare @MessageCount int = 0 --added this so you can track the number of messages sent.
--added select to see what the result set should look like.
--SELECT a.flightId,a.FlightTime,a.BlockTime,a.TTEngine1,a.TCEngine1,a.TTEngine2,a.TCEngine2,a.TTAirframe,a.TCAirframe
--FROM flightsClosed as a
--left JOIN flightsClosed2 b
--ON a.FlightID = b.FlightID
--WHERE a.StatusTypeID != b.StatusTypeID
--AND a.StatusTypeID = 44
declare FlightClosedList cursor for
SELECT 1, 10, 10, 10, 10, 10, 10, 10, 10 union all
SELECT 1, 100, 100, 100, 100, 100, 100, 100, 100
--a.flightId,a.FlightTime,a.BlockTime,a.TTEngine1,a.TCEngine1,a.TTEngine2,a.TCEngine2,a.TTAirframe,a.TCAirframe
--FROM flightsClosed as a
--left JOIN flightsClosed2 b
--ON a.FlightID = b.FlightID
--WHERE a.StatusTypeID != b.StatusTypeID
--AND a.StatusTypeID = 44
print 'Is the time slow for the above query?'
open FlightClosedList
fetch next from FlightClosedList into @flightId, @flightTime, @blockTime, @ttEngine1, @tcEngine1, @ttEngine2, @tcEngine2, @ttAirframe, @tcAirframe
while @@FETCH_STATUS = 0
begin
set @MessageCount = @MessageCount + 1
set @Message = 'Flight ID: ' + Cast(@flightId as varchar(50)) + ' is now closed.'
+ CHAR(13) + CHAR(13) +
'Actual Flight: ' + Cast(@flightTime as varchar(50)) + CHAR(13) +
'Actual Block: ' + Cast(@blockTime as varchar(50)) + CHAR(13) +
'Hours End: ' + Cast(@ttAirframe as varchar(50)) + CHAR(13) +
'Landings End: ' + Cast(@tcAirframe as varchar(50)) + CHAR(13) +
'Engine 1 Hours End: ' + Cast(@ttEngine1 as varchar(50)) + CHAR(13) +
'Engine 1 Cycles End: ' + Cast(@tcEngine1 as varchar(50)) + CHAR(13) +
'Engine 2 Hours End: ' + Cast(@TTEngine2 as varchar(50)) + CHAR(13) +
'Engine 2 Cycles End: ' + Cast(@TCEngine2 as varchar(50))
--exec msdb.dbo.sp_send_dbmail
--@profile_name = @profilename,
--@recipients = @To,
--@from_address = @From,
--@subject = @Subject,
--@body = @Message
select @Message
--need to fetch the next record INSIDE the cursor
fetch next from FlightClosedList into @flightId, @flightTime, @blockTime, @ttEngine1, @tcEngine1, @ttEngine2, @tcEngine2, @ttAirframe, @tcAirframe
end
close FlightClosedList
deallocate FlightClosedList
PRINT cast(@MessageCount as varchar(5)) + ' Message(s) sent'
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 30, 2012 at 1:49 pm
so that sent back 2 emails:
Flight ID: 1 is now closed.
Actual Flight: 10.00
Actual Block: 10.00
Hours End: 10.00
Landings End: 10.00
Engine 1 Hours End: 10.00
Engine 1 Cycles End: 10.00
Engine 2 Hours End: 10.00
Engine 2 Cycles End: 10.00
---------
Flight ID: 1 is now closed.
Actual Flight: 100.00
Actual Block: 100.00
Hours End: 100.00
Landings End: 100.00
Engine 1 Hours End: 100.00
Engine 1 Cycles End: 100.00
Engine 2 Hours End: 100.00
Engine 2 Cycles End: 100.00
for:
--SELECT 1, 10, 10, 10, 10, 10, 10, 10, 10 union all
--SELECT 1, 100, 100, 100, 100, 100, 100, 100, 100
but when i try to query for the real data it doesn't work
April 30, 2012 at 1:53 pm
I guess that demonstrates that the cursor is working. Short of you posting ddl and sample data there is really nothing else I can do to help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 30, 2012 at 1:55 pm
true
recidFlightIDStatusTypeIDAircraftIDFlightTimeBlockTimeTTEngine1TCEngine1TTEngine2TCEngine2RegistrationTTAirframeTCairframerecidFlightIDStatusTypeIDAircraftIDFlightTimeBlockTimeTTEngine1TCEngine1TTEngine2TCEngine2RegistrationTTAirframeTCairframe
118181012520154442.302.502884.802321.002702.102040.00N106SL8849.006351.0070882512520153742.302.402884.802321.002702.102040.00N106SL8846.706350.00
118194712530314440.600.902884.202320.002701.502039.00N106SL8849.006351.0070896212530311440.600.902884.202320.002701.502039.00N106SL8846.706350.00
April 30, 2012 at 1:56 pm
both tables are the same minus the names.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[flightsClosed](
[recid] [int] IDENTITY(1,1) NOT NULL,
[FlightID] [int] NULL,
[StatusTypeID] [int] NOT NULL,
[AircraftID] [int] NULL,
[FlightTime] [decimal](10, 2) NULL,
[BlockTime] [decimal](10, 2) NULL,
[TTEngine1] [decimal](10, 2) NULL,
[TCEngine1] [decimal](10, 2) NULL,
[TTEngine2] [decimal](10, 2) NULL,
[TCEngine2] [decimal](10, 2) NULL,
[Registration] [nvarchar](6) NULL,
[TTAirframe] [decimal](10, 2) NULL,
[TCairframe] [decimal](10, 2) NULL
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[flightsClosed2](
[recid] [int] IDENTITY(1,1) NOT NULL,
[FlightID] [int] NULL,
[StatusTypeID] [int] NOT NULL,
[AircraftID] [int] NULL,
[FlightTime] [decimal](10, 2) NULL,
[BlockTime] [decimal](10, 2) NULL,
[TTEngine1] [decimal](10, 2) NULL,
[TCEngine1] [decimal](10, 2) NULL,
[TTEngine2] [decimal](10, 2) NULL,
[TCEngine2] [decimal](10, 2) NULL,
[Registration] [nvarchar](6) NULL,
[TTAirframe] [decimal](10, 2) NULL,
[TCairframe] [decimal](10, 2) NULL
) ON [PRIMARY]
GO
April 30, 2012 at 1:57 pm
wmaa (4/30/2012)
truerecidFlightIDStatusTypeIDAircraftIDFlightTimeBlockTimeTTEngine1TCEngine1TTEngine2TCEngine2RegistrationTTAirframeTCairframerecidFlightIDStatusTypeIDAircraftIDFlightTimeBlockTimeTTEngine1TCEngine1TTEngine2TCEngine2RegistrationTTAirframeTCairframe
118181012520154442.302.502884.802321.002702.102040.00N106SL8849.006351.0070882512520153742.302.402884.802321.002702.102040.00N106SL8846.706350.00
118194712530314440.600.902884.202320.002701.502039.00N106SL8849.006351.0070896212530311440.600.902884.202320.002701.502039.00N106SL8846.706350.00
Any chance you can turn that into insert statements for me?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 30, 2012 at 2:03 pm
INSERT INTO [dbo].[flightsClosed]
([FlightID]
,[StatusTypeID]
,[AircraftID]
,[FlightTime]
,[BlockTime]
,[TTEngine1]
,[TCEngine1]
,[TTEngine2]
,[TCEngine2]
,[Registration]
,[TTAirframe]
,[TCairframe])
VALUES
(1252015
,44
,4
,4
,2.30
,2.50
,2884.80
,2321.00
,2702.10
,2040.00
,N106sl
,8849.00
,6351.00)
GO
INSERT INTO [dbo].[flightsClosed]
([FlightID]
,[StatusTypeID]
,[AircraftID]
,[FlightTime]
,[BlockTime]
,[TTEngine1]
,[TCEngine1]
,[TTEngine2]
,[TCEngine2]
,[Registration]
,[TTAirframe]
,[TCairframe])
VALUES
(1253031
,44
,4
,4
,2.30
,2.50
,2884.80
,2321.00
,2702.10
,2040.00
,N106sl
,8849.00
,6351.00)
GO
INSERT INTO [dbo].[flightsClosed2]
([FlightID]
,[StatusTypeID]
,[AircraftID]
,[FlightTime]
,[BlockTime]
,[TTEngine1]
,[TCEngine1]
,[TTEngine2]
,[TCEngine2]
,[Registration]
,[TTAirframe]
,[TCairframe])
VALUES
(1252015
,45
,4
,4
,2.30
,2.50
,2884.80
,2321.00
,2702.10
,2040.00
,N106sl
,8849.00
,6351.00)
GO
INSERT INTO [dbo].[flightsClosed2]
([FlightID]
,[StatusTypeID]
,[AircraftID]
,[FlightTime]
,[BlockTime]
,[TTEngine1]
,[TCEngine1]
,[TTEngine2]
,[TCEngine2]
,[Registration]
,[TTAirframe]
,[TCairframe])
VALUES
(1253031
,45
,4
,4
,2.30
,2.50
,2884.80
,2321.00
,2702.10
,2040.00
,N106sl
,8849.00
,6351.00)
GO
April 30, 2012 at 2:24 pm
Something is not quite right with those.
Msg 110, Level 15, State 1, Line 1
There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.
Msg 207, Level 16, State 1, Line 3
Invalid column name 'N106sl'.
Msg 110, Level 15, State 1, Line 3
There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.
Msg 207, Level 16, State 1, Line 5
Invalid column name 'N106sl'.
Msg 110, Level 15, State 1, Line 5
There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.
Msg 207, Level 16, State 1, Line 3
Invalid column name 'N106sl'.
Msg 110, Level 15, State 1, Line 3
There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 30, 2012 at 2:28 pm
quotes
INSERT INTO [dbo].[flightsClosed]
([FlightID]
,[StatusTypeID]
,[AircraftID]
,[FlightTime]
,[BlockTime]
,[TTEngine1]
,[TCEngine1]
,[TTEngine2]
,[TCEngine2]
,[Registration]
,[TTAirframe]
,[TCairframe])
VALUES
(1252015
,44
,4
,4
,2.30
,2.50
,2884.80
,2321.00
,2702.10
,2040.00
,'N106sl'
,8849.00
,6351.00)
GO
INSERT INTO [dbo].[flightsClosed]
([FlightID]
,[StatusTypeID]
,[AircraftID]
,[FlightTime]
,[BlockTime]
,[TTEngine1]
,[TCEngine1]
,[TTEngine2]
,[TCEngine2]
,[Registration]
,[TTAirframe]
,[TCairframe])
VALUES
(1253031
,44
,4
,4
,2.30
,2.50
,2884.80
,2321.00
,2702.10
,2040.00
,'N106sl'
,8849.00
,6351.00)
GO
INSERT INTO [dbo].[flightsClosed2]
([FlightID]
,[StatusTypeID]
,[AircraftID]
,[FlightTime]
,[BlockTime]
,[TTEngine1]
,[TCEngine1]
,[TTEngine2]
,[TCEngine2]
,[Registration]
,[TTAirframe]
,[TCairframe])
VALUES
(1252015
,45
,4
,4
,2.30
,2.50
,2884.80
,2321.00
,2702.10
,2040.00
,'N106sl'
,8849.00
,6351.00)
GO
INSERT INTO [dbo].[flightsClosed2]
([FlightID]
,[StatusTypeID]
,[AircraftID]
,[FlightTime]
,[BlockTime]
,[TTEngine1]
,[TCEngine1]
,[TTEngine2]
,[TCEngine2]
,[Registration]
,[TTAirframe]
,[TCairframe])
VALUES
(1253031
,45
,4
,4
,2.30
,2.50
,2884.80
,2321.00
,2702.10
,2040.00
,'N106sl'
,8849.00
,6351.00)
GO
April 30, 2012 at 2:34 pm
wmaa (4/30/2012)
quotes
That was a problem but the counts are still off.
Msg 110, Level 15, State 1, Line 2
There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.
Msg 110, Level 15, State 1, Line 3
There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.
Msg 110, Level 15, State 1, Line 5
There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.
Msg 110, Level 15, State 1, Line 3
There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 30, 2012 at 2:37 pm
dang
INSERT INTO [dbo].[flightsClosed]
([FlightID]
,[StatusTypeID]
,[AircraftID]
,[FlightTime]
,[BlockTime]
,[TTEngine1]
,[TCEngine1]
,[TTEngine2]
,[TCEngine2]
,[Registration]
,[TTAirframe]
,[TCairframe])
VALUES
(1252015
,44
,4
,2.30
,2.50
,2884.80
,2321.00
,2702.10
,2040.00
,'N106sl'
,8849.00
,6351.00)
GO
INSERT INTO [dbo].[flightsClosed]
([FlightID]
,[StatusTypeID]
,[AircraftID]
,[FlightTime]
,[BlockTime]
,[TTEngine1]
,[TCEngine1]
,[TTEngine2]
,[TCEngine2]
,[Registration]
,[TTAirframe]
,[TCairframe])
VALUES
(1253031
,44
,4
,2.30
,2.50
,2884.80
,2321.00
,2702.10
,2040.00
,'N106sl'
,8849.00
,6351.00)
GO
INSERT INTO [dbo].[flightsClosed2]
([FlightID]
,[StatusTypeID]
,[AircraftID]
,[FlightTime]
,[BlockTime]
,[TTEngine1]
,[TCEngine1]
,[TTEngine2]
,[TCEngine2]
,[Registration]
,[TTAirframe]
,[TCairframe])
VALUES
(1252015
,45
,4
,2.30
,2.50
,2884.80
,2321.00
,2702.10
,2040.00
,'N106sl'
,8849.00
,6351.00)
GO
INSERT INTO [dbo].[flightsClosed2]
([FlightID]
,[StatusTypeID]
,[AircraftID]
,[FlightTime]
,[BlockTime]
,[TTEngine1]
,[TCEngine1]
,[TTEngine2]
,[TCEngine2]
,[Registration]
,[TTAirframe]
,[TCairframe])
VALUES
(1253031
,45
,4
,2.30
,2.50
,2884.80
,2321.00
,2702.10
,2040.00
,'N106sl'
,8849.00
,6351.00)
GO
April 30, 2012 at 2:42 pm
hehe no worries. 😛
Now if I take your ddl and sample data. Then run my code it works just fine.
CREATE TABLE [dbo].[flightsClosed](
[recid] [int] IDENTITY(1,1) NOT NULL,
[FlightID] [int] NULL,
[StatusTypeID] [int] NOT NULL,
[AircraftID] [int] NULL,
[FlightTime] [decimal](10, 2) NULL,
[BlockTime] [decimal](10, 2) NULL,
[TTEngine1] [decimal](10, 2) NULL,
[TCEngine1] [decimal](10, 2) NULL,
[TTEngine2] [decimal](10, 2) NULL,
[TCEngine2] [decimal](10, 2) NULL,
[Registration] [nvarchar](6) NULL,
[TTAirframe] [decimal](10, 2) NULL,
[TCairframe] [decimal](10, 2) NULL
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[flightsClosed2](
[recid] [int] IDENTITY(1,1) NOT NULL,
[FlightID] [int] NULL,
[StatusTypeID] [int] NOT NULL,
[AircraftID] [int] NULL,
[FlightTime] [decimal](10, 2) NULL,
[BlockTime] [decimal](10, 2) NULL,
[TTEngine1] [decimal](10, 2) NULL,
[TCEngine1] [decimal](10, 2) NULL,
[TTEngine2] [decimal](10, 2) NULL,
[TCEngine2] [decimal](10, 2) NULL,
[Registration] [nvarchar](6) NULL,
[TTAirframe] [decimal](10, 2) NULL,
[TCairframe] [decimal](10, 2) NULL
) ON [PRIMARY]
GO
INSERT INTO [dbo].[flightsClosed]
([FlightID]
,[StatusTypeID]
,[AircraftID]
,[FlightTime]
,[BlockTime]
,[TTEngine1]
,[TCEngine1]
,[TTEngine2]
,[TCEngine2]
,[Registration]
,[TTAirframe]
,[TCairframe])
VALUES
(1252015
,44
,4
,2.30
,2.50
,2884.80
,2321.00
,2702.10
,2040.00
,'N106sl'
,8849.00
,6351.00)
GO
INSERT INTO [dbo].[flightsClosed]
([FlightID]
,[StatusTypeID]
,[AircraftID]
,[FlightTime]
,[BlockTime]
,[TTEngine1]
,[TCEngine1]
,[TTEngine2]
,[TCEngine2]
,[Registration]
,[TTAirframe]
,[TCairframe])
VALUES
(1253031
,44
,4
,2.30
,2.50
,2884.80
,2321.00
,2702.10
,2040.00
,'N106sl'
,8849.00
,6351.00)
GO
INSERT INTO [dbo].[flightsClosed2]
([FlightID]
,[StatusTypeID]
,[AircraftID]
,[FlightTime]
,[BlockTime]
,[TTEngine1]
,[TCEngine1]
,[TTEngine2]
,[TCEngine2]
,[Registration]
,[TTAirframe]
,[TCairframe])
VALUES
(1252015
,45
,4
,2.30
,2.50
,2884.80
,2321.00
,2702.10
,2040.00
,'N106sl'
,8849.00
,6351.00)
GO
INSERT INTO [dbo].[flightsClosed2]
([FlightID]
,[StatusTypeID]
,[AircraftID]
,[FlightTime]
,[BlockTime]
,[TTEngine1]
,[TCEngine1]
,[TTEngine2]
,[TCEngine2]
,[Registration]
,[TTAirframe]
,[TCairframe])
VALUES
(1253031
,45
,4
,2.30
,2.50
,2884.80
,2321.00
,2702.10
,2040.00
,'N106sl'
,8849.00
,6351.00)
GO
declare @flightId int
declare @flightTime DECIMAL(10,2)
declare @blockTime DECIMAL(10,2)
declare @ttEngine1 DECIMAL(10,2)
declare @tcEngine1 DECIMAL(10,2)
declare @ttEngine2 DECIMAL(10,2)
declare @tcEngine2 DECIMAL(10,2)
declare @ttAirframe DECIMAL(10,2)
declare @tcAirframe DECIMAL(10,2)
declare @profileName nvarchar(128)= 'I'
declare @To nvarchar(255) = 'to'
declare @From nvarchar(255) ='from'
declare @Subject nvarchar(128)= 'Flight Closed'
declare @Message varchar(2000)
declare @MessageCount int = 0 --added this so you can track the number of messages sent.
--added select to see what the result set should look like.
SELECT a.flightId,a.FlightTime,a.BlockTime,a.TTEngine1,a.TCEngine1,a.TTEngine2,a.TCEngine2,a.TTAirframe,a.TCAirframe
FROM flightsClosed as a
left JOIN flightsClosed2 b
ON a.FlightID = b.FlightID
WHERE a.StatusTypeID != b.StatusTypeID
AND a.StatusTypeID = 44
declare FlightClosedList cursor for
select
a.flightId,a.FlightTime,a.BlockTime,a.TTEngine1,a.TCEngine1,a.TTEngine2,a.TCEngine2,a.TTAirframe,a.TCAirframe
FROM flightsClosed as a
left JOIN flightsClosed2 b
ON a.FlightID = b.FlightID
WHERE a.StatusTypeID != b.StatusTypeID
AND a.StatusTypeID = 44
print 'Is the time slow for the above query?'
open FlightClosedList
fetch next from FlightClosedList into @flightId, @flightTime, @blockTime, @ttEngine1, @tcEngine1, @ttEngine2, @tcEngine2, @ttAirframe, @tcAirframe
while @@FETCH_STATUS = 0
begin
set @MessageCount = @MessageCount + 1
set @Message = 'Flight ID: ' + Cast(@flightId as varchar(50)) + ' is now closed.'
+ CHAR(13) + CHAR(13) +
'Actual Flight: ' + Cast(@flightTime as varchar(50)) + CHAR(13) +
'Actual Block: ' + Cast(@blockTime as varchar(50)) + CHAR(13) +
'Hours End: ' + Cast(@ttAirframe as varchar(50)) + CHAR(13) +
'Landings End: ' + Cast(@tcAirframe as varchar(50)) + CHAR(13) +
'Engine 1 Hours End: ' + Cast(@ttEngine1 as varchar(50)) + CHAR(13) +
'Engine 1 Cycles End: ' + Cast(@tcEngine1 as varchar(50)) + CHAR(13) +
'Engine 2 Hours End: ' + Cast(@TTEngine2 as varchar(50)) + CHAR(13) +
'Engine 2 Cycles End: ' + Cast(@TCEngine2 as varchar(50))
--exec msdb.dbo.sp_send_dbmail
--@profile_name = @profilename,
--@recipients = @To,
--@from_address = @From,
--@subject = @Subject,
--@body = @Message
select @Message
--need to fetch the next record INSIDE the cursor
fetch next from FlightClosedList into @flightId, @flightTime, @blockTime, @ttEngine1, @tcEngine1, @ttEngine2, @tcEngine2, @ttAirframe, @tcAirframe
end
close FlightClosedList
deallocate FlightClosedList
PRINT cast(@MessageCount as varchar(5)) + ' Message(s) sent'
Here is the output:
flightId FlightTime BlockTime TTEngine1 TCEngine1 TTEngine2 TCEngine2 TTAirframe TCAirframe
----------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
1252015 2.30 2.50 2884.80 2321.00 2702.10 2040.00 8849.00 6351.00
1253031 2.30 2.50 2884.80 2321.00 2702.10 2040.00 8849.00 6351.00
(2 row(s) affected)
(1 row(s) affected)
Is the time slow for the above query?
(1 row(s) affected)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Flight ID: 1252015 is now closed.
Actual Flight: 2.30
Actual Block: 2.50
Hours End: 8849.00
Landings End: 6351.00
Engine 1 Hours End: 2884.80
Engine 1 Cycles End: 2321.00
Engine 2 Hours End: 2702.10
Engine 2 Cycles End: 2040.00
(1 row(s) affected)
(1 row(s) affected)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Flight ID: 1253031 is now closed.
Actual Flight: 2.30
Actual Block: 2.50
Hours End: 8849.00
Landings End: 6351.00
Engine 1 Hours End: 2884.80
Engine 1 Cycles End: 2321.00
Engine 2 Hours End: 2702.10
Engine 2 Cycles End: 2040.00
(1 row(s) affected)
(1 row(s) affected)
2 Message(s) sent
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 30, 2012 at 2:49 pm
why the heck does it not work over here - like it's walking around the planet then to mars and back --- so don't get it
April 30, 2012 at 2:51 pm
i debug and when it reaches the fetch next from it goes into debugging query neverland..........
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply