Repeat and Unique customers at multiple locations

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)

Repeat and Unique customers at multiple locations

Author

Topic

pixelwiz
Starting Member

25 Posts

Posted - 2012-04-04 : 08:56:47

Hi,

Let's say I have a table of locations (currently 3 but can grow), a table of customers, and a table of transactions.

I need to generate a report that shows how many customers shopped at the following locations:
Location 1 only
Location 2 only
Location 3 only
Location 1&2 but not 3
Location 1&3 but not 2
Location 2&2 but not 1
Location 1&2&3

Any suggestions for how to write such a query? Is there a way to do this dynamically so that it'll still work when the locations change?

martind1
Starting Member

28 Posts

Posted - 2012-04-04 : 09:44:24

What data does your transactions table hold (what columns).

--
http://www.tutorial-resource.com - Free Web Development Resources

pixelwiz
Starting Member

25 Posts

Posted - 2012-04-04 : 11:06:39

This is just a simplified example, but basically we scan a barcode when a customer comes to each location. So it would store customer_id, location_id and date.

pixelwiz
Starting Member

25 Posts

Posted - 2012-04-04 : 11:35:17

Here is some sample data if that helps..

Query to create a temp table:
-----------
CREATE TABLE [dbo].[mytemptable](
[id] [int] IDENTITY(1,1) NOT NULL,
[customer_id] [int] NOT NULL,
[location_id] [int] NOT NULL,
[date_entered] [datetime] NOT NULL,
CONSTRAINT [PK_mytemptable] PRIMARY KEY CLUSTERED
(
[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
----------------------

Here is some sample data:
---------------------------
INSERT INTO mytemptable (customer_id, location_id, date_entered)
VALUES (1,1,'2012-04-01 00:00:00.000'),
(2,1,'2012-04-01 00:00:00.000'),
(3,2,'2012-04-01 00:00:00.000'),
(4,3,'2012-04-01 00:00:00.000'),
(5,3,'2012-04-01 00:00:00.000'),
(1,2,'2012-04-02 00:00:00.000'),
(1,3,'2012-04-03 00:00:00.000'),
(3,3,'2012-04-02 00:00:00.000'),
(4,2,'2012-04-02 00:00:00.000'),
(5,3,'2012-04-02 00:00:00.000'),
(1,1,'2012-04-04 00:00:00.000'),
(2,1,'2012-04-04 00:00:00.000'),
(3,2,'2012-04-04 00:00:00.000'),
(4,3,'2012-04-04 00:00:00.000'),
(5,3,'2012-04-04 00:00:00.000')
---------------------

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-04-04 : 15:37:31

Is there a front end associated with this? or is this auto generated reports?

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

pixelwiz
Starting Member

25 Posts

Posted - 2012-04-04 : 16:02:16

Here is the only solution I've come up with so far, and it's not dynamic. Below partners are actually locations


SELECT 
	SUM(CASE WHEN p1 > 0 AND p2 = 0 AND p3 = 0 THEN 1 ELSE 0 END) p1Only,
	SUM(CASE WHEN p1 = 0 AND p2 > 0 AND p3 = 0 THEN 1 ELSE 0 END) p2Only,
	SUM(CASE WHEN p1 = 0 AND p2 = 0 AND p3 > 0 THEN 1 ELSE 0 END) p3Only,
	SUM(CASE WHEN p1 > 0 AND p2 > 0 AND p3 = 0 THEN 1 ELSE 0 END) p1p2,
	SUM(CASE WHEN p1 > 0 AND p2 = 0 AND p3 > 0 THEN 1 ELSE 0 END) p1p3,
	SUM(CASE WHEN p1 = 0 AND p2 > 0 AND p3 > 0 THEN 1 ELSE 0 END) p2p3,
	SUM(CASE WHEN p1 > 0 AND p2 > 0 AND p3 > 0 THEN 1 ELSE 0 END) p1p2p3
FROM (
	SELECT customer_id, [1] AS p1, [2] AS p2, [3] AS p3
	FROM (
		SELECT	DISTINCT c.customer_id, p.partner_id, (
			SELECT COUNT(id) 
			FROM vw_dcs_scans c2 
			WHERE c2.customer_id = c.customer_id 
			AND c2.partner_id = p.partner_id) cnt
		FROM vw_dcs_scans c, 
		 (
			SELECT DISTINCT spl.partner_id
			FROM source_partners sp 
			INNER JOIN source_partner_locations spl
			ON sp.id = spl.partner_id
			INNER JOIN sources s
			ON s.id = spl.source_id
			INNER JOIN events e
			ON e.id = s.event_id
			WHERE e.id =  122
		) p
	) AS sourceTable
	PIVOT 
	(
		SUM(cnt) FOR partner_id IN ([1],[2],[3])
	) AS pvtTable
) AS dataTable

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources