Help with Complex Query - 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

General SQL Server Forums

New to SQL Server Programming

Help with Complex Query

Author

Topic

BobRoberts
Posting Yak Master

109 Posts

Posted - 2012-01-05 : 11:28:41

Thanks in advance for reading this. I have a table called TBL_customer_visits that contains the customer ID, date of each visit, and a running lifetime total for sales, stored as an int field:


CustID  Visit Date          LTD_Sales
-------------------------------------
111111	2010-03-17 00:00:00	700
111111	2010-11-30 00:00:00	1025
333333	2010-12-29 00:00:00	10378
333333	2011-05-19 00:00:00	10876
444444	2011-10-21 00:00:00	600
444444	2011-11-02 19:00:00	720
444444	2011-12-15 00:00:00	1000
444444	2012-01-01 00:00:00	8998
555555	2010-07-12 00:00:00	350
555555	2011-09-21 00:00:00	1100
555555	2012-01-03 00:00:00	1480
666666	2011-06-28 00:00:00	300
666666	2011-07-14 00:00:00	800
666666	2011-08-30 00:00:00	920
222222	2010-12-18 00:00:00	1180
777777	2012-01-02 00:00:00	2350
888888	2010-09-12 00:00:00	15575
888888	2011-01-22 00:00:00	16076

I'm trying to generate a query that will list those customers who will receive a reward.  The reward will be based on a $500 level of sales 

reached in the year 2011.  However, the award requires a minimum of $1000 in sales. The report should list the customer id, visit date the 

level is reached, the level, and the actual sales on that date.

For the data above, customer 111111 would not qualify because all sales are in year 2010.

Customer 333333 would not qualify because the 10876 in sales only reflects an increase of 498 in year 2011.

Customer 444444 would qualify on visit date 12-15-2011 at sales of 1000 (which is the mimimum level).

Customer 555555 would qualify on visit date 09-21-2011 at level 1000 on sales of 1100.

Customer 666666 would not qualify because sales are below the minimum 1000 level.

Customer 222222 would not qualify because sales of 1180 are for year 2010.

Customer 777777 would not qualify because sales are for year 2012.

Customer 888888 would qualify on visit date 01-22-2011 at level 16000 for sales of 16076 (an increase of 501 from 2010).

So, my result should look like:

Customer_ID     Visit_Date   500-Level Acheived   Actual LTD_Sales
------------------------------------------------------------------
444444          12-15-2011   1000                 1000
555555          09-21-2011   1000                 1100
888888          01-22-2011   15500                15575
  
I've come up with this query to start off:

select customer_id
      ,(LTD_sales/500)*500 AS Level_Achieved
      ,LTD_sales
      ,visit_date
from TBL_Customer_Visits
where LTD_sales = (select max(LTD_sales) from TBL_Customer_Visits as v where v.customer_id = TBL_Customer_Visits.customer_id)
ORDER BY customer_id

It works ok in that it shows the latest LTD_sales value for each customer, but that is not sufficient for such a complex query.  I know I 

have to run some variation of this twice:  once to establish a "base LTD_sale" for prior to 2011, and another time to establish values from 

2011.  Somehow these need to be subtracted from each other to find out the true difference and to see if it exceeds 500.  And also the exact 

first "visit_date" where this level has been reached needs to be isolated.  I've tried putting date qualifiers in the query, such as "where 

visit_date < '2011-01-01", but depending on where I put it, it either yields duplicates or nothing at all. 

Here is a script to construct the above test database:

USE [Northwind]
GO
/****** Object:  Table [dbo].[TBL_Customer_Visits]    Script Date: 01/05/2012 13:40:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TBL_Customer_Visits](
	[customer_id] [nvarchar](6) NULL,
	[visit_date] [smalldatetime] NULL,
	[LTD_sales] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[TBL_Customer_Visits] ([customer_id], [visit_date], [LTD_sales]) VALUES (N'111111', CAST(0x9D3C0000 AS SmallDateTime), 700)
INSERT [dbo].[TBL_Customer_Visits] ([customer_id], [visit_date], [LTD_sales]) VALUES (N'111111', CAST(0x9E3E0000 AS SmallDateTime), 1025)
INSERT [dbo].[TBL_Customer_Visits] ([customer_id], [visit_date], [LTD_sales]) VALUES (N'333333', CAST(0x9E5B0000 AS SmallDateTime), 10378)
INSERT [dbo].[TBL_Customer_Visits] ([customer_id], [visit_date], [LTD_sales]) VALUES (N'333333', CAST(0x9EE80000 AS SmallDateTime), 10876)
INSERT [dbo].[TBL_Customer_Visits] ([customer_id], [visit_date], [LTD_sales]) VALUES (N'444444', CAST(0x9F830000 AS SmallDateTime), 600)
INSERT [dbo].[TBL_Customer_Visits] ([customer_id], [visit_date], [LTD_sales]) VALUES (N'444444', CAST(0x9F8F0474 AS SmallDateTime), 720)
INSERT [dbo].[TBL_Customer_Visits] ([customer_id], [visit_date], [LTD_sales]) VALUES (N'444444', CAST(0x9FBA0000 AS SmallDateTime), 1000)
INSERT [dbo].[TBL_Customer_Visits] ([customer_id], [visit_date], [LTD_sales]) VALUES (N'444444', CAST(0x9FCB0000 AS SmallDateTime), 8998)
INSERT [dbo].[TBL_Customer_Visits] ([customer_id], [visit_date], [LTD_sales]) VALUES (N'555555', CAST(0x9DB10000 AS SmallDateTime), 350)
INSERT [dbo].[TBL_Customer_Visits] ([customer_id], [visit_date], [LTD_sales]) VALUES (N'555555', CAST(0x9F650000 AS SmallDateTime), 1100)
INSERT [dbo].[TBL_Customer_Visits] ([customer_id], [visit_date], [LTD_sales]) VALUES (N'555555', CAST(0x9FCD0000 AS SmallDateTime), 1480)
INSERT [dbo].[TBL_Customer_Visits] ([customer_id], [visit_date], [LTD_sales]) VALUES (N'666666', CAST(0x9F100000 AS SmallDateTime), 300)
INSERT [dbo].[TBL_Customer_Visits] ([customer_id], [visit_date], [LTD_sales]) VALUES (N'666666', CAST(0x9F200000 AS SmallDateTime), 800)
INSERT [dbo].[TBL_Customer_Visits] ([customer_id], [visit_date], [LTD_sales]) VALUES (N'666666', CAST(0x9F4F0000 AS SmallDateTime), 920)
INSERT [dbo].[TBL_Customer_Visits] ([customer_id], [visit_date], [LTD_sales]) VALUES (N'222222', CAST(0x9E500000 AS SmallDateTime), 1180)
INSERT [dbo].[TBL_Customer_Visits] ([customer_id], [visit_date], [LTD_sales]) VALUES (N'777777', CAST(0x9FCC0000 AS SmallDateTime), 2350)
INSERT [dbo].[TBL_Customer_Visits] ([customer_id], [visit_date], [LTD_sales]) VALUES (N'888888', CAST(0x9DEF0000 AS SmallDateTime), 15575)
INSERT [dbo].[TBL_Customer_Visits] ([customer_id], [visit_date], [LTD_sales]) VALUES (N'888888', CAST(0x9E730000 AS SmallDateTime), 16076)

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-05 : 11:48:28

So....IF they have $1,000 in Sales or More, Total, AND have had an increase of $500.00 or over from the previous year?

Can you phrase it simply like this if I'm wrong?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

BobRoberts
Posting Yak Master

109 Posts

Posted - 2012-01-05 : 12:10:27

It doesn't have to necessarily be an increase "from the previous year" - all that's required is that they have achieved a new $500 level in sales sometime during 2011. They may not have had any sales in previous years, in which I suppose the "base" would be 0.
They could have a $500 increase from Febrary of 2011 to March of 2011, and in that case they would qualify regardless of what they did in previous years, as long as the total is over $1000. But I think you're right in that any last value in the previous year always establishes a "base" from which any value in this year is compared against - at least that's how I tend to think of it.

quote:
Originally posted by X002548

So....IF they have $1,000 in Sales or More, Total, AND have had an increase of $500.00 or over from the previous year?

Can you phrase it simply like this if I'm wrong?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-05 : 13:56:31

the last provided sample data script doesnt matches the data which you posted. Also it doesnt have any records which satisfies your criteria

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

BobRoberts
Posting Yak Master

109 Posts

Posted - 2012-01-05 : 14:45:22

quote:
Originally posted by visakh16

the last provided sample data script doesnt matches the data which you posted. Also it doesnt have any records which satisfies your criteria

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

Sorry about that - a mixup with prior data. I've changed the post by replacing it with this:

USE [Northwind]
GO
/****** Object:  Table [dbo].[TBL_Customer_Visits]    Script Date: 01/05/2012 13:40:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TBL_Customer_Visits](
	[customer_id] [nvarchar](6) NULL,
	[visit_date] [smalldatetime] NULL,
	[LTD_sales] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[TBL_Customer_Visits] ([customer_id], [visit_date], [LTD_sales]) VALUES (N'111111', CAST(0x9D3C0000 AS SmallDateTime), 700)
INSERT [dbo].[TBL_Customer_Visits] ([customer_id], [visit_date], [LTD_sales]) VALUES (N'111111', CAST(0x9E3E0000 AS SmallDateTime), 1025)
INSERT [dbo].[TBL_Customer_Visits] ([customer_id], [visit_date], [LTD_sales]) VALUES (N'333333', CAST(0x9E5B0000 AS SmallDateTime), 10378)
INSERT [dbo].[TBL_Customer_Visits] ([customer_id], [visit_date], [LTD_sales]) VALUES (N'333333', CAST(0x9EE80000 AS SmallDateTime), 10876)
INSERT [dbo].[TBL_Customer_Visits] ([customer_id], [visit_date], [LTD_sales]) VALUES (N'444444', CAST(0x9F830000 AS SmallDateTime), 600)
INSERT [dbo].[TBL_Customer_Visits] ([customer_id], [visit_date], [LTD_sales]) VALUES (N'444444', CAST(0x9F8F0474 AS SmallDateTime), 720)
INSERT [dbo].[TBL_Customer_Visits] ([customer_id], [visit_date], [LTD_sales]) VALUES (N'444444', CAST(0x9FBA0000 AS SmallDateTime), 1000)
INSERT [dbo].[TBL_Customer_Visits] ([customer_id], [visit_date], [LTD_sales]) VALUES (N'444444', CAST(0x9FCB0000 AS SmallDateTime), 8998)
INSERT [dbo].[TBL_Customer_Visits] ([customer_id], [visit_date], [LTD_sales]) VALUES (N'555555', CAST(0x9DB10000 AS SmallDateTime), 350)
INSERT [dbo].[TBL_Customer_Visits] ([customer_id], [visit_date], [LTD_sales]) VALUES (N'555555', CAST(0x9F650000 AS SmallDateTime), 1100)
INSERT [dbo].[TBL_Customer_Visits] ([customer_id], [visit_date], [LTD_sales]) VALUES (N'555555', CAST(0x9FCD0000 AS SmallDateTime), 1480)
INSERT [dbo].[TBL_Customer_Visits] ([customer_id], [visit_date], [LTD_sales]) VALUES (N'666666', CAST(0x9F100000 AS SmallDateTime), 300)
INSERT [dbo].[TBL_Customer_Visits] ([customer_id], [visit_date], [LTD_sales]) VALUES (N'666666', CAST(0x9F200000 AS SmallDateTime), 800)
INSERT [dbo].[TBL_Customer_Visits] ([customer_id], [visit_date], [LTD_sales]) VALUES (N'666666', CAST(0x9F4F0000 AS SmallDateTime), 920)
INSERT [dbo].[TBL_Customer_Visits] ([customer_id], [visit_date], [LTD_sales]) VALUES (N'222222', CAST(0x9E500000 AS SmallDateTime), 1180)
INSERT [dbo].[TBL_Customer_Visits] ([customer_id], [visit_date], [LTD_sales]) VALUES (N'777777', CAST(0x9FCC0000 AS SmallDateTime), 2350)
INSERT [dbo].[TBL_Customer_Visits] ([customer_id], [visit_date], [LTD_sales]) VALUES (N'888888', CAST(0x9DEF0000 AS SmallDateTime), 15575)
INSERT [dbo].[TBL_Customer_Visits] ([customer_id], [visit_date], [LTD_sales]) VALUES (N'888888', CAST(0x9E730000 AS SmallDateTime), 16076)

Ifor
Aged Yak Warrior

700 Posts

Posted - 2012-01-06 : 08:31:56

[code]
DECLARE @year datetime;
SET @year = '2011';

WITH MaxPrev
AS
(
SELECT customer_id, MAX(LTD_sales) AS LTD_sales
FROM dbo.TBL_Customer_Visits
WHERE visit_date < @year
GROUP BY customer_id
)
,Over500
AS
(
SELECT V.customer_id, V.visit_date, V.LTD_sales
,ROW_NUMBER() OVER (PARTITION BY V.customer_id ORDER BY V.visit_date) AS RowNum
FROM dbo.TBL_Customer_Visits V
LEFT JOIN MaxPrev P
ON V.customer_id = P.customer_id
WHERE V.visit_date >= @year
AND V.visit_date < DATEADD(year, 1, @year)
AND V.LTD_sales >= 1000
AND V.LTD_sales - COALESCE(P.LTD_sales, 0) >= 500
)
SELECT customer_id
,(LTD_sales/500) * 500 AS Level_Achieved
,LTD_sales, visit_date
FROM Over500
WHERE RowNum = 1
ORDER BY customer_id;
[/code]

BobRoberts
Posting Yak Master

109 Posts

Posted - 2012-01-06 : 11:49:01

I want to thank you very much for your amazing, elegant solution. I think I'll learn a lot by just studying what you have done here.

quote:

Originally posted by Ifor


DECLARE @year datetime;
SET @year = '2011';

WITH MaxPrev
AS
(
	SELECT customer_id, MAX(LTD_sales) AS LTD_sales
	FROM dbo.TBL_Customer_Visits
	WHERE visit_date < @year
	GROUP BY customer_id
)
,Over500
AS
(
	SELECT V.customer_id, V.visit_date, V.LTD_sales
		,ROW_NUMBER() OVER (PARTITION BY V.customer_id ORDER BY V.visit_date) AS RowNum
	FROM dbo.TBL_Customer_Visits V
		LEFT JOIN MaxPrev P
			ON V.customer_id = P.customer_id
	WHERE V.visit_date >= @year
		AND V.visit_date < DATEADD(year, 1, @year)
		AND V.LTD_sales >= 1000
		AND V.LTD_sales - COALESCE(P.LTD_sales, 0) >= 500
)
SELECT customer_id
	,(LTD_sales/500) * 500 AS Level_Achieved
	,LTD_sales, visit_date
FROM Over500
WHERE RowNum = 1
ORDER BY customer_id;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources