Post reply

Query Solution one Column convert into two

  • March 1, 2011 at 10:09 pm

    #234505

    Hi Every one hope all of you are fine..

    i have a salary table that have data like this

    ID EmpNo mYear Net Salary

    29 3 January 2010 29217

    1210 3 February 2010 29217

    2052 3 March 2010 29217

    3102 3 April 2010 29447

    4153 3 May 2010 29447

    6376 3 July 2010 28803

    7231 3 August 2010 28281

    7893 3 September 2010 29303

    5207 3 June 2010 29197

    8874 3 October 2010 29303

    10076 3 November 2010 31276

    11318 3 December 2010 31216

    12503 3 January 2011 31266

    13880 3 February 2011 31266

    13414 1613 January 2011 19445

    12043 1613 December 2010 19445

    10563 1613 November 2010 19445

    9364 1613 October 2010 18150

    6163 1613 June 2010 10075

    8433 1613 September 2010 18150

    5154 1613 May 2010 18806

    But i want to create a view like this

    ID EmpNo ThisMonth Previous Month mYear ThisSalary PreSalary NetSalary

    29 3 January 2010 December 2009 January 2010 29217 0 29217

    1210 3 February 2010 January 2010 February 2010 29217 29217 29217

    2052 3 March 2010 February 2010 March 2010 29217 29217 29217

    3102 3 April 2010 March 2010 April 2010 29447 29217 29447

    4153 3 May 2010 April 2010 May 2010 29447 29447 29447

    5207 3 June 2010 May 2010 June 2010 29197 29447 29197

    6376 3 July 2010 June 2010 July 2010 28803 29197 28803

    7231 3 August 2010 July 2010 August 2010 28281 28803 28281

    7893 3 September 2010 August 2010 September 2010 29303 28281 29303

    8874 3 October 2010 September 2010 October 2010 29303 29303 29303

    10076 3 November 2010 October 2010 November 2010 31276 29303 31276

    11318 3 December 2010 November 2010 December 2010 31216 31276 31216

    12503 3 January 2011 December 2010 January 2011 31266 31216 31266

    13880 3 February 2011 January 2011 February 2011 31266 31266 31266

    13881 3 March 2011 February 2011 March 2011 0 31266 0

    5154 1613 May 2010 April 2010 May 2010 18806 0 18806

    6163 1613 June 2010 May 2010 June 2010 10075 18806 10075

    8433 1613 September 2010 August 2010 September 2010 18150 10075 18150

    9364 1613 October 2010 September 2010 October 2010 18150 18150 18150

    10563 1613 November 2010 October 2010 November 2010 19445 18150 19445

    12043 1613 December 2010 November 2010 December 2010 19445 19445 19445

    13414 1613 January 2011 December 2010 January 2011 19445 19445 19445

    13415 1613 February 2011 January 2011 February 2011 0 19445 0

    Please Help Me

  • March 2, 2011 at 7:40 am

    #1293500

    What is PreSalary? Where do you want to get this from? And NetSalary?

  • March 2, 2011 at 9:41 am

    #1293594

    How's this?

    /*

    See how we take the test data and put it into a table?

    Doing this really helps out all of the volunteers

    (and some won't help if you didn't do this for them!)

    */

    DECLARE @test-2 TABLE (ID INT,

    EmpNo INT,

    MonthName VARCHAR(15),

    [Year] SMALLINT,

    [Net Salary] SMALLINT);

    INSERT INTO @test-2

    SELECT 29, 3, 'January', 2010, 29217 UNION ALL

    SELECT 1210, 3, 'February', 2010, 29217 UNION ALL

    SELECT 2052, 3, 'March', 2010, 29217 UNION ALL

    SELECT 3102, 3, 'April', 2010, 29447 UNION ALL

    SELECT 4153, 3, 'May', 2010, 29447 UNION ALL

    SELECT 6376, 3, 'July', 2010, 28803 UNION ALL

    SELECT 7231, 3, 'August', 2010, 28281 UNION ALL

    SELECT 7893, 3, 'September', 2010, 29303 UNION ALL

    SELECT 5207, 3, 'June', 2010, 29197 UNION ALL

    SELECT 8874, 3, 'October', 2010, 29303 UNION ALL

    SELECT 10076, 3, 'November', 2010, 31276 UNION ALL

    SELECT 11318, 3, 'December', 2010, 31216 UNION ALL

    SELECT 12503, 3, 'January', 2011, 31266 UNION ALL

    SELECT 13880, 3, 'February', 2011, 31266 UNION ALL

    SELECT 13414, 1613, 'January', 2011, 19445 UNION ALL

    SELECT 12043, 1613, 'December', 2010, 19445 UNION ALL

    SELECT 10563, 1613, 'November', 2010, 19445 UNION ALL

    SELECT 9364, 1613, 'October', 2010, 18150 UNION ALL

    SELECT 6163, 1613, 'June', 2010, 10075 UNION ALL

    SELECT 8433, 1613, 'September', 2010, 18150 UNION ALL

    SELECT 5154, 1613, 'May', 2010, 18806;

    WITH CTE AS

    (

    -- get the month/year into a real date first

    SELECT ID, EmpNo, MonthYear = CONVERT(DATETIME, '01 ' + MonthName + ' ' + CONVERT(VARCHAR(4),[Year])), [Net Salary]

    FROM @test-2

    ),

    CTE2 AS

    (

    -- get the previous month

    SELECT *, PreviousMonth = DATEADD(MONTH, -1, MonthYear)

    FROM CTE

    )

    SELECT t1.ID,

    t1.EmpNo,

    ThisMonth = DATENAME(MONTH, t1.MonthYear) + ' ' + DATENAME(YEAR, t1.MonthYear),

    PreviousMonth = DATENAME(MONTH, t1.PreviousMonth) + ' ' + DATENAME(YEAR, t1.PreviousMonth),

    mYear = DATENAME(MONTH, t1.MonthYear) + ' ' + DATENAME(YEAR, t1.MonthYear),

    ThisSalary = t1.[Net Salary],

    PreSalary = t2.[Net Salary],

    t1.[Net Salary]

    FROM CTE2 t1

    LEFT JOIN CTE2 t2

    ON t1.EmpNo = t2.EmpNo

    AND t1.MonthYear = DATEADD(MONTH, 1, t2.MonthYear)

    ORDER BY t1.EmpNo, t1.MonthYear

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • March 15, 2011 at 11:06 pm

    #1299212

    Thank you jason.. i have found solution by your code thanks.

Viewing 4 posts - 1 through 3 (of 3 total)

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