470,593 Members | 2,411 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,593 developers. It's quick & easy.

Create a specific view in SQL server

4
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?

Expand|Select|Wrap|Line Numbers
  1. -- The query I'm using to create the view, but I think something is missing or not correct.
  2.  
  3. USE [TestExec]
  4. GO
  5.  
  6. CREATE VIEW OrdersToShip AS
  7. SELECT O.[ID_Orders]
  8. ,O.[DataInsert]
  9. ,C.[Name]
  10. ,C.[Surname]
  11. ,O.[Shipping]
  12. ,A.[Article]
  13. ,A.[Description] 
  14. ,O.[Quantity]
  15. ,O.[Quantity] * A.[Unit_Cost]
  16. ,TP.[Description]
  17. ,O.[Quantity] * 
  18. ,O.[Quantity] * A.[Net_Weight_Kg]
  19.  
  20. FROM ORDERS O
  21. INNER JOIN CUSTOMERS C ON O.Client_ID = C.Client_ID
  22. INNER JOIN ARTICLES A ON O.ARTICLE = A.ARTICLE
  23. INNER JOIN TYPEPAYMENT TP ON O.PAYMENT_ID = TP.ID_PAYMENT
  24.  
  25. GO
  26.  
  27. -- Articles table
  28.  
  29. CREATE TABLE [DW]. [Articles] (
  30. [Article] [nvarchar] (30) NOT NULL,
  31. [Description] [nvarchar] (150) NOT NULL,
  32. [Unit_di_misura] [nvarchar] (20) NOT NULL,
  33. [Pieces_per_Box] [nvarchar] (20) NOT NULL,
  34. [Box_for_Pallet] [int] NOT NULL,
  35. [Unit_Cost] [float] NOT NULL,
  36. [Net_Weight_Kg] [float] NOT NULL,
  37. [Height_cm] [int] NOT NULL,
  38. [Length_cm] [int] NOT NULL,
  39. [Width_cm] [int] NOT NULL,
  40. [DataInsert] [datetime] NOT NULL
  41.  
  42. -- Customers table
  43.  
  44. CREATE TABLE [DW]. [Customers] (
  45. [Client_ID] [int] IDENTITY (1,1) NOT NULL,
  46. [Name] [nvarchar] (50) NOT NULL,
  47. [Surname] [nvarchar] (50) NOT NULL,
  48. [Social_Reason] [nvarchar] (80) NULL,
  49. [CF_PIVA] [nvarchar] (30) NOT NULL,
  50. [email] [nvarchar] (80) NOT NULL,
  51. [Telephone] [nvarchar] (30) NOT NULL,
  52. [Address] [nvarchar] (50) NOT NULL,
  53. [CAP] [int] NOT NULL,
  54. [City] [nvarchar] (50) NOT NULL,
  55. [Province] [nvarchar] (50) NOT NULL,
  56. [Country] [nvarchar] (50) NOT NULL,
  57. [DataInsert] [datetime] NOT NULL,
  58. CONSTRAINT [PK_ID_Cliente] PRIMARY KEY CLUSTERED 
  59.  
  60. -- Orders table
  61.  
  62. CREATE TABLE [DW]. [Orders] (
  63. [ID_Orders] [int] IDENTITY (1,1) NOT NULL,
  64. [Customer] [nvarchar] (80) NOT NULL,
  65. [Client_ID] [int] NOT NULL,
  66. [Article] [nvarchar] (30) NOT NULL,
  67. [Quantity] [int] NOT NULL,
  68. [Delivery] [date] NULL,
  69. [Payment] [nvarchar] (50) NOT NULL,
  70. [Payment_ID] [int] NULL,
  71. [DataInsert] [datetime] NOT NULL,
  72. [Shipping] [date] NULL,
  73.  CONSTRAINT [PK_Ordini] PRIMARY KEY CLUSTERED
  74.  
  75. -- Payment type table
  76.  
  77. CREATE TABLE [DW]. [TypePayment] (
  78. [ID_Payment] [int] IDENTITY (1,1) NOT NULL,
  79. [Payment] [nvarchar] (50) NOT NULL,
  80. [Description] [nchar] (150) NULL,
  81.  CONSTRAINT [PK_ID_Payment] PRIMARY KEY CLUSTERED
  82.  
  83.  
Jan 19 '22 #1
0 18655

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by M. Mehta | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.