1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Rep Power

    Talking Week range to single day

    ok guys, i want to hear what you would do in my current situation. i am currently fixing a crystal report, which its source is a stored procedure in SQL Server 2000. The problem here is this.

    if the user files a overtime for a week, it would save it per day, well, kinda like this.

    idno overtime from overtime to
    19960072 2012-07-03 07:00:00 2012-07-06 08:00:00

    well, the above statement is shortened, it should have the shift start and end of an employee, time in and time out of an employee and many many more.

    this is the sql query used in the report.

    select A.*,
    L.dtfrom as [leave_dtfrom],
    L.dtto as [leave_dtto],

    convert(SMALLDATETIME,convert(varchar,O.otdtfrom,1 01) + ' ' + convert(varchar,O.otstart,108)) as [otdtfrom],
    convert(SMALLDATETIME,convert(varchar,O.otdtto,101 ) + ' ' + convert(varchar,O.otend,108)) as [otdtto],
    convert(SMALLDATETIME,convert(varchar,O.advdtfrom, 101) + ' ' + convert(varchar,O.advotstart,108)) as [advdtfrom],
    convert(SMALLDATETIME,convert(varchar,O.advdtto,10 1) + ' ' + convert(varchar,O.advotend,108)) as [advdtto],
    convert(SMALLDATETIME,convert(varchar,O.spldatefro m,101) + ' ' + convert(varchar,O.splotstart,108)) as [spldatefrom],
    convert(SMALLDATETIME,convert(varchar,O.spldateto, 101) + ' ' + convert(varchar,O.splotend,108)) as [spldateto],

    convert(smalldatetime,D.dyn_value) as [holidaydate],
    D.dyn_key as [holidaytype],
    D.dyn_name as [holidayname],
    case when AL.al_month is not null then 1 else 0 end as [al]
    into #tempA
    from attendance_table A
    left join leavebalances L on
    and A.ref_no=L.leaveref_no
    and cast(convert(varchar,A.shift_from,101) as smalldatetime) between L.dtfrom and L.dtto
    left join overtime O on
    and A.otrefno=O.refno
    and ( (cast(convert(varchar,A.shift_from,101) as smalldatetime) between O.otdtfrom and O.otdtto)
    or (cast(convert(varchar,A.shift_from,101) as smalldatetime) between O.advdtfrom and O.advdtto)
    or (cast(convert(varchar,A.shift_from,101) as smalldatetime) between O.spldatefrom and O.spldateto)
    left join dyntable D on
    D.dyn_key like '%holiday%'
    and cast(convert(varchar,A.shift_from,101) as smalldatetime)=convert(smalldatetime,D.dyn_value)
    left join employee E on
    left join al_maintenance_table AL on
    cast(convert(varchar,A.shift_from,101) as smalldatetime) =
    CONVERT(smalldatetime, convert(varchar,AL.al_month) + '/' + convert(varchar,AL.al_day) + '/' + convert(varchar,AL.al_year)
    where --A.div_cde='XO0' and
    --A.dept_cde='A10' and
    E.div_cde not in ('XO5','OJ0','CL0') and
    E.rankdesc not in ('M','') and
    E.hired <= @datefrom and
    cast(convert(varchar,A.shift_from,101) as smalldatetime) between @datefrom and @dateto
    order by A.shift_from

    to test it, i used this variables

    declare @datefrom as smalldatetime
    declare @dateto as smalldatetime

    set @datefrom='7/1/2012'
    set @dateto='7/31/2012'

    what i would like to do is something like this:

    idno overtime from overtime to
    19960072 2012-07-03 07:00:00 2012-07-03 08:00:00
    19960072 2012-07-04 07:00:00 2012-07-04 08:00:00
    19960072 2012-07-05 07:00:00 2012-07-05 08:00:00
    19960072 2012-07-06 07:00:00 2012-07-06 08:00:00

    to make it simple, i would like to have a query which, from a date range eg: 2012-07-03 07:00:00 2012-07-06 08:00:00
    to something like the above statement. thanks for any help which you could provide me
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Rep Power
    Do you think guys this is too difficult? i think so too hahaha so i want to know any answers... please... or do you guys need some other information?

IMN logo majestic logo threadwatch logo seochat tools logo