Results 1 to 1 of 1

Thread: Query Assitance

  1. #1
    Join Date
    Mar 2010
    Posts
    1

    Unanswered: Query Assistance

    Hello all.

    This is my first post and Im hoping for some advice or assistance.
    Im fairly new to SQL and over the last week I have been trying to run various queries which calculate statistics and the one below has led to me to ask you guys.

    The query below will list all current active users for the specified period and calculate the daily average amount recorded based on the number of working days between the periods listed. However, the problem I am trying to get round is that user1 started employment after periodstart and therefore the calculation for this user is incorrect.

    The second query below is for that specific user where the period start is based on their employment date and the result is correct.

    What I am hoping to achieve is getting one query / stored procedure to get the correct results.

    I am using SQL 2000 sp4. If you need any more information please let me know.

    ------Query 1--------

    Declare @startdate datetime, @enddate datetime,@periodStart datetime, @periodend datetime

    Set @PeriodStart = (SELECT pebedt FROM db1.periodt WHERE pe = 0509)
    Set @PeriodEnd = (SELECT peendt FROM db1periodt WHERE pe = 0210)

    Select distinct ivalue as 'User',
    SUM(thhrwkdw)/(Select BDays=Count(*)
    From db2.tblBusinessCalendar
    Where BType=1
    And BDate Between @periodstart And @periodend)*((SUM(thdobidb)-SUM (thdowkdb))/SUM (thdowkdb)+1) as 'Recorded Amount',
    tkemdate as 'Employment Date'
    FROM db1.inqtimewahs, db1.timekeep
    where icode = 'TT' AND
    iper IN (select pe from db1.dbo.periodt where pebedt Between @PeriodStart and @PeriodEnd) and ivalue = tkinit
    group by ivalue, tkemdate
    having sum(thdowkdb) >0

    ----Results------

    USER Recorded Amount Employment Date
    User1 1.027478 2009-09-21 00:00:00.000
    User2 8.319394 1987-05-05 00:00:00.000
    User3 2.597081 2008-07-14 00:00:00.000
    User4 3.239473 2007-02-26 00:00:00.000

    ------Query 2--------

    Declare @startdate datetime, @enddate datetime,@periodStart datetime, @periodend datetime,@User varchar(14)

    Set @User = 'User1'
    Set @startdate=(select tkemdate from db1.timekeep where tkinit = @User)
    Set @PeriodStart = (SELECT pebedt FROM db1.periodt WHERE pe = 0509)
    Set @PeriodEnd = (SELECT peendt FROM db1.periodt WHERE pe = 0210)

    SELECT
    ivalue as 'User',
    SUM(thhrwkdw)/(Select BDays=Count(*)
    From db2.tblBusinessCalendar
    Where BType=1
    And BDate Between @startdate And @periodend)*((SUM(thdobidb)-SUM (thdowkdb))/SUM (thdowkdb)+1) as 'Recorded Amount',
    tkemdate as 'Employment Date'
    FROM db1.inqtimewahs, db1.timekeep
    WHERE icode = 'TT' AND
    iper IN (select pe from db1.periodt where pebedt Between @PeriodStart and @PeriodEnd)
    and ivalue = @User and ivalue = tkinit
    group by ivalue, tkemdate
    having sum(thdowkdb) >0

    --------Result---------

    USER Recorded Amount Employment Date
    User1 1.926522 2009-09-21 00:00:00.000
    Last edited by mistertubbs; 03-25-10 at 06:41. Reason: typo

Posting Permissions

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