Professional Web Applications Themes

Query trouble - Microsoft SQL / MS SQL Server

Good news is I got the insert script installed :) bad news is that the query you gave only returns 1 record instead of all the project records. Here are the insert statements: INSERT INTO [project] ([PJ_ID],[PJ_Name],[PJ_Active],[PJ_Color],[PJ_Hourrate],[PJ_CAID],[PJ_USID], [PJ_Wide])VALUES(1,'Project1',1,13422920,'60',1,3,1) INSERT INTO [project] ([PJ_ID],[PJ_Name],[PJ_Active],[PJ_Color],[PJ_Hourrate],[PJ_CAID],[PJ_USID], [PJ_Wide])VALUES(2,'Project2',1,5275647,'45',1,1,1) INSERT INTO [timesheet] ([TS_ID],[TS_Begin],[TS_End],[TS_Project],[TS_Hourrate],[TS_Desc],[TS_USID]) VALUES(2,'Aug 15 2003 10:00:00:000AM','Aug 15 2003 11:15:00:000AM',1,60.00,'Walking the dog',1) INSERT INTO [timesheet] ([TS_ID],[TS_Begin],[TS_End],[TS_Project],[TS_Hourrate],[TS_Desc],[TS_USID]) VALUES(3,'Aug 13 2003 4:00:00:000PM','Aug 13 2003 5:45:00:000PM',2,45.00,'Testing an app',1) INSERT INTO [timesheet] ([TS_ID],[TS_Begin],[TS_End],[TS_Project],[TS_Hourrate],[TS_Desc],[TS_USID]) VALUES(6,'Aug 13 2003 5:45:00:000PM','Aug 13 2003 7:15:00:000PM',2,45.00,'Answering phone',1) INSERT INTO [detailcosts] ([DC_ID],[DC_COID],[DC_TSID],[DC_Qty],[DC_Cost])VALUES(17,3,2,1,2.00) INSERT INTO [detailcosts] ([DC_ID],[DC_COID],[DC_TSID],[DC_Qty],[DC_Cost])VALUES(18,1,2,3,0.50) The result should be 2 projects (project1 & project2) with their ...

  1. #1

    Default Re: Query trouble

    Good news is I got the insert script installed :) bad news is that the query
    you gave only returns 1 record instead of all the project records.

    Here are the insert statements:

    INSERT INTO [project]
    ([PJ_ID],[PJ_Name],[PJ_Active],[PJ_Color],[PJ_Hourrate],[PJ_CAID],[PJ_USID],
    [PJ_Wide])VALUES(1,'Project1',1,13422920,'60',1,3,1)
    INSERT INTO [project]
    ([PJ_ID],[PJ_Name],[PJ_Active],[PJ_Color],[PJ_Hourrate],[PJ_CAID],[PJ_USID],
    [PJ_Wide])VALUES(2,'Project2',1,5275647,'45',1,1,1)

    INSERT INTO [timesheet]
    ([TS_ID],[TS_Begin],[TS_End],[TS_Project],[TS_Hourrate],[TS_Desc],[TS_USID])
    VALUES(2,'Aug 15 2003 10:00:00:000AM','Aug 15 2003
    11:15:00:000AM',1,60.00,'Walking the dog',1)
    INSERT INTO [timesheet]
    ([TS_ID],[TS_Begin],[TS_End],[TS_Project],[TS_Hourrate],[TS_Desc],[TS_USID])
    VALUES(3,'Aug 13 2003 4:00:00:000PM','Aug 13 2003
    5:45:00:000PM',2,45.00,'Testing an app',1)
    INSERT INTO [timesheet]
    ([TS_ID],[TS_Begin],[TS_End],[TS_Project],[TS_Hourrate],[TS_Desc],[TS_USID])
    VALUES(6,'Aug 13 2003 5:45:00:000PM','Aug 13 2003
    7:15:00:000PM',2,45.00,'Answering phone',1)

    INSERT INTO [detailcosts]
    ([DC_ID],[DC_COID],[DC_TSID],[DC_Qty],[DC_Cost])VALUES(17,3,2,1,2.00)
    INSERT INTO [detailcosts]
    ([DC_ID],[DC_COID],[DC_TSID],[DC_Qty],[DC_Cost])VALUES(18,1,2,3,0.50)

    The result should be 2 projects (project1 & project2) with their hours
    worked: 1.25 hours on project 1 & 3.25 hours on project 2. Only project 2
    should have costs listed.

    Ok I hope now I gave all the necessary info :). I don't believe it's a
    simple query.


    Thanks again,

    Stijn Verrept.


    "Jacco Schalkwijk" <co.uk> wrote in message
    news:phx.gbl... 
    amount_earned, 


    Stijn Guest

  2. #2

    Default Re: Query trouble

    YES! That's the one! Just tried it out and this is what I was looking for.
    Thanks a lot (also Jacco and the other contributors!).

    "David Portas" <org> wrote in message
    news:phx.gbl... 


    Stijn Guest

  3. #3

    Default Re: Query trouble

    Try this (amended version of Jacco's query).

    SELECT P.PJ_ID, MAX(p.PJ_Name) AS pj_name,
    SUM(DATEDIFF(MINUTE, t.TS_Begin, t.TS_end))/60.0 AS hours_spent,
    SUM(DATEDIFF(MINUTE, t.TS_Begin, t.TS_end) * TS_Hourrate) /60.0 AS
    amount_earned,
    SUM(dc.total_costs) AS Total_costs
    FROM Project p
    LEFT JOIN Timesheet t
    ON P.PJ_ID = t.TS_Project
    FULL JOIN
    (SELECT DC_TSID, SUM(dc_qty*dc_cost) AS total_costs
    FROM DetailCosts
    GROUP BY DC_TSID) AS dc
    ON t.TS_ID = dc.DC_TSID
    GROUP BY P.PJ_ID

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Guest

Similar Threads

  1. Trouble with MSSQL query
    By cf_dev2 in forum Coldfusion Database Access
    Replies: 11
    Last Post: October 23rd, 11:51 PM
  2. query trouble
    By heath_edw in forum Coldfusion - Getting Started
    Replies: 9
    Last Post: August 27th, 02:22 AM
  3. Trouble with sql query
    By Fernando Rodríguez in forum MySQL
    Replies: 2
    Last Post: January 23rd, 07:24 PM
  4. Trouble with url variable in query
    By Vampyr_Bytes in forum Coldfusion - Getting Started
    Replies: 2
    Last Post: August 17th, 08:02 AM
  5. Trouble with database query
    By _Roudh in forum Macromedia ColdFusion
    Replies: 2
    Last Post: June 15th, 11:30 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139