August 31, 2016 at 3:17 am
HI
We have 4 Tables :
1- Order (Head of orders)
2- OrderItems (Details Of orders)
3- ReturnORD (head of some Orders which have some returned items)
3- ReturnORDItems (detail of OrderItems which have returned)
Now :
we need a view from Tbl_OrderItem
which show us the remain records : including
1) All records which did not have any return + 2) Records which after return have remain count (5-2=3 we have 3 count of this item yet)
I used Left join . but it does not support difference count And I lost them. I can do it with functions but I am willing for a better performance.
Thank you .
/****** Object: Table [dbo].[Table_Order] Script Date: 31/08/2016 12:50:52 ب.ظ ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table_Order](
[Id] [int] NULL,
[OrderPayable] [int] NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Table_OrderItem] Script Date: 31/08/2016 12:50:52 ب.ظ ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table_OrderItem](
[Id] [int] NULL,
[OrderId] [int] NULL,
[ItemId] [int] NULL,
[Count] [int] NULL,
[ItemPayable] [int] NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Table_ReturnORD] Script Date: 31/08/2016 12:50:52 ب.ظ ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table_ReturnORD](
[Id] [int] NULL,
[OrderId] [int] NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Table_ReturnORDItem] Script Date: 31/08/2016 12:50:52 ب.ظ ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table_ReturnORDItem](
[Id] [int] NULL,
[ReturnORDId] [int] NULL,
[ItemId] [int] NULL,
[Count] [int] NULL,
[ReturnORDItemPayable] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Table_Order] ([Id], [OrderPayable]) VALUES (1, 1200)
GO
INSERT [dbo].[Table_Order] ([Id], [OrderPayable]) VALUES (2, 380)
GO
INSERT [dbo].[Table_Order] ([Id], [OrderPayable]) VALUES (3, 400)
GO
INSERT [dbo].[Table_OrderItem] ([Id], [OrderId], [ItemId], [Count], [ItemPayable]) VALUES (100, 1, 8880, 5, 50)
GO
INSERT [dbo].[Table_OrderItem] ([Id], [OrderId], [ItemId], [Count], [ItemPayable]) VALUES (101, 1, 8881, 1, 150)
GO
INSERT [dbo].[Table_OrderItem] ([Id], [OrderId], [ItemId], [Count], [ItemPayable]) VALUES (102, 1, 8882, 2, 400)
GO
INSERT [dbo].[Table_OrderItem] ([Id], [OrderId], [ItemId], [Count], [ItemPayable]) VALUES (103, 2, 8881, 2, 150)
GO
INSERT [dbo].[Table_OrderItem] ([Id], [OrderId], [ItemId], [Count], [ItemPayable]) VALUES (104, 2, 8883, 1, 80)
GO
INSERT [dbo].[Table_OrderItem] ([Id], [OrderId], [ItemId], [Count], [ItemPayable]) VALUES (105, 3, 8882, 1, 400)
GO
INSERT [dbo].[Table_ReturnORD] ([Id], [OrderId]) VALUES (500, 1)
GO
INSERT [dbo].[Table_ReturnORD] ([Id], [OrderId]) VALUES (501, 2)
GO
INSERT [dbo].[Table_ReturnORDItem] ([Id], [ReturnORDId], [ItemId], [Count], [ReturnORDItemPayable]) VALUES (1, 500, 8880, 2, 50)
GO
INSERT [dbo].[Table_ReturnORDItem] ([Id], [ReturnORDId], [ItemId], [Count], [ReturnORDItemPayable]) VALUES (2, 500, 8881, 1, 150)
GO
INSERT [dbo].[Table_ReturnORDItem] ([Id], [ReturnORDId], [ItemId], [Count], [ReturnORDItemPayable]) VALUES (3, 501, 8881, 1, 150)
GO
August 31, 2016 at 4:50 am
the ItemId's in the Orders/returns tables are entirely different...is this a typo?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 31, 2016 at 4:58 am
Sorry Yes
Delete from Table_ReturnORDItem
INSERT [dbo].[Table_ReturnORDItem] ([Id], [ReturnORDId], [ItemId], [Count], [ReturnORDItemPayable]) VALUES (1, 500, 8880, 2, 50)
GO
INSERT [dbo].[Table_ReturnORDItem] ([Id], [ReturnORDId], [ItemId], [Count], [ReturnORDItemPayable]) VALUES (2, 500, 8881, 1, 150)
GO
INSERT [dbo].[Table_ReturnORDItem] ([Id], [ReturnORDId], [ItemId], [Count], [ReturnORDItemPayable]) VALUES (3, 501, 8881, 1, 150)
GO
August 31, 2016 at 7:52 am
maybe something to build on ??
SELECT Id
FROM Table_Order
EXCEPT
(
SELECT OrderId
FROM Table_ReturnORD
);
SELECT O.Id,
OI.ItemId,
OI.Count,
x.Count
FROM Table_Order AS O
INNER JOIN Table_OrderItem AS OI ON O.Id = OI.OrderId
CROSS APPLY
(
SELECT ROI.Count
FROM Table_ReturnORD AS RO
INNER JOIN Table_ReturnORDItem AS ROI ON RO.Id = ROI.ReturnORDId
AND RO.OrderId = O.Id
AND ROI.ItemId = OI.ItemId
) x
WHERE OI.Count > x.count;
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 1, 2016 at 11:56 am
>> We have 4 Tables :
1- Order (Head of orders)
2- OrderItems (Details Of orders)
3- ReturnORD (head of some Orders which have some returned items)
4- ReturnORDItems (detail of OrderItems which have returned) <<
Do you understand that a table models a set of things? That means their names have to be plurals or collectives; you just told you have only one order. You also do not understand that rows are not records [sic]; completely different concept.
Your prefixes of "TBL_" and "" are a design flaw called a Tibble; we regarded as so silly we actually have a funny name for it. It comes from the early days about 50 years ago when we had very simple one pass compilers, you might also want to look up the syntax for an insert statement; you do not have to use the old Sybase single row constructor anymore; you can put in a set of row constructors and put in the whole table all at once. This means the optimizer can figure out the best way to insert the data instead of being required to do it row by row.
By definition, a table must have a key. But what you posted can never have a key! All the columns are nulls. Where is the DRI that would hold the strong and weak entities together? In short, you not posted is a valid schema at all. This is a pile of punchcards written in SQL.
There is also no such thing as a magical, universal "id" in RDBMS. This is more of your punch card mentality, trying to consecutively number the physical storage used for the data. One of the first principles of data modeling is at a data element should have one and only one name and a schema.
What is "item_payable"?
Each table should represent a totally different kind of entity, as unlike as automobiles and squids. So why are items and returned items that kind of different? I do not see it. My thought is that an item in an order can be returned, so we have a status for the item.
CREATE TABLE Orders
(order_nbr CHAR(15) NOT NULL PRIMARY KEY,
order_payable INTEGER NOT NULL); –-What is this?
CREATE TABLE Order_Items
(order_nbr INTEGER NOT NULL
REFERENCES Orders(order_nbr)
ON UPDATE CASCADE
ON DELETE CASCADE,
item_gtin CHAR(15) NOT NULL,
PRIMARY KEY (order_nbr, item_gtin, item_status)
item_qty INTEGER DEFAULT 1 NOT NULL
CHECK(item_qty > 0),
item_status CHAR(8) DEFAULT ‘sold’ NOT NULL
CHECK item_status IN (‘sold’, ‘shipped’, ‘returned’))
item_payable INTEGER NOT NULL);
>> 1) All records [sic] which did not have any return + 2) Records [sic] which after return have remain count (5-2=3 we have 3 count of this item yet) <<
SELECT order_nbr, item_gtin,
SUM (CASE item_status
WHEN ‘shipped’ THEN item_qty
WHEN ‘returned’ THEN -item_qty
ELSE 0 END) onhand_item_qty
FROM Order_Items
GROUP BY order_nbr, item_gtin;
See how simple this gets new got valid DDL? As an exercise, why do not you add a check () constraint that make sure the quantity sold is less than or equal to the quantity returned? A little simple fraud protection here.
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
September 1, 2016 at 2:55 pm
Hi Oldhands
Thanks alot "J Livingston SQL "
You wrote smart query . But I need this result. It means I need All orderitem records with their final status :
Like this :
Id OrderId ItemId Count ItemPayable
100 1 8880 3 50
102 1 8882 2 400
103 2 8881 1 150
104 2 8883 1 80
105 3 8882 1 400
I would be greatfull if you help me to achieve this result.
And you CELKO
You have devoted a lot of time to review our design and mention my mistakes.
You are great. I was frastrated when I saw loads of faults in my design.
Thank you again.
I realised I shouls Start again but this time with new resources.
:-D:-P:-P
And I think I Should strt with this : Data and Databases: Concepts in Practice
September 1, 2016 at 3:57 pm
sm_iransoftware (9/1/2016)
Hi OldhandsThanks alot "J Livingston SQL "
You wrote smart query . But I need this result. It means I need All orderitem records with their final status :
Like this :
Id OrderId ItemId Count ItemPayable
100 1 8880 3 50
102 1 8882 2 400
103 2 8881 1 150
104 2 8883 1 80
105 3 8882 1 400
I would be greatfull if you help me to achieve this result.
And you CELKO
You have devoted a lot of time to review our design and mention my mistakes.
You are great. I was frastrated when I saw loads of faults in my design.
Thank you again.
I realised I shouls Start again but this time with new resources.
:-D:-P:-P
And I think I Should strt with this : Data and Databases: Concepts in Practice
OK...and I am going to assume this is homework for the minute based on you reply to Joe Celko.
are you going to change your data structure as he advises....or you staying put (because the structure is pre defined) ??
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 1, 2016 at 7:36 pm
And you CELKO
You have devoted a lot of time to review our design and mention my mistakes.
You are great. I was frustrated when I saw loads of faults in my design.
Do not expect to be able to speak a new foreign language perfectly the first time out. I have been at this since SQL 86, and had the advantage of knowing the intent of the committee and the people who actually created the language.;-)
And I think I Should start with this : Data and Databases: Concepts in Practice
That is a good choice, but also look at "Thinking in Sets"; do not jump directly into "SQL for Smarties" (this is a pile of rather detailed coding tricks which assumes you have the foundation). Of course on the tell you to buy all my books because I have a house payment:-)
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply