April 17, 2016 at 6:22 am
oh ...and by the way, I am afraid that screen shots arent the best way of providing data....they can visually explain what you are attempting, but it far better to post code that can be easily cut and paste into SSMS and run to recreate representative tables/data.
please see this post
https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
as an example
CREATE TABLE #yourtable(
OrderId INTEGER NOT NULL
,OrderLineNo INTEGER NOT NULL
,ProdId INTEGER NOT NULL
,SalesUnitPrice NUMERIC(13,2) NOT NULL
);
INSERT INTO #yourtable(OrderId,OrderLineNo,ProdId,SalesUnitPrice) VALUES
(1,1,352,67.22),(1,2,605,58.84),(1,3,1085,29.88),(1,4,4033,51.90),(1,5,5250,67.30)
,(1,6,6098,19.36),(1,7,6327,44.35),(2,1,269,19.15),(2,2,840,11.49),(2,3,2978,19.66)
,(2,4,4164,93.33),(3,1,3758,39.12),(3,2,4244,86.60),(3,3,7988,52.66),(4,1,3817,20.10)
,(5,1,1549,81.44),(5,2,6011,13.38),(5,3,7223,46.42);
SELECT * FROM #yourtable
DROP TABLE #yourtable
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 17, 2016 at 6:29 am
Hi,
Do u want me to add further code for my database?
Thanks
April 17, 2016 at 6:40 am
fareedhmohamed (4/17/2016)
Hi,Do u want me to add further code for my database?
Thanks
by all means do...it will probably help us help you
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 17, 2016 at 6:45 am
USE [Supermarket]
GO
/****** Object: Table [dbo].[Customer] Script Date: 17/04/2016 13:44:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Customer](
[CustomerID] [varchar](10) NOT NULL,
[FirstName] [nvarchar](50) NULL,
[Surname] [nvarchar](50) NULL,
[Gender] [varchar](5) NULL,
[FirstLineAddress] [nvarchar](50) NULL,
[SecondLineAddress] [nvarchar](50) NULL,
[City] [nvarchar](50) NULL,
[Postcode] [nvarchar](8) NULL,
[TelephoneNumber] [varchar](50) NULL,
[StatusLevel] [varchar](50) NULL,
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
[CustomerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Customer] WITH CHECK ADD CONSTRAINT [CK_Gender] CHECK (([Gender]='F' OR [Gender]='M'))
GO
ALTER TABLE [dbo].[Customer] CHECK CONSTRAINT [CK_Gender]
GO
ALTER TABLE [dbo].[Customer] WITH CHECK ADD CONSTRAINT [CK_StatusLevels] CHECK (([StatusLevel]='Regular' OR [StatusLevel]='Standard' OR [StatusLevel]='New'))
GO
ALTER TABLE [dbo].[Customer] CHECK CONSTRAINT [CK_StatusLevels]
GO
USE [Supermarket]
GO
/****** Object: Table [dbo].[Order] Script Date: 17/04/2016 13:44:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Order](
[OrderID] [varchar](10) NOT NULL,
[OrderDate] [date] NULL,
[TotalPrice] [numeric](5, 2) NULL,
[ProductID] [varchar](10) NULL,
[ProductName] [varchar](30) NULL,
[CustomerID] [varchar](10) NULL,
[TotalQuantity] [numeric](18, 0) NULL,
CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED
(
[OrderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Order] WITH CHECK ADD CONSTRAINT [FK_Order_Product] FOREIGN KEY([ProductID])
REFERENCES [dbo].[Product] ([ProductID])
GO
ALTER TABLE [dbo].[Order] CHECK CONSTRAINT [FK_Order_Product]
GO
USE [Supermarket]
GO
/****** Object: Table [dbo].[Product] Script Date: 17/04/2016 13:45:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Product](
[ProductID] [varchar](10) NOT NULL,
[ProductName] [varchar](50) NULL,
[Price] [numeric](5, 2) NULL,
[Quantity] [numeric](18, 0) NULL,
[CategoryID] [varchar](10) NULL,
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
[ProductID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Product] WITH CHECK ADD CONSTRAINT [FK_Product_Category] FOREIGN KEY([CategoryID])
REFERENCES [dbo].[Category] ([CategoryID])
GO
ALTER TABLE [dbo].[Product] CHECK CONSTRAINT [FK_Product_Category]
GO
I have pasted the code above for the tables involved.
Thanks,
April 17, 2016 at 6:49 am
great start...now some sample data please and your expected results based on that sample data.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 17, 2016 at 7:45 am
fareedhmohamed (4/17/2016)
USE [Supermarket]GO
/****** Object: Table [dbo].[Customer] Script Date: 17/04/2016 13:44:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Customer](
[CustomerID] [varchar](10) NOT NULL,
[FirstName] [nvarchar](50) NULL,
[Surname] [nvarchar](50) NULL,
[Gender] [varchar](5) NULL,
[FirstLineAddress] [nvarchar](50) NULL,
[SecondLineAddress] [nvarchar](50) NULL,
[City] [nvarchar](50) NULL,
[Postcode] [nvarchar](8) NULL,
[TelephoneNumber] [varchar](50) NULL,
[StatusLevel] [varchar](50) NULL,
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
[CustomerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Customer] WITH CHECK ADD CONSTRAINT [CK_Gender] CHECK (([Gender]='F' OR [Gender]='M'))
GO
ALTER TABLE [dbo].[Customer] CHECK CONSTRAINT [CK_Gender]
GO
ALTER TABLE [dbo].[Customer] WITH CHECK ADD CONSTRAINT [CK_StatusLevels] CHECK (([StatusLevel]='Regular' OR [StatusLevel]='Standard' OR [StatusLevel]='New'))
GO
ALTER TABLE [dbo].[Customer] CHECK CONSTRAINT [CK_StatusLevels]
GO
USE [Supermarket]GO
/****** Object: Table [dbo].[Order] Script Date: 17/04/2016 13:44:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Order](
[OrderID] [varchar](10) NOT NULL,
[OrderDate] [date] NULL,
[TotalPrice] [numeric](5, 2) NULL,
[ProductID] [varchar](10) NULL,
[ProductName] [varchar](30) NULL,
[CustomerID] [varchar](10) NULL,
[TotalQuantity] [numeric](18, 0) NULL,
CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED
(
[OrderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Order] WITH CHECK ADD CONSTRAINT [FK_Order_Product] FOREIGN KEY([ProductID])
REFERENCES [dbo].[Product] ([ProductID])
GO
ALTER TABLE [dbo].[Order] CHECK CONSTRAINT [FK_Order_Product]
GO
USE [Supermarket]GO
/****** Object: Table [dbo].[Product] Script Date: 17/04/2016 13:45:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Product](
[ProductID] [varchar](10) NOT NULL,
[ProductName] [varchar](50) NULL,
[Price] [numeric](5, 2) NULL,
[Quantity] [numeric](18, 0) NULL,
[CategoryID] [varchar](10) NULL,
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
[ProductID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Product] WITH CHECK ADD CONSTRAINT [FK_Product_Category] FOREIGN KEY([CategoryID])
REFERENCES [dbo].[Category] ([CategoryID])
GO
ALTER TABLE [dbo].[Product] CHECK CONSTRAINT [FK_Product_Category]
GO
I have pasted the code above for the tables involved.
Thanks,
Great start. I also see that a customer can have one of 3 "StatusLevels".
ALTER TABLE [dbo].[Customer] WITH CHECK ADD CONSTRAINT [CK_StatusLevels] CHECK (([StatusLevel]='Regular' OR [StatusLevel]='Standard' OR [StatusLevel]='New'))
GO
Does that have anything to do with the following?
fareedhmohamed (4/16/2016)
Hi,I am trying to create a trigger, which will provide a discount of 10%, if the same customer number has at least bought 4 items. I have posted 3 of the table structures that would be involved in the trigger.
There's also a critical piece of information missing from the above statement concerning "WHEN" that will make this problem a whole lot more understandable. WHEN do you want the customer to get the 10% discount? I know you said "if the same customer number has at least bought 4 item" but we need the "WHEN" for that. Does that mean 1) WHEN the customer has bought as least 4 items total since they became a customer and gets 10% off all future orders or 2) WHEN the customer buys at least 4 items for a given order, they get 10% off just that order?
There are other possibilities for the "WHEN" but those two should be enough to incite you to provide a more detailed description of the actual problem.
Either way, where do you want to store that fact? If #1 above, then there should be a column in a table somewhere (a separate "incentive" table should probably be created). Whether or not problem #1 or #2 is the truth above, a discount column needs to be added to the header table for orders. And, no... just doing the application of the 10% discount and applying it to some SELECT is NOT the right way to do this. The fact that the discount was applied MUST be recorded (stored in a table) at least at the order header level.
Until then, this question actually has no answer because the problem has not been adequately defined.
As a bit of a sidebar, when does the "StatusLevel" of "New" change to something else and what's the difference between "Regular" and "Standard"??? :blink: Considering your original question, maybe it's not just a sidebar. [font="Arial Black"]Maybe it's the crux of the question.[/font] If so, is it the "StatusLevel" that you want to change for your original question (quoted below)? If so, "WHAT" do you want to change the "StatusLevel" to???
I want to update a field in customer table, if the same customer has more than two orders. I am not sure on how do I go about doing this?
Finally, considering the rather under-designed tables for this this task, I have to ask, is this homework or a project at work? If it's for a project at work, they're in deep Kimchi so far as table design goes.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 17, 2016 at 11:06 am
Hi,
Thanks for your very detailed response. Its just practise for home really.
I just want to give a discount of 10 % if the customer has ordered more than 4 items.
The StatusLevel is just a filed which contains NEW, STANDARD AND REGULAR. Its just manually filled in, its not a trigger. I would like to change the statusLevel to Regular, if the customer has more than 4 orders. I am not sure which is the easier option or which is more do able. But either way, its just practise since I want to get some experience on triggers.
Thanks,
April 17, 2016 at 11:49 am
fareedhmohamed (4/16/2016)
I am not sure how to write a query, that will display the customer who has more than one order in the order table. I have attached the table structures.THanks
forget triggers for a moment and
apologies if I am doing you a disservice, but are you saying that you cannot write a query to deliver "customer who has more than one order in the order table"
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 17, 2016 at 12:09 pm
Hi,
Is this correct?
SELECT COUNT(CustomerID), TotalQuantity, CustomerID, ProductName
FROM [Order]
GROUP BY TotalQuantity, CustomerID, ProductName;
April 17, 2016 at 12:37 pm
fareedhmohamed (4/17/2016)
Hi,Is this correct?
SELECT COUNT(CustomerID), TotalQuantity, CustomerID, ProductNameFROM [Order]
GROUP BY TotalQuantity, CustomerID, ProductName;
"is this correct".....maybe maybe not........
in the absence of any sample data and expected results then I will guess that the following just maybe what you are looking for
SELECT CustomerID, COUNT(*) AS ordercnt
FROM Order
GROUP BY CustomerID
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 17, 2016 at 12:49 pm
Hi,
I have attached the sample data from the query ad the order table.
Thanks,
April 17, 2016 at 12:55 pm
fareedhmohamed (4/17/2016)
Hi,I have attached the sample data from the query ad the order table.
Thanks,
ok...you have given yet another scree shot....please re read http://www.sqlservercentral.com/Forums/FindPost1778496.aspx
now....what are you expecting?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 17, 2016 at 1:19 pm
in an effort to help you move on and hopefully to use as a basis for any future questions that you post
and we are nowhere near using triggers yet
CREATE TABLE #yourorder(
OrderlD INTEGER NOT NULL PRIMARY KEY
,OrderDate DATETIME NOT NULL
,Total_Price NUMERIC(5,2) NOT NULL
,ProductID INTEGER NOT NULL
,Product_Name VARCHAR(12) NOT NULL
,CustomerID INTEGER NOT NULL
,TotalQuantity INTEGER NOT NULL
);
INSERT INTO #yourorder(OrderlD,OrderDate,Total_Price,ProductID,Product_Name,CustomerID,TotalQuantity) VALUES
(1,'2016-11-04',5.62,1,'Baked Beans',1,2)
,(2,'2016-11-04',5.62,1,'Baked Beans',1,2)
,(3,'2016-12-04',15.00,2,'T-Shirt',2,1)
,(4,'2016-10-04',2.00,6,'Cusps',1,2)
,(5,'2016-01-04',60.00,3,'Jeans',4,3)
,(6,'2016-12-04',24.00,5,'Fish Fingers',10,6)
,(7,'2016-03-29',8.00,6,'Socks',7,2)
,(8,'2016-01-01',4.00,1,'Bread',7,4)
,(9,'2016-02-02',2.50,4,'Pencil',8,5);
--your query
SELECT COUNT(CustomerID), TotalQuantity, CustomerID, Product_Name
FROM #yourorder
GROUP BY TotalQuantity, CustomerID, Product_Name;
--my suggestion
SELECT CustomerID, COUNT(*) AS ordercnt
FROM #yourOrder
GROUP BY CustomerID
DROP TABLE #yourOrder
so...we now have a test table, some sample data and two different queries......what thoughts?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 17, 2016 at 1:32 pm
Hi,
Well thanks for the effort.
Your suggestion works too. The only reason I added the product Name was for some clarification. To make sure it was pulling the right data.
Thanks,
April 17, 2016 at 1:37 pm
fareedhmohamed (4/17/2016)
Hi,Well thanks for the effort.
Your suggestion works too. The only reason I added the product Name was for some clarification. To make sure it was pulling the right data.
Thanks,
ok so where do we go next.....???
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply