Beginner needing help

Transact-SQL
1

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.

2

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
3

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

1 Like
4

Thank you for your feedback :slight_smile:

1 Like