July 17, 2011 at 3:32 pm
Hi,
I have a query and want your help with it.
-----------------Question ------------------------
Retrieve a report of contact name, sales rep name, the total number of orders per contact and the total value of all orders per contact delivered since midnight this morning.
-------THis is what I have until now ------------
SELECT [1contact].name AS CONTACTNAME, [1sales_reps].name AS SALESREPNAME,COUNT([1orders].account_ID)
FROM [1sales_reps] INNER JOIN
[1accounts] ON [1sales_reps].sales_rep_id = [1accounts].sales_rep_id INNER JOIN
[1contact] ON [1accounts].account_id = [1contact].account_ID INNER JOIN
[1orders] ON [1contact].account_ID = [1orders].account_ID
where datepart(DD,[1orders].dilivery_date) = DATEPART(dd,getdate())
group BY [1contact].name,[1sales_reps].name
--------Below are all the scripts for you -----------
Schema -
USE [Liheap2]
GO
/****** Object: Table [dbo].[1sales_reps] Script Date: 07/17/2011 17:28:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[1sales_reps](
[sales_rep_id] [int] NOT NULL,
[name] [varchar](45) NULL,
[commission_rate] [int] NULL,
PRIMARY KEY CLUSTERED
(
[sales_rep_id] 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
/****** Object: Table [dbo].[1parts] Script Date: 07/17/2011 17:28:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[1parts](
[part_id] [int] NOT NULL,
[description] [varchar](45) NULL,
[price] [varchar](45) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[1orders] Script Date: 07/17/2011 17:28:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[1orders](
[orders_id] [int] NOT NULL,
[account_ID] [int] NULL,
[dilivery_date] [datetime] NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[1order_items] Script Date: 07/17/2011 17:28:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[1order_items](
[orders_items_id] [int] NOT NULL,
[order_ID] [int] NULL,
[part_id] [int] NULL,
[quantity] [int] NULL,
PRIMARY KEY CLUSTERED
(
[orders_items_id] 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
/****** Object: Table [dbo].[1contact] Script Date: 07/17/2011 17:28:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[1contact](
[contact_id] [int] NOT NULL,
[account_ID] [int] NULL,
[name] [varchar](45) NULL,
[phone] [varchar](45) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[1accounts] Script Date: 07/17/2011 17:28:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[1accounts](
[account_id] [int] NOT NULL,
[credit_limit] [int] NULL,
[sales_rep_id] [int] NULL
) ON [PRIMARY]
GO
------------Insert statements ------------
USE [Liheap2];
SET NOCOUNT ON;
SET XACT_ABORT ON;
GO
BEGIN TRANSACTION;
INSERT INTO [dbo].[1accounts]([account_id], [credit_limit], [sales_rep_id])
SELECT 50, 4500, 10 UNION ALL
SELECT 55, 5000, 10 UNION ALL
SELECT 60, 5100, 20
COMMIT;
RAISERROR (N'[dbo].[1accounts]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;
GO
BEGIN TRANSACTION;
INSERT INTO [dbo].[1contact]([contact_id], [account_ID], [name], [phone])
SELECT 100, 50, N'tim', N'2172172171' UNION ALL
SELECT 101, 55, N'Mike', N'7127127121' UNION ALL
SELECT 102, 60, N'Sid', N'0909090909'
COMMIT;
RAISERROR (N'[dbo].[1contact]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;
GO
BEGIN TRANSACTION;
INSERT INTO [dbo].[1order_items]([orders_items_id], [order_ID], [part_id], [quantity])
SELECT 30, 1, 17, 2 UNION ALL
SELECT 31, 2, 17, 4 UNION ALL
SELECT 32, 3, 8, 2 UNION ALL
SELECT 33, 4, 9, 4
COMMIT;
RAISERROR (N'[dbo].[1order_items]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;
GO
BEGIN TRANSACTION;
INSERT INTO [dbo].[1orders]([orders_id], [account_ID], [dilivery_date])
SELECT 1, 55, '20110101 00:00:00.000' UNION ALL
SELECT 2, 55, '20110303 00:00:00.000' UNION ALL
SELECT 3, 50, '20110908 00:00:00.000' UNION ALL
SELECT 4, 60, '20110703 00:00:00.000'
COMMIT;
RAISERROR (N'[dbo].[1orders]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;
GO
BEGIN TRANSACTION;
INSERT INTO [dbo].[1parts]([part_id], [description], [price])
SELECT 5, N'widget12', N'3000' UNION ALL
SELECT 6, N'12widget12', N'4000' UNION ALL
SELECT 5, N'widget12', N'3000' UNION ALL
SELECT 6, N'12widget12', N'4000' UNION ALL
SELECT 8, N'spark', N'700' UNION ALL
SELECT 9, N'laptop', N'7000'
COMMIT;
RAISERROR (N'[dbo].[1parts]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;
GO
BEGIN TRANSACTION;
INSERT INTO [dbo].[1sales_reps]([sales_rep_id], [name], [commission_rate])
SELECT 10, N'Matt', 10 UNION ALL
SELECT 20, N'Fred', 20 UNION ALL
SELECT 30, N'Betty', 30
COMMIT;
RAISERROR (N'[dbo].[1sales_reps]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;
GO
----------------------------------------------------------------------
July 17, 2011 at 4:18 pm
Sounds like test or homework. (what other reason would exist that you cannot reformat the question?)
[Edit] hint: in order to find all orders from today you could use something like
WHERE [1orders].dilivery_date > = DATEADD(dd,DATEDIFF(dd,0,getdate()),0)
July 17, 2011 at 8:21 pm
Well. I just lazy to change the formatting. But I have changed it now. To be specific, I wanted to know a way to find a way to get the 'total value of all the orders'. I think using a CTE would do the trick but wanted to advices from the Pros.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply