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:

needs to be a left outer join since your top 25 abandoned carts may not have resulted in any
actual orders for those products (consider a new/advance launch scenario... with problems)

if you specify the last modified condition like this lastmodified ... then you have a better chance
that any index on last modified that you have will be used... otherwise the datediff function
will tend to preclude index usage....

similar with purchase date... (you didn't say what the appropriate date range for that should be...)

on a further datetime point .... normally you should probably drop the time portion of the date
at least from getdate() so that during the "day" you will get consistent results from any additional/
subsequent queries..

hth

Select A.CartProductcode As "Product Code" ,A.[Cart Qty] ,A.[Total Qty] as [Aband Qty] ,coalesce(b.orders,0) as [Orders] ,coalesce(b.purchased,0} as [Purchased] from (SELECT TOP 25 CartProductCode ,COUNT(*) AS "Cart Qty" ,SUM(CartItemQuantity) As "Total Qty"FROM Cart WHERE lastmodified between dateadd(d,-1,getdate()) and dateadd(d,-31,getdate())GROUP By CartProductCodeORDER By 2 DESC ) as ALeft Outer Join (select od.productcode ,COUNT(distinct orderid) as [Orders] ,SUM(od.itemQuantity) as [Purchased] from OrderDetails as OD Inner Join Orders as O on Od.Orderid=o.orderid and O.purchaseDate between dateadd(d,-1,getdate()) and dateadd(d,-31,getdate()) group by od.productcode ) as B on A.Cartproductcode=B.productcode

Sorry Got the date range the wrong way around...
and forgot the order by.....

Select A.CartProductcode As "Product Code"
,A.[Cart Qty]
,A.[Total Qty] as [Aband Qty]
,coalesce(b.orders,0) as [Orders]
,coalesce(b.purchased,0} as [Purchased]
from (
SELECT TOP 25 CartProductCode
,COUNT(*) AS "Cart Qty"
,SUM(CartItemQuantity) As "Total Qty"
FROM Cart
WHERE lastmodified between dateadd(d,-31,getdate()) and dateadd(d,-1,getdate())
GROUP By CartProductCode
ORDER By 2 DESC
) as A
Left Outer Join
(select od.productcode
,COUNT(distinct orderid) as [Orders]
,SUM(od.itemQuantity) as [Purchased]
from OrderDetails as OD
Inner Join Orders as O
on Od.Orderid=o.orderid
and O.purchaseDate between dateadd(d,-31,getdate()) and dateadd(d,-1,getdate())
group by od.productcode
) as B
on A.Cartproductcode=B.productcode
Order by 2 desc

Actually, none of my carts will have resulted in order. As soon as the cart is purchased it is deleted from the abandoned carts table and an order I created pm the order tables. This is where I ran into a problem.

Correct. I'm simply trying pull the quantities for cart products, then pull the matching orders so that I can do comparison math on them later bin not sure we should join the tables, but rather pull from tables separately. I thought about putting my current statement into parentheses as it's own table, then use it to match the part numbers for the orders columns in a separate join

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Ok, I had a chance to play with the query, and it ALMOST does exactly what I need it to do. The problem it's having is that the Abandoned Products columns are correctly reporting, but the purchased product quantities are not matching to the correct product code. I don't understand why, given the explicit Join condition of A.CartProductCode = B.ProductCode. I can't figure out why.

I had to add a new condition to the Orders section of the query, "AND O.OrderStatus <> 'Cancelled'", and I made some adjustments to the column names to they match the actual column names in the database, now that I know what they are. You'll notice PurchaseDate is now OrderDate and ItemQuantity is now just Quantity, for instance. Here's the new version of the Query that I'm using:

In the first file (RESULTS_OF_PRODUCTS_QUERY.csv), are the results of the above query. In the second file are the results of only the "B" query, which is the part of the query grabbing quantities of products actually purchased. RESULTS-OF-PRODUCTS-QUERY.csvRESULTS-OF-ORDERS-QUERY.csv

You'll notice right off the bat that the first product (CL-34S) is matching up with the quantities for CL-100 for purchased quantities.

Actually, sorry I'm wrong about that. They are matching correctly according to the second query's results. I was looking at an actual report from our database showing the purchased quantities and those are the numbers I was seeing that are different.

The reported quantities are definitely low, as the CL-34S should be somewhere around 1500 orders. I'm gonna look a little further to see where the problem is. I'll get back to you shortly.

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.

Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…