CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Paul
Paul

Reputation: 314

Is this possible in SQL?

I have this homework to do and I have been trying for hours without even knowing if this is possible to be done: For every Client and every month of 2009, find the average transaction before the month the average transaction after the month and show a list of the Client Code and the months where the second average transaction is bigger than the first one.

I have tried using while but failed then tried to use one selection but failed then thought of creating views but I would need 12 or 24 depending on how I do it, then thought of creating a table holding dates of each month and then creating 2 views,but I doubt this is gonna work. (The tables I need here and its columns are Clients(Code), Transactions(Date_and_time_of_transaction, Amount_of_transaction) I don't mind not getting the full solution at all. I just need something to help me go on and solve this.Let's say a "guiding light".

EDIT:Out of the top of my head when I started I tried this

DECLARE @COUNTER as int

SET @COUNTER = 0

WHILE @COUNTER <= 12

  BEGIN

   SET @COUNTER = @COUNTER + 1

    SELECT     @COUNTER as Month,Clients.Clients_Code, AVG(Transactions.Amount_of_indebtedness) as AVERAGE

    FROM         Account INNER JOIN
                  Clients ON Account.Account_Number = Clients.Account_Number INNER JOIN
                  Credit_Card ON Account.Account_Number = Credit_Card.Account_Number INNER JOIN
                  Transactions ON Credit_Card.Credit_Number = Transactions.Credit_Number

WHERE     (YEAR(Transactions.Date_and_time_of_transaction) = '2009') 
      AND (MONTH(Transactions.Date_and_time_of_transaction) < @COUNTER)

GROUP BY Clients.Clients_Code

    SELECT     @COUNTER as Month,Clients.Clients_Code, 
AVG(Transactions.Amount_of_indebtedness) as AVERAGE2

FROM         Account INNER JOIN
              Clients ON Account.Account_Number = Clients.Account_Number INNER JOIN
              Credit_Card ON Account.Account_Number = Credit_Card.Account_Number INNER JOIN
              Transactions ON Credit_Card.Credit_Number = Transactions.Credit_Number

WHERE     (YEAR(Transactions.Date_and_time_of_transaction) = '2009') 
      AND (MONTH(Transactions.Date_and_time_of_transaction) >= @COUNTER)

GROUP BY Clients.Clients_Code

  END

Upvotes: 0

Views: 146

Answers (2)

hol
hol

Reputation: 8423

I would create a temporary view (by means of the "with" clause). In this view I would have the averages per client and month. Then I would join them together in a way that one is the current month and the other is the month+1. Then in the where clause I would check whether the average of the bigger month is higher then the previous month. Hope that helps.

edit: actually maybe you do not even join the "with view" maybe you could simply use the subquery in the where clause, like:"select * from withview w1 where w1.averageamount > (select 2.averageamount from withview w2 where w2.month = w1.month + 1". This is not the correct syntax but something like this.

Upvotes: 2

Robert Hanson
Robert Hanson

Reputation: 36

You will need subqueries for this.

If you have a query that produces a result select a, b, c from d you can make that look like a table in a query like this:

select a, b from (select a, b, c from d) e where "e" is a synonym for the "table" produced by the query within the select

That should get you going. You'll need one subquery for the previous month, one subquery for the subsequent month, and a way to relate the columns in the subqueries to the current row.

Upvotes: 1

Related Questions