Click here to Skip to main content
15,498,966 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
In the last few days I developed a small database inside the SQL Server, seeking to improve my knowledge.

Now, I need to create a view for the analysis team, where the data reported below points to. How should I write the query to make this view?

Here is the data that I must enter in the view, for this analysis team:

- 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

Tables and fields inside the database:

What I have tried:

SQL
-- 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,
[Insert] [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,
[Insert] [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,
[Insert] [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
Posted
Updated 17-Oct-22 23:12pm
Comments
CHill60 12-Jan-22 3:49am    
And where is the query that will get the data you want?

Your database schema misses foreign key relationships, that makes it a bad design.

Your query Skelton should be like this

SQL
SELECT <Required Columns> .....
FROM ORDERS O
INNER JOIN CUSTOMERS C ON O.CUSTOMER = C.CLIENT_ID
INNER JOIN ARTICLES A ON O.ARTICLE = A.ARTICLE
INNER JOIN TYPEPAYMENT TP ON O.PAYMENT_ID = TP.ID_PAYMENT


You can tweek the query as per your requirement.
 
Share this answer
 
Comments
iMati 12-Jan-22 11:35am     CRLF
Thank you very much for your time. I've made several attempts, but I still haven't gotten a positive solution. For example, in SELECT I entered this data: A.Articolo AS A, A.Descrizione, O.ID_Ordini, O.Cliente, O.Articolo AS O, O.Quantita, O.ID_Pagamento, O.Inserimento, TP.ID_Pagamento AS TP
Solution 1 won't work because of this line
SQL
INNER JOIN CUSTOMERS C ON O.CUSTOMER = C.CLIENT_ID
Based on the schema you have shown us that is attempting to match an [nvarchar] (80) to an [int] and that simply won't work.

Because you haven't given us any foreign keys to go by I can only suggest that it is supposed to be
SQL
INNER JOIN CUSTOMERS C ON O.Client_ID = C.Client_ID
At first glance it looks as if your select statement should be something like this
SQL
SELECT O.[ID_Orders]
,O.[Insert]
,C.[Name]
,C.[Surname]
,O.[Shipping] -- assume Shipping Date is the same as Delivery date
,A.[Article]
,A.[Description] 
,O.[Quantity]
,O.[Quantity] * A.[Unit_Cost]
,TP.[Description]
,O.[Quantity] * 
,O.[Quantity] * A.[Net_Weight_Kg]
Note the use of the table aliases that tell us which table we have taken the data from. But there is no obvious means of calculating the "Total order volume" - unless they are all rectangular prisms and you want to use
SQL
O.[Quantity] * O.[Height_cm] * O.[Length_cm] * O.[Width_cm]
But this is a moot point as you have a fundamental flaw in your design.

Orders can have many order items or "articles" in your design so there should be another table that lists the lines/items/articles per order, that table should link to the Articles table to get the attributes of each article, but it is this Order Item table that should contain the article ID and the Quantity of that article in this order.

As your design currently stands you can only have one Article per Order.

Once you have worked out what your query should be then here are the instructions for creating a view CREATE VIEW (Transact-SQL) - SQL Server | Microsoft Docs[^]
 
Share this answer
 
In the order table, you perform an INNER JOIN against
[ID_Orders] [int] IDENTITY (1,1) NOT NULL,

Which is the same name as the view you're attempting to create. That is the issue. I'm assuming you already have a table with that name. If that is the case, simply rename the view you are creating. To avoid this, prefixing tables with T_ and views with V_ is a useful naming convention.

To create a view that takes data from two or more tables to give an output in a simplified manner. To understand this better you create a view by taking data from two table names. Here, read this article by scaler topics on how to create views in sql from single and multiple tables.
CREATE VIEW view_name AS  
SELECT column1, column2...column N 
FROM table1, table2...table N 
WHERE condition;
 
Share this answer
 
Comments
CHill60 18-Oct-22 5:47am     CRLF
Incorrect. [ID_Orders] is a column name and it is perfectly acceptable to have a View with the same name as a column. The table is called [Orders]. The OP has already been provided information on how to create a View so your link adds nothing new to the thread

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900