Trouble working with dates again and union and sum

jkirkerx

So I wrote this to represent the TSQL below Not sure where I messed up, but I think it's the sum at the end. I got one result for $46.51, but it should of been $0.00, now I broke it.

Using context As New OrdersContext

Dim DateStart As DateTime = DateTime.Today.AddDays(-1)
Dim DateStop As DateTime = DateTime.Today.AddDays(+1)

pValue = _
(From oh In context.Order_History
Where oh.OrderStatus = "COMPLETED" _
And oh.OrderDate > DateStart _
And oh.OrderDate < DateStop
Select oh).Union _
(From oc In context.Order_Completed
Where oc.OrderStatus = "QUEUED_FOR_FULFILLMENT" _
Or oc.OrderStatus = "OUT_FOR_FULFILLMENT" _
Or oc.OrderStatus = "QUEUED_FOR_SHIPPING" _
Or oc.OrderStatus = "OUT_FOR_PACKAGING" _
Or oc.OrderStatus = "READY_TO_SHIP" _
And oc.OrderDate > DateStart _
And oc.OrderDate < DateStop
Select oc).Sum(Function(m) m.GrandTotal)

End Using

To represent this

DECLARE @StartDate AS Date;
DECLARE @StopDate AS Date;
SET @StartDate = CONVERT(CHAR(10),GETDATE(),101);
SET @StopDate = CONVERT(CHAR(10),DATEADD(d,1, GETDATE()),101);
WITH sums AS (
SELECT SUM(GrandTotal) GrandTotal
FROM CompletedOrdersHistory
WHERE OrderStatus='COMPLETED'
AND OrderDate >= @StartDate
AND OrderDate < @StopDate
UNION ALL
SELECT SUM(GrandTotal) GrandTotal
FROM CompletedOrders
WHERE OrderStatus = 'QUEUED_FOR_FULFILLMENT'
OR OrderStatus = 'OUT_FOR_FULFILLMENT'
OR OrderStatus = 'QUEUED_FOR_SHIPPING'
OR OrderStatus = 'OUT_FOR_PACKAGING'
OR OrderStatus = 'READY_TO_SHIP'
AND OrderDate >= @StartDate
AND OrderDate < @StopDate
)
SELECT Sum(GrandTotal) AS GrandTotal
FROM sums

jkirkerx

Got the union working, and sum, just have trouble with the date range.

pValue = _
((From oh In context.Order_History
Where oh.OrderStatus = "COMPLETED" _
And oh.OrderDate = DbFunctions.AddDays(DateTime.Now(), 0)
Select oh.GrandTotal).Union _
(From oc In context.Order_Completed
Where oc.OrderStatus = "QUEUED_FOR_FULFILLMENT" _
Or oc.OrderStatus = "OUT_FOR_FULFILLMENT" _
Or oc.OrderStatus = "QUEUED_FOR_SHIPPING" _
Or oc.OrderStatus = "OUT_FOR_PACKAGING" _
Or oc.OrderStatus = "READY_TO_SHIP" _
And oc.OrderDate = DbFunctions.AddDays(DateTime.Now(), 0)
Select oc.GrandTotal)).Sum()

jkirkerx

Well I'm closer now. I suspect that I have to redesign this Linq in order for it to work. More of a From Sum Select then where The cast to value type 'System.Decimal' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type.

Dim pValue As Nullable(Of Decimal) = 0

    Using context As New OrdersContext

        Dim DateStart As DateTime = DateTime.Today.AddDays(-1)
        Dim DateStop As DateTime = DateTime.Today.AddDays(+1)

        pValue = \_
            ((From oh In context.Order\_History
            Where oh.OrderDate >= DateStart \_
            And oh.OrderDate <= DateStop \_
            And oh.OrderStatus = "COMPLETED" \_
            Select oh.GrandTotal).Union \_
            (From oc In context.Order\_Completed
            Where oc.OrderDate >= DateStart \_
            And oc.OrderDate <= DateStop \_
            And oc.OrderStatus = "QUEUED\_FOR\_FULFILLMENT" \_
            Or oc.OrderStatus = "OUT\_FOR\_FULFILLMENT" \_
            Or oc.OrderStatus = "QUEUED\_FOR\_SHIPPING" \_
            Or oc.OrderStatus = "OUT\_FOR\_PACKAGING" \_
            Or oc.OrderStatus = "READY\_TO\_SHIP"
            Select oc.GrandTotal)).Sum()

    End Using

    Return pValue

jkirkerx

Finally

Using context As New OrdersContext

        pValue = \_
            (
                From oh In context.Order\_History
                Where oh.OrderDate >= DateStart \_
                And oh.OrderDate <= DateStop \_
                And oh.OrderStatus = "COMPLETED" \_
                Select oh.GrandTotal
            ).Union \_
            (
                From oc In context.Order\_Completed
                Where oc.OrderDate >= DateStart \_
                And oc.OrderDate <= DateStop \_
                And oc.OrderStatus = "QUEUED\_FOR\_FULFILLMENT" \_
                Or oc.OrderStatus = "OUT\_FOR\_FULFILLMENT" \_
                Or oc.OrderStatus = "QUEUED\_FOR\_SHIPPING" \_
                Or oc.OrderStatus = "OUT\_FOR\_PACKAGING" \_
                Or oc.OrderStatus = "READY\_TO\_SHIP"
                Select oc.GrandTotal
            ).DefaultIfEmpty().Sum()

    End Using

jkirkerx

After testing with a real dataset, I was getting the wrong values. For doing this in VB, you have to wrap the date range in (), in order to properly evaluate it. So this produces the correct result, even if the value is 0. I was banging my head against the wall trying to figure this one out. Thanks to Richard Deeming for setting me straight on the DateTime.

Dim pValue As Decimal = 0

    Dim baseDate As DateTime = DateTime.Today()
    Dim dateStart As New DateTime(baseDate.Year, baseDate.Month, baseDate.Day, 0, 0, 0, 0)
    Dim dateStop As New DateTime(baseDate.Year, baseDate.Month, baseDate.Day, 23, 59, 59, 999)

    Using context As New hx5Context

        pValue = \_
        (
            From oh In context.Order\_History
            Where (oh.OrderDate = dateStart And oh.OrderDate <= dateStop) \_
            And Not (oh.OrderStatus = "ORDER\_CANCELED")
            Select oh.GrandTotal
        ).Union \_
        (
            From oc In context.Order\_Completed
            Where (oc.OrderDate >= dateStart And oc.OrderDate <= dateStop) \_
            And
            (
                oc.OrderStatus = "QUEUED\_FOR\_FULFILLMENT" \_
                Or oc.OrderStatus = "OUT\_FOR\_FULFILLMENT" \_
                Or oc.OrderStatus = "QUEUED\_FOR\_SHIPPING" \_
                Or oc.OrderStatus = "OUT\_FOR\_PACKAGING" \_
                Or oc.OrderStatus = "READY\_TO\_SHIP" \_
            )
            Select oc.GrandTotal
        ).DefaultIfEmpty().Sum()

    End Using

    Return pValue

Mycroft Holmes

jkirkerx wrote:

Thanks to Richard Deeming for setting me straight on the DateTime.

You can up vote his responses to show appreciation (up/down arrows appear on the left of the response).

Never underestimate the power of human stupidity RAH

jkirkerx

I haven't been able to do that in years, its like they took that away from me. Seriously, I don't get the vote interface when I log in.

Brisingr Aerowing

Hover your mouse pointer over a message and two arrows should appear on the left side. The green upward pointing one is an upvote. The old voting system was removed due to abuse, but now a spam/abuse vote is used as a one vote by wuck fits and trolls. No real change there.

What do you get when you cross a joke with a rhetorical question? The metaphorical solid rear-end expulsions have impacted the metaphorical motorized bladed rotating air movement mechanism. Do questions with multiple question marks annoy you???

jkirkerx

I've seen those, never bothered to click on them. Thanks Guess you need to delete the message to keep it secret secret!