In the last few days I have developed a small database within SQL Server, with the aim of improving my knowledge.
For now, I need to create a view (for the analytics team), to which the following data should be reported in the view.
➤ Order ID
➤ Date and time of order creation
➤ Customer's first and last name
➤ Delivery date
➤ Item code and description
➤ Quantity demanded
➤ Amount to be paid
➤ Payment Type Description
➤ Total order weight
➤ Total order volume
For the moment I am stuck in this part, and therefore, I would need the query to compose this view by reporting the above data.
---
The last question I ask you is:
To create the view I would first have to create a relationship between the items, for example: Between items in the item table and items in the order table?
For now, I need to create a view (for the analytics team), to which the following data should be reported in the view.
➤ Order ID
➤ Date and time of order creation
➤ Customer's first and last name
➤ Delivery date
➤ Item code and description
➤ Quantity demanded
➤ Amount to be paid
➤ Payment Type Description
➤ Total order weight
➤ Total order volume
For the moment I am stuck in this part, and therefore, I would need the query to compose this view by reporting the above data.
---
The last question I ask you is:
To create the view I would first have to create a relationship between the items, for example: Between items in the item table and items in the order table?
Code:
-- The query I'm using to create the view, but I think something is missing or not correct.
USE [TestExec]
GO
CREATE VIEW OrdersToShip AS
SELECT O.[ID_Orders]
,O.[DataInsert]
,C.[Name]
,C.[Surname]
,O.[Shipping]
,A.[Article]
,A.[Description]
,O.[Quantity]
,O.[Quantity] * A.[Unit_Cost]
,TP.[Description]
,O.[Quantity] *
,O.[Quantity] * A.[Net_Weight_Kg]
FROM ORDERS O
INNER JOIN CUSTOMERS C ON O.Client_ID = C.Client_ID
INNER JOIN ARTICLES A ON O.ARTICLE = A.ARTICLE
INNER JOIN TYPEPAYMENT TP ON O.PAYMENT_ID = TP.ID_PAYMENT
GO
-- Articles table
CREATE TABLE [DW]. [Articles] (
[Article] [nvarchar] (30) NOT NULL,
[Description] [nvarchar] (150) NOT NULL,
[Unit_di_misura] [nvarchar] (20) NOT NULL,
[Pieces_per_Box] [nvarchar] (20) NOT NULL,
[Box_for_Pallet] [int] NOT NULL,
[Unit_Cost] [float] NOT NULL,
[Net_Weight_Kg] [float] NOT NULL,
[Height_cm] [int] NOT NULL,
[Length_cm] [int] NOT NULL,
[Width_cm] [int] NOT NULL,
[DataInsert] [datetime] NOT NULL
-- Customers table
CREATE TABLE [DW]. [Customers] (
[Client_ID] [int] IDENTITY (1,1) NOT NULL,
[Name] [nvarchar] (50) NOT NULL,
[Surname] [nvarchar] (50) NOT NULL,
[Social_Reason] [nvarchar] (80) NULL,
[CF_PIVA] [nvarchar] (30) NOT NULL,
[Email] [nvarchar] (80) NOT NULL,
[Telephone] [nvarchar] (30) NOT NULL,
[Address] [nvarchar] (50) NOT NULL,
[CAP] [int] NOT NULL,
[City] [nvarchar] (50) NOT NULL,
[Province] [nvarchar] (50) NOT NULL,
[Country] [nvarchar] (50) NOT NULL,
[DataInsert] [datetime] NOT NULL,
CONSTRAINT [PK_ID_Cliente] PRIMARY KEY CLUSTERED
-- Orders table
CREATE TABLE [DW]. [Orders] (
[ID_Orders] [int] IDENTITY (1,1) NOT NULL,
[Customer] [nvarchar] (80) NOT NULL,
[Client_ID] [int] NOT NULL,
[Article] [nvarchar] (30) NOT NULL,
[Quantity] [int] NOT NULL,
[Delivery] [date] NULL,
[Payment] [nvarchar] (50) NOT NULL,
[Payment_ID] [int] NULL,
[DataInsert] [datetime] NOT NULL,
[Shipping] [date] NULL,
CONSTRAINT [PK_Ordini] PRIMARY KEY CLUSTERED
-- Payment type table
CREATE TABLE [DW]. [TypePayment] (
[ID_Payment] [int] IDENTITY (1,1) NOT NULL,
[Payment] [nvarchar] (50) NOT NULL,
[Description] [nchar] (150) NULL,
CONSTRAINT [PK_ID_Payment] PRIMARY KEY CLUSTERED