SQLTeam.com | Weblogs | Forums

Beginner needing help

Hi all, I'm new to this forum and just starting out on my SQL learnings.

I have an issue where I'm trying to show data in a particular way based on multiple events. I'll try my best to explain my problem, and hopefully someone can help or offer advice. Thanks in advance.

Problem statement -
I'm trying to show when a customer (who has a unique identifier) has a series of job references booked over a period of 3 weeks, in order to show volume of repeat jobs for that customer.

Example of what I have (how I have the data now in rows and columns)

Row 1 - (Column 1) = Week 1, (Column 2) = Customer 123, (Column 3) = Job ref 123

Row 2 - (Column 1) = Week 3, (Column 2) = Customer 123, (Column 3) = Job ref 345

I'd like to be able to simplify this view and show that over a 3 week period, that Customer 123 has had a repeat job ref against their unique customer number.

I'd like it to almost show something like the below and instead of 2 rows, just the one row now as its picked up the second event relating to that unique customer identifier from week 1.

Row 1 - (Column 1) = Week of initial job, (Column 2) = Customer 123, Column 3 = count of first job in week 1, (Column 4) = Count of any repeat jobs in the weeks after week 1 occurred.

Hopefully that makes sense, and this is my first time posting, so any further info needed then please ask.

Thanks in advance for any help relating to my issue.

You should use a group by in your query.

So Column 1 would be the first Week ... keep in mind with years
So Column 2 would be a Customer, you need this column in group by
So Column 3 would be a COUNT(*) as your table contains hopefully only unique jobs for each customer a week.

So Column 4 can't be selected because you only have the first week. You need to make an extra query for that to count all jobs except the first week. You can use a CTE to do this:

DROP TABLE IF EXISTS #TableCustomerJob

SELECT 
	'Week1' AS Column1,
	'Customer1' AS Column2,
	'Job abc' AS Column3
	INTO #TableCustomerJob
UNION
SELECT 
	'Week1' AS Column1,
	'Customer1' AS Column2,
	'Job def' AS Column3	
UNION
SELECT
	'Week3' AS Column1,
	'Customer1' AS Column2,
	'Job ghi' AS Column3


SELECT * FROM #TableCustomerJob;

WITH CustomerFirstWeek AS
(
SELECT
   MIN(Column1) AS FirstWeek,
   Column2 AS Customer
FROM
  #TableCustomerJob
GROUP BY 
  Column2
)
SELECT 
	TCJ.Column1,
	TCJ.Column2,
	COUNT(*) AS NumberOfJobsInTheFirstWeek,
	(
		SELECT 
			COUNT(*)
		FROM
			#TableCustomerJob
		WHERE Column2=TCJ.Column2 AND Column1 <> TCJ.Column1
	) AS AllJobsExceptTheFirstWeek
FROM 
	#TableCustomerJob TCJ
		INNER JOIN CustomerFirstWeek CFW
			ON (TCJ.Column2=CFW.Customer AND TCJ.Column1=CFW.FirstWeek)
GROUP BY 
	TCJ.Column1,
	TCJ.Column2
1 Like

This is brilliant, thanks very much for your help and how easily you've described the solution too. Appreciate it, thanks!

1 Like

Thank you for your feedback :slight_smile:

1 Like