Click here to Skip to main content
15,850,446 members
Home / Discussions / Database
   

Database

 
GeneralRe: SQL Linq, select from select and join Pin
jkirkerx22-Jul-15 7:02
professionaljkirkerx22-Jul-15 7:02 
GeneralRe: SQL Linq, select from select and join Pin
Richard Deeming22-Jul-15 9:00
mveRichard Deeming22-Jul-15 9:00 
GeneralRe: SQL Linq, select from select and join Pin
jkirkerx22-Jul-15 10:04
professionaljkirkerx22-Jul-15 10:04 
QuestionTrouble working with dates again and union and sum Pin
jkirkerx20-Jul-15 14:08
professionaljkirkerx20-Jul-15 14:08 
AnswerRe: Trouble working with dates again and union and sum Pin
jkirkerx20-Jul-15 14:34
professionaljkirkerx20-Jul-15 14:34 
Generalmake nullable type Pin
jkirkerx20-Jul-15 14:59
professionaljkirkerx20-Jul-15 14:59 
General[Got It] Pin
jkirkerx21-Jul-15 10:58
professionaljkirkerx21-Jul-15 10:58 
GeneralThe above post is incorrect - SQL Linq, Date Range, Union in VB Pin
jkirkerx22-Jul-15 9:54
professionaljkirkerx22-Jul-15 9:54 
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

GeneralRe: The above post is incorrect - SQL Linq, Date Range, Union in VB Pin
Mycroft Holmes22-Jul-15 15:15
professionalMycroft Holmes22-Jul-15 15:15 
GeneralRe: The above post is incorrect - SQL Linq, Date Range, Union in VB Pin
jkirkerx23-Jul-15 7:38
professionaljkirkerx23-Jul-15 7:38 
GeneralRe: The above post is incorrect - SQL Linq, Date Range, Union in VB Pin
Brisingr Aerowing23-Jul-15 8:37
professionalBrisingr Aerowing23-Jul-15 8:37 
GeneralRe: The above post is incorrect - SQL Linq, Date Range, Union in VB Pin
jkirkerx23-Jul-15 9:21
professionaljkirkerx23-Jul-15 9:21 
QuestionDatabase management Pin
Otekpo Emmanuel19-Jul-15 10:22
Otekpo Emmanuel19-Jul-15 10:22 
GeneralRe: Database management Pin
PIEBALDconsult19-Jul-15 10:40
mvePIEBALDconsult19-Jul-15 10:40 
AnswerRe: Database management Pin
Richard MacCutchan19-Jul-15 23:00
mveRichard MacCutchan19-Jul-15 23:00 
AnswerRe: Database management Pin
Mycroft Holmes19-Jul-15 23:32
professionalMycroft Holmes19-Jul-15 23:32 
AnswerRe: Database management Pin
Wendelius20-Jul-15 7:50
mentorWendelius20-Jul-15 7:50 
QuestionDatabase management Pin
Otekpo Emmanuel19-Jul-15 10:38
Otekpo Emmanuel19-Jul-15 10:38 
QuestionRe: Database management Pin
ZurdoDev22-Jul-15 10:48
professionalZurdoDev22-Jul-15 10:48 
Questionlarge database Pin
V.16-Jul-15 22:46
professionalV.16-Jul-15 22:46 
AnswerRe: large database Pin
Wendelius16-Jul-15 23:03
mentorWendelius16-Jul-15 23:03 
GeneralRe: large database Pin
V.16-Jul-15 23:10
professionalV.16-Jul-15 23:10 
GeneralRe: large database Pin
Wendelius16-Jul-15 23:20
mentorWendelius16-Jul-15 23:20 
GeneralRe: large database Pin
Mycroft Holmes17-Jul-15 15:02
professionalMycroft Holmes17-Jul-15 15:02 
GeneralRe: large database Pin
V.18-Jul-15 1:12
professionalV.18-Jul-15 1:12 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.