January 9, 2009 at 11:32 am
PRODUCTID PRODUCTGROUP LEVELS
1 CFA L1
2 FRM L2
3 CAIA L5
4 PD L6
5 CA L7
6 CAA L8
7 PERL1 L9
8 CKAA A1
10 CFSA A2
Requirement: I need a report where no one can order more than one productgroup but have exception if
CFA &FRM Ordered Together that means they can order any number on this combination.
CAIA & CFA Ordered Together that means they can order any number on this combination.
CAIA & FRM Ordered Together that means they can order any number on this combination.
Please help me on this query.
Thanks In Advance.
&
Thanks for reading my post.
January 9, 2009 at 11:56 am
January 9, 2009 at 11:56 am
Please post some data using the following http://www.sqlservercentral.com/articles/Best+Practices/61537/.
It will help people help you, since they will have some real data. Try to explain what you want to do and give a snippet of your table, and query too.
Cheers,
J-F
Cheers,
J-F
January 9, 2009 at 12:26 pm
I'm not clear on what's being requested. What does a report have to do with how many someone can order of something?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 9, 2009 at 12:36 pm
Requirement: I need a query to report where if any one ordered more than one product group it should show me on report but have exception if
CFA &FRM Ordered Together that means they can order any number on this combination.
CAIA & CFA Ordered Together that means they can order any number on this combination.
CAIA & FRM Ordered Together that means they can order any number on this combination.
January 9, 2009 at 12:39 pm
Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 9, 2009 at 12:45 pm
Table Name : Products And Orders
PRODUCTID PRODUCTGROUP LEVELS
1 CFA L1
2 FRM L2
3 CAIA L5
4 PD L6
5 CA L7
6 CAA L8
7 PERL1 L9
8 CKAA A1
10 CFSA A2
Ordrid productid quantity
001 1 1
002 10 1
003 1 2
004 2 1
Requirement: I need a query to report where if any one ordered more than one product group it should show me on report but have exception if
CFA &FRM Ordered Together that means they can order any number on this combination.
CAIA & CFA Ordered Together that means they can order any number on this combination.
CAIA & FRM Ordered Together that means they can order any number on this combination.
Please help me on this query.
Thanks In Advance.
&
Thanks for reading my post.
January 9, 2009 at 1:01 pm
Did you read the link that I and several other people posted?
Based on the sample data that you gave, what are the exact results that you want?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 9, 2009 at 1:22 pm
Table Name : Products And Orderdetail
PRODUCTID PRODUCTGROUP LEVELS
1 CFA L1
2 FRM L2
3 CAIA L5
4 PD L6
5 CA L7
6 CAA L8
7 PERL1 L9
8 CKAA A1
10 CFSA A2
Ordrid productid quantity
001 1 1
002 10 1
003 1 2
004 2 1
Requirement: I need a query to report where if any one ordered more than one product group it should show me on report but have exception if
CFA &FRM Ordered Together that means they can order any number on this combination.
CAIA & CFA Ordered Together that means they can order any number on this combination.
CAIA & FRM Ordered Together that means they can order any number on this combination.
Table script as follows:
Orderdetail:
USE [Production]
GO
/****** Object: Table [dbo].[OrderDetail] Script Date: 01/09/2009 14:15:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[OrderDetail](
[OrderDetailID] [int] IDENTITY(1,1) NOT NULL,
[OrderID] [int] NULL,
[WebOrderDetailID] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[ProductID] [int] NULL,
[PackageProductID] [int] NULL CONSTRAINT [DF_OrderDetail_PackageID] DEFAULT ((0)),
[CustomerID] [int] NULL CONSTRAINT [DF_OrderDetail_CustomerID] DEFAULT ((0)),
[WarehouseID] [int] NULL CONSTRAINT [DF_OrderDetail_WarehouseID] DEFAULT ((1)),
[FirstName] [varchar](50) COLLATE Latin1_General_CI_AI NULL CONSTRAINT [DF_OrderDetail_FirstName] DEFAULT (''),
[LastName] [varchar](50) COLLATE Latin1_General_CI_AI NULL CONSTRAINT [DF_OrderDetail_LastName] DEFAULT (''),
[Quantity] [int] NULL CONSTRAINT [DF_OrderDetail_Quantity] DEFAULT ((1)),
[CustomerPrice] [money] NULL CONSTRAINT [DF_OrderDetail_CustomerPrice] DEFAULT ((0)),
[TaxablePrice] [money] NULL CONSTRAINT [DF_OrderDetail_TaxablePrice] DEFAULT ((0)),
[EstimatedValue] [money] NULL CONSTRAINT [DF_OrderDetail_EstimatedValue] DEFAULT ((0)),
[CustomsValue] [money] NULL CONSTRAINT [DF_OrderDetail_CustomsValue] DEFAULT ((0)),
[SubTotal] [money] NULL CONSTRAINT [DF_OrderDetail_SubTotal] DEFAULT ((0)),
[FreightCharge] [money] NULL CONSTRAINT [DF_OrderDetail_FreightCharge] DEFAULT ((0)),
[TaxAmount] [money] NULL CONSTRAINT [DF_OrderDetail_TaxAmount] DEFAULT ((0)),
[TotalWithTax] [money] NULL CONSTRAINT [DF_OrderDetail_TotalWithTax] DEFAULT ((0)),
[InProcess] [bit] NULL CONSTRAINT [DF_OrderDetail_InProcess] DEFAULT ((0)),
[ShippingDate] [smalldatetime] NULL,
[UPSTracking] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[Approved] [bit] NULL CONSTRAINT [DF_OrderDetail_Approved] DEFAULT ((0)),
[ToBeShipped] [bit] NULL CONSTRAINT [DF_OrderDetail_ToBeShipped] DEFAULT ((0)),
[Paid] [bit] NULL CONSTRAINT [DF_OrderDetail_Paid] DEFAULT ((0)),
[ToPrint] [bit] NULL CONSTRAINT [DF_OrderDetail_ToPrint] DEFAULT ((0)),
[PrintDate] [datetime] NULL,
[ReturnQuantity] [int] NULL CONSTRAINT [DF_OrderDetail_ReturnQuantity] DEFAULT ((0)),
[ReturnDate] [datetime] NULL,
[CreditAmount] [money] NULL CONSTRAINT [DF_OrderDetail_CreditAmount] DEFAULT ((0)),
[CreditDate] [datetime] NULL,
[ARDate] [datetime] NULL,
[Last_update] [datetime] NULL CONSTRAINT [DF_OrderDetail_Last_update] DEFAULT (getdate()),
[LastUpdatedBy] [varchar](80) COLLATE Latin1_General_CI_AI NULL CONSTRAINT [DF_OrderDetail_LastUpdatedBy] DEFAULT (host_name()),
[PONumber] [varchar](100) COLLATE Latin1_General_CI_AI NULL,
[IsRecognitionComplete] [bit] NULL CONSTRAINT [DF_OrderDetail_IsRecognitionComplete] DEFAULT ((0)),
[UpgradeFromProductID] [int] NULL,
[IsRecognized] [bit] NULL,
[OrgCode] [varchar](10) COLLATE Latin1_General_CI_AI NULL,
[BatchID] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
CONSTRAINT [PK_OrderDetail] PRIMARY KEY CLUSTERED
(
[OrderDetailID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [Production]
GO
ALTER TABLE [dbo].[OrderDetail] WITH CHECK ADD CONSTRAINT [FK_OrderDetail_Organizations] FOREIGN KEY([OrgCode])
REFERENCES [dbo].[Organizations] ([OrgCode])
ON UPDATE CASCADE
Product:
USE [Production]
GO
/****** Object: Table [dbo].[Product] Script Date: 01/09/2009 14:17:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Product](
[ProductID] [int] IDENTITY(1,1) NOT NULL,
[CourseID] [varchar](50) COLLATE Latin1_General_CI_AI NULL CONSTRAINT [DF_Product_CourseID] DEFAULT (''),
[PPN] [varchar](20) COLLATE Latin1_General_CI_AI NULL CONSTRAINT [DF_Product_PPN] DEFAULT (''),
[ISBN] [varchar](50) COLLATE Latin1_General_CI_AI NULL CONSTRAINT [DF_Product_ISBN] DEFAULT (''),
[CostCenterID] [varchar](4) COLLATE Latin1_General_CI_AI NULL,
[Levels] [varchar](20) COLLATE Latin1_General_CI_AI NULL,
[ProductName] [varchar](200) COLLATE Latin1_General_CI_AI NULL,
[ProductCat] [varchar](50) COLLATE Latin1_General_CI_AI NULL CONSTRAINT [DF_Product_ProductCat] DEFAULT (''),
[ProductYear] [varchar](20) COLLATE Latin1_General_CI_AI NULL,
[ProductGroup] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[ProductType] [varchar](50) COLLATE Latin1_General_CI_AI NULL CONSTRAINT [DF_Product_ProductType] DEFAULT (''),
[DescHarmCode] [varchar](250) COLLATE Latin1_General_CI_AI NULL CONSTRAINT [DF_Product_DescHarmCode] DEFAULT (''),
[ProductDesc] [varchar](50) COLLATE Latin1_General_CI_AI NULL CONSTRAINT [DF_Product_ProductDesc] DEFAULT (''),
[UnitPrice] [money] NULL CONSTRAINT [DF_Product_UnitPrice_1] DEFAULT ((0)),
[CanadianPrice] [money] NULL CONSTRAINT [DF_Product_CanadianPrice] DEFAULT ((0)),
[TaxablePrice] [money] NULL CONSTRAINT [DF_Product_TaxablePrice_1] DEFAULT ((0)),
[Weight] [float] NULL CONSTRAINT [DF_Product_Weight_1] DEFAULT ((0)),
[DeliveryType] [int] NULL CONSTRAINT [DF_Product_DeliveryType] DEFAULT ((0)),
[Sequence] [int] NULL CONSTRAINT [DF_Product_Sequence] DEFAULT ((0)),
[Taxable] [bit] NULL CONSTRAINT [DF_Product_Taxable] DEFAULT ((0)),
[Viewable] [bit] NULL CONSTRAINT [DF_Product_Viewable] DEFAULT ((0)),
[WebViewable] [bit] NULL CONSTRAINT [DF_Product_WebViewable] DEFAULT ((0)),
[SurePay] [bit] NULL CONSTRAINT [DF_Product_SurePay] DEFAULT ((0)),
[InProduction] [bit] NOT NULL CONSTRAINT [DF_Product_InProduction] DEFAULT ((0)),
[WorldWide] [int] NULL CONSTRAINT [DF_Product_WorldWide] DEFAULT ((0)),
[MaxNumber] [int] NULL CONSTRAINT [DF_Product_MaxNumber] DEFAULT ((0)),
[QuantityOnHand] [numeric](18, 0) NULL CONSTRAINT [DF_Product_QuantityOnHand_1] DEFAULT ((0)),
[ReorderPoint] [float] NULL CONSTRAINT [DF_Product_ReorderPoint] DEFAULT ((0)),
[BackOrder] [bit] NULL CONSTRAINT [DF_Product_BackOrder] DEFAULT ((0)),
[ReorderNotify] [int] NULL CONSTRAINT [DF_Product_ReorderNotify] DEFAULT ((1)),
[ExpectedDate] [datetime] NULL,
[ExpireDate] [datetime] NULL,
[Note] [varchar](4000) COLLATE Latin1_General_CI_AI NULL,
[Last_update] [datetime] NULL CONSTRAINT [DF_Product_Last_update] DEFAULT (getdate()),
[LastUpdatedBy] [varchar](80) COLLATE Latin1_General_CI_AI NULL CONSTRAINT [DF_Product_LastUpdatedBy] DEFAULT (host_name()),
[RevenueRecognitionType] [varchar](2) COLLATE Latin1_General_CI_AI NULL,
[IsGradingRequired] [bit] NULL,
[SSNRequired] [bit] NULL,
[StateCode] [char](2) COLLATE Latin1_General_CI_AI NULL,
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
[ProductID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [Production]
GO
ALTER TABLE [dbo].[Product] WITH CHECK ADD CONSTRAINT [FK_Product_States] FOREIGN KEY([StateCode])
REFERENCES [dbo].[States] ([StateCode])
ON UPDATE CASCADE
-----------------------------------------------------------------------insert into script:
1.
INSERT INTO [Production].[dbo].[OrderDetail]
([OrderID]
,[WebOrderDetailID]
,[ProductID]
,[PackageProductID]
,[CustomerID]
,[WarehouseID]
,[FirstName]
,[LastName]
,[Quantity]
,[CustomerPrice]
,[TaxablePrice]
,[EstimatedValue]
,[CustomsValue]
,[SubTotal]
,[FreightCharge]
,[TaxAmount]
,[TotalWithTax]
,[InProcess]
,[ShippingDate]
,[UPSTracking]
,[Approved]
,[ToBeShipped]
,[Paid]
,[ToPrint]
,[PrintDate]
,[ReturnQuantity]
,[ReturnDate]
,[CreditAmount]
,[CreditDate]
,[ARDate]
,[Last_update]
,[LastUpdatedBy]
,[PONumber]
,[IsRecognitionComplete]
,[UpgradeFromProductID]
,[IsRecognized]
,[OrgCode]
,[BatchID])
VALUES
(
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
, )
2.
INSERT INTO [Production].[dbo].[Product]
([CourseID]
,[PPN]
,[ISBN]
,[CostCenterID]
,[Levels]
,[ProductName]
,[ProductCat]
,[ProductYear]
,[ProductGroup]
,[ProductType]
,[DescHarmCode]
,[ProductDesc]
,[UnitPrice]
,[CanadianPrice]
,[TaxablePrice]
,[Weight]
,[DeliveryType]
,[Sequence]
,[Taxable]
,[Viewable]
,[WebViewable]
,[SurePay]
,[InProduction]
,[WorldWide]
,[MaxNumber]
,[QuantityOnHand]
,[ReorderPoint]
,[BackOrder]
,[ReorderNotify]
,[ExpectedDate]
,[ExpireDate]
,[Note]
,[Last_update]
,[LastUpdatedBy]
,[RevenueRecognitionType]
,[IsGradingRequired]
,[SSNRequired]
,[StateCode])
VALUES
(
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
, )
Please help me on this query.
Thanks In Advance.
&
Thanks for reading my post.
January 9, 2009 at 1:28 pm
I don't see from the sample where anyone could order more than 1 product.
There is no customer key, nor is it apparent if an order is one line, or multiple lines.
I'm confused to whether this is a report, or possibly related to enforcing business logic / rules on order entry.
Without more of the structure (in the format requested several times previously), it will be hard give much help.
Greg E
January 9, 2009 at 1:29 pm
Great, we've got table structure and sample data.
Based on the sample data you have posted, what are the exact results that you want? Post exactly what values you need the query to return.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 9, 2009 at 1:36 pm
GilaMonster (1/9/2009)
Great, we've got table structure and sample data.Based on the sample data you have posted, what are the exact results that you want? Post exactly what values you need the query to return.
hmmmm, something missing I am. No data I see in the insert statements.
Perhaps, first article below you should read (many have suggested same).
January 9, 2009 at 1:37 pm
Does an order have multiple lines?
Our system has a Order Header with a Doc # and type, and individual lines - 1 line per item.
Greg E
January 9, 2009 at 1:42 pm
I didn't see any sample data either. Must be either my old eyes, or the time on the Friday afternoon clock. Almost time to hang it up for the week. 😛
Greg E
January 9, 2009 at 1:45 pm
Lynn Pettis (1/9/2009)
hmmmm, something missing I am. No data I see in the insert statements.
It's do-it-yourself sample data. Values at the top of the post, insert statements at the bottom. 😛 😀
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply