Post reply

Having Problem with Calculate TimeStamp within same column.

  • February 4, 2013 at 12:22 pm

    #1584085

    I know you are trying here but you have got to test these scripts if you want help. At this point your ddl is string across a number of posts and the queries you have continue to reference columns that don't exist.

    Now that you posted the Facility table I tried to run just the query that you use to populate your cursor (I removed the date check for now just to see what your query looks like).

    SELECT Distinct T.ApptID,Year,Month ,

    FirstofMonth,TimeStampId,

    SUM(CONVERT(decimal(18,0),DATEPART(MI,TimeStamp))) AS TotalDuration,

    COUNT(A.ApptCode) As NumOfAppts,

    f.FacilityID,

    DoctorID,

    GetDate()as CurrentDate

    FROM t_Facility f INNER JOIN

    t_Appointment A ON A.FacilityID = f.FacilityID INNER JOIN

    t_Appointment_TimeStamp T on T.ApptID = A.ApptID

    GROUP BY T.ApptID,f.FacilityID,DoctorID, YEAR, Month,FirstofMonth,TimeStampId

    Here is what I get.

    Msg 207, Level 16, State 1, Line 9

    Invalid column name 'FacilityID'.

    Msg 207, Level 16, State 1, Line 11

    Invalid column name 'DoctorID'.

    Msg 207, Level 16, State 1, Line 11

    Invalid column name 'YEAR'.

    Msg 207, Level 16, State 1, Line 11

    Invalid column name 'Month'.

    Msg 207, Level 16, State 1, Line 11

    Invalid column name 'FirstofMonth'.

    Msg 207, Level 16, State 1, Line 1

    Invalid column name 'Year'.

    Msg 207, Level 16, State 1, Line 1

    Invalid column name 'Month'.

    Msg 207, Level 16, State 1, Line 2

    Invalid column name 'FirstofMonth'.

    Msg 207, Level 16, State 1, Line 6

    Invalid column name 'DoctorID'.

    Please post all of you code in a single post along with sample data. Before you post it please load it into a test database and make sure it works.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing post 16 (of 15 total)

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