Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273

    Unanswered: Crosstab in SQL Server using Month()

    I am trying to take the Month out of a field which is a datetime object

    this is my code below and keep getting error messages which tells me that there is an ambiguous column name

    I dont understand how this is as I thought I wasnt specifing the START_DTTM as the column name.

    Code:
    SELECT [SERVICE],
    	SUM(CASE WHEN MONTH(START_DTTM) = 01 THEN 1 ELSE 0 END) AS January,
    	SUM(CASE WHEN MONTH(START_DTTM) = 02 THEN 1 ELSE 0 END) AS February,
    	SUM(CASE WHEN MONTH(START_DTTM) = 03 THEN 1 ELSE 0 END) AS March,
    	SUM(CASE WHEN MONTH(START_DTTM) = 04 THEN 1 ELSE 0 END) AS April,
    	SUM(CASE WHEN MONTH(START_DTTM) = 05 THEN 1 ELSE 0 END) AS May,
    	SUM(CASE WHEN MONTH(START_DTTM) = 06 THEN 1 ELSE 0 END) AS June,
    	SUM(CASE WHEN MONTH(START_DTTM) = 07 THEN 1 ELSE 0 END) AS July,
    	SUM(CASE WHEN MONTH(START_DTTM) = 08 THEN 1 ELSE 0 END) AS August,
    	SUM(CASE WHEN MONTH(START_DTTM) = 09 THEN 1 ELSE 0 END) AS September,
    	SUM(CASE WHEN MONTH(START_DTTM) = 10 THEN 1 ELSE 0 END) AS October,
    	SUM(CASE WHEN MONTH(START_DTTM) = 11 THEN 1 ELSE 0 END) AS November,
    	SUM(CASE WHEN MONTH(START_DTTM) = 12 THEN 1 ELSE 0 END) AS December 
    FROM dbo.REP_NON_PATIENT_EVENT_PERF_MAN 
    LEFT JOIN jez.PARA_SUEPROFCARERS2 
    ON dbo.REP_NON_PATIENT_EVENT_PERF_MAN.PROCA_REFNO = jez.PARA_SUEPROFCARERS2.PROCA_REFNO
    WHERE (dbo.REP_NON_PATIENT_EVENT_PERF_MAN.START_DTTM 
    BETWEEN '1/4/2008' AND '30/9/2008 23:59:59') 
    AND (dbo.REP_NON_PATIENT_EVENT_PERF_MAN.MAIN_CODE 
    IN ('ANT', 'CHILD', 'DENT', 'GRPSS', 'HED', 'HPROM', 'PATNT', 'WELL'))
    GROUP BY jez.PARA_SUEPROFCARERS2.[SERVICE]
    How do I get this to work?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    If you're joining tables, then each column name should use two part naming (i.e. object.column_name)

    START_DTTM needs to be qualified
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •