Queries - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Queries

Author

Topic

lakers34kb
Starting Member

15 Posts

Posted - 2010-03-02 : 11:14:28

Queries to satisfy these two report requests (use your CCI database):

1. Retrieve all rows of active inventory where current on hands is less than minimum level. Create a field in the results that computes the difference between the max and current on hands. Name this field "ReorderQuantity". Include vendor info to make reordering easier.

2. Number of all Vendors that are not the preferred vendor that supply products for CategoryId 2. Exclude all disabled products and those introduced into the system prior to 2007.

///////////////////////////

Anyone know how to get started on this? Any help will be appreciated. thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-02 : 11:40:15

can we see what you started on with?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

lakers34kb
Starting Member

15 Posts

Posted - 2010-03-02 : 11:48:39

I uploaded the document with it all here. http://www.megaupload.com/?d=K9C0Q9RW

lakers34kb
Starting Member

15 Posts

Posted - 2010-03-02 : 18:46:11

This is the database.

USE [CCIGDB]
GO
/****** Object: Table [dbo].[Product] Script Date: 01/15/2010 21:23:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Product](
[ProductId] [int] NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[Desription] [nvarchar](255) NOT NULL,
[SKU] [nchar](10) NOT NULL,
[Price] [smallmoney] NOT NULL,
[Max] [numeric](18, 0) NOT NULL,
[Min] [numeric](18, 0) NOT NULL,
[Quantity] [numeric](18, 0) NOT NULL,
[InventoryId] [int] NOT NULL,
[Active] [bit] NOT NULL,
[Introduction] [datetime] NOT 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
ALTER TABLE [dbo].[Product] WITH CHECK ADD CONSTRAINT [FK_Product_Inventory] FOREIGN KEY([InventoryId])
REFERENCES [dbo].[Inventory] ([InventoryId])
GO
ALTER TABLE [dbo].[Product] CHECK CONSTRAINT [FK_Product_Inventory]

USE [CCIGDB]
GO
/****** Object: Table [dbo].[Vendors] Script Date: 01/15/2010 21:24:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Vendors](
[VendorId] [int] NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[Address] [nvarchar](50) NOT NULL,
[City] [nvarchar](50) NOT NULL,
[State] [nvarchar](50) NOT NULL,
[Zip] [nchar](10) NOT NULL,
[Phone] [nchar](12) NOT NULL,
[Fax] [nchar](12) NULL,
[Email] [nvarchar](max) NULL,
[Active] [bit] NOT NULL,
CONSTRAINT [PK_Vendors] PRIMARY KEY CLUSTERED
(
[VendorId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

USE [CCIGDB]
GO
/****** Object: Table [dbo].[Inventory] Script Date: 01/15/2010 21:25:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Inventory](
[InventoryId] [int] NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[Description] [varchar](max) NOT NULL,
CONSTRAINT [PK_Inventory] PRIMARY KEY CLUSTERED
(
[InventoryId] 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

USE [CCIGDB]
GO
/****** Object: Table [dbo].[Supplier] Script Date: 01/15/2010 21:26:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Supplier](
[SupplierId] [int] NOT NULL,
[ProductId] [int] NOT NULL,
[VendorId] [int] NOT NULL,
[Perferred] [bit] NOT NULL,
CONSTRAINT [PK_Supplier] PRIMARY KEY CLUSTERED
(
[SupplierId] 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
ALTER TABLE [dbo].[Supplier] WITH CHECK ADD CONSTRAINT [FK_Supplier_Product] FOREIGN KEY([ProductId])
REFERENCES [dbo].[Product] ([ProductId])
GO
ALTER TABLE [dbo].[Supplier] CHECK CONSTRAINT [FK_Supplier_Product]
GO
ALTER TABLE [dbo].[Supplier] WITH CHECK ADD CONSTRAINT [FK_Supplier_Vendors] FOREIGN KEY([VendorId])
REFERENCES [dbo].[Vendors] ([VendorId])
GO
ALTER TABLE [dbo].[Supplier] CHECK CONSTRAINT [FK_Supplier_Vendors]

lakers34kb
Starting Member

15 Posts

Posted - 2010-03-02 : 19:50:22

Can anyone help? Willing to paypal some money for time.

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-03-05 : 16:35:44

It is like people do not know what you want.

You should provide your example tables (with data) and the result that you want query to return.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-06 : 02:13:55

show some sample data and explain what you need as output

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources