locked
How to put grouped results all on one line. RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    I amt trying to get Principal, Interest, Principal YTD, Interest YTD from a transaction table to get them all on one line, but I can't seem to get it. I have a script here that can run without modification to see what I have here. The very last query is incorrect, as it puts each value on a separate line, but it demonstrates what I am getting so far. What I want is one account number with 4 columns, as above mentioned. Keep in mind, in this illustration, YTD is last year and forward: 
    CREATE TABLE #a
(
 PMT_ID INT IDENTITY(1,1),ACCT_NO INT, PMT_TYPE CHAR(1), PMT_AMT MONEY, PMT_DATE DATE)

INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 1, 'P', 450, '2012-09-01')
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 1, 'I', 100, '2012-09-01')
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 1, 'P', 450, '2012-10-01')
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 1, 'I', 100, '2012-11-01')
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 1, 'P', 450, '2012-12-01')
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 1, 'I', 100, '2012-12-01')

INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 2, 'P', 351, '2012-09-01')
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 2, 'I', 102, '2012-09-01')
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 2, 'P', 352, '2012-10-01')
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 2, 'I', 102, '2012-11-01')
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 2, 'P', 353, '2012-12-01')
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 2, 'I', 102, '2012-12-01')

INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 3, 'P', 850, '2013-09-01')
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 3, 'I', 200, '2013-09-01')
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 3, 'P', 880, '2013-10-01')
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 3, 'I', 201, '2013-11-01')
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 3, 'P', 855, '2013-12-01')
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 3, 'I', 203, '2013-12-01')

INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 1, 'P', 451, '2013-09-01')
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 1, 'I', 99, '2013-09-01')
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 1, 'P', 451, '2013-10-01')
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 1, 'I', 99, '2013-11-01')
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 1, 'P', 451, '2013-12-01')
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 1, 'I', 99, '2013-12-01')

INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 2, 'P', 352, '2013-09-01')
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 2, 'I', 101, '2013-09-01')
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 2, 'P', 353, '2013-10-01')
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 2, 'I', 103, '2013-11-01')
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 2, 'P', 354, '2013-12-01')
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 2, 'I', 103, '2013-12-01')

INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 3, 'P', 851, '2013-09-01')
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 3, 'I', 199, '2013-09-01')
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 3, 'P', 881, '2013-10-01')
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 3, 'I', 201, '2013-11-01')
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 3, 'P', 854, '2013-12-01')
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 3, 'I', 205, '2013-12-01')

SELECT PMT_ID, ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE 
FROM #a

SELECT ACCT_NO, SUM(PMT_AMT) TOT_PRINCIPAL_PAID
FROM #a
WHERE PMT_TYPE = 'P'
GROUP BY ACCT_NO

SELECT ACCT_NO, SUM(PMT_AMT) TOTAL_INTEREST_PAID
FROM #a
WHERE PMT_TYPE = 'I'
GROUP BY ACCT_NO

SELECT ACCT_NO, SUM(PMT_AMT) TOTAL_PRINCIPAL_PAID_YTD
FROM #a
WHERE PMT_TYPE = 'P'
AND DATEPART(year,[PMT_DATE])=DATEPART(year,DATEADD(year,-1,GETDATE()))
GROUP BY ACCT_NO

SELECT ACCT_NO, SUM(PMT_AMT) TOTAL_INTEREST_PAID_YTD
FROM #a
WHERE PMT_TYPE = 'I' 
AND DATEPART(year,[PMT_DATE])=DATEPART(year,DATEADD(year,-1,GETDATE()))
GROUP BY ACCT_NO

SELECT ACCT_NO
,SUM(PMT_AMT) AS TOTAL_PRINCIPAL_PAID
,NULL AS TOTAL_INTEREST_PAID
,NULL AS TOTAL_PRINCIPAL_PAID_YTD
,NULL AS TOTAL_INTEREST_PAID_YTD
FROM #a
WHERE PMT_TYPE = 'P'
GROUP BY ACCT_NO
UNION
SELECT ACCT_NO
,NULL AS TOTAL_PRINCIPAL_PAID
,SUM(PMT_AMT) AS TOTAL_INTEREST_PAID
,NULL AS TOTAL_PRINCIPAL_PAID_YTD
,NULL AS TOTAL_INTEREST_PAID_YTD
FROM #a
WHERE PMT_TYPE = 'I'
GROUP BY ACCT_NO
UNION
SELECT ACCT_NO
,NULL         AS TOTAL_PRINCIPAL_PAID
,NULL         AS TOTAL_INTEREST_PAID
,SUM(PMT_AMT) AS TOTAL_PRINCIPAL_PAID_YTD
,NULL         AS TOTAL_INTEREST_PAID_YTD
FROM #a
WHERE PMT_TYPE = 'P'
AND DATEPART(year,[PMT_DATE])=DATEPART(year,DATEADD(year,-1,GETDATE()))
GROUP BY ACCT_NO
UNION
SELECT ACCT_NO
,NULL         AS TOTAL_PRINCIPAL_PAID
,NULL         AS TOTAL_INTEREST_PAID
,NULL         AS TOTAL_PRINCIPAL_PAID_YTD
,SUM(PMT_AMT) AS TOTAL_INTEREST_PAID_YTD
FROM #a
WHERE PMT_TYPE = 'I'
AND DATEPART(year,[PMT_DATE])=DATEPART(year,DATEADD(year,-1,GETDATE()))
GROUP BY ACCT_NO

-- Query to put this all these 4 columns on the same line, ending up with 3 lines.
-- ??

--

DROP TABLE #a


    • Edited by duanewilson Wednesday, January 15, 2014 7:54 PM explanation of YTD
    Wednesday, January 15, 2014 7:52 PM

Answers

  • Question
    Sign in to vote
    2
    Sign in to vote

    Try:

    SELECT ACCT_NO, 
SUM(CASE WHEN PMT_TYPE = 'P' THEN PMT_AMT END) TOT_PRINCIPAL_PAID,
SUM(CASE WHEN PMT_TYPE = 'I' THEN PMT_AMT END) TOTAL_INTEREST_PAID,
SUM(CASE WHEN PMT_TYPE = 'P'  
AND PMT_DATE >=dateadd(YEAR, DATEDIFF(year, '19010101', CURRENT_TIMESTAMP),'19000101') 
AND PMT_DATE < dateadd(YEAR, DATEDIFF(year, '19000101', CURRENT_TIMESTAMP),'19000101') 

THEN PMT_AMT END) TOTAL_PRINCIPAL_PAID_YTD,
SUM(CASE WHEN PMT_TYPE = 'I'  
AND PMT_DATE >=dateadd(YEAR, DATEDIFF(year, '19010101', CURRENT_TIMESTAMP),'19000101') 
AND PMT_DATE < dateadd(YEAR, DATEDIFF(year, '19000101', CURRENT_TIMESTAMP),'19000101') 

THEN PMT_AMT END) TOTAL_INTEREST_PAID_YTD

FROM #a

GROUP BY ACCT_NO

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


    My blog

    My TechNet articles

    Wednesday, January 15, 2014 8:04 PM

All replies

  • Question
    Sign in to vote
    2
    Sign in to vote

    Try the below query

    CREATE TABLE #a
    (
     PMT_ID INT IDENTITY(1,1),ACCT_NO INT, PMT_TYPE CHAR(1), PMT_AMT MONEY, PMT_DATE DATE)

    INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 1, 'P', 450, '2012-09-01')
    INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 1, 'I', 100, '2012-09-01')
    INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 1, 'P', 450, '2012-10-01')
    INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 1, 'I', 100, '2012-11-01')
    INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 1, 'P', 450, '2012-12-01')
    INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 1, 'I', 100, '2012-12-01')

    INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 2, 'P', 351, '2012-09-01')
    INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 2, 'I', 102, '2012-09-01')
    INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 2, 'P', 352, '2012-10-01')
    INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 2, 'I', 102, '2012-11-01')
    INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 2, 'P', 353, '2012-12-01')
    INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 2, 'I', 102, '2012-12-01')

    INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 3, 'P', 850, '2013-09-01')
    INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 3, 'I', 200, '2013-09-01')
    INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 3, 'P', 880, '2013-10-01')
    INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 3, 'I', 201, '2013-11-01')
    INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 3, 'P', 855, '2013-12-01')
    INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 3, 'I', 203, '2013-12-01')

    INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 1, 'P', 451, '2013-09-01')
    INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 1, 'I', 99, '2013-09-01')
    INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 1, 'P', 451, '2013-10-01')
    INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 1, 'I', 99, '2013-11-01')
    INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 1, 'P', 451, '2013-12-01')
    INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 1, 'I', 99, '2013-12-01')

    INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 2, 'P', 352, '2013-09-01')
    INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 2, 'I', 101, '2013-09-01')
    INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 2, 'P', 353, '2013-10-01')
    INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 2, 'I', 103, '2013-11-01')
    INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 2, 'P', 354, '2013-12-01')
    INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 2, 'I', 103, '2013-12-01')

    INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 3, 'P', 851, '2013-09-01')
    INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 3, 'I', 199, '2013-09-01')
    INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 3, 'P', 881, '2013-10-01')
    INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 3, 'I', 201, '2013-11-01')
    INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 3, 'P', 854, '2013-12-01')
    INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 3, 'I', 205, '2013-12-01')

    SELECT PMT_ID, ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE 
    FROM #a

    SELECT ACCT_NO, SUM(PMT_AMT) TOT_PRINCIPAL_PAID
    FROM #a
    WHERE PMT_TYPE = 'P'
    GROUP BY ACCT_NO

    SELECT ACCT_NO, SUM(PMT_AMT) TOTAL_INTEREST_PAID
    FROM #a
    WHERE PMT_TYPE = 'I'
    GROUP BY ACCT_NO

    SELECT ACCT_NO, SUM(PMT_AMT) TOTAL_PRINCIPAL_PAID_YTD
    FROM #a
    WHERE PMT_TYPE = 'P'
    AND DATEPART(year,[PMT_DATE])=DATEPART(year,DATEADD(year,-1,GETDATE()))
    GROUP BY ACCT_NO

    SELECT ACCT_NO, SUM(PMT_AMT) TOTAL_INTEREST_PAID_YTD
    FROM #a
    WHERE PMT_TYPE = 'I' 
    AND DATEPART(year,[PMT_DATE])=DATEPART(year,DATEADD(year,-1,GETDATE()))
    GROUP BY ACCT_NO

    select 
    T123.ANO,
    SUM(TOTAL_PRINCIPAL_PAID) TOTAL_PRINCIPAL_PAID,
    SUM(TOTAL_INTEREST_PAID) TOTAL_INTEREST_PAID ,
    SUM(TOTAL_PRINCIPAL_PAID_YTD) TOTAL_PRINCIPAL_PAID_YTD,
    SUM(TOTAL_INTEREST_PAID_YTD) TOTAL_INTEREST_PAID_YTD
    from
    (
    SELECT ACCT_NO ANO
    ,SUM(PMT_AMT) AS TOTAL_PRINCIPAL_PAID
    ,NULL AS TOTAL_INTEREST_PAID
    ,NULL AS TOTAL_PRINCIPAL_PAID_YTD
    ,NULL AS TOTAL_INTEREST_PAID_YTD
    FROM #a
    WHERE PMT_TYPE = 'P'
    GROUP BY ACCT_NO
    UNION
    SELECT ACCT_NO
    ,NULL AS TOTAL_PRINCIPAL_PAID
    ,SUM(PMT_AMT) AS TOTAL_INTEREST_PAID
    ,NULL AS TOTAL_PRINCIPAL_PAID_YTD
    ,NULL AS TOTAL_INTEREST_PAID_YTD
    FROM #a
    WHERE PMT_TYPE = 'I'
    GROUP BY ACCT_NO
    UNION
    SELECT ACCT_NO
    ,NULL         AS TOTAL_PRINCIPAL_PAID
    ,NULL         AS TOTAL_INTEREST_PAID
    ,SUM(PMT_AMT) AS TOTAL_PRINCIPAL_PAID_YTD
    ,NULL         AS TOTAL_INTEREST_PAID_YTD
    FROM #a
    WHERE PMT_TYPE = 'P'
    AND DATEPART(year,[PMT_DATE])=DATEPART(year,DATEADD(year,-1,GETDATE()))
    GROUP BY ACCT_NO
    UNION
    SELECT ACCT_NO
    ,NULL         AS TOTAL_PRINCIPAL_PAID
    ,NULL         AS TOTAL_INTEREST_PAID
    ,NULL         AS TOTAL_PRINCIPAL_PAID_YTD
    ,SUM(PMT_AMT) AS TOTAL_INTEREST_PAID_YTD
    FROM #a
    WHERE PMT_TYPE = 'I'
    AND DATEPART(year,[PMT_DATE])=DATEPART(year,DATEADD(year,-1,GETDATE()))
    GROUP BY ACCT_NO)T123
    Group by T123.ANO 


    DROP TABLE #a

    Wednesday, January 15, 2014 8:01 PM
  • Question
    Sign in to vote
    2
    Sign in to vote

    Try:

    SELECT ACCT_NO, 
SUM(CASE WHEN PMT_TYPE = 'P' THEN PMT_AMT END) TOT_PRINCIPAL_PAID,
SUM(CASE WHEN PMT_TYPE = 'I' THEN PMT_AMT END) TOTAL_INTEREST_PAID,
SUM(CASE WHEN PMT_TYPE = 'P'  
AND PMT_DATE >=dateadd(YEAR, DATEDIFF(year, '19010101', CURRENT_TIMESTAMP),'19000101') 
AND PMT_DATE < dateadd(YEAR, DATEDIFF(year, '19000101', CURRENT_TIMESTAMP),'19000101') 

THEN PMT_AMT END) TOTAL_PRINCIPAL_PAID_YTD,
SUM(CASE WHEN PMT_TYPE = 'I'  
AND PMT_DATE >=dateadd(YEAR, DATEDIFF(year, '19010101', CURRENT_TIMESTAMP),'19000101') 
AND PMT_DATE < dateadd(YEAR, DATEDIFF(year, '19000101', CURRENT_TIMESTAMP),'19000101') 

THEN PMT_AMT END) TOTAL_INTEREST_PAID_YTD

FROM #a

GROUP BY ACCT_NO

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


    My blog

    My TechNet articles

    Wednesday, January 15, 2014 8:04 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thank you, Ed, for the solution, and Prashanth for a good work-around. Prashanth helped me understand the direction I was going to completion, and Ed provided a good, concise rework of it. I had thought it could be shortened, but wasn't sure how.
    Wednesday, January 15, 2014 9:22 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thank you, Ed, for the solution, and Prashanth for a good work-around. Prashanth helped me understand the direction I was going to completion, and Ed provided a good, concise rework of it. I had thought it could be shortened, but wasn't sure how.

    You can thank me for proposing Naomi's fantastic solution. =^)

    I'm glad she can help you so quickly!

    Ed Price, Power BI & SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Wednesday, January 15, 2014 9:34 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Oh, it is Naomi! Thank you for the solution. And thank you, Ed, for proposing it.
    Thursday, January 16, 2014 11:57 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    I've added a new TechNet article for this problem

    http://social.technet.microsoft.com/wiki/contents/articles/22502.t-sql-how-to-put-grouped-results-on-the-same-line.aspx

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


    My blog

    My TechNet articles

    Sunday, January 26, 2014 7:06 PM