Jump to content

yrstruly

Members
  • Posts

    172
  • Joined

  • Last visited

Profile Information

  • Location
    Cpt
  • Interests
    php sql java seo

yrstruly's Achievements

Member

Member (2/7)

0

Reputation

  1. 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
  2. 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
  3. 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
  4. yrstruly

    Stored Procedure

    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?
  5. Im am trying to do this SQL code; CREATE DATABASE AdventureWorks2012 ON (FILENAME = '{drive}:\ {file path}\AdventureWorks2012_Data.mdf') I am getting the following error when i run it: Msg 1036, Level 16, State 2, Line 1 File option NAME is required in this CREATE/ALTER DATABASE statement. My file location is: '{c}:/AAPOLLIS-M/Users/aapollis/Downloads/AdventureWorks2012_Data.mdf') What am i doing wrong? Running on SQL Server 2014(free or trial version).
  6. yrstruly

    TERADATA TEO 141

    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?
  7. Have tried the phpadmin, how do i import @Q3? Pls provide me with examples or links also API @Q4?
  8. Repeat schedule
  9. Pls assist with last question of task?
  10. Pls see my last Questions and correct me if that link are suitable to search for answers on single query and index?
  11. 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?
  12. This for a different non academic assigment, text book explains differntly.
  13. Have no clue how to even start this...
  14. I dont know how to design the tables and combine as it is required?
  15. 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
×
×
  • Create New...