SQL help/convert asp script to one sql statement

Is there a way to convert/combine two fields into a date time within an sql statement. Right now I am using an asp script  (below) to calcluate the date and then get the percentage of the time differences within a date range.  The fields are

[ED DOOR DT] = 12/01/2007
[ED DOOR TM] = a number from 0001-2399 sybmolizing 12:00am->11:59pm
[TRANS TO FLR DT]= 12/01/2007
[TRANS TO FLR tm] = 0001 up to 2399

I am combining the fileds above and converting it over to date, then I get the diffence in time, and get a percentage of how many fall into a time range of 30/60/90/120/120 + minutes.

I want to graph the data and having it into one sql statement I can use multiple non-asp graphical tools.

here is the asp script:

sql="SELECT * FROM edstats where [ED DOOR DT] BETWEEN '"& GETDATE1 &"' AND '"& GETDATE33 &"'  and [TRANS TO FLR TM] <> ''  and [ED DOOR TM] <> ''ORDER BY ID"
i=0
value30F=0
value60F=0
value90F=0
value120F=0
value120gF=0
value30=0
value60=0
value90=0
value120=0
value140=0
value180=0
value240=0
maxvalue=0
negative=0
RS.Open sql, Conn
if   not Rs.EOF then
      do until rs.EOF
'GET TIME STAMP
acct=rs("acct#")
      EDDOORDATE=RS("ED DOOR DT")
      EDDOORTIME=RS("ED DOOR TM")
      ORDDOORDATE=RS("TRANS TO FLR DT")
      ORDDOORTIME=RS("TRANS TO FLR TM")
            theLen2 = Len(ORDDOORTIME)
      theLen = Len(EDDOORTIME)

' add 0's to make time 4 digits long
If theLen < 4 then
     theDiff = 4 - theLen
     For i = 0 To theDiff
          value = "0" & value
     Next
End if

      
      'do until len(EDDOORTM)<4
      'EDDOORTIME="0" & EDDOORTIME
      'loop
      
      ConvEDHour = Trim(Left(EDDOORTIME, 2))
      ConvEDMinute = Trim(Mid(EDDOORTIME, 3, 2))
      EDHOURS=ConvEDHour &":"&ConvEDMinute
      EDFINALDATE=EDDOORDATE&" " & EDHOURS
      EDFINALDATE=cdate(edfinaldate)
            If theLen2 < 4 then
           theDiff2 = 4 - theLen2
           For i = 0 To theDiff2
          value = "0" & value
     Next
End if

      
      
ConvORDHour = Trim(Left(ORDDOORTIME, 2))
ConvORDMinute = Trim(Mid(ORDDOORTIME, 3, 2))
ORDHOURS=ConvORDHour &":"&ConvORDMinute
ORDINALDATE=ORDDOORDATE&" " & ORDHOURS
 ORDINALDATE=cdate(ORDINALDATE)      
      DIFF=(DateDiff("n",EDFINALDATE,ORDINALDATE))
      'RESPONSE.Write("acct="&acct&"")
      'RESPONSE.Write("DIFF="&DIFF&"")
      'RESPONSE.Write("<BR>")
            if diff < 0  then
            negative=negative+1
            end if
            if diff > 0  and diff < 30 then
                  value30 = value30 + 1
            else if diff > 29 and  diff < 60 then
                  value60=value60+1
            else if diff >59 and diff < 90 then
                  value90=value90+1
            else if diff >89 and diff < 120 then
                  value120=value120+1
            else if diff > 119 and diff < 180 then
                  value180 = value180+1
            else if diff > 179 and diff < 240 then
                  value240 = value240+1
            else if diff >239 then
                  maxvalue=maxvalue +1      
                  
                  end if
                  end if
                  end if
                  end if
                  end if
                  end if
                  end if
                  
      I=I+1
    Rs.MoveNext
loop
end if

value30F=(value30/I) * 100
value60F=(value60/i) * 100
value90f=(value90/i) * 100
value120f=(value120/i) * 100
value180f=(value180/i) * 100
value240f=(value240/i) * 100
maxvaluef=(maxvalue/i) * 100


value30F=round(value30F,2)
value60F=round(value60F,2)
value90F=round(value90F,2)
value120F=round(value120F,2)
value180f=ro
nd(value180f,2)
value240f=round(value240f,2)
maxvaluef=round(maxvaluef,2)

Rs.Close
set Rs = Nothing

conn.close
set conn= nothing

cseinkAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
derekkrommConnect With a Mentor Commented:
Step 1: Put what DeathToSpam coded for you into a function:

CREATE FUNCTION convToDate(
      @sDate varchar(20),
      @sTime varchar(20))
RETURNS datetime as
begin

SELECT
      @sDate = '12-01-2007',
      @sTime = '0001'

SELECT
      @sDate AS sDate,
      -- Convert the date string to an actual datetime value.
      CAST(@sDate AS datetime) AS dtDate

SELECT
      @sTime AS sTime,
      -- Convert the time string to an integer, to make it easier to work with.
      CAST(@sTime AS int) iTime,
      -- Extract the hour from the time integer.
      (CAST(@sTime AS int) / 100) AS iTimeHours,
      -- Extract the minute from the time integer.
      (CAST(@sTime AS int) % 100) AS iTimeMinutes

RETURN
      CAST((@sDate + ' ' + CAST((CAST(@sTime AS int) / 100) AS varchar(2)) + ':' + CAST((CAST(@sTime AS int) % 100) AS varchar(2)) + ':00') AS datetime)

END

Step 2: Run the following query to get counts:

select
(select count(*) from edstats where datediff(mi, convToDate([ED DOOR DT], [ED DOOR TM]), convToDate(mi, [TRANS TO FLR DT], [TRANS TO FLR TM])) < 30) ct30,
(select count(*) from edstats where datediff(mi, convToDate([ED DOOR DT], [ED DOOR TM]), convToDate(mi, [TRANS TO FLR DT], [TRANS TO FLR TM])) between 30 and 60) ct60,
(select count(*) from edstats where datediff(mi, convToDate([ED DOOR DT], [ED DOOR TM]), convToDate(mi, [TRANS TO FLR DT], [TRANS TO FLR TM])) between 60 and 90) ct90,
(select count(*) from edstats where datediff(mi, convToDate([ED DOOR DT], [ED DOOR TM]), convToDate(mi, [TRANS TO FLR DT], [TRANS TO FLR TM])) between 90 and 120) ct120,
(select count(*) from edstats where datediff(mi, convToDate([ED DOOR DT], [ED DOOR TM]), convToDate(mi, [TRANS TO FLR DT], [TRANS TO FLR TM])) > 120) ct120plus
0
 
Mass Dot NetCommented:
How do you convert [TRANS TO FLR tm] to a time value, if it can go up to 2399?  Stated another way: what time does 2399 represent, in [hh:mm AM|PM] format?


-= DeathToSpam =-
0
 
cseinkAuthor Commented:
My bad. 59 not 99 is the top value. 23:59 = 11:59a.    0530 = 5:30am  
0
 
Mass Dot NetCommented:
Cseink --

I'm still not sure about the specifics of what you want to do.  But here's a SQL script that should help illustrate how you can combine fields like the ones you described to a datetime value.

====================================================
DECLARE
      @sDate varchar(20),
      @sTime varchar(20)

SELECT
      @sDate = '12-01-2007',
      @sTime = '0001'

SELECT
      @sDate AS sDate,
      -- Convert the date string to an actual datetime value.
      CAST(@sDate AS datetime) AS dtDate

SELECT
      @sTime AS sTime,
      -- Convert the time string to an integer, to make it easier to work with.
      CAST(@sTime AS int) iTime,
      -- Extract the hour from the time integer.
      (CAST(@sTime AS int) / 100) AS iTimeHours,
      -- Extract the minute from the time integer.
      (CAST(@sTime AS int) % 100) AS iTimeMinutes

SELECT
      CAST((@sDate + ' ' + CAST((CAST(@sTime AS int) / 100) AS varchar(2)) + ':' + CAST((CAST(@sTime AS int) % 100) AS varchar(2)) + ':00') AS datetime)
====================================================


-= DeathToSpam =-
0
 
Mass Dot NetConnect With a Mentor Commented:
My code wasn't meant to run as a function, since it returns multiple recordsets and hard-codes the date/time string values.  But derekkromm has the right idea -- here's what the function should look like:

====================================================

CREATE FUNCTION [dbo].[ConvertToDatetime] (
      @sDate varchar(50),
      @sTime varchar(50)
)  RETURNS datetime AS  
BEGIN
      DECLARE
            @iTime integer,
            @iTimeHours integer,
            @iTimeMinutes integer,
            @sNewTime varchar(8),
            @dtNewDateTime datetime

      SELECT
            -- Convert the time string to an integer, to make it easier to work with.
            @iTime = CAST(@sTime AS int),
            @iTimeHours = (@iTime / 100),
            @iTimeMinutes = (@iTime % 100),
            @sNewTime = CAST(@iTimeHours AS varchar(2)) + ':' + CAST(@iTimeMinutes AS varchar(2)) + ':00',
            @dtNewDateTime = CAST((@sDate + ' ' + @sNewTime) AS datetime)

      RETURN @dtNewDateTime
END


To call it, do something like this:

====================================================

SELECT dbo.ConvertToDatetime('3-16-2007', '1647')

====================================================


-= DTS =-
0
All Courses

From novice to tech pro — start learning today.