Complex SQL Query 1

[Sverre]

I have a table with FK customersID and the fields Year (XXXX), Month (values 1 to 12) and amount. Each Customer can have several rows with the same year but different month and amount. Some customers have records for the month 1,2,3,4, but others only month 1,3 and so on.

What I like to do is to have one select Query that groups together to the customers and sum the amount, but only those costomers who have amounts registered in the same months as my spesially selected Customer.
For instance: customerID 34 has an amount in month 2,3, and 5. I what to group the other customers who also (and only) have amounts in the same three months (2,3 and 5).
It should all be done in one single Query from an .asp webpage.


Sverre

 

[vongrunt]

Can each customer have several rows with the same year and month? In other words, are (customerID, [Year], [Month]) together unique?

------
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]

 

[Sverre]

Each customer should have only unique combinations of year and months. So customersID, Year and Month would together make an composite??? key (bad english?).

Sverre

There are only 10 types of people in the world. Those who know digital numbers and those who don't

 

[ESquared]

SELECT C.CustomerID, Sum(C.Amount)
FROM Customers C INNER JOIN (
  SELECT B.CustomerID
  FROM Customers A FULL JOIN Customers B
    ON A.Year = B.Year AND A.Month = B.Month AND A.CustomerID = [blue][b]12345[/b][/blue] -- CustomerID here
  GROUP BY B.CustomerID
  HAVING
    0 = Sum(CASE WHEN A.Month IS NULL THEN 1 ELSE 0 END) -- no nonmatching months
    AND Sum(CASE WHEN B.Month IS NULL THEN 0 ELSE 1 END)
      = (SELECT COUNT(*) FROM Customers WHERE CustomerID = 1) -- same count of matching months
) X ON C.CustomerID = X.CustomerID
GROUP BY C.CustomerID

 

[ESquared]

P.S. All numbers in any base are digital (use digits). Do you mean binary?

 

[Sverre]

Binary Yes offcourse - Thank you - and thank you a lot. Will test your query during the weekend.

Sverre

---------------------------
There are only 10 types of people in the world. Those who know binary numbers and those who don't

 

[vongrunt]

Yup, full outer join should do the trick.

E2, I was also thinking about something like this:

select A.CustomerID, A.TotalAmount
from
(	select CustomerID, checksum_agg([Year]*12 + [Month]) as chk, sum(Amount) as TotalAmount
	from Customers
	group by CustomerID
) A
where A.chk = (select checksum_agg([Year]*12 + [Month]) from Customers where CustomerID=34)

Problem here is aggregate expression (each different ordered set of years/months should produce unique value). Checksums by definition don't guarantee that

------
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]

 

[ESquared]

Shouldn't year * 12 + month already be unique?

 

[vongrunt]

Yes, but SUM() of that wouldn't.

------
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]

 

[ESquared]

Oh, got it.

And in my query I forgot to make the second Customer = 1 to be Customer = 12345. You have to put the customer number in twice...

 

[Sverre]

Thank you for good ideas!

ESquareds query worked, but did not completely solve my problem. So finally I solved it by running two separat queries on the asp page, filling variables with values from the first, and then using those variables in the second query. This required some additional lines of code, but solved my problem. Anyway, thank you again. It's been a nice experience to join tek-tips so far, and I will probably find it very usefull in the future. I also hope my knowledge in VB/VBA/asp can be usefull for someone, sometime.

Sverre

---------------------------
There are only 10 types of people in the world. Those who know binary numbers and those who don't