-1

My data is in two tables. The format of the two tables is below :

Table Transaction

Table Customer

I had to figure out for all customers aged between 25 to 35 years find what is the net total revenue generated by these customers in last 30 days of transactions from max transaction date available in the data ?

I wrote below code

SELECT      
TOP 1       YEAR(T2.TRAN_DATE)[TRAN_YEAR] ,MONTH(T2.TRAN_DATE)[TRAN_Month],
            SUM(T2.Total_amt)[REVENUE]  
FROM        TRANSACTIONS T2
RIGHT JOIN  CUSTOMER T1
ON          T1.CUSTOMER_ID = T2.CUST_ID
WHERE       DATEDIFF(YY, T1.DOB, GETDATE()) BETWEEN 25 AND 35
GROUP BY    YEAR(T2.TRAN_DATE),MONTH(T2.TRAN_DATE)
ORDER BY    YEAR(T2.TRAN_DATE) DESC, MONTH(T2.TRAN_DATE) DESC

My query works but when i calculated the same thing on excel it gave a different answer.

I am not able to figure out my mistake.

Vinamra Bali
  • 81
  • 8
  • 4
    *"My query works but when i calculated the same thing on excel it gave a different answer."* then show us what your Excel query was. Also, as an FYI `DATEDIFF(YY, T1.DOB, GETDATE()) BETWEEN 25 AND 35` does **not** give you a list of people who are aged between 25 and 35 inclusive. You will get users who are 24 as well, and miss some out that are 35. – Thom A Nov 04 '19 at 16:06
  • 2
    Avoid right joins - they work but are more confusing than they are worth. – P.Salmon Nov 04 '19 at 16:09
  • @Larnu thanks for the info. Could you tell me why it will also include some data of age 24 and miss some of 35. also do you have a more efficient way of getting the age. In excel I calculated age [(function Today - DOB from customer)/365] applied vlookups to get revenue and transaction date from transaction table to customer table. applied year and month on transaction date to filter out data of the largest month and year. – Vinamra Bali Nov 04 '19 at 16:17
  • 1
    *"Could you tell me why it will also include some data of age 24 and miss some of 35"*. Someone born on `12 December 1994` would be 24 today (04 November 2019), however, if you use the query `SELECT DATEDIFF(YY, '19941212', GETDATE());` you get the result `25` so would be **included** in your result. Similarly, someone born on 12 December 1983 would be 35 today, but would be **excluded** from your results (`SELECT DATEDIFF(YY, '19831212', GETDATE());` returns `36`). – Thom A Nov 04 '19 at 16:24
  • @Larnu , This makes much more sense. So how would you calculate the age if you have to. – Vinamra Bali Nov 06 '19 at 13:36
  • Have a look at https://stackoverflow.com/a/57720/3484879 – Thom A Nov 06 '19 at 14:17

1 Answers1

1

I am expecting a query like this:

SELECT SUM(T.Total_amt) as REVENUE] 
FROM TRANSACTIONS T JOIN
     CUSTOMER c
     ON c.CUSTOMER_ID = t.CUST_ID
WHERE c.DOB >= DATEADD(YEAR, -35, GETDATE()) AND
      c.DOB < DATEADD(YEAR, -24, GETDATE()) AND
      t.TRAN_DATE > DATEADD(DAY, -30, GETDATE());

Note that this uses direct date comparisons rather than DATEDIFF(). These are usually more accurate.

Thom A
  • 88,727
  • 11
  • 45
  • 75
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • This is a much better way and after someone already pointed out i get why my approach was not accurate. I got a different answer fro "DATEADD" but its still off from what i computed on excel. Between the question asks to get the net total revenue generated by these customers in last 30 days of transactions from max transaction date available in the data not the current date. Anyways, Thanks for the help. :) – Vinamra Bali Nov 06 '19 at 13:35
  • @VinamraBali . . . When a question has text and a query that don't match, it can be very hard to determine which to believe. That is why I preface this with "I am expecting . . ". – Gordon Linoff Nov 06 '19 at 13:37