Help with Query(Matrix Format)

  • Hi ,

    I have a table Temp and the data as follows.

    Create table Temp

    (

    PAN nvarchar(100),

    Month int,

    Year int,

    inputDate datetime,

    Program nvarchar(10)

    )

    Insert into Temp Values('123456',1,2011,'2011-01-01','P1')

    Insert into Temp Values('456798',1,2011,'2011-01-02','P1')

    Insert into Temp Values('789456',1,2011,'2011-01-03','P1')

    Insert into Temp Values('456123',1,2011,'2011-01-04','P1')

    Insert into Temp Values('147258',1,2011,'2011-01-05','P1')

    Insert into Temp Values('258147',1,2011,'2011-01-06','P1')

    Insert into Temp Values('369258',1,2011,'2011-01-07','P1')

    Insert into Temp Values('123456',2,2011,'2011-02-01','P1')

    Insert into Temp Values('456798',2,2011,'2011-02-02','P1')

    Insert into Temp Values('789456',2,2011,'2011-02-03','P1')

    Insert into Temp Values('159265',2,2011,'2011-02-04','P1')

    Insert into Temp Values('487159',2,2011,'2011-02-05','P1')

    Insert into Temp Values('123456',3,2011,'2011-03-01','P1')

    Insert into Temp Values('456798',3,2011,'2011-03-02','P1')

    Insert into Temp Values('159265',3,2011,'2011-03-03','P1')

    Insert into Temp Values('487159',3,2011,'2011-03-04','P1')

    Insert into Temp Values('745874',3,2011,'2011-03-05','P1')

    Insert into Temp Values('123456',4,2011,'2011-04-06','P1')

    Insert into Temp Values('456798',4,2011,'2011-04-07','P1')

    Insert into Temp Values('487159',4,2011,'2011-04-08','P1')

    Insert into Temp Values('745874',4,2011,'2011-04-09','P1')

    Insert into Temp Values('841526',4,2011,'2011-04-10','P1')

    Insert into Temp Values('365847',4,2011,'2011-04-11','P1')

    I would like output in matrix format(Month wise in rows and columns of matrix). Please find attached the excel.

    It should contain,

    The repeated PAN's in each month

    Newly added pans of each month ( That does not exists in previous months)

    In the above scenario,

    In Jan-2011 ---- 7 pans exists out of which 3 pans repeated in Feb-2011 and 2 Pans repeated in March and 2 pans repeated in Apr 2011.

    In Feb 2011 --- 2 newly added pans(That does not exists in Jan) out of which 2 Pans repeated in March and 1 pan repeated in Apr 2011.

    In Mar 2011 --- 1 newly added pan(That does not exists in Jan and Feb) out of which 1 pan repeated in Apr 2011.

    In Apr 2011 -- 1 Newly added Pan(That does not exists in Jan, Feb and Mar).

    Kindly let me know the procedure to achieve this.

  • How about

    ;with distinctMonths

    as

    (

    select distinct dateadd(month,(t.[month]-1),

    dateadd(year,(t.[year]-1900),0)) as MonthDate,

    t.[MONTH],

    t.[year]

    from temp as t

    ),

    earliestMonth

    as

    (

    select t.PAN,min(dm.monthdate) as EarliestMonth

    from distinctMonths as dm

    INNER JOIN temp as t

    on dm.Month = t.Month and

    t.Year = dm.Year

    group by t.PAN

    ),

    PANCounts

    as

    (

    selectem.EarliestMonth,

    dm.monthdate as RepeatedInMonth,

    COUNT(distinct t.PAN) as NumberOfPANs

    from temp as t

    inner join earliestMonth as em

    on t.PAN = em.PAN

    inner join distinctMonths as dm

    on t.Month = dm.Month and

    t.Year = dm.Year

    where exists (select PAN

    from earliestMonth as em

    where em.EarliestMonth <= dm.MonthDate and

    em.PAN = t.PAN)

    group by em.EarliestMonth,dm.MonthDate

    )

    selectpvt.CreatedInMonth,

    [2011-01-01],

    [2011-02-01],

    [2011-03-01],

    [2011-04-01]

    from (

    selectcast(dm.MonthDate as date) as CreatedInMonth,

    pc.RepeatedInMonth,

    pc.NumberOfPANs

    from distinctMonths as dm

    LEFT JOIN PANCounts as pc

    on dm.MonthDate = pc.earliestMonth

    ) as src

    PIVOT (SUM(NumberOfPANs) FOR

    RepeatedInMonth in ([2011-01-01],

    [2011-02-01],

    [2011-03-01],

    [2011-04-01])) as pvt

  • Just to Note: not everyone will have ability (or wish) to download attached files from i-net, especially at their work places...

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • adlan (5/29/2012)


    How about

    ;with distinctMonths

    as

    (

    select distinct dateadd(month,(t.[month]-1),

    dateadd(year,(t.[year]-1900),0)) as MonthDate,

    t.[MONTH],

    t.[year]

    from temp as t

    ),

    earliestMonth

    as

    (

    select t.PAN,min(dm.monthdate) as EarliestMonth

    from distinctMonths as dm

    INNER JOIN temp as t

    on dm.Month = t.Month and

    t.Year = dm.Year

    group by t.PAN

    ),

    PANCounts

    as

    (

    selectem.EarliestMonth,

    dm.monthdate as RepeatedInMonth,

    COUNT(distinct t.PAN) as NumberOfPANs

    from temp as t

    inner join earliestMonth as em

    on t.PAN = em.PAN

    inner join distinctMonths as dm

    on t.Month = dm.Month and

    t.Year = dm.Year

    where exists (select PAN

    from earliestMonth as em

    where em.EarliestMonth <= dm.MonthDate and

    em.PAN = t.PAN)

    group by em.EarliestMonth,dm.MonthDate

    )

    selectpvt.CreatedInMonth,

    [2011-01-01],

    [2011-02-01],

    [2011-03-01],

    [2011-04-01]

    from (

    selectcast(dm.MonthDate as date) as CreatedInMonth,

    pc.RepeatedInMonth,

    pc.NumberOfPANs

    from distinctMonths as dm

    LEFT JOIN PANCounts as pc

    on dm.MonthDate = pc.earliestMonth

    ) as src

    PIVOT (SUM(NumberOfPANs) FOR

    RepeatedInMonth in ([2011-01-01],

    [2011-02-01],

    [2011-03-01],

    [2011-04-01])) as pvt

    Thanks a lot. I have one more question related to this.

    If the date ranges are between 2009-01-01 to 2011-12-31 so it has to be hardcoded in pivot query ?? the dates are dynamic.

    How to achieve this.??

  • Depending on your constraints (coding standards etc) it may be possible to use Dynamic SQL to do this.

    Google is your friend...

    http://beyondrelational.com/modules/2/blogs/88/Posts/14196/changing-rows-to-columns-using-pivot-dynamic-columns-for-pivoting-in-sql-server.aspx

    This should not be done lightly - I think a good explanation of the pros and cons of dynamic SQL is at

    http://www.sommarskog.se/dynamic_sql.html

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

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