Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Using Date() in a Calculation

Status
Not open for further replies.
smurf01

smurf01

IS-IT--Management
Jul 6, 2002
470
GB
Hi,
I wonder if someone can help me I amusing tables from a legacy system to create an asp page and I need to calculate the days in stock for certain products. below is the sql i am using and I would like to add the following but I cannot get it to work

Sum(JBSPEC.due_date - Date())As DaysinStock

SELECT master_spec, ULOADC.Job_number, Sum( load_quantity)As Qty,Count( unit_load_number)As NoPallets, customer_name, stock_manuf_flag, due_date, Sum(JBSPEC.due_date-Date()) AS DaysinStock
FROM kiwieur.ULOADC, kiwieur.JBSPEC
WHERE ULOADC.Job_number = JBSPEC.Job_number AND stock_manuf_flag = 0 GROUP BY master_spec, ULOADC.Job_number, stock_manuf_flag, customer_name, due_date
ORDER BY customer_name

Regards

Paul
 
Sort by date Sort by votes
try collectiong your data into vaibales and then perform subtraction or sum or whatever u need.
Fist lets start with the simple logic...what kind of total do u need?
1. total of days that item has been in stock
or
2. the total of days for all items?

the latter makes no sense!
what u have:

Total(date.recevied-todays.date) for x records
-------------------------
Item1.....77 days in stock
Item2.......33 days in stock
-----------------------etc.
Total(sum) is 110 days!

which makes no sense since "Total age" of items (ANY OF THEM) will be = to the total of the oldest item in there.
Basically "days spent in stock are shared" as item A and B sit.

***So post some more info on what type of information ur trying to generate...but anyhow


so instead of:
--------------------------
Sum(JBSPEC.due_date-Date())
--------------------------

try using varibales throughout the calculation until the final SQL execution:

in theory_______________________
dim varX,varZ...

varFROM= <DB_field_value_of_start_date>
varTODAY= <todays_date>
varINSTOCK= (varTODAY-varFROM)
varRESULT= Sum(<varINSTOCK>)
...Write the variable&quot;<varRESULT>..
___________________________________

aply ASP synthax

some more info:

I hope this helps......perhaps I read the post wrong.
All the best!

> need more info?
:: don't click HERE ::
 
Upvote 0 Downvote
You will also need to take into consideration the format of the date in the database. Have a look in the faqs for date issues

Cheech

[Peace][Pipe]
 
Upvote 0 Downvote
Guys,
I have managed to overcome my problem by doing as suggested and using a variable as shown below and then using the ASP to show the results

Code: 
<%Dim DaysinStock
	DaysinStock = (RSstocks.Fields.Item(&quot;due_date&quot;) - Date)%>

The only problem I have to solve now is that I have all of my results showing in a table however, I would like to filter the results so that it only shows data for stock that is 1 day old or more below is an example

customer order due date daysinstock
Paul 12345 19/01/04 2
tony 23456 01/01/04 -20
bob 98765 21/01/04 0

therefore the only record i would like displayed is

tony 23456 01/01/04 -20

can anyone think of a way to be able to filter the data. I have tried adding code into the sql without any luck.

Any help would be appreciated





Regards

Paul
 
Upvote 0 Downvote
good work Paul!
Now, the next &quot;topic&quot; would be
< Filtering RecordSet by The Variable >
whihc would look like:
Code: 
Dim RSstocks
DaysinStock = (RSstocks.Fields.Item(&quot;due_date&quot;) - Date)%>

set RecordSet= Server.CreateObject(&quot;ADODB.Recordset&quot;)
RecordSet.ActiveConnection = MM_CONNECTION_STRING
RecordSet.Source = &quot;SELECT *  FROM my_table WHERE my_field = '&quot; + Replace(DaysinStock, &quot;'&quot;, &quot;''&quot;) + &quot;'  ORDER BY some_filed DESC&quot;
.....

the key being in WHERE my_field = DaysInStovck

ALl the best!

> need more info?
:: don't click HERE ::
 
Upvote 0 Downvote
To pull records that are over 1 day old you need to tell the sql that

myDate = dateadd(&quot;d&quot;, -1, date())

mySql = &quot;SELECT * FROM myTable WHERE my_field < #&quot; + myDate + &quot;# ORDER BY some_other_field&quot;

Cheech

[Peace][Pipe]
 
Upvote 0 Downvote
Cheech,
Tried this but it did not work,
Code: 
SELECT master_spec,  ULOADC.Job_number, Sum( load_quantity)As Qty,Count( unit_load_number)As NoPallets,  customer_name, stock_manuf_flag, due_date, sales_rep_code
FROM kiwieur.ULOADC, kiwieur.JBSPEC
WHERE ULOADC.Job_number = JBSPEC.Job_number AND stock_manuf_flag = 0 AND due_date <#&quot; + myDate + &quot;#     
GROUP BY master_spec, ULOADC.Job_number,  stock_manuf_flag, customer_name,  due_date, sales_rep_code
ORDER BY sales_rep_code, customer_name, due_date

I got the error message
[transoft][TSODBC][usqlsd]superfluous[BY]

Regards

Paul
 
Upvote 0 Downvote
Hi,
I have been struggling with this problem for around two weeks now, I have tried the suggestions posted by both lebisol & cheech but withour success. I have also trawled the &quot;Net&quot; looking for any solution to my problem, no matter what I try I keep getting error messages. I have posted my code below in the hope that someone out there may be able to point me in the right direction

Code: 
 <%@LANGUAGE=&quot;VBSCRIPT&quot; CODEPAGE=&quot;1252&quot;%>
<!--#include file=&quot;../Connections/KIWI.asp&quot; -->
<%Dim DaysinStock
	Dim myDate
	myDate = dateadd(&quot;d&quot;, -1, date())
	DaysinStock = (RSstocks.Fields.Item(&quot;due_date&quot;) - Date)%>
	<%Response.Write(&quot;myDate&quot;)%>
<%
Dim RSstocks
Dim RSstocks_numRows

Set RSstocks = Server.CreateObject(&quot;ADODB.Recordset&quot;)
RSstocks.ActiveConnection = MM_KIWI_STRING
RSstocks.Source = &quot;SELECT master_spec,  ULOADC.Job_number, Sum( load_quantity)As Qty,Count( unit_load_number)As NoPallets,  customer_name, stock_manuf_flag, due_date, sales_rep_code  FROM kiwieur.ULOADC, kiwieur.JBSPEC  WHERE ULOADC.Job_number = JBSPEC.Job_number AND stock_manuf_flag = 0 AND due_date < #&quot; + myDate + &quot;#   GROUP BY master_spec, ULOADC.Job_number,  stock_manuf_flag, customer_name,  due_date, sales_rep_code  ORDER BY sales_rep_code, customer_name, due_date&quot;
RSstocks.CursorType = 0
RSstocks.CursorLocation = 2
RSstocks.LockType = 1
RSstocks.Open()

RSstocks_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index


Repeat1__numRows = -1
Repeat1__index = 0
RSstocks_numRows = RSstocks_numRows + Repeat1__numRows
%>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv=&quot;Content-Type&quot; content=&quot;text/html; charset=iso-8859-1&quot;>
<link href=&quot;StockStyles.css&quot; rel=&quot;stylesheet&quot; type=&quot;text/css&quot;>
</head>

<body>
<table width=&quot;85%&quot; height=&quot;52&quot; border=&quot;0&quot; align=&quot;center&quot; cellpadding=&quot;1&quot; cellspacing=&quot;1&quot;>
  <tr bgcolor=&quot;#000099&quot;> 
    <td width=&quot;10%&quot; height=&quot;26&quot;> <div align=&quot;center&quot;><font color=&quot;#FFFFFF&quot; size=&quot;2&quot; face=&quot;Arial, Helvetica, sans-serif&quot;>REPS 
        CODE </font></div></td>
    <td width=&quot;10%&quot;>
<div align=&quot;center&quot;><font color=&quot;#FFFFFF&quot;>CUSTOMER</font></div></td>
    <td width=&quot;10%&quot;> <div align=&quot;center&quot;><font color=&quot;#FFFFFF&quot; size=&quot;2&quot; face=&quot;Arial, Helvetica, sans-serif&quot;>SPEC 
        No. </font></div></td>
    <td width=&quot;10%&quot;> <div align=&quot;center&quot;><font color=&quot;#FFFFFF&quot; size=&quot;2&quot; face=&quot;Arial, Helvetica, sans-serif&quot;>MB 
        NUMBER </font></div></td>
    <td width=&quot;10%&quot;> <div align=&quot;center&quot;><font color=&quot;#FFFFFF&quot; size=&quot;2&quot; face=&quot;Arial, Helvetica, sans-serif&quot;>QTY</font></div></td>
    <td width=&quot;10%&quot;> <div align=&quot;center&quot;><font color=&quot;#FFFFFF&quot; size=&quot;2&quot; face=&quot;Arial, Helvetica, sans-serif&quot;>No. 
        PALLETS </font></div></td>
    <td width=&quot;10%&quot;> <div align=&quot;center&quot;><font color=&quot;#FFFFFF&quot; size=&quot;2&quot; face=&quot;Arial, Helvetica, sans-serif&quot;>DUE 
        DATE </font></div></td>
    <td width=&quot;10%&quot;> <div align=&quot;center&quot;><font color=&quot;#FFFFFF&quot; size=&quot;2&quot; face=&quot;Arial, Helvetica, sans-serif&quot;>DAYS 
        IN STOCK</font></div></td>
  </tr>
  <% 
While ((Repeat1__numRows <> 0) AND (NOT RSstocks.EOF)) 
%>
  <%If (Repeat1__numRows Mod 2) Then%>
  <tr bgcolor=&quot;#66CCFF&quot;> 
    <%Else%>
  <tr bgcolor=&quot;#CCCCCC&quot;> 
    <%End If%>
    
    <td width=&quot;166&quot;><font size=&quot;2&quot; face=&quot;Arial, Helvetica, sans-serif&quot;><%=(RSstocks.Fields.Item(&quot;sales_rep_code&quot;).Value)%></font> 
    <td width=&quot;144&quot;><%=(RSstocks.Fields.Item(&quot;customer_name&quot;).Value)%></td>
    <td width=&quot;144&quot; height=&quot;21&quot;><div align=&quot;center&quot;><font size=&quot;2&quot; face=&quot;Arial, Helvetica, sans-serif&quot;><%=(RSstocks.Fields.Item(&quot;master_spec&quot;).Value)%></font></div></td>
    <td width=&quot;139&quot;> 
      <div align=&quot;center&quot;><font size=&quot;2&quot; face=&quot;Arial, Helvetica, sans-serif&quot;><%=(RSstocks.Fields.Item(&quot;Job_number&quot;).Value)%></font></div></td>
    <td width=&quot;90&quot;> 
      <div align=&quot;center&quot;><font size=&quot;2&quot; face=&quot;Arial, Helvetica, sans-serif&quot;><%=(RSstocks.Fields.Item(&quot;Qty&quot;).Value)%></font></div></td>
    <td width=&quot;128&quot;> 
      <div align=&quot;center&quot;><font size=&quot;2&quot; face=&quot;Arial, Helvetica, sans-serif&quot;><%=(RSstocks.Fields.Item(&quot;NoPallets&quot;).Value)%></font></div></td>
    <td width=&quot;122&quot;> 
      <div align=&quot;center&quot;><font size=&quot;2&quot; face=&quot;Arial, Helvetica, sans-serif&quot;><%=(RSstocks.Fields.Item(&quot;due_date&quot;).Value)%></font></div></td>
    <td> 
      <div align=&quot;center&quot;><font size=&quot;2&quot; face=&quot;Arial, Helvetica, sans-serif&quot;><%=DaysinStock%></font></div></td>
  </tr>
  <% 
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  RSstocks.MoveNext()
Wend
%>
</table>
</body>
</html>
<%
RSstocks.Close()
Set RSstocks = Nothing
%>


Regards

Paul
 
Upvote 0 Downvote
Your date calc is wrong, take out the red part from the top of your document.

<%Dim DaysinStock
Dim myDate
myDate = dateadd(&quot;d&quot;, -1, date())
DaysinStock = (RSstocks.Fields.Item(&quot;due_date&quot;) - Date)%>

And add this just after the repeat region

<%
DaysinStock = Datediff(&quot;d&quot;, RSstocks.Fields.Item(&quot;due_date&quot;), Date())
%>

Basically the calculation for days in stock needs to be done for each record. Another error may be caused by using &quot;+&quot; instead of &quot;&&quot; for the myDate in the sql. Make my first changes and see if that fixes the problem though.

Cheech

[Peace][Pipe]
 
Upvote 0 Downvote
Cheech,
Did the alterations as suggested, it still did not work then i changed the + for & and tried that still no joy.

I keep getting this error message

[transoft][TSODBC][usqlsd]superfluous[BY]

I have posted the modified code

Code: 
<%@LANGUAGE=&quot;VBSCRIPT&quot; CODEPAGE=&quot;1252&quot;%>
<!--#include file=&quot;../Connections/KIWI.asp&quot; -->
<%
Dim RSstocks
Dim RSstocks_numRows
Dim DaysinStock
Dim myDate
myDate = dateadd(&quot;d&quot;, -1, date())

Set RSstocks = Server.CreateObject(&quot;ADODB.Recordset&quot;)
RSstocks.ActiveConnection = MM_KIWI_STRING
RSstocks.Source = &quot;SELECT master_spec,  ULOADC.Job_number, Sum( load_quantity)As Qty,Count( unit_load_number)As NoPallets,  customer_name, stock_manuf_flag, due_date, sales_rep_code  FROM kiwieur.ULOADC, kiwieur.JBSPEC  WHERE ULOADC.Job_number = JBSPEC.Job_number AND stock_manuf_flag = 0 AND due_date < #&quot; & myDate & &quot;#  GROUP BY master_spec, ULOADC.Job_number,  stock_manuf_flag, customer_name,  due_date, sales_rep_code  ORDER BY sales_rep_code, customer_name, due_date&quot;
RSstocks.CursorType = 0
RSstocks.CursorLocation = 2
RSstocks.LockType = 1
RSstocks.Open()

RSstocks_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index


Repeat1__numRows = -1
Repeat1__index = 0
RSstocks_numRows = RSstocks_numRows + Repeat1__numRows
%>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv=&quot;Content-Type&quot; content=&quot;text/html; charset=iso-8859-1&quot;>
<link href=&quot;StockStyles.css&quot; rel=&quot;stylesheet&quot; type=&quot;text/css&quot;>
</head>

<body>
<table width=&quot;85%&quot; height=&quot;52&quot; border=&quot;0&quot; align=&quot;center&quot; cellpadding=&quot;1&quot; cellspacing=&quot;1&quot;>
  <tr bgcolor=&quot;#000099&quot;> 
    <td width=&quot;10%&quot; height=&quot;26&quot;> <div align=&quot;center&quot;><font color=&quot;#FFFFFF&quot; size=&quot;2&quot; face=&quot;Arial, Helvetica, sans-serif&quot;>REPS 
        CODE </font></div></td>
    <td width=&quot;10%&quot;>
<div align=&quot;center&quot;><font color=&quot;#FFFFFF&quot;>CUSTOMER</font></div></td>
    <td width=&quot;10%&quot;> <div align=&quot;center&quot;><font color=&quot;#FFFFFF&quot; size=&quot;2&quot; face=&quot;Arial, Helvetica, sans-serif&quot;>SPEC 
        No. </font></div></td>
    <td width=&quot;10%&quot;> <div align=&quot;center&quot;><font color=&quot;#FFFFFF&quot; size=&quot;2&quot; face=&quot;Arial, Helvetica, sans-serif&quot;>MB 
        NUMBER </font></div></td>
    <td width=&quot;10%&quot;> <div align=&quot;center&quot;><font color=&quot;#FFFFFF&quot; size=&quot;2&quot; face=&quot;Arial, Helvetica, sans-serif&quot;>QTY</font></div></td>
    <td width=&quot;10%&quot;> <div align=&quot;center&quot;><font color=&quot;#FFFFFF&quot; size=&quot;2&quot; face=&quot;Arial, Helvetica, sans-serif&quot;>No. 
        PALLETS </font></div></td>
    <td width=&quot;10%&quot;> <div align=&quot;center&quot;><font color=&quot;#FFFFFF&quot; size=&quot;2&quot; face=&quot;Arial, Helvetica, sans-serif&quot;>DUE 
        DATE </font></div></td>
    <td width=&quot;10%&quot;> <div align=&quot;center&quot;><font color=&quot;#FFFFFF&quot; size=&quot;2&quot; face=&quot;Arial, Helvetica, sans-serif&quot;>DAYS 
        IN STOCK</font></div></td>
  </tr>
  <% 
While ((Repeat1__numRows <> 0) AND (NOT RSstocks.EOF)) 
%>
  <%If (Repeat1__numRows Mod 2) Then%>
  <tr bgcolor=&quot;#66CCFF&quot;> 
    <%Else%>
  <tr bgcolor=&quot;#CCCCCC&quot;> 
    <%End If%>
	<%
DaysinStock = Datediff(&quot;d&quot;, RSstocks.Fields.Item(&quot;due_date&quot;), Date())
%>

    <td width=&quot;166&quot;><font size=&quot;2&quot; face=&quot;Arial, Helvetica, sans-serif&quot;><%=(RSstocks.Fields.Item(&quot;sales_rep_code&quot;).Value)%></font> 
    <td width=&quot;144&quot;><%=(RSstocks.Fields.Item(&quot;customer_name&quot;).Value)%></td>
    <td width=&quot;144&quot; height=&quot;21&quot;><div align=&quot;center&quot;><font size=&quot;2&quot; face=&quot;Arial, Helvetica, sans-serif&quot;><%=(RSstocks.Fields.Item(&quot;master_spec&quot;).Value)%></font></div></td>
    <td width=&quot;139&quot;> 
      <div align=&quot;center&quot;><font size=&quot;2&quot; face=&quot;Arial, Helvetica, sans-serif&quot;><%=(RSstocks.Fields.Item(&quot;Job_number&quot;).Value)%></font></div></td>
    <td width=&quot;90&quot;> 
      <div align=&quot;center&quot;><font size=&quot;2&quot; face=&quot;Arial, Helvetica, sans-serif&quot;><%=(RSstocks.Fields.Item(&quot;Qty&quot;).Value)%></font></div></td>
    <td width=&quot;128&quot;> 
      <div align=&quot;center&quot;><font size=&quot;2&quot; face=&quot;Arial, Helvetica, sans-serif&quot;><%=(RSstocks.Fields.Item(&quot;NoPallets&quot;).Value)%></font></div></td>
    <td width=&quot;122&quot;> 
      <div align=&quot;center&quot;><font size=&quot;2&quot; face=&quot;Arial, Helvetica, sans-serif&quot;><%=(RSstocks.Fields.Item(&quot;due_date&quot;).Value)%></font></div></td>
    <td> 
      <div align=&quot;center&quot;><font size=&quot;2&quot; face=&quot;Arial, Helvetica, sans-serif&quot;><%=DaysinStock%></font></div></td>
  </tr>
  <% 
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  RSstocks.MoveNext()
Wend
%>
</table>
</body>
</html>
<%
RSstocks.Close()
Set RSstocks = Nothing
%>


Regards

Paul
 
Upvote 0 Downvote
[transoft][TSODBC][usqlsd]superfluous[BY]

SLECT field1,field3, SUM(field3)AS Qty GROUP BY field1,field2
FROM table

which is nothing more than saying that you want summed field3 (and retured as field.expression &quot;qty&quot;) where &quot;distincts&quot; are field1 and field2

Give it a shoot!

> need more info?
:: don't click HERE ::
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

RBGraphics
  • Locked
  • Question
Adobe Portfolio
Replies
0
Views
611
RBGraphics
RBGraphics
Mike Lewis
  • Locked
  • Question
Using Dreamweaver to maintain a site not created in Dreamweaver
Replies
2
Views
744
Mike Lewis
Mike Lewis
LyndonOHRC
  • Locked
  • Question
Need help using ImageCrop()
Replies
1
Views
248
LyndonOHRC
LyndonOHRC
mdvp
  • Locked
  • Question
Intermittent X-ref problem with figures in FM 12
Replies
0
Views
544
mdvp
mdvp
LyndonOHRC
  • Locked
  • Question
Javascript code will not execute if a cfdocument tag is in my document
Replies
1
Views
371
LyndonOHRC
LyndonOHRC

Part and Inventory Search

Sponsor

Back
Top