SELECT TOP 25 CartProductCode As "Product Code"
,COUNT(CartProductCode) AS "Cart Qty"
,SUM(CartItemQuantity) As "Total Qty"
WHERE DATEDIFF(day, LastModified, GETDATE())>= 1
AND DATEDIFF(day, LastModified, GETDATE())<= 31
GROUP By CartProductCode
ORDER By COUNT(CartProductCode) DESC
I need to modify this SQL code. The code, as it is now, grabs the count of abandoned carts containing each product code for the last 30 days, then adds the total number of that product included in those carts.
This is the important information that I need, but it means nothing unless I can compare it to the number actually purchased. So, where my sample data looks like this:
Product Code Cart Qty Total Qty
SY7F29 438 580
SY5F29 414 664
SY13F3 297 298
SY13F9 210 571
I actually need it to look like this:
Product Code Cart Qty Aband Qty Order Total Purch Qty
SY7F29 438 580 4339 5149
SY5F29 414 664 9812 13757
SY13F3 297 298 1574 1763
SY13F9 210 571 6581 9442
The problem I'm running into is that the order total and purchase quantity are on a different table altogether, and to make it worse, the dates on those orders are in yet another table, so I have
I figure I'll have to add a select statement inside of a FROM t INNER JOIN line. Any help would be appreciated.