Solved

database question

Posted on 2007-04-07
5
170 Views
Last Modified: 2010-03-19
database question.
i want to calculate a trend values in a field
i have field which shows total count of numbers. from that i want to show the trend in a separate field. by present date.
how can i do that
i want the query for both access and mssql
can some body help  me in this.
0
Comment
Question by:romeiovasu
  • 3
5 Comments
 
LVL 3

Expert Comment

by:AbhishekSharma
ID: 18869699
Please provide some data
0
 

Author Comment

by:romeiovasu
ID: 18869715
for example
i have count of 100 numbers and i want to forcast for this month
what i am doing in or access manually is
trend = (100/day of the month)*no of days in a month
like
trend = (100/6)*30 = 500
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 18869757
in access sql

select  (100/Day(Date()) * DaysInMonth(Date())) as trend
from tableName


you need this function (place in a module)

Function DaysInMonth(MyDate)

   ' This function takes a date as an argument and returns
   ' the total number of days in the month.

   Dim NextMonth, EndOfMonth

   NextMonth = DateAdd("m", 1, MyDate)
   EndOfMonth = NextMonth - DatePart("d", NextMonth)
   DaysInMonth = DatePart("d", EndOfMonth)

End Function
0
 

Author Comment

by:romeiovasu
ID: 18869925
can you tell me how to use in a sql query
i got it for access i used in a simple way
DateDiff("d",Date()-(Day(Date()- 1)), DateSerial(Year(Date()),(Month(Date())+1),1))

now i am using sql 2005 i want to get in sql query
0
 

Author Comment

by:romeiovasu
ID: 18869965
ok got it thank you
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question