CODEWITHSUNDEEP
sqlsql-server-2008
thatuxguy
thatuxguy

Reputation: 2528

Get 2 weeks of data from Database

I am returning a list of highest selling products. Currently my query brings back totals from the last 7 days worth of orders.

I also need to bring back the sum of the item qty for the previous 7 days, i would like to know how i would go about doing this? I was thinking that its simply adding 7 days onto the @startDate and 13 days to the @endDate (this already has the date set for 7 days after todays date)

currently getting the item qty in the sum(amzOrdersItem.itemQty) AS Orders line. This returns a count of the products orders essentially.

ALTER PROCEDURE [dbo].[GetHighestSellingItems]
@startDate datetime, 
@endDate datetime
AS
BEGIN

SET NOCOUNT ON;

SELECT    TOP (20) twProducts.sku, 
          twProducts.title + ' /  ' + LTRIM(CAST(twProducts.strength AS varchar(15))) + '%' AS Title, 
          sum(amzOrdersItem.itemQty) AS Orders, 
          twProducts.stock, aboProducts.asin, aboProducts.amzPrice
FROM      twProducts INNER JOIN
          amzOrdersItem ON twProducts.sku = amzOrdersItem.productSku INNER JOIN
          amzOrders ON amzOrdersItem.amzOrderId = amzOrders.amzOrderId INNER JOIN
          aboProducts ON twProducts.sku = aboProducts.sku
where    (amzOrders.orderDate between @startDate and @endDate) and  amzOrders.cancelled = 0
          group by twProducts.sku, twProducts.title, twProducts.strength, 
          twProducts.stock, aboProducts.asin, aboProducts.amzPrice
ORDER BY sum(amzOrdersItem.itemQty) DESC
END

Thought this might be handy, this is .net code i am padding into the query, and getting results.

public List<GetHighSelling> GetHighSellingWeek()
    {
        DateTime? endDate = DateTime.Now.Date;
        DateTime? startDate = DateTime.Now.Date.AddDays(-6).Date;

        using (aboDataDataContext dc = new aboDataDataContext())
        {
            var query = from o in dc.GetHighestSellingItems(startDate, endDate)
                        select new GetHighSelling
                        {
                            sku = o.sku,
                            title = o.Title,
                            itemQty = o.Orders,
                            stock = o.stock,
                            amzPrice = o.amzPrice.ToString(),
                            asin = o.asin
                        };
            return query.ToList();
        }
    }

Upvotes: 0

Views: 404

Answers (2)

Guffa
Guffa

Reputation: 700670

You should subtract seven days. If you add seven days, you will be asking for the highest selling products for one week into the future.

(Although that would be extremely useful information, the database isn't capable of seeing into the future. ;)

You can calculate the start date from the end date:

DateTime? endDate = DateTime.Today.AddDays(-7);
DateTime? startDate = endDate.AddDays(-6);

Upvotes: 1

podiluska
podiluska

Reputation: 51504

Change the code to

 DateTime startDate = DateTime.Now.Date.AddDays(-13).Date;

Upvotes: 2

Related Questions