Styjun

I am running SQL Server 2008 R2 (RTM).

I have a SQL query that pulls Dates, Products, Customers and Units:

select 

[Transaction Date] as Date, 
[SKU] as Product, 
[Customer Name] as Customer, 
sum(Qty) as Units 

from dataset

where [Transaction Date] < '2019-03-01' and [Transaction Date] >= '2016-01-01'

group by [Transaction Date], [SKU], [Customer Name]

order by [Transaction Date]

This pulls hundreds of thousands of records and I wanted to determine if a certain transaction was a new order or reorder based on the following logic:

Reorder: That specific Customer has ordered that specific product in the last 6 months

New Order: That specific Customer hasn’t ordered that specific product in the last 6 months

For that I have this formula in Excel that seems to be working:

=IF(COUNTIFS(A$1:A1,">="&DATE(YEAR(A2),MONTH(A2)-6,DAY(A2)),C$1:C1,C2,B$1:B1,B2),"Reorder","New Order")

The formula works when I paste it individually or in a smaller dataset, but when I try to copy paste it to all 500K+ rows, Excel gives up because it loops for each calculation.

This could probably be done in SQL, but I don’t have the knowledge on how to convert this excel formula to SQL, I just started studying it.

You're doing pretty well with the start of your query there. There are three additional functions you're looking to add to your query.

The first thing you'll need is the easiest. GETDATE() simply returns the current date. You'll need that when you're comparing the current date to the transaction date.

The second function is DATEDIFF, which will give you a unit of time between two dates (months, days, years, quarters, etc). Using DATEDIFF, you can say "is this date within the last 6 months". The format for this is pretty easy. It's DATEDIFF(interval, date1, date2).

The thrid function you're looking for is CASE, which allows you to tell SQL to give you one answer if one condition is met, but a different answer if a different condition is met. For your example, you can say "if the difference in days is < 60, return 'Reorder', if not give me 'New Order'".

Putting it all together:

SELECT CASE 
 WHEN DATEDIFF(MONTH, [Transaction Date], GETDATE()) <= 6
 THEN 'Reorder'
 ELSE 'New Order'
 END as ORDER_TYPE
 ,[Transaction Date] AS DATE
 ,[SKU] AS PRODUCT
 ,[Customer Name] AS CUSTOMER
 ,Qty AS UNITS
FROM DATASET

• For additonal examples on CASE, take a look at this site: https://www.w3schools.com/sql/sql_ref_case.asp
  


• For additional examples on DATEDIFF, take a look here: See the
  following webpage for examples and a chance to try it out:
  https://www.w3schools.com/sql/func_sqlserver_datediff.asp
  

SELECT CASE
 WHEN Datediff(day, [transaction date], Getdate()) <= 180 THEN 'reorder'
 ELSE 'Neworder'
 END,
 [transaction date] AS Date,
 [sku] AS Product,
 [customer name] AS Customer,
 qty AS Units
FROM datase 

If I understand correctly, you want to peak at the previous date and make a comparison. This suggests lag():

select (case when lag([Transaction Date]) over (partition by SKU, [Customer Name] order by [Transaction Date]) >
 dateadd(month, -6, [Transaction Date])
 then 'Reorder'
 else 'New Order'
 end) as Order_Type
 [Transaction Date] as Date, 
 [SKU] as Product, 
 [Customer Name] as Customer, 
 sum(Qty) as Units 
from dataset d
group by [Transaction Date], [SKU], [Customer Name];

EDIT:

In SQL Server 2008, you can emulate the LAG() using OUTER APPLY:

select (case when dprev.[Transaction Date] >
 dateadd(month, -6, d.[Transaction Date])
 then 'Reorder'
 else 'New Order'
 end) as Order_Type
 d.[Transaction Date] as Date, 
 d.[SKU] as Product, 
 d.[Customer Name] as Customer, 
 sum(d.Qty) as Units 
from dataset d outer apply
 (select top (1) dprev.*
 from dataset dprev
 where dprev.SKU = d.SKU and
 dprev.[Customer Name] = d.[Customer Name] and
 dprev.[Transaction Date] < d.[Transaction Date]
 order by dprev.[Transaction Date] desc
 ) dprev
group by d.[Transaction Date], d.[SKU], d.[Customer Name];