Forum Replies Created

Viewing 12 posts - 1 through 12 (of 12 total)

  • Reply To: Using Results from CASE Statement to create a calculated Field

    You could also move the CASE statement into a CROSS APPLY to calculate the newAmount.

    SELECT 
    ca.newAmount
    , ca.newAmount + t.Adjust as TotalAmount
    From...

    • This reply was modified 10 months, 1 week ago by  kherald69. Reason: Fixed missing table/field aliasing
    • This reply was modified 10 months, 1 week ago by  kherald69.
  • Reply To: Need to create gaps in a table of date ranges

    In my company's case we use that date of 2078-12-31 as the maximum date because there are fields in some tables that are defined as SMALLDATETIME and since the maximum...

  • Reply To: Need to create gaps in a table of date ranges

    Ken, that is some pretty neat code.  I had to tweak it just a bit to support a scenario that I forgot to include in my original post.

    The extra scenario...

  • Reply To: Need to create gaps in a table of date ranges

    I completely forgot about trying the EXCEPT route. The method I was trying is similar to what you have by expanding the date ranges but my method of remerging the...

  • Reply To: JOIN 4 TABLES with UNIQUE ID

    You just need to change the INNER JOINs to FULL JOINs and change the a.id field in the SELECT portion to COALESCE(a.id, b.id, c.id, d.id) and give it the alias...

  • Reply To: Dynamic SQL and parameters

    You were almost there.  You correctly parameterized the @localtable variable in the first field of the select. However, to ensure that the variables that you concatenate for the object names...

  • RE: CONVERT DATETIME to TIME and remove seconds and milliseconds

    Sergiy (10/5/2016)


    SELECT CONVERT(TIME,CONVERT(SMALLDATETIME, @StartTime2))

    Please be aware that when converting a DATETIME to a SMALLDATETIME that when the seconds are <30 the minutes will match but when the seconds are 30+...

  • RE: In The Beginning

    I can see that it works but what is the meaning of the time int value.

    Here is an example of why I say it is stored as a float.

    with cte...

  • RE: In The Beginning

    Ed Wagner (11/25/2014)


    PHYData DBA (11/25/2014)


    Brian.Klinect (11/25/2014)


    That's exactly what I did. I had no idea I could use a negative number for a datetime!

    Since DateTime is stored as an Integer,...

  • RE: Logical Query Processing order

    :ermm: Is it just me or are options 1 and 4 identical?

    Option #1: FROM,ON,JOIN,WHERE,GROUP BY,CUBE | ROLLUP,HAVING,SELECT,DISTINCT,ORDER BY,TOP

    Option #4: FROM,ON,JOIN,WHERE,GROUP BY,CUBE | ROLLUP,HAVING,SELECT,DISTINCT,ORDER BY,TOP

    I chose option #1 and it looks...

  • RE: Missing Century in Date

    Ok, I just have to toss a wrench into the logic here.

    Since we are talking about birthdays, what if the person is over 100 years old? This logic will...

  • RE: Get particular day between two dates

    You can use a recursive common table expression to achieve this.

    DECLARE @startDate DATETIME = '8/1/2014'

    DECLARE @endDate DATETIME = '8/31/2014'

    DECLARE @dayOfWeek INT = 1 -- 1=Sun, 7=Sat

    ;WITH cte_Recursion AS

    (

    SELECT...

Viewing 12 posts - 1 through 12 (of 12 total)