Forum Replies Created

Viewing 15 posts - 886 through 900 (of 2,462 total)

  • RE: Calendar Table Function

    Alan.B (3/30/2016)

    ScottPletcher (3/30/2016)

    Alan.B (3/30/2016)

    ScottPletcher (3/30/2016)

    Alan.B (3/29/2016)

    Lastly, ditto the last Thursday of the month:

    SET STATISTICS IO ON;

    PRINT 'Old way:';

    SELECT DISTINCT MAX([Date]) OVER (PARTITION BY [Year],[MonthName],[WeekdayName]) AS [Date]

    FROM ...

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    March 30, 2016 at 8:14 pm

    #1868729

  • RE: Calendar Table Function

    ScottPletcher (3/30/2016)

    Alan.B (3/30/2016)

    ScottPletcher (3/30/2016)

    Alan.B (3/29/2016)

    Lastly, ditto the last Thursday of the month:

    SET STATISTICS IO ON;

    PRINT 'Old way:';

    SELECT DISTINCT MAX([Date]) OVER (PARTITION BY [Year],[MonthName],[WeekdayName]) AS [Date]

    FROM dbo.itvf_CALENDAR('01/01/2014','12/31/2014')

    WHERE...

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    March 30, 2016 at 1:34 pm

    #1868711

  • RE: Calendar Table Function

    ScottPletcher (3/30/2016)

    Alan.B (3/29/2016)

    Lastly, ditto the last Thursday of the month:

    SET STATISTICS IO ON;

    PRINT 'Old way:';

    SELECT DISTINCT MAX([Date]) OVER (PARTITION BY [Year],[MonthName],[WeekdayName]) AS [Date]

    FROM dbo.itvf_CALENDAR('01/01/2014','12/31/2014')

    WHERE ...

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    March 30, 2016 at 11:17 am

    #1868681

  • RE: By date

    girl_bj (3/29/2016)

    It will be continuous. I am just testing for 1 month on how it works. The weekly data will be send as an output file.

    Is there any way that...

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    March 29, 2016 at 10:50 pm

    #1868475

  • RE: Calendar Table Function

    Nice work, well done sir. A few things to note:

    First a couple of ticky-tacky things that aren't a huge deal but worth mentioning...

    You may want to consider using a...

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    March 29, 2016 at 10:46 pm

    #1868473

  • RE: By date

    If you had a calendar table available the you can do it like this (note my code and comments):

    USE tempdb

    GO

    -- Simplified calendar table with only the columns you need

    IF OBJECT_ID('dbo.calendar')...

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    March 29, 2016 at 9:51 pm

    #1868462

  • RE: Creating Combined Queries

    Phil Parkin (3/29/2016)

    Alan.B (3/29/2016)

    You would use AND.

    SELECT vend_id, prod_id, pro_price

    FROM products

    WHERE prod_price <=5

    AND vend_id IN (1001, 1002);

    Disagree. This requires an OR.

    select a from b where condition1

    union (should probably be union...

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    March 29, 2016 at 10:41 am

    #1868327

  • RE: Creating Combined Queries

    You would use AND.

    SELECT vend_id, prod_id, pro_price

    FROM products

    WHERE prod_price <=5

    AND vend_id IN (1001, 1002);

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    March 29, 2016 at 10:31 am

    #1868320

  • RE: issue with single quotes in the column values

    Can you include some DDL and sample data?

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    March 29, 2016 at 10:29 am

    #1868319

  • RE: Does it make a sense to use parallelism for single HD drive ?

    SQL Guy 1 (3/28/2016)

    And about the value for threshold for parallelism, you recommend to do some research. Can I ask what should I check for it?

    I am not an...

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    March 28, 2016 at 2:39 pm

    #1868084

  • RE: Does it make a sense to use parallelism for single HD drive ?

    SQL Guy 1 (3/28/2016)

    I have a complex query with multiple joins and conditions. My test server has only one drive for data. Same for production.

    Estimated execution plan inserts...

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    March 28, 2016 at 12:18 pm

    #1868038

  • RE: Running Totals with window function

    Jacob Wilkins (3/28/2016)

    DesNorton (3/28/2016)

    Thank you Sachin and Jacob. From my initial dev testing, this appears to do the trick. Now we see if the QA team can break...

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    March 28, 2016 at 9:52 am

    #1868014

  • RE: Trigger Happy

    Great article Andy, well done.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    March 28, 2016 at 9:18 am

    #1867997

  • RE: MDX Book Recomendation

    Jon.Morisi (3/25/2016)

    I bet there's only 2 to pick from, if you can even find one. :hehe: BI books in general have been hard to come by since 2008...

    Yep.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    March 26, 2016 at 11:16 am

    #1867862

  • RE: Running Totals with window function

    Jacob Wilkins (3/25/2016)

    Sachin Nandanwar (3/25/2016)

    ...But again it looks to cumbersome with all those conditions in the CASE expression.Your original solution is more subtle.

    Your version doesn't have to be that complicated.

    It...

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    March 25, 2016 at 11:28 am

    #1867770

Viewing 15 posts - 886 through 900 (of 2,462 total)