| Author |
 Topic  |
|
|
pixelwiz
Starting Member
25 Posts |
 Posted - 04/04/2012 : 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
United Kingdom
28 Posts |
|
|
pixelwiz
Starting Member
25 Posts |
Posted - 04/04/2012 : 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 - 04/04/2012 : 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
Flowing Fount of Yak Knowledge
USA
1608 Posts |
Posted - 04/04/2012 : 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 - 04/04/2012 : 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
|
|
|
| |
Topic |
|
|
|
Repeat and Unique customers at multiple locations
