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? -
-- 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
-
-
0 20542 Sign in to post your reply or Sign up for a free account.
Similar topics
by: M. Mehta |
last post by:
It seems that you can not create a materialized view if you are using
outer joins...can someone please verify this?
Thanks
M. Mehta
Please follow my example below:
created 2 tables:
|
by: ljlevend2 |
last post by:
Is there any way to create a local server during runtime? For example, if
you add an existing Web Site to a Solution from within Visual Studio (by
right clicking the solution in the Solution...
|
by: qfchen |
last post by:
Hi
I'm using Visual Studio 2005 for database application. the database is MS access. I need to create a view in server explorer, when I right click on view folder, I can't see "Add New View" from...
|
by: zgh1970 |
last post by:
Hi, All,
We have a database on db2 udb aix.
The database has federated server to db2 mainframe.
Now We have already catalog database of db2/400 on the db2 UDB aix
server.
I tried to create new...
|
by: NithyaJai |
last post by:
Hai,
I got a doubt on creating a view?
Is that passible to create a view in MS Access 2000.......
if so plz help me to do it...
Thanks....
|
by: WayneW |
last post by:
I need to create a view of tables in 2 databases. Is this possible?
|
by: nirmalsingh |
last post by:
hai all, i can view only solution explorer , i cant view server explorer window. i am new to .net. what should i do to view server explorer?
...
|
by: rupinder03 |
last post by:
I want to create tree view in data grid in vb.net 2005.can anyone tell me hw to create it................its urgent.......plz reply soon..............
|
by: kensushi |
last post by:
Hi,
Im somewhat new to oracle SP writing. I need to do the following from the SP
1. execute the query
2. create a view and populate with the result of the above query
3 query that view and...
|
by: Liena |
last post by:
Hi, How can I redirect from one page to anothe in an specific view from a multiview control? How do I do this in ASP. NET using C#?
For ex:
prtected void Button_Click(object sender, EventArgs...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM).
In this month's session, the creator of the excellent VBE...
|
by: DolphinDB |
last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation.
Take...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: Aftab Ahmad |
last post by:
Hello Experts!
I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
|
by: Aftab Ahmad |
last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below.
Dim IE As Object
Set IE =...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: marcoviolo |
last post by:
Dear all,
I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
| |