April 30, 2011 at 12:25 am
Hi,
I am new to SQL and I am still learning SQL. Please help me with this query . Here is the situation
--Table structure:
--Region: region_id, name
--Employee: employee_id, name, region_id
--Sales: sales_id, employee_id, sale_date, sale_amount
-- -there’s an individual entry in the sales table for each sale
--I have write a query that returns a list of the Regions
--and the number of employees in each region. The employee only gets added to the count
--if they had total sales greater than $10,000 last month.
--Sort the information by the number of employees per region who fit this criteria.
Here is what I have but I am having problems with adding the $10,000 criteria and the last Month. Below is what I have written so far.
SELECT region.name, count(employee.employee_id) as Num
FROM employee INNER JOIN
region ON employee.regionid = region.regionid INNER JOIN
sale ON employee.employee_id = sale.employee_id
GROUP BY region.name
order by Num
April 30, 2011 at 12:44 am
NewBie23 (4/30/2011)
Hi,I am new to SQL and I am still learning SQL. Please help me with this query . Here is the situation
--Table structure:
--Region: region_id, name
--Employee: employee_id, name, region_id
--Sales: sales_id, employee_id, sale_date, sale_amount
-- -there’s an individual entry in the sales table for each sale
--I have write a query that returns a list of the Regions
--and the number of employees in each region. The employee only gets added to the count
--if they had total sales greater than $10,000 last month.
--Sort the information by the number of employees per region who fit this criteria.
Here is what I have but I am having problems with adding the $10,000 criteria and the last Month. Below is what I have written so far.
SELECT region.name, count(employee.employee_id) as Num
FROM employee INNER JOIN
region ON employee.regionid = region.regionid INNER JOIN
sale ON employee.employee_id = sale.employee_id
GROUP BY region.name
order by Num
May I suggest that you read the first article I reference below in my signature block regarding asking for help? If you follow the instructions in that article regarding posting DDL (CREATE TABLE statements), sample data (INSERT INTO statements), as well as the expected output based on the sample data, you will get much better assistance, in addition to tested code in return for the extra effort on your part.
Please remember, we are volunteers giving of our own time to assist. The more you do up front, the easier it is for us to help you.
April 30, 2011 at 12:58 am
The following is untested but may help you with your problem. Hopefully you are using SQL Server 2008 (this is the forum you posted in) or at least SQL Server 2005.
with PrevSales10000 (
employee_id,
sale_amount
) as (
select
emp.employee_id,
sum(sal.sale_amount)
from
dbo.employee emp
inner join dbo.sales sal
on (emp.employee_id = sal.employee_id)
where
sale_date >= dateadd(mm, datediff(mm, 0, getdate()) - 1, 0) and
sale_date < dateadd(mm, datediff(mm, 0, getdate()), 0)
group by
emp.employee_id
having
sum(sal.sale_amount) > 10000
)
select
reg.region_id,
count(ps.employee_id) as EmpCnt
from
dbo.region reg
inner join PrevSales10000 ps
on (reg.employee_id = ps.employee_id);
April 30, 2011 at 1:03 am
I am sorry Lynn . Below is all the info you require.
/****** Object: Table [dbo].[sale] Script Date: 04/30/2011 02:01:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[sale](
[sales_id] [int] NULL,
[employee_id] [int] NULL,
[sale_date] [datetime] NULL,
[sale_amount] [money] NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[region] Script Date: 04/30/2011 02:01:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[region](
[regionid] [int] NULL,
[name] [varchar](10) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[employee] Script Date: 04/30/2011 02:01:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[employee](
[regionid] [int] NULL,
[employee_id] [int] NULL,
[name] [varchar](10) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET NOCOUNT ON;
SET XACT_ABORT ON;
GO
BEGIN TRANSACTION;
INSERT INTO [dbo].[employee]([regionid], [employee_id], [name])
SELECT 1, 1, N'rick' UNION ALL
SELECT 1, 2, N'tom' UNION ALL
SELECT 2, 3, N'Jesse' UNION ALL
SELECT 2, 4, N'Rose' UNION ALL
SELECT 3, 5, N'Micky'
COMMIT;
RAISERROR (N'[dbo].[employee]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;
GO
BEGIN TRANSACTION;
INSERT INTO [dbo].[region]([regionid], [name])
SELECT 1, N'chicago' UNION ALL
SELECT 2, N'LA' UNION ALL
SELECT 3, N'Reno' UNION ALL
SELECT 4, N'Hyd'
COMMIT;
RAISERROR (N'[dbo].[region]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;
GO
BEGIN TRANSACTION;
INSERT INTO [dbo].[sale]([sales_id], [employee_id], [sale_date], [sale_amount])
SELECT 1, 1, '20110323 00:00:00.000', 20000.0000 UNION ALL
SELECT 2, 1, '20110323 00:00:00.000', 40000.0000 UNION ALL
SELECT 3, 2, '20110323 00:00:00.000', 20000.0000 UNION ALL
SELECT 4, 2, '20110323 00:00:00.000', 60000.0000 UNION ALL
SELECT 5, 3, '20110323 00:00:00.000', 20000.0000 UNION ALL
SELECT 6, 4, '20110123 00:00:00.000', 80000.0000 UNION ALL
SELECT 7, 5, '20110323 00:00:00.000', 10000.0000
COMMIT;
RAISERROR (N'[dbo].[sale]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;
GO
April 30, 2011 at 1:06 am
NewBie23 (4/30/2011)
I am sorry Lynn . Below is all the info you require./****** Object: Table [dbo].[sale] Script Date: 04/30/2011 02:01:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[sale](
[sales_id] [int] NULL,
[employee_id] [int] NULL,
[sale_date] [datetime] NULL,
[sale_amount] [money] NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[region] Script Date: 04/30/2011 02:01:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[region](
[regionid] [int] NULL,
[name] [varchar](10) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[employee] Script Date: 04/30/2011 02:01:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[employee](
[regionid] [int] NULL,
[employee_id] [int] NULL,
[name] [varchar](10) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET NOCOUNT ON;
SET XACT_ABORT ON;
GO
BEGIN TRANSACTION;
INSERT INTO [dbo].[employee]([regionid], [employee_id], [name])
SELECT 1, 1, N'rick' UNION ALL
SELECT 1, 2, N'tom' UNION ALL
SELECT 2, 3, N'Jesse' UNION ALL
SELECT 2, 4, N'Rose' UNION ALL
SELECT 3, 5, N'Micky'
COMMIT;
RAISERROR (N'[dbo].[employee]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;
GO
BEGIN TRANSACTION;
INSERT INTO [dbo].[region]([regionid], [name])
SELECT 1, N'chicago' UNION ALL
SELECT 2, N'LA' UNION ALL
SELECT 3, N'Reno' UNION ALL
SELECT 4, N'Hyd'
COMMIT;
RAISERROR (N'[dbo].[region]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;
GO
BEGIN TRANSACTION;
INSERT INTO [dbo].[sale]([sales_id], [employee_id], [sale_date], [sale_amount])
SELECT 1, 1, '20110323 00:00:00.000', 20000.0000 UNION ALL
SELECT 2, 1, '20110323 00:00:00.000', 40000.0000 UNION ALL
SELECT 3, 2, '20110323 00:00:00.000', 20000.0000 UNION ALL
SELECT 4, 2, '20110323 00:00:00.000', 60000.0000 UNION ALL
SELECT 5, 3, '20110323 00:00:00.000', 20000.0000 UNION ALL
SELECT 6, 4, '20110123 00:00:00.000', 80000.0000 UNION ALL
SELECT 7, 5, '20110323 00:00:00.000', 10000.0000
COMMIT;
RAISERROR (N'[dbo].[sale]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;
GO
Partly there, I don't see the expected results based on provided sample data. This shows us what you are looking for and provides us something to test against as well.
April 30, 2011 at 1:12 am
I understand Lynn. I told you that I was new. Here is what I have
SELECT region.name, count(employee.employee_id) as Num
FROM employee INNER JOIN
region ON employee.regionid = region.regionid INNER JOIN
sale ON employee.employee_id = sale.employee_id
GROUP BY region.name
having SUM(sale.sale_amount) > 50000
order by Num
---------------Not sure how to get only the records of previous month. Is it something to do with DATEDIFF
April 30, 2011 at 1:36 am
NewBie23 (4/30/2011)
I understand Lynn. I told you that I was new. Here is what I haveSELECT region.name, count(employee.employee_id) as Num
FROM employee INNER JOIN
region ON employee.regionid = region.regionid INNER JOIN
sale ON employee.employee_id = sale.employee_id
GROUP BY region.name
having SUM(sale.sale_amount) > 50000
order by Num
---------------Not sure how to get only the records of previous month. Is it something to do with DATEDIFF
Have you even looked at my other posts?
April 30, 2011 at 9:12 am
The silence is deafening. Did any of my posts help you with your problem?
April 30, 2011 at 10:46 am
Lynn,
I am sorry. I was working late and went to bed the moment I posted my question. I looked at the query which you posted and after a minor change, it works perfectly fine. Here is the end product.
Thanks a ton.
with PrevSales10000 (
regionid,
employee_id,
sale_amount
) as (
select
reg.regionid,
emp.employee_id,
sum(sal.sale_amount)
from
dbo.employee emp
inner join dbo.sale sal
on (emp.employee_id = sal.employee_id) inner join region reg
on reg.regionid = emp.regionid
where
sale_date >= dateadd(mm, datediff(mm, 0, getdate()) - 1, 0) and
sale_date < dateadd(mm, datediff(mm, 0, getdate()), 0)
group by
emp.employee_id,reg.regionid
having
sum(sal.sale_amount) > 10000
)
select
reg.regionid,
count(ps.employee_id) as EmpCnt
from
dbo.region reg
inner join PrevSales10000 ps
on (reg.regionid = ps.regionid)
group by reg.regionid;
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply