help in making query

  • I have a below table:

    --===== If the test table already exists, drop it

    IF OBJECT_ID('tempdb..#complaints') IS NOt NULL

    DROP TABLe #complaints

    --===== Create the test table with

    create table #complaints([Fiscal Year] float,

    [Fiscal Week] float,

    [Fiscal Month] float,

    [Complaint #] float)

    -===== All Inserts into the IDENTITY column

    SET IDENTITY_INSERT #complaints ON

    INSERT INTO #complaints

    ([Fiscal Year], [Fiscal Week], [Fiscal Month], [Complaint #])

    SELECT 2015, 14, 5, 12 UNION ALL

    SELECT 2015, 15, 5, 11 UNION ALL

    SELECT 2014, 14, 5, 18 UNION ALL

    SELECT 2014, 15, 5, 12 UNION ALL

    SELECT 2014, 16, 5, 10 UNION ALL

    SELECT 2013, 16, 5, 29 UNION ALL

    SELECT 2014, 17, 5, 9 UNION ALL

    SELECT 2013, 17, 5, 20 UNION ALL

    SELECT 2013, 18, 6, 22 UNION ALL

    SELECT 2014, 18, 6, 25 UNION ALL)

    --===== Set the identity insert back to normal

    SET IDENTITY_INSERT #mytable OFF

    ******************************************************

    Query to be made: Below table needs to be created:

    create table #results( [Fiscal Week] float,

    [Fiscal Month] float,

    [C1] float,

    [C2] float)

    Where C1: [Complaint #] from table #complaints for current year (2015) if it exist for that year and a particular month of current year else it will be of previous year (i.e. 2014)

    C2: If data for current year and a particular month exist it will be #complaints for previous year (2014) else it will be of previous to previous year (2013).

    Example:

    Result table:

    weekC1c2

    141218

    151112

    161029

    172920

    182522

    Thanks for help.

  • Rough and dirty, and won't work unless there are at least two years for each week.

    Convert to an outer join if you need to take that into consideration.

    SELECT a.[Fiscal Week], a.[Complaint #], b.[Complaint #]

    FROM [#complaints] a, #complaints b

    WHERE a.[Fiscal Week] = b.[Fiscal Week]

    AND a.[Fiscal Year] = (SELECT MAX(ax.[Fiscal Year]) FROM #complaints ax WHERE ax.[Fiscal Week] = a.[Fiscal Week])

    AND b.[Fiscal Year] = a.[Fiscal Year] -1

    Regards

    Wayne

  • This gives the expected results with the data supplied, but you haven't said what the expected behavior should be when 2012 and 2014 have data, but 2013 and 2015 don't. Do you want the most recent previous data or do you want to display a NULL. This will show the most recent data available, based on the fact that when 2015 is missing, you're showing 2014 and 2013 instead of showing a NULL for 2015 and the appropriate value for 2014.

    ;

    WITH complaints_ordered AS

    (

    SELECT

    Fiscal_Week,

    Complaint_Num AS c1,

    LAG(Complaint_Num) OVER(PARTITION BY Fiscal_Week ORDER BY Fiscal_Year) AS c2,

    ROW_NUMBER() OVER(PARTITION BY Fiscal_Week ORDER BY Fiscal_Year DESC) AS rn

    FROM #complaints

    )

    SELECT Fiscal_Week, C1, C2

    FROM complaints_ordered

    WHERE rn = 1

    ORDER BY Fiscal_Week

    Also, IMNSHO you should almost never use spaces in column or table names. I only ever use spaces in column names when I am creating an ad hoc query to send to someone who is not a techie.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • I would like to display NULL.

  • drew.allen (6/11/2015)


    Also, IMNSHO you should almost never use spaces in column or table names.

    +1000.

    Is this homework? I'm asking because you have things in your OP that don't make sense. For example, you're turning identity insert on and off, but you don't have an identity column. You also have your year, week and month as the float data type, but they should be integers.

  • This is also one of several threads asking the same question. Just haven't taken the time to locate them all.

  • sqlinterset (6/11/2015)


    I would like to display NULL.

    Then you will need to add a CASE statement like so

    ;

    WITH complaints_ordered AS

    (

    SELECT

    Fiscal_Week,

    Complaint_Num AS c1,

    CASE WHEN LAG(Fiscal_Year) OVER(PARTITION BY Fiscal_Week ORDER BY Fiscal_Year) = Fiscal_Year - 1

    THEN LAG(Complaint_Num) OVER(PARTITION BY Fiscal_Week ORDER BY Fiscal_Year) END AS c2,

    ROW_NUMBER() OVER(PARTITION BY Fiscal_Week ORDER BY Fiscal_Year DESC) AS rn

    FROM #complaints

    )

    SELECT Fiscal_Week, C1, C2

    FROM complaints_ordered

    WHERE rn = 1

    ORDER BY Fiscal_Week

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • When i am using this query its working fine in SSMS, but when i am creating excel pivot table to fetch SQL server data through this query then it gives me below error:

    excel warning changed database context to

    any help?

  • It's not an error. It's a message informing you that the database you're using has been changed.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I have a vague memory of something like this, but I don't remember the exact solution. Therefore, I will use the force. 😛

    1. Check your connection string to make sure you're connecting to the right database in your "Initial Catalog" parameter.

    2. If that doesn't do the trick, try using three-part naming conventions in your table names.

  • This is the sequence of errors when running the same query through excel pivot option

    1)Changed database context to XXXXX

    2) Problems obtaining data

    3) A pivot table using connection 'YYYY' has failed to refresh.

  • sqlinterset (6/18/2015)


    This is the sequence of errors when running the same query through excel pivot option

    1)Changed database context to XXXXX

    2) Problems obtaining data

    3) A pivot table using connection 'YYYY' has failed to refresh.

    Can you post the exact set of SQL statements you are using in the Excel pivot table connection?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Tables which already exist in database i am using 3 name table convention but there are two tables which are created in database itself, where i can't use 3 name convention. Like, complaints_ordered below is local table and can't be used with three name.

    ;

    WITH complaints_ordered AS

    (

    SELECT

    Fiscal_Week,

    Complaint_Num AS c1,

    LAG(Complaint_Num) OVER(PARTITION BY Fiscal_Week ORDER BY Fiscal_Year) AS c2,

    ROW_NUMBER() OVER(PARTITION BY Fiscal_Week ORDER BY Fiscal_Year DESC) AS rn

    FROM #complaints

    )

    SELECT Fiscal_Week, C1, C2

    FROM complaints_ordered

    WHERE rn = 1

    ORDER BY Fiscal_Week

  • sqlinterset (6/18/2015)


    Tables which already exist in database i am using 3 name table convention but there are two tables which are created in database itself, where i can't use 3 name convention. Like, complaints_ordered below is local table and can't be used with three name.

    ;

    WITH complaints_ordered AS

    (

    SELECT

    Fiscal_Week,

    Complaint_Num AS c1,

    LAG(Complaint_Num) OVER(PARTITION BY Fiscal_Week ORDER BY Fiscal_Year) AS c2,

    ROW_NUMBER() OVER(PARTITION BY Fiscal_Week ORDER BY Fiscal_Year DESC) AS rn

    FROM #complaints

    )

    SELECT Fiscal_Week, C1, C2

    FROM complaints_ordered

    WHERE rn = 1

    ORDER BY Fiscal_Week

    This doesn't look like an entire set of statements from the Command Text box of an Excel connection.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Thank You all. problem got solved. I removed the database name that i was using like "Use DDDD". Once i removed it, data was fetched in excel Thanks.

Viewing 15 posts - 1 through 15 (of 16 total)

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