cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
StidifordN Regular Visitor
Regular Visitor

Green with SQL - Need some assistance

Howdy all, 

 

I'm so very green with SQL coding but need to accomplish someting.

In my procurement data, transactional data, i have upwards to 5 years of transactions.  So alot of line items!


I need to have a table spit out a list of all suppliers and what their total spend was for the preceeding 12months from today.  Thats it.  A unique list of suppliers and their Spend looking back 12 months.
And to clarify - not spend from that suppliers last transaction back 12 months, from Today back 12 months.  Just to clarify.

The query would be on a the datacubes we have.

Now i've got the below code that is used for another report for another purpose but I thought it was on the right path/ballpark.  This one was giving a result of 50 or 25 depending if a transaction had occuring in the last 12 or 24 months.

SELECT [PTRN_SUPP], 
       CASE WHEN MAX( PTRN_POSTED ) >= DATEADD(YEAR, -1, GETDATE()) THEN 50       
       ELSE 25       
       END AS Result
FROM [ManhattanReporting].[Reporting].[vw_APALL]
WHERE PTRN_POSTED >= DATEADD(YEAR,-2,GETDATE())
       AND PTRN_TYPE <> 'PAY'
GROUP BY [PTRN_SUPP]

The key field names are 
PTRN_SUPP (supplier code)

NET_AMOUNT (Transaction Value)

PTRN_POSTED (Transaction Date)

 

The output fields I'd like to see

PTRN_SUPP (Supplier Code)

NET_AMOUNT_12MNTH (the total spend looking back 12 months)

Any help would be greated appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
TeigeGao Super Contributor
Super Contributor

Re: Green with SQL - Need some assistance

Hi @StidifordN ,

According to your description, my understanding is that you want to calculate the total spend of each suppliers in the last 12 months.

In this scenario, we can use the following SQL query:

SELECT [ptrn_supp], 
       Sum([net_amount]) AS NET_AMOUNT_12MNTH 
FROM   [ManhattanReporting].[Reporting].[vw_apall] 
WHERE  [ptrn_posted] >= Dateadd(year, -1, Getdate()) 
GROUP  BY [ptrn_supp] 

Best Regards,

Teige

2 REPLIES 2
TeigeGao Super Contributor
Super Contributor

Re: Green with SQL - Need some assistance

Hi @StidifordN ,

According to your description, my understanding is that you want to calculate the total spend of each suppliers in the last 12 months.

In this scenario, we can use the following SQL query:

SELECT [ptrn_supp], 
       Sum([net_amount]) AS NET_AMOUNT_12MNTH 
FROM   [ManhattanReporting].[Reporting].[vw_apall] 
WHERE  [ptrn_posted] >= Dateadd(year, -1, Getdate()) 
GROUP  BY [ptrn_supp] 

Best Regards,

Teige

StidifordN Regular Visitor
Regular Visitor

Re: Green with SQL - Need some assistance

Perfecto!