question

Gehima2016 avatar image
Gehima2016 asked

table variables

Hi, Pls, check if my syntax is correct for the table variable below; USE TestDemo CREATE TABLE Flights ( FlightID int PRIMARY KEY NOT NULL, FlightDateTime datetime NULL, FlightDepartureCity varchar(50) NULL, FlightDestinationCity varchar(50) NULL, Ontime int NULL ) INSERT INTO [dbo].[Flights] (FlightDateTime, FlightDepartureCity, FlightDestinationCity, Ontime) SELECT '1/1/2012', 'Dallas-Texas', 'L.A.', 1 UNION SELECT '1/2/2012', 'Austin-Texas', 'New York', 1 UNION SELECT '1/3/2012', 'Houston-Texas', 'New Jersey', 0 UNION SELECT '1/4/2012', 'San Antonio-Texas', 'Mesquite', 1 UNION SELECT '1/5/2012', 'Lewisville-Texas', 'Albany', 0 UNION SELECT '1/6/2012', 'Orlando-Florida', 'Atlanta', 1 UNION SELECT '1/7/2012', 'Chicago-Illinois', 'Oklahoma City', 1 UNION SELECT '1/8/2012', 'New Orleans-Louisiana', 'Memphis', 0 UNION SELECT '1/9/2012', 'Miami-Florida', 'Charlotte', 1 UNION SELECT '1/10/2012', 'Sacramento-California', 'San Francisco', 1 --ASSUMING ONTIME BOOLEAN: 0 = LATE, 1 = ONTIME --Create and set a Variable equal to the number of Flights that were late DECLARE @NoOfLateFlights int SET @NoOfLateFlights = ( SELECT COUNT(*) FROM [dbo].[Flights] WHERE [Ontime] = 0 ) --MULTIPLY THAT AMOUNT BY THE AMOUNT LOST PER LATE FLIGHT ($1,029) AND STORE THE AMOUNT IN ANOTHER VARIABLE DECLARE @AmtLostPerFlight MONEY SET @AmtLostPerFlight = 1029.00 DECLARE @TotalAmtLost MONEY SET @TotalAmtLost = (@NoOfLateFlights * @AmtLostPerFlight) --SELECT @TotalAmtLost AS TotalAmtLost --5TAKE THE TOTAL AMOUNT LOST (#4) AND SUBTRACT IT FROM PROFIT($45,000.00) AND STORE IT IN A VARIABLE DECLARE @TotalProfit MONEY SET @TotalProfit = 45000.00 DECLARE @TotalNetProfit MONEY SET @TotalNetProfit = (@TotalProfit - @TotalAmtLost) SELECT @TotalNetProfit AS NetProfit --6) Find out the earliest FlightDate and add 10 years to it and store it in variable DECLARE @earliestFlightDate datetime SET @earliestFlightDate = ( SELECT MIN([FlightDateTime]) FROM [dbo].[Flights] ) DECLARE @earliestFlightDate10 datetime SET @earliestFlightDate10 = @earliestFlightDate + 10 SELECT @earliestFlightDate10 AS Earliest10years --7)Find out the day of the week for the latest FlightDate and store it in a variable. DECLARE @DayNameOfLastestFlight varchar(50) SET @DayNameOfLastestFlight = (SELECT DATENAME(dw,(SELECT MAX([FlightDateTime]) FROM [dbo].[Flights])) ) SELECT @DayNameOfLastestFlight --8) Create a table variable with Departure City and State in 2 different columns SELECT * FROM [dbo].[Flights] SELECT CHARINDEX('-', '[FlightDepartureCity]', 1) FROM [dbo].[Flights] --9)Create a Table variable storing all info from the dbo.Flights that were on time DECLARE @AllFightsOnTime TABLE ( [FlightID] [int] IDENTITY(100,1) NOT NULL, [FlightDateTime] [datetime] NULL, [FlightDepartureCity] [varchar](50) NULL, [FlightDestinationCity] [varchar](50) NULL, [Ontime] [int] NULL ) INSERT INTO @AllFightsOnTime ([FlightDateTime], [FlightDepartureCity], [FlightDestinationCity], [Ontime]) SELECT [FlightDateTime], [FlightDepartureCity], [FlightDestinationCity], [Ontime] FROM [dbo].[Flights] WHERE [Ontime] = 1 SELECT * FROM @AllFightsOnTime --10) Create a Table variable storing all info from the dbo.Flights table of non Texas Flight DECLARE @NonTexasFlights TABLE ( [FlightID] [int] IDENTITY(100,1) NOT NULL, [FlightDateTime] [datetime] NULL, [FlightDepartureCity] [varchar](50) NULL, [FlightDestinationCity] [varchar](50) NULL, [Ontime] [int] NULL ) INSERT INTO @NonTexasFlights ([FlightDateTime], [FlightDepartureCity], [FlightDestinationCity], [Ontime]) SELECT [FlightDateTime], [FlightDepartureCity], [FlightDestinationCity], [Ontime] FROM [dbo].[Flights] WHERE [FlightDepartureCity] like '%Texas%' SELECT * FROM @NonTexasFlights
tablehomeworktable-variable
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image ThomasRushton ♦♦ commented ·
What error message are you getting that causes you to ask this question?
1 Like 1 ·

1 Answer

·
Avi1 avatar image
Avi1 answered
I see you have created only 1 table variable @AllFightsOnTime, create table statement is correct. Did you try running all script and getting any error?
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.