Post reply

cte Coding... Need the case statement to get this to work

  • January 14, 2019 at 1:44 pm

    #379021

    Hello,

    I had a cte query that is working.  We need to reset the date every August.  So, the cte coding that I have is like this:

       
    Declare @TestDate as Date
    Set @TestDate = '2019-07-01';

    WITH cte AS (
     SELECT 1 AS NumMonths
     UNION ALL
     SELECT NumMonths + 1
     FROM cte
     WHERE NumMonths <13
    )

    SELECT DateAdd(Month, DateDiff(Year, 0, DateAdd(Month, -7, @TestDate)) * 12+(6) + NumMonths, 0) AS StartMonth,

    datename(month,DateAdd(Month, DateDiff(Year, 0, DateAdd(Month, -7, @TestDate)) * 12+(6) + NumMonths, 0)) + ' - ' +
    convert(char(4),datepart(year,DateAdd(Month, DateDiff(Year, 0, DateAdd(Month, -7, @TestDate)) * 12+(6) + NumMonths, 0)), 0) as MonthYear,
    DATEDIFF(month, DateAdd(Month, DateDiff(Year, 0, DateAdd(Month, -7, @TestDate)) * 12+(6) + NumMonths, 0),
    DATEADD(MONTH, DATEDIFF(MONTH, 0, @TestDate)-0, 0)) as MonthsBack

    FROM cte
    where DATEDIFF(month, DateAdd(Month, DateDiff(Year, 0, DateAdd(Month, -7, @TestDate)) * 12+(6) + NumMonths, 0),
    DATEADD(MONTH, DATEDIFF(MONTH, 0, @TestDate)-0, 0)) > 0
    order by DATEDIFF(month, DateAdd(Month, DateDiff(Year, 0, DateAdd(Month, -7, @TestDate)) * 12+(6) + NumMonths, 0),
    DATEADD(MONTH, DATEDIFF(MONTH, 0, @TestDate)-0, 0))

    I got the result as expected, which is like the photo below:

    When I changed the variable date to: '2019-09-01', it also provided me with the correct result that it should return only last full month.

       
    Declare @TestDate as Date
    Set @TestDate = '2019-09-01';

    WITH cte AS (
     SELECT 1 AS NumMonths
     UNION ALL
     SELECT NumMonths + 1
     FROM cte
     WHERE NumMonths <13
    )

    SELECT DateAdd(Month, DateDiff(Year, 0, DateAdd(Month, -7, @TestDate)) * 12+(6) + NumMonths, 0) AS StartMonth,

    datename(month,DateAdd(Month, DateDiff(Year, 0, DateAdd(Month, -7, @TestDate)) * 12+(6) + NumMonths, 0)) + ' - ' +
    convert(char(4),datepart(year,DateAdd(Month, DateDiff(Year, 0, DateAdd(Month, -7, @TestDate)) * 12+(6) + NumMonths, 0)), 0) as MonthYear,
    DATEDIFF(month, DateAdd(Month, DateDiff(Year, 0, DateAdd(Month, -7, @TestDate)) * 12+(6) + NumMonths, 0),
    DATEADD(MONTH, DATEDIFF(MONTH, 0, @TestDate)-0, 0)) as MonthsBack

    FROM cte
    where DATEDIFF(month, DateAdd(Month, DateDiff(Year, 0, DateAdd(Month, -7, @TestDate)) * 12+(6) + NumMonths, 0),
    DATEADD(MONTH, DATEDIFF(MONTH, 0, @TestDate)-0, 0)) > 0
    order by DATEDIFF(month, DateAdd(Month, DateDiff(Year, 0, DateAdd(Month, -7, @TestDate)) * 12+(6) + NumMonths, 0),
    DATEADD(MONTH, DATEDIFF(MONTH, 0, @TestDate)-0, 0))

    However, when I changed the variable date to: '2019-08-01', I got the blank result.

    Question:

    What should I do to have the results returned as below, when the variable has been changed to: '2019-08-01' or any other 'August'? 

    I think I need to add the case statement in there, like: DATENAME(month, @TestDate) = 'August'  THEN xxxxx  ELSE (follow to the original syntax that I have.)  Also, the WHERE clause should be modified as well.

    This is the correct result, when August 2019, '2019-08-01',  is selected. 

    :

    Thanks for looking into this.

  • January 14, 2019 at 1:55 pm

    #2018878

    Are you just adjusting for a Fiscal Year or similar? You could do that with a Calendar table.

  • January 14, 2019 at 2:30 pm

    #2018881

    pietlinden - Monday, January 14, 2019 1:55 PM

    Are you just adjusting for a Fiscal Year or similar? You could do that with a Calendar table.

    Thanks for your reply.  

    Our fiscal year starts in August, not January. So, we have to adjust the codes in order to make this to work. 

    I am looking for the case statement and the where clause that might need to trigger the result, if the variable is 'August'. 

    Thanks again for your reply.

  • January 14, 2019 at 2:43 pm

    #2018882

    If you add a Calendar table and put the FM, FY in there as columns, then this is trivial.

  • January 14, 2019 at 3:04 pm

    #2018885

    There are several things wrong with this query.

    1)  rCTEs are a HORRIBLE way to do a sequence of numbers.  You're not noticing it, because the size is so small.  Using ROW_NUMBER in conjunction with a table value constructor (and possible CROSS JOINS depending on size) is blazing fast even for very large sequences.

    2)  You're referencing the "StartMonth" value a lot.  Use a CROSS APPLY so that you can reference it with an alias instead of having to type out the calculation every single time you need to reference it.

    3)  You're date calculations are needlessly complex.

    4)  You can greatly simplify the where clause.

    Declare @TestDate as Date
    Set @TestDate = '2019-08-01';

    WITH cte AS
    (
        SELECT ROW_NUMBER() OVER(ORDER BY @@VERSION) AS NumMonths
        FROM ( VALUES(0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0) ) n(n)
    )

    SELECT
        dt.StartMonth,
        NumMonths AS MonthsBack,
        DATENAME(MONTH, dt.StartMonth) + ' - ' + DATENAME(YEAR, dt.StartMonth)
    FROM cte
    CROSS APPLY (VALUES(CAST(DATEADD(MONTH, -NumMonths, @TestDate) AS DATETIME)) ) dt(StartMonth)
    WHERE dt.StartMonth >= DATEADD(MONTH, DATEDIFF(MONTH, '1900-09-01', @TestDate)/12 * 12, '1900-08-01')
    ORDER BY NumMonths

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • January 15, 2019 at 9:21 am

    #2018948

    First of all, there is no such thing as a case statement in SQL. We have a case expression, which returns a single scalar value. But more than that, your whole approach to SQL is wrong. This is a declarative language based on sets. What you're writing code as if you were still in Fortran or COBOL procedural languages that do computations. An SQL programmer would simply set up a calendar table with the fiscal year information in it. Since it's a calendar, you don't need to worry about your fill factor, but you probably should put it in descending order.

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • January 15, 2019 at 9:32 am

    #2018950

    jcelko212 32090 - Tuesday, January 15, 2019 9:21 AM

    First of all, there is no such thing as a case statement in SQL. We have a case expression, which returns a single scalar value. But more than that, your whole approach to SQL is wrong. This is a declarative language based on sets. What you're writing code as if you were still in Fortran or COBOL procedural languages that do computations. An SQL programmer would simply set up a calendar table with the fiscal year information in it. Since it's a calendar, you don't need to worry about your fill factor, but you probably should put it in descending order.

    Really getting tired of your bullying people, Mr. Celko. Just stop it.

  • January 15, 2019 at 9:50 am

    #2018952

    jcelko212 32090 - Tuesday, January 15, 2019 9:21 AM

    First of all, there is no such thing as a case statement in SQL. We have a case expression, which returns a single scalar value. But more than that, your whole approach to SQL is wrong. This is a declarative language based on sets. What you're writing code as if you were still in Fortran or COBOL procedural languages that do computations. An SQL programmer would simply set up a calendar table with the fiscal year information in it. Since it's a calendar, you don't need to worry about your fill factor, but you probably should put it in descending order.

    If he is working with a vendor's database, he may not have the option to create a calendar table, and, even if he does, he might want to use the date math to originally populate the calendar table.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • January 15, 2019 at 1:46 pm

    #2019000

    drew.allen - Tuesday, January 15, 2019 9:50 AM

    jcelko212 32090 - Tuesday, January 15, 2019 9:21 AM

    If he is working with a vendor's database, he may not have the option to create a calendar table, and, even if he does, he might want to use the date math to originally populate the calendar table.

    Drew

    Using temporal math to fill out a calendar table is always dangerous. Would you really like to write a program to compute Easter? Which Easter? Orthodox or Catholic? Do you have other weird national holidays? I found the best thing to do is to go down to the accounting department, and ask them to look at what they're doing with their spreadsheets or have done in the past with her spreadsheets, assuming they talk to the guys in legal, so that where coordinated with them. Take a few days of research and get it right.

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • January 15, 2019 at 2:07 pm

    #2019003

    jcelko212 32090 - Tuesday, January 15, 2019 1:46 PM

    drew.allen - Tuesday, January 15, 2019 9:50 AM

    jcelko212 32090 - Tuesday, January 15, 2019 9:21 AM

    If he is working with a vendor's database, he may not have the option to create a calendar table, and, even if he does, he might want to use the date math to originally populate the calendar table.

    Drew

    Using temporal math to fill out a calendar table is always dangerous. Would you really like to write a program to compute Easter? Which Easter? Orthodox or Catholic? Do you have other weird national holidays? I found the best thing to do is to go down to the accounting department, and ask them to look at what they're doing with their spreadsheets or have done in the past with her spreadsheets, assuming they talk to the guys in legal, so that where coordinated with them. Take a few days of research and get it right.

    Note that I said "originally populate the calendar table."  I never said that that would be the ONLY method used to fill out the table.

    There are also arguments that holidays should be stored in a separate table rather than directly in the calendar table.  I don't have a strong opinion either way on that argument.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • January 15, 2019 at 4:29 pm

    #2019019

    drew.allen - Tuesday, January 15, 2019 2:07 PM

    jcelko212 32090 - Tuesday, January 15, 2019 1:46 PM

    drew.allen - Tuesday, January 15, 2019 9:50 AM

    jcelko212 32090 - Tuesday, January 15, 2019 9:21 AM

    If he is working with a vendor's database, he may not have the option to create a calendar table, and, even if he does, he might want to use the date math to originally populate the calendar table.

    Drew

    Using temporal math to fill out a calendar table is always dangerous. Would you really like to write a program to compute Easter? Which Easter? Orthodox or Catholic? Do you have other weird national holidays? I found the best thing to do is to go down to the accounting department, and ask them to look at what they're doing with their spreadsheets or have done in the past with her spreadsheets, assuming they talk to the guys in legal, so that where coordinated with them. Take a few days of research and get it right.

    Note that I said "originally populate the calendar table."  I never said that that would be the ONLY method used to fill out the table.

    There are also arguments that holidays should be stored in a separate table rather than directly in the calendar table.  I don't have a strong opinion either way on that argument.

    Drew

    And who says they need to track Easter as a holiday?

  • January 16, 2019 at 11:35 am

    #2019127

    drew.allen - Tuesday, January 15, 2019 2:07 PM

    jcelko212 32090 - Tuesday, January 15, 2019 1:46 PM

    drew.allen - Tuesday, January 15, 2019 9:50 AM

    jcelko212 32090 - Tuesday, January 15, 2019 9:21 AM

    There are also arguments that holidays should be stored in a separate table rather than directly in the calendar table.  I don't have a strong opinion either way on that argument.
    I never liked putting the holidays in a separate table. I don't like having to bounce back and forth between a very vanilla calendar table and this holiday table to get my business days. It feels too much like attribute splitting. The Ordinal business day is a good trick. Number the days from whenever your calendar starts and repeat a number for a weekend or company holiday.

    CREATE TABLE Calendar
    (cal_date DATE NOT NULL PRIMARY KEY,
    ordinal_business_nbr INTEGER NOT NULL,
    ...);

    INSERT INTO Calendar
    VALUES ('2007-04-05', 42);
     ('2007-04-06', 43); -- Good Friday
     ('2007-04-07', 43);
     ('2007-04-08', 43); -- Easter Sunday
     ('2007-04-09', 44);
     ('2007-04-10', 45); -- Tuesday, back to work

    To compute the business days from '2007-04-05', Thursday before Easter in 2007 to '2007-04-10', the next Tuesday:

    SELECT (C2.ordinal_business_nbr - C1.ordinal_business_nbr -1) AS business_day_cnt
    FROM Calendar AS C1, Calendar AS C2
    WHERE C1.cal_date = '2007-04-05'
     AND C2.cal_date = '2007-04-10';

    This saves having to scan all of the rows within the range to get a sum. The -1 is to avoid counting the current day as one of the business days, but you might have a different rule in your company.

    Please post DDL and follow ANSI/ISO standards when asking for help. 

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

You must be logged in to reply to this topic. Login to reply