locked
CTE Query RRS feed

  • Question

  • Hi,

    I have query which is given below

    WITH FTS AS
         (
        SELECT     
         InventoryPlanSettingsID AS InventoryPlanSettingsID,    
         CONVERT(INT,(DATEDIFF(MM,CAST(CONVERT(CHAR(4),'20'+SUBSTRING(FTSRunPeriod,1,2))+'-'+ CONVERT(CHAR(2),SUBSTRING(FTSRunPeriod,4,2))+'-01' AS DATE),CAST(SUBSTRING(CAST(FTSForecastYearPeriod AS CHAR(6)),1,4)+'-'+ SUBSTRING(CAST(FTSForecastYearPeriod AS CHAR(6)),5,2)+'-01' AS DATE)))) AS DemandPlanIntervalID,
         ROW_NUMBER() OVER(
         PARTITION BY  FTSRunID, CONVERT(INT,(DATEDIFF(MM,CAST(CONVERT(CHAR(4),'20'+SUBSTRING(FTSRunPeriod,1,2))+'-'+ CONVERT(CHAR(2),SUBSTRING(FTSRunPeriod,4,2))+'-01' AS DATE),CAST(SUBSTRING(CAST(FTSForecastYearPeriod AS CHAR(6)),1,4)+'-'+ SUBSTRING(CAST(FTSForecastYearPeriod AS CHAR(6)),5,2)+'-01' AS DATE))))
         ORDER BY FTSRunID,CONVERT(INT,(DATEDIFF(MM,CAST(CONVERT(CHAR(4),'20'+SUBSTRING(FTSRunPeriod,1,2))+'-'+ CONVERT(CHAR(2),SUBSTRING(FTSRunPeriod,4,2))+'-01' AS DATE),CAST(SUBSTRING(CAST(FTSForecastYearPeriod AS CHAR(6)),1,4)+'-'+ SUBSTRING(CAST(FTSForecastYearPeriod AS CHAR(6)),5,2)+'-01' AS DATE)))) ASC
         ) AS RN
         FROM Staging.InventoryPlanSettingsForecastTimeSeries
         WHERE
         (__$operation is null) or (__$operation is not null and (__$operation = 1 or __$operation = 4))
         AND FTSRunPeriod NOT LIKE '%W%'
         )
         SELECT
         InventoryPlanSettingsID,
         DemandPlanIntervalID
         FROM FTS
         WHERE RN=1

    when i execute it , it throwing an error. The reason is simple FTSRunPeriod NOT LIKE '%W%' is placed wrong.

    So I need to select something like first

        SELECT
         FTSRunPeriod
         FROM Staging.InventoryPlanSettingsForecastTimeSeries
         WHERE FTSRunPeriod NOT LIKE '%W%'

    Then can use the CTE, but how ..please help me with query


    Regards, Kishlay Anand

    Thursday, October 18, 2012 11:18 AM

Answers

  • Please tell the error. Also, I suggest:

    ;with cte as (SELECT InventoryPlanSettingsID AS InventoryPlanSettingsID, FTSRunID,

    CONVERT(CHAR(4),'20'+SUBSTRING(@FTSFTSRunPeriod,1,2))+ CONVERT(CHAR(2),

    SUBSTRING(@FTSFTSRunPeriod,4,2))+'01') as RunPeriodDate,

    SUBSTRING(CAST(FTSForecastYearPeriod AS CHAR(6)),1,4)+

    SUBSTRING(CAST(FTSForecastYearPeriod AS CHAR(6)),5,2)+'01' AS ForecastDate FROM Staging.InventoryPlanSettingsForecastTimeSeries WHERE       (__$operation is null) or (__$operation is not null and (__$operation = 1 or __$operation = 4))      AND FTSRunPeriod NOT LIKE '%W%'      ), fts as (select *, case when ISDATE(RunPeriodDate)=1 and ISDATE(ForecastDate)=1 THEN DATEDIFF(month,CAST(RunPeriodDate as DATE),

    CAST(ForecastDate AS DATE)) END AS DemandPlanIntervalID FROM CTE), fts1 AS (SELECT *, ROW_NUMBER() OVER (partition by FTSRunId, DemandPlanIntervalId) as Rn from fts) SELECT * from fts1 WHERE Rn = 1

    In other words, do it step by step and also add check for ISDATE.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    • Edited by Naomi N Thursday, October 18, 2012 3:45 PM
    Thursday, October 18, 2012 3:41 PM

All replies

  • And the error message is?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, October 18, 2012 12:01 PM
  • Conversion failed to date/time from String.

    Please help me out..


    Regards, Kishlay Anand

    Thursday, October 18, 2012 12:03 PM
  • Actually FTSRunPeriod has data like '12W39'

    So thats why pur condition to remove data with W so that conversion could be done to date/time. But i think condition is not working properly with CTE


    Regards, Kishlay Anand

    Thursday, October 18, 2012 12:09 PM
  • You need to construct your expression using CASE statement and something like

    CASE ISDATE(FTSRunPeriod) = 1 then Yourexpression END

    in addition to filtering bad data using WHERE expression.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, October 18, 2012 12:19 PM
  • I didnt get you Naomi..Please help me with query.

    I have written query something like..but its throwing an error

    DECLARE @FTSFTSRunPeriod NVARCHAR(20)
    SET @FTSFTSRunPeriod =(SELECT FTSRunPeriod FROM Staging.InventoryPlanSettingsForecastTimeSeries WHERE FTSRunPeriod LIKE '%M%')
    ;WITH FTS AS
         (    
         SELECT     
         InventoryPlanSettingsID AS InventoryPlanSettingsID,    
         CONVERT(INT,(DATEDIFF(MM,CAST(CONVERT(CHAR(4),'20'+SUBSTRING(@FTSFTSRunPeriod,1,2))+'-'+ CONVERT(CHAR(2),SUBSTRING(@FTSFTSRunPeriod,4,2))+'-01' AS DATE),CAST(SUBSTRING(CAST(FTSForecastYearPeriod AS CHAR(6)),1,4)+'-'+ SUBSTRING(CAST(FTSForecastYearPeriod AS CHAR(6)),5,2)+'-01' AS DATE)))) AS DemandPlanIntervalID,
         ROW_NUMBER() OVER(
         PARTITION BY  FTSRunID, CONVERT(INT,(DATEDIFF(MM,CAST(CONVERT(CHAR(4),'20'+SUBSTRING(@FTSFTSRunPeriod,1,2))+'-'+ CONVERT(CHAR(2),SUBSTRING(@FTSFTSRunPeriod,4,2))+'-01' AS DATE),CAST(SUBSTRING(CAST(FTSForecastYearPeriod AS CHAR(6)),1,4)+'-'+ SUBSTRING(CAST(FTSForecastYearPeriod AS CHAR(6)),5,2)+'-01' AS DATE))))
         ORDER BY FTSRunID,CONVERT(INT,(DATEDIFF(MM,CAST(CONVERT(CHAR(4),'20'+SUBSTRING(@FTSFTSRunPeriod,1,2))+'-'+ CONVERT(CHAR(2),SUBSTRING(@FTSFTSRunPeriod,4,2))+'-01' AS DATE),CAST(SUBSTRING(CAST(FTSForecastYearPeriod AS CHAR(6)),1,4)+'-'+ SUBSTRING(CAST(FTSForecastYearPeriod AS CHAR(6)),5,2)+'-01' AS DATE)))) ASC
         ) AS RN
         FROM Staging.InventoryPlanSettingsForecastTimeSeries
         WHERE
         (__$operation is null) or (__$operation is not null and (__$operation = 1 or __$operation = 4))
         --AND FTSRunPeriod NOT LIKE '%W%'
         )
         SELECT
         InventoryPlanSettingsID,
         DemandPlanIntervalID
         FROM FTS
         WHERE RN=1


    Regards, Kishlay Anand

    Thursday, October 18, 2012 12:31 PM
  • try this

    WITH FTS1 as (
    select 
    InventoryPlanSettingsID
    ,FTSRunPeriod 
    ,FTSForecastYearPeriod
    FROM Staging.InventoryPlanSettingsForecastTimeSeries
         WHERE 
         (__$operation is null) or (__$operation is not null and (__$operation = 1 or __$operation = 4))
         AND FTSRunPeriod NOT LIKE '%W%')
    ,
    FTS AS 
         (
        SELECT      
         InventoryPlanSettingsID AS InventoryPlanSettingsID,     
         CONVERT(INT,(DATEDIFF(MM,CAST(CONVERT(CHAR(4),'20'+SUBSTRING(FTSRunPeriod,1,2))+'-'+ CONVERT(CHAR(2),SUBSTRING(FTSRunPeriod,4,2))+'-01' AS DATE),CAST(SUBSTRING(CAST(FTSForecastYearPeriod AS CHAR(6)),1,4)+'-'+ SUBSTRING(CAST(FTSForecastYearPeriod AS CHAR(6)),5,2)+'-01' AS DATE)))) AS DemandPlanIntervalID,
         ROW_NUMBER() OVER(
         PARTITION BY  FTSRunID, CONVERT(INT,(DATEDIFF(MM,CAST(CONVERT(CHAR(4),'20'+SUBSTRING(FTSRunPeriod,1,2))+'-'+ CONVERT(CHAR(2),SUBSTRING(FTSRunPeriod,4,2))+'-01' AS DATE),CAST(SUBSTRING(CAST(FTSForecastYearPeriod AS CHAR(6)),1,4)+'-'+ SUBSTRING(CAST(FTSForecastYearPeriod AS CHAR(6)),5,2)+'-01' AS DATE))))
         ORDER BY FTSRunID,CONVERT(INT,(DATEDIFF(MM,CAST(CONVERT(CHAR(4),'20'+SUBSTRING(FTSRunPeriod,1,2))+'-'+ CONVERT(CHAR(2),SUBSTRING(FTSRunPeriod,4,2))+'-01' AS DATE),CAST(SUBSTRING(CAST(FTSForecastYearPeriod AS CHAR(6)),1,4)+'-'+ SUBSTRING(CAST(FTSForecastYearPeriod AS CHAR(6)),5,2)+'-01' AS DATE)))) ASC
         ) AS RN
         FROM FTS1
         )
         SELECT 
         InventoryPlanSettingsID,
         DemandPlanIntervalID
         FROM FTS
         WHERE RN=1

    Thursday, October 18, 2012 12:41 PM
  • Hi,

    There is something mistake in the query.

    FTSRunPeriod and 
    FTSForecastYearPeriod  not found


    FTS AS 
         (
        SELECT      
         InventoryPlanSettingsID AS InventoryPlanSettingsID,     
         CONVERT(INT,(DATEDIFF(MM,CAST(CONVERT(CHAR(4),'20'+SUBSTRING(FTSRunPeriod,1,2))+'-'+ CONVERT(CHAR(2),SUBSTRING(FTSRunPeriod,4,2))+'-01' AS DATE),CAST(SUBSTRING(CAST(FTSForecastYearPeriod AS CHAR(6)),1,4)+'-'+ SUBSTRING(CAST(FTSForecastYearPeriod AS CHAR(6)),5,2)+'-01' AS DATE)))) AS DemandPlanIntervalID,
         ROW_NUMBER() OVER(
         PARTITION BY  FTSRunID, CONVERT(INT,(DATEDIFF(MM,CAST(CONVERT(CHAR(4),'20'+SUBSTRING(FTSRunPeriod,1,2))+'-'+ CONVERT(CHAR(2),SUBSTRING(FTSRunPeriod,4,2))+'-01' AS DATE),CAST(SUBSTRING(CAST(FTSForecastYearPeriod AS CHAR(6)),1,4)+'-'+ SUBSTRING(CAST(FTSForecastYearPeriod AS CHAR(6)),5,2)+'-01' AS DATE))))
         ORDER BY FTSRunID,CONVERT(INT,(DATEDIFF(MM,CAST(CONVERT(CHAR(4),'20'+SUBSTRING(FTSRunPeriod,1,2))+'-'+ CONVERT(CHAR(2),SUBSTRING(FTSRunPeriod,4,2))+'-01' AS DATE),CAST(SUBSTRING(CAST(FTSForecastYearPeriod AS CHAR(6)),1,4)+'-'+ SUBSTRING(CAST(FTSForecastYearPeriod AS CHAR(6)),5,2)+'-01' AS DATE)))) ASC
         ) AS RN
         FROM FTS1
         )
         SELECT 
         InventoryPlanSettingsID,
         DemandPlanIntervalID
         FROM FTS
         WHERE RN=1


    Regards, Kishlay Anand

    Thursday, October 18, 2012 12:52 PM
  • There some error in the code..

    FTSRunPeriod and 
    FTSForecastYearPeriod not found


    Regards, Kishlay Anand

    Thursday, October 18, 2012 12:59 PM
  • Please reply

    Regards, Kishlay Anand

    Thursday, October 18, 2012 1:27 PM
  • I have not studied your query very closely, because it is a bit unwieldy. I may be wrong, but it seems that the same expressions appear over and over again. I suggest that for legibility that you clean up the query, by first having a CTE where you do something like:

    WITH CTE AS (
        SELECT my_yadayada = <complex_expression>, ...
    )

    And then you can use those complex expression in the next CTE. This makes the query much easier to read, and then it will be easier to spot where the problem is. Hey, you may even be able to find it on your own.

    Please also beware that as long you don't post the necesarry SQL to permit us to test our solutions, you will get untested solutions and they may contain errors. (In this case, the required SQL would be the CREATE TABLE statement for the table(s).)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, October 18, 2012 2:01 PM
  • Hi Erland,

    I am writing my query properly now:

    FTSRunPeriod has data like '12W39'

    I am using CTE to

    DECLARE @FTSRunPeriod NVARCHAR(20)
    DECLARE @FTSForecastYearPeriod INT
    SET @FTSForecastYearPeriod=201009
    SET @FTSRunPeriod='12W39'
    ;WITH CTE AS
    (
    SELECT
        CONVERT(INT,(DATEDIFF(MM,CAST(CONVERT(CHAR(4),'20'+SUBSTRING(@FTSRunPeriod,1,2))+'-'+ CONVERT(CHAR(2),SUBSTRING(@FTSRunPeriod,4,2))+'-01' AS DATE),CAST(SUBSTRING(CAST(@FTSForecastYearPeriod AS CHAR(6)),1,4)+'-'+ SUBSTRING(CAST(@FTSForecastYearPeriod AS CHAR(6)),5,2)+'-01' AS DATE)))) AS DemandPlanIntervalID
         ,ROW_NUMBER() OVER(
         PARTITION BY  CONVERT(INT,(DATEDIFF(MM,CAST(CONVERT(CHAR(4),'20'+SUBSTRING(@FTSRunPeriod,1,2))+'-'+ CONVERT(CHAR(2),SUBSTRING(@FTSRunPeriod,4,2))+'-01' AS DATE),CAST(SUBSTRING(CAST(@FTSForecastYearPeriod AS CHAR(6)),1,4)+'-'+ SUBSTRING(CAST(@FTSForecastYearPeriod AS CHAR(6)),5,2)+'-01' AS DATE))))
         ORDER BY CONVERT(INT,(DATEDIFF(MM,CAST(CONVERT(CHAR(4),'20'+SUBSTRING(@FTSRunPeriod,1,2))+'-'+ CONVERT(CHAR(2),SUBSTRING(@FTSRunPeriod,4,2))+'-01' AS DATE),CAST(SUBSTRING(CAST(@FTSForecastYearPeriod AS CHAR(6)),1,4)+'-'+ SUBSTRING(CAST(@FTSForecastYearPeriod AS CHAR(6)),5,2)+'-01' AS DATE)))) ASC
     ) AS RN
        WHERE @FTSRunPeriod NOT LIKE '%W%'
        )
        SELECT DemandPlanIntervalID
        FROM  CTE
        WHERE RN=1

    And i wont something

    WITH FTS1 AS (
    SELECT
    InventoryPlanSettingsID
    ,FTSRunID
    ,FTSRunPeriod
    ,FTSForecastYearPeriod
    FROM Staging.InventoryPlanSettingsForecastTimeSeries
         WHERE
         (__$operation is null) or (__$operation is not null and (__$operation = 1 or __$operation = 4))
         AND FTSRunPeriod NOT LIKE '%W%')
    ,
    FTS AS
         (
        SELECT      
         InventoryPlanSettingsID AS InventoryPlanSettingsID,     
         CONVERT(INT,(DATEDIFF(MM,CAST(CONVERT(CHAR(4),'20'+SUBSTRING(FTSRunPeriod,1,2))+'-'+ CONVERT(CHAR(2),SUBSTRING(FTSRunPeriod,4,2))+'-01' AS DATE),CAST(SUBSTRING(CAST(FTSForecastYearPeriod AS CHAR(6)),1,4)+'-'+ SUBSTRING(CAST(FTSForecastYearPeriod AS CHAR(6)),5,2)+'-01' AS DATE)))) AS DemandPlanIntervalID,
         ROW_NUMBER() OVER(
         PARTITION BY  FTSRunID, CONVERT(INT,(DATEDIFF(MM,CAST(CONVERT(CHAR(4),'20'+SUBSTRING(FTSRunPeriod,1,2))+'-'+ CONVERT(CHAR(2),SUBSTRING(FTSRunPeriod,4,2))+'-01' AS DATE),CAST(SUBSTRING(CAST(FTSForecastYearPeriod AS CHAR(6)),1,4)+'-'+ SUBSTRING(CAST(FTSForecastYearPeriod AS CHAR(6)),5,2)+'-01' AS DATE))))
         ORDER BY FTSRunID,CONVERT(INT,(DATEDIFF(MM,CAST(CONVERT(CHAR(4),'20'+SUBSTRING(FTSRunPeriod,1,2))+'-'+ CONVERT(CHAR(2),SUBSTRING(FTSRunPeriod,4,2))+'-01' AS DATE),CAST(SUBSTRING(CAST(FTSForecastYearPeriod AS CHAR(6)),1,4)+'-'+ SUBSTRING(CAST(FTSForecastYearPeriod AS CHAR(6)),5,2)+'-01' AS DATE)))) ASC
         ) AS RN
         FROM FTS1
         )
         SELECT
         InventoryPlanSettingsID,
         DemandPlanIntervalID
         FROM FTS
         WHERE RN=1


    Regards, Kishlay Anand

    Thursday, October 18, 2012 2:13 PM
  • Please tell the error. Also, I suggest:

    ;with cte as (SELECT InventoryPlanSettingsID AS InventoryPlanSettingsID, FTSRunID,

    CONVERT(CHAR(4),'20'+SUBSTRING(@FTSFTSRunPeriod,1,2))+ CONVERT(CHAR(2),

    SUBSTRING(@FTSFTSRunPeriod,4,2))+'01') as RunPeriodDate,

    SUBSTRING(CAST(FTSForecastYearPeriod AS CHAR(6)),1,4)+

    SUBSTRING(CAST(FTSForecastYearPeriod AS CHAR(6)),5,2)+'01' AS ForecastDate FROM Staging.InventoryPlanSettingsForecastTimeSeries WHERE       (__$operation is null) or (__$operation is not null and (__$operation = 1 or __$operation = 4))      AND FTSRunPeriod NOT LIKE '%W%'      ), fts as (select *, case when ISDATE(RunPeriodDate)=1 and ISDATE(ForecastDate)=1 THEN DATEDIFF(month,CAST(RunPeriodDate as DATE),

    CAST(ForecastDate AS DATE)) END AS DemandPlanIntervalID FROM CTE), fts1 AS (SELECT *, ROW_NUMBER() OVER (partition by FTSRunId, DemandPlanIntervalId) as Rn from fts) SELECT * from fts1 WHERE Rn = 1

    In other words, do it step by step and also add check for ISDATE.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    • Edited by Naomi N Thursday, October 18, 2012 3:45 PM
    Thursday, October 18, 2012 3:41 PM
  • Is the query working for you or not?

    (And you might be able to read that query. I will have to confess, I am not. That's why I asked you to clean it up.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, October 18, 2012 9:58 PM
  • Can you post sample data  and the expected result with a explanation of the logic, from there we can help.

    So far the query can run but who knows if it is what you want or where the error is happening if it doesn't run

    Thursday, October 18, 2012 11:05 PM