Database

jkirkerx wrote:

The thumbnail was just to show the picture of the item.

jkirkerx wrote:

Normally, i would just do the math in asp.net, but I thought I would expand my knowledge of SQL.

DECLARE <a href="/Members/Year">@Year</a>AS int;
DECLARE <a href="/Members/month">@Month</a>AS int;
DECLARE <a href="/Members/Day">@Day</a>AS int;
SET <a href="/Members/Year">@Year</a>= 2013;
SET <a href="/Members/month">@Month</a>= 8;
SET <a href="/Members/Day">@Day</a>= 29;

DECLARE @startDate AS DATE;
DECLARE @stopDate AS DATE;
SET @startDate = DATETIMEFROMPARTS(@Year, <a href="/Members/month">@Month</a> <a href="/Members/Day">@Day</a> 0, 0, 0, 0);
SET @stopDate = DATETIMEFROMPARTS(@Year, <a href="/Members/month">@Month</a> <a href="/Members/Day">@Day</a> 23, 59, 59, 999);
DECLARE @SummaryItems TABLE(
	PartNumber VarChar(80),
    ThumbNail VarChar(250),
    SDescription VarChar(250),
    Qty INT,
    Cost Decimal,
    Price Decimal,
    SubTotal Decimal      
)
INSERT INTO @SummaryItems (PartNumber, ThumbNail, SDescription, Qty, Cost, Price, SubTotal)
SELECT PartNumber, ThumbNail, SDescription, Qty, Cost, Price, SUM(Qty * Price) From CompletedOrdersCart 
WHERE OrderDate > @startDate AND OrderDate < @stopDate
GROUP BY PartNumber, Thumbnail, SDescription, Qty, Cost, Price
SELECT PartNumber, Thumbnail, SDescription, Qty, Cost, Price, SubTotal FROM @SummaryItems;

PN,      Thumbnail,                   Description,               Qty, Cost,  Price, SubTotal
06-10,   /Images/thumbnail/06-10.jpg, 10 inch Continuious Blade, 1,   22.00, 44.00, 308.00 

Shopping Cart History
date ------- PN  ---------- Qty    
08/29/2013 - 8AM  06-CR10   1
08/29/2013 - 9AM  06-CR10   1
08/29/2013 - 10AM 06-CR10   1
08/29/2013 - 11AM 12-ZZZ1   2
So the Qty would be 3

the result set would be

Items requiring fulfillment today
PN --------Qty
06-CR10    3
12-ZZZ1    2

DECLARE <a href="/Members/Year">@Year</a>AS int;
DECLARE <a href="/Members/month">@Month</a>AS int;
DECLARE <a href="/Members/Day">@Day</a>AS int;
SET <a href="/Members/Year">@Year</a>= 2013;
SET <a href="/Members/month">@Month</a>= 8;
SET <a href="/Members/Day">@Day</a>= 29;
 
DECLARE @startDate AS DATE;
DECLARE @stopDate AS DATE;
SET @startDate = DATETIMEFROMPARTS(@Year, <a href="/Members/month">@Month</a> <a href="/Members/Day">@Day</a> 0, 0, 0, 0);
SET @stopDate = DATETIMEFROMPARTS(@Year, <a href="/Members/month">@Month</a> <a href="/Members/Day">@Day</a> 23, 59, 59, 999);

SELECT PartNumber, ThumbNail, SDescription, sum(Qty) Qty, Cost, Price, SUM(Qty * Price) SubTotal From CompletedOrdersCart 
WHERE OrderDate > @startDate AND OrderDate < @stopDate
GROUP BY PartNumber, Thumbnail, SDescription, Cost, Price

jkirkerx wrote:

I tried between, but I would of had to deduct a day, and add a day for start and stop.

WHERE OrderDate BETWEEN @StartDate AND @StopDate

WHERE OrderDate >= @startDate AND OrderDate <= @stopDate

WHERE OrderDate > @startDate AND OrderDate < @stopDate

"DECLARE @startDate AS DATE; " & _
        "DECLARE @stopDate AS DATE; " & _
        "SET @startDate = DATETIMEFROMPARTS(@Year, @Month, @Day, 0, 0, 0, 0); " & _
        "SET @stopDate = DATETIMEFROMPARTS(@Year, @Month, @Day, 23, 59, 59, 999); " & _
        "DECLARE @SummaryItems TABLE(" & _
            "PartNumber VarChar(80), " & _
            "ThumbNail VarChar(250), " & _
            "SDescription VarChar(250), " & _
            "Qty INT, " & _
            "Cost Decimal, " & _
            "Price Decimal, " & _
            "SubTotal Decimal " & _
        ") " & _
        "INSERT INTO @SummaryItems (PartNumber, ThumbNail, SDescription, Qty, Cost, Price, SubTotal) " & _
        "SELECT PartNumber, ThumbNail, SDescription, SUM(Qty), Cost, Price, SUM(Qty * Price) From CompletedOrdersCart  " & _
        "WHERE OrderDate > @startDate AND OrderDate < @stopDate " & _
        "GROUP BY PartNumber, Thumbnail, SDescription, Qty, Cost, Price " & _
        "SELECT PartNumber, Thumbnail, SDescription, Qty, Cost, Price, SubTotal FROM @SummaryItems;"