help with SQL query

Results 1 to 2 of 2

Thread: help with SQL query

  1. #1
    Join Date
    Dec 1969

    Default help with SQL query

    I&#039;m not really a great SQL person (as will be obvious after this question) and need some help with a query.<BR><BR>I have this data:<BR><BR>table_project<BR><BR>projectid (primary key, identity)<BR>clientid<BR>employeeid<BR>notes<BR>Is Active<BR><BR>table_project_data<BR><BR>projectid< BR>timespent<BR>workdate (datetime)<BR><BR>I want to retreive all records in the table_project_data table for each project found in the table_project table and am not sure about the best way of doing it.<BR><BR>I have created a view in SQL 2K that "pre-joins" the tables to get the info about the projects (employee name, project name, client name etc) and then I want to retrieve all the hours worked on a daily basis for each project found. I also want to be able to pass the number of days I want to retrieve.<BR><BR>This is the logic I&#039;m following to get the 5 most recent days of data:<BR><BR>execute view (based on either emp # or active projects etc)<BR>for each record found<BR>select top 5 * from table_project_data order by workdate desc<BR><BR>So i&#039;d want the data to come out like this:<BR><BR>project&#124Employee&#124jan 1&#124 jan 2 &#124 jan 3 &#124 jan 4 <BR>--------------------------------------------------<BR>test &#124 arthur &#124 2 hours &#124 4 hours &#124 2 hours &#124 1 hour<BR><BR>Should I create a temp table with the info created from the SQL view and then use a cursor to loop through the 2nd table or somehow use a join to get the data?

  2. #2
    Join Date
    Dec 1969

    Default You need to start... getting the last 5 dates, only:<BR> SELECT TOP 5 workdate FROM yourView ORDER BY workdate DESC<BR><BR>You probably need to do that as a separate query, in order to know what dates to display in the header of your table.<BR><BR>And *then* you apply that to the main query. SO *something* like:<BR> SELECT * FROM yourView<BR> WHERE workdate IN ( ...the list of dates... )<BR> ORDER BY project, employee, workdate<BR><BR>That will get you output records something like:<BR> proj1 - Adam - 1/1/2003 - 2 (hours)<BR> proj1 - Adam - 1/3/2003 - 2<BR> proj1 - Bob - 1/2/2003 - 4<BR> proj1 - Bob - 1/4/2003 - 4<BR> proj1 - Bob - 1/5/2003 - 1<BR> etc.<BR><BR>As you can see, you won&#039;t necessarily have data for all dates for each employee.<BR><BR>[If you want to show *all* employees and projects, even those with zero hours total, you&#039;ll have to use a LEFT JOIN, so your VIEW might not cut it.]<BR><BR>Anyway, now you simply have to consolidate all the data for each employee into one &#060;TABLE&#062; row of output. And that&#039;s easy to do in VBS code. For a *start* on doing it, look here:<BR><BR>But that will need some adaptation.<BR><BR><BR><BR>

Posting Permissions

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