yrstruly

Please assist. I would like to answer this question: "What is the average order value in the African Region per month?"

My Data Model:

My Code:

SELECT Avg(Orders)  AS Order Value

FROM Orders, Customer, Nation, Region

Join Customer AS C

ON Customer.Custkey = Orders.Custkey

Join Customer AS C ON Customer.Nationkey=Nation.Nationkey

Join Nation.Nationkey=Region.Nationkey

where Region = Africa

I have this function running(see code). I would like to select data beteen dates e.g 30-06-2022 to 31-07-2022. Specificaly for columns Eligible, Fuel Levy, Claimable and % Eligible of purchase. I am gettig zero for these columns.

Is my code the reason i am getting these errors or my data missing. More of the views/tables ddl:

CREATE VIEW [dbo].[vw_UsageReportCached]
AS 
SELECT *
  FROM [dbo].[cacheUsageLogbook]
WHERE 
1=1
AND RegNumber NOT IN (SELECT le.RegNumber
                        FROM lstEquipment AS le WHERE le.Id IN (SELECT EquipmentId FROM mapEquipmentLocation AS mel)

)
GO
---------------------------------
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[auditLevyBreakdown](
[Id] [int] IDENTITY(1,1) NOT NULL,
[StartDate] [datetime] NOT NULL,
[EndDate] [datetime] NULL,
[IsActive] [bit] NOT NULL,
[CreateDate] [datetime] NOT NULL,
[ModifyDate] [datetime] NOT NULL,
[RAFLevy] [float] NOT NULL,
[FuelLevy] [float] NOT NULL,
[PercEligible] [float] NOT NULL,
 CONSTRAINT [PK_auditLevyBreakdown] PRIMARY KEY CLUSTERED 
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[auditLevyBreakdown] ADD  CONSTRAINT [DF_auditLevyBreakdown_IsActive]  DEFAULT ((1)) FOR [IsActive]
GO

ALTER TABLE [dbo].[auditLevyBreakdown] ADD  CONSTRAINT [DF_auditLevyBreakdown_CreateDate]  DEFAULT (getdate()) FOR [CreateDate]
GO

ALTER TABLE [dbo].[auditLevyBreakdown] ADD  CONSTRAINT [DF_auditLevyBreakdown_ModifyDate]  DEFAULT (getdate()) FOR [ModifyDate]
GO

------------------------
/****** Object:  Table [dbo].[lstEquipment]    Script Date: 2022/08/01 17:09:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[lstEquipment](
[Id] [int] IDENTITY(1,1) NOT NULL,
[FleetId] [nvarchar](25) NOT NULL,
[RegNumber] [nvarchar](25) NULL,
[EquipmentDescription] [nvarchar](100) NULL,
[ModelId] [int] NOT NULL,
[MakeId] [int] NOT NULL,
[TankSize] [float] NULL,
[ConsumptionTypeId] [int] NULL,
[VehicleTypeId] [int] NULL,
[DecommissionDate] [datetime] NULL,
[CreateDate] [datetime] NOT NULL,
[ModifyDate] [datetime] NOT NULL,
[IsActive] [bit] NOT NULL,
[AFSEquipmentId] [int] NULL,
 CONSTRAINT [PK_lstEquipment] PRIMARY KEY CLUSTERED 
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[lstEquipment] ADD  CONSTRAINT [DF_lstEquipment_CreateDate]  DEFAULT (getdate()) FOR [CreateDate]
GO

ALTER TABLE [dbo].[lstEquipment] ADD  CONSTRAINT [DF_lstEquipment_ModifyDate]  DEFAULT (getdate()) FOR [ModifyDate]
GO

ALTER TABLE [dbo].[lstEquipment] ADD  CONSTRAINT [DF_lstEquipment_IsActive]  DEFAULT ((1)) FOR [IsActive]
GO

ALTER TABLE [dbo].[lstEquipment]  WITH CHECK ADD  CONSTRAINT [FK_lstEquipment_lstConsumptionType] FOREIGN KEY([ConsumptionTypeId])
REFERENCES [dbo].[lstConsumptionType] ([Id])
GO

ALTER TABLE [dbo].[lstEquipment] CHECK CONSTRAINT [FK_lstEquipment_lstConsumptionType]
GO

ALTER TABLE [dbo].[lstEquipment]  WITH CHECK ADD  CONSTRAINT [FK_lstEquipment_lstMake] FOREIGN KEY([MakeId])
REFERENCES [dbo].[lstMake] ([Id])
GO

ALTER TABLE [dbo].[lstEquipment] CHECK CONSTRAINT [FK_lstEquipment_lstMake]
GO

ALTER TABLE [dbo].[lstEquipment]  WITH CHECK ADD  CONSTRAINT [FK_lstEquipment_lstModel] FOREIGN KEY([ModelId])
REFERENCES [dbo].[lstModel] ([Id])
GO

ALTER TABLE [dbo].[lstEquipment] CHECK CONSTRAINT [FK_lstEquipment_lstModel]
GO

ALTER TABLE [dbo].[lstEquipment]  WITH CHECK ADD  CONSTRAINT [FK_lstEquipment_lstVehicleType] FOREIGN KEY([VehicleTypeId])
REFERENCES [dbo].[lstVehicleType] ([Id])
GO

ALTER TABLE [dbo].[lstEquipment] CHECK CONSTRAINT [FK_lstEquipment_lstVehicleType]
GO


------------------
AND


SELECT * FROM [dbo].[fn_GetSummaryReport] (
   <@pStartDate, date,>
  ,<@pEndDate, date,>)

/****** Object:  UserDefinedFunction [dbo].[fn_GetSummaryReport]    Script Date: 2022/08/01 16:57:49 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO





CREATE FUNCTION [dbo].[fn_GetSummaryReport]
(
@pStartDate     DATE,
@pEndDate       DATE
)
RETURNS TABLE

RETURN
SELECT SUM(vurc.QuantityReceived)       TotalLitres,
       SUM(vurc.QuantityReceived * afp.PricePerL) TotalValue,
       SUM(vurc.EligiblePurchases)      EligibleLitres,
       SUM(vurc.EligiblePurchases * afp.PricePerL) EligibleValue,
       SUM(vurc.NonEligible)            IneligibleLitres,
       SUM(vurc.NonEligible * afp.PricePerL) IneligibleValue,
       COUNT(vurc.QuantityReceived)     TotalNumberOfTransactions,
       COUNT(IIF(le.RegNumber = 'RNF',NULL,1)) TotalNumberOfCompleteTransactions,
       COUNT(IIF(le.RegNumber = 'RNF',1,NULL)) TotalNumberOfFaultyTransactions,
       SUM(IIF(le.RegNumber = 'RNF',NULL,vurc.QuantityReceived)) TotalVolumeOfCompleteTransactions,
       SUM(IIF(le.RegNumber = 'RNF',vurc.QuantityReceived,NULL)) TotalVolumeOfFaultyTransactions,
       alb.RAFLevy                      RAFLevy,
       alb.FuelLevy                     FuelLevy,
       alb.PercEligible                 PercEligible,
       (alb.RAFLevy + alb.FuelLevy) / 100 * alb.PercEligible FinalRebatePL,
       (
           SUM(vurc.EligiblePurchases * alb.FuelLevy) + SUM(vurc.EligiblePurchases * alb.RAFLevy)
       ) / 100 * alb.PercEligible       TotalClaimable
FROM   vw_UsageReportCached          AS vurc
       LEFT JOIN vw_FuelPrice      AS afp
            ON  vurc.TransactionDateTime BETWEEN afp.PurchaseDate AND ISNULL(afp.NextPurchaseDate, GETDATE())
       LEFT JOIN auditLevyBreakdown  AS alb
            ON  vurc.TransactionDateTime BETWEEN alb.StartDate AND ISNULL(alb.EndDate, GETDATE())
            AND alb.IsActive = 1
       LEFT JOIN lstEquipment AS le ON le.FleetId = vurc.RegNumber
WHERE  CAST(vurc.TransactionDateTime AS DATE) BETWEEN @pStartDate AND @pEndDate
GROUP BY
       alb.RAFLevy,
       alb.FuelLevy,
       alb.PercEligible
GO

To create an output like this: https://drive.google.com/file/d/1NlImnhE35Zattw4KKH7gVrQcGcMsSlRJ/view?usp=sharing

Do i create a stored procedure or view that would allow this end result above?

SQL: https://drive.google.com/file/d/1mkBA8Uc8tZjhWdzqGaxLAPnNoqpBSfMZ/view?usp=sharing

I have a table: Named  HighScores  

"INSERT HighScores 
VALUES ('Bob', 2500, '2 Jan 2013 13:13'),
           ('Jon', 1500, '2 Jan 2013 13:15'),
       ('Amy', 3500, '2 Jan 2013 13:18')"

I want to create a stored procedure that would:

The score must show the TOP 5 or so people.

Show your position on the high score board

Show the person in front and below you on the high score board.

If the scores are tied, the person who got it the most recent is shown higher.

Can anybody assist me please?

Im am trying to do this SQL code;

I am getting the following error when i run it:

Hi

Any of you on here Teradata 14 Certified? Im trying to get certified. Have all the prescribed study material and also purchased the app. Failed the exam twice as its very difficult to pass and also including questions which are not in the study metrial, but are in the exam.

Anybody point me in the right direction,tips etc?

Have tried the phpadmin, how do i import @Q3? Pls provide me with examples or links also API @Q4?

Repeat schedule

Pls assist with last question of task?

Pls see my last Questions and correct me if that link are suitable to search for answers on single query and index?

will this www.w3schools.com/sql/sql_create_index.asp be sufficient to apply index and single query statement, also point 3 i dont understand or know where to search for?

This for a different non academic assigment, text book explains differntly.

Have no clue how to even start this...

I dont know how to design the tables and combine as it is required?

Does Anybody have a SQL code that does the following ones of or is it done in parts?1. DB Schema Design - Design a basic database to store the following information + A fleet of Vehicles - Include details such as mileage, vin numbers etc + A pool of drivers - Include relevant documentation, e.g. Drivesr license, ID Book + A group of routes - Marking out GPS coords for directions etc. is not necessary, just include a start & end address as well as any other info you believe to be pertinent + A drivers schedule, tying the above 3 tables together

Does Anybody knows how to do the following,API's and Indexes etc?CREATE TABLE ¿tUSER¿ ( ¿id¿ bigint(20) NOT NULL AUTO§INCREMENT, ¿id§number¿ varchar(20) NOT NULL, ¿first§names¿ varchar(100) NOT NULL, ¿last§name¿ varchar(100) NOT NULL PRIMARY KEY (¿id¿), <<INDEXES>>)CREATE TABLE ¿tPROFILE¿ ( ¿id¿ bigint(20) NOT NULL AUTO§INCREMENT, ¿tUSER§id¿ bigint(20) DEFAULT NULL, ¿tTYPES§id¿ bigint(20) DEFAULT NULL, ¿value¿ varchar(100) NOT NULL, PRIMARY KEY (¿id¿), <<INDEXES>>)CREATE TABLE ¿tTYPES¿ ( ¿id¿ bigint(20) NOT NULL AUTO§INCREMENT, ¿type¿ varchar(100) NOT NULL DEFAULT '', ¿description¿ varchar(255) NOT NULL, ¿deleted¿ tinyint(1) NOT NULL DEFAULT '0', PRIMARY KEY (¿id¿), <<INDEXES>>) Apply indexes to the above tables, and design a SINGLE query to retrieve a full list of user information based on: 2.A) an ID number 2. a cellphone number (¿Cellphone¿ is a record in the ¿tTYPES¿ table)3. Write a re-usable script for importing information into the above 3 tables, from the example dsv file below: - NB - All data of the same type MUST be stored in the same format - NB2- You may assume that if a column doesn't seem to exist for a piece of data, that data type is listed in the tTYPES table<<BOF>>record§numberöid§numberöfirst nameölast nameömsisdnönetworköpointsöcard numberögender312ö9101011234011öTest JunioröSmithö071 123 4321öMTNö73ö1241551413214444öM313ö9012023213011öBoböSmithö27743334321öVodacomö3ö1231233232323244öM314ö8706055678011öFranköFrankinsonö2771 156 1567ö8taö0ö1231123453214444öM315ö9102078765011öMaryöVan Niekerkö+27(0)711236677öCellCö2ö1278933213214444öF316ö9005074545011öSusanöWilsonö0821121124öCellCö705ö1231233216544444öF317ö9101013232011öKatherineöJeevesö+271233214ö8taö112ö1231233678214444öF318ö9101011234011öMatthewöMatthiasö0711111111öMTNöö1231555213214444öM319ö9103126666011öMichaelöBayö085-6122-161ö8taö63ö1231244413214444öM320ö7506023232300öTyroneöOlivierö711234322öCellCö89ö1234563213214444öM321ö8901020304055öBurtöJacksonö071 4566544öVodacomö1ö4567233213214444öM<<EOF>>4. After the data has been imported, Write a basic API to wrap the Database. Include the following function points: - Add a new User - Update a User's details - Delete a User - Search users5. Describe what the following bash statement does: grep ¿date +%Y-%m-%d --date='1 day ago'¿ /path/to/file/FILE§PREFIX§Ö¿date +%Y%m%d --date='1 day ago'¿.dsv ö grep -v 'ERROR' ö cut -d "ö" -f 2 ö sed 's/Ü0/27/'------------------------------------------------PART II: Interesting Stuff------------------------------------------------1. If a resturaunt serves: - 3 types of starters - 5 types of main - N types of drinks - 3 types of desserts + How many different meals are available, if you can order 1 item of each type? + How many different meals are available, if you can order 1 item of each type, BUT you can order 2 drinks as long as you do not order the same drink twice? + How many different meals are available, if you can only order a dessert OR a starter?2. If you need to profile 1000 users, each with 3 different attributes, and each attribute has 4 possible values - before parsing any of the date: + What can we guarantee about the resultset?3. A wild director appears. He uses "I want to profile my user database using an additional attribute!" - Describe a ¿super-effective¿ method which we can implement, which will allow us to handle an indeterminate number of this type of request.4. If we have two seperate tables, the first detailing a list of registered club members, and the second detailing a list of competition entrants (assuming we have a key we can join on), what do/could the following resultsets represent: - The INTERSECT of the tables - The MINUS of the tables - The UNION of the tables

Hi Im having trouble creating a database via php and selecting it.Can anybody help me? <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en"><head> <meta http-equiv="content-type" content="text/html; charset=utf-8" /> <title>Create the Database</title></head><body><?php // Script 12.3 - create_db.php/* This script connects to the MySQL server. It also creates and selects the database. */$connection = mysql_connect ("sql106.byethost17.com", "b17_13305510", "musica");// Attempt to connect to MySQL and print out messages:if ($dbc = @mysql_connect('sql106.byethost17.com', 'b17_13305510', 'musica')) { print '<p>Successfully connected to MySQL!</p>'; // Try to create the database:$sql="CREATE DATABASE myblog"; if (mysql_query($connection,$sql)){// if (@mysqli_query($dbc,'CREATE DATABASE myblog' )) { print '<p>The database has been created!</p>'; } else { // Could not create it. print '<p style="color: red;">Could not create the database because:<br />' . mysql_error() . '.</p>'; } // Try to select the database: if (@mysql_select_db( $dbc,'myblog')) { print '<p>The database has been selected!</p>'; } else { print '<p style="color: red;">Could not select the database because:<br />' . mysql_error($dbc) . '.</p>'; } mysql_close($dbc); // Close the connection.} else { print '<p style="color: red;">Could not connect to MySQL:<br />' . mysql_error() . '.</p>';}?></body></html> help me here?

I have included all of the codes and password in the previous posting.

This is what i get when i go the cpanel and select databases or phpmyadmin Current Databases MySQL DB Name b12_12422275_20423 MySQL User Name b12_12422275 MySQL Host Name sql103.byethost12.comMySQL Password musica

yes... I gave u the login details can u pls try.

I am doing everyting like said, nothing works! Anybody who know how to do this pls help, password: musica for login etc.

Can somebody pls help me connect to my online host pls? <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en"><head> <meta http-equiv="content-type" content="text/html; charset=utf-8" /> <title>Connect to MySQL</title></head><body><?php // Script 12.1 - mysql_connect.php/* This script connects to the MySQL server. */ // Attempt to connect to MySQL and print out messages:if ($dbc = mysql_connect('http://yrstruly3.0fees.net ', '', '')) { print '<p>Successfully connected to MySQL!</p>'; mysql_close($dbc); // Close the connection. } else { print '<p style="color: red;">Could not connect to MySQL.</p>'; } ?></body></html> Details of host: I can give u the password!Cpanel Username: fees0_13306514 Cpanel Password: Your URL: http://yrstruly3.0fees.net or http://www.yrstruly3.0fees.net FTP Server : ftp.0fees.net FTP Login : fees0_13306514 FTP Password : MySQL Database Name: MUST CREATE IN CPANEL MySQL Username : fees0_13306514 MySQL Password : MySQL Server: SEE THE CPANEL

Can anybody tell me how do i start this task, have never used photoshop?

HTML Assessment1.doc

I have the following code and i cant connect. usually i can if i leave out the username and password or type it in, but some how i just cant get this one right now. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en"><head> <meta http-equiv="content-type" content="text/html; charset=utf-8" /> <title>Connect to MySQL</title></head><body><?php // Script 12.1 - mysql_connect.php/* This script connects to the MySQL server. */// Attempt to connect to MySQL and print out messages:if ($dbc = mysql_connect('localhost', 'b17_13305510', '')) { print '<p>Successfully connected to MySQL!</p>'; mysql_close($dbc); // Close the connection.} else { print '<p style="color: red;">Could not connect to MySQL.</p>';}?></body></html> Hers the login details and i replace it int he file as follows Your URL: http://yrstruly.byethost17.com or http://www.yrstruly.byethost17.com FTP Server : ftp.byethost17.com FTP Login : b17_13305510 FTP Password : ****** MySQL Database Name: MUST CREATE IN CPANEL MySQL Username : b17_13305510 MySQL Password : ****** MySQL Server: SEE THE CPANEL

Did you have a look at my link @ the bottom?Its the machine im working on, then its working n then not...