...

View Full Version : Display records for 1 month



urko
09-02-2005, 02:21 PM
How can I display records from my DB only for 1 month!

Example:
When you insert a review to a certain record, I would like that record with most reviews for this month (i.e. August) would be displayed.
Reviews from July, etc.. will be in archive.


My rs:

<%
Dim caliber
Dim caliber_numRows

Set caliber = Server.CreateObject("ADODB.Recordset")
caliber.ActiveConnection = MM_search_STRING
caliber.Source = "SELECT * FROM [Submit_load Poizvedba] ORDER BY date DESC"
caliber.CursorType = 0
caliber.CursorLocation = 2
caliber.LockType = 1
caliber.Open()

caliber_numRows = 0
%>

Thanks,

miranda
09-02-2005, 04:32 PM
Use the DatePart function

btw date is a reserved word and you should not name a column with a reserved word


<%
Dim caliber
Dim caliber_numRows

Set caliber = Server.CreateObject("ADODB.Recordset")
caliber.ActiveConnection = MM_search_STRING
caliber.Source = "SELECT * FROM [Submit_load Poizvedba] WHERE DatePart("m",[Submit_load Poizvedba].[date]) = DatePart("m",Date()) AND DatePart("yyyy",[Submit_load Poizvedba].[date]) = DatePart("yyyy",Date()) ORDER BY date DESC"
caliber.CursorType = 0
caliber.CursorLocation = 2
caliber.LockType = 1
caliber.Open()

caliber_numRows = 0
%>

NancyJ
09-02-2005, 04:37 PM
datediff may also work for you


caliber.Source = "SELECT * FROM [Submit_load Poizvedba] WHERE DateDiff("m",[Submit_load Poizvedba].[date], date()) = 0 ORDER BY date DESC"

urko
09-02-2005, 04:46 PM
Thanks concerning the date warning! ( I will fix that ).

The first one worked, but I get all records displayed which were entered in Septemeber.

What I would like is that I would get all reviews from 1 record only, and possibly for previous month ( in this case August ).

By 1 record only is to show to users which record from the DB had most reviews in previous month and all those reviews from that 1 record would be visible!

Just to give u an idea. The name of that page would be: Item of the month!
So that item had most reviews and is now shown specialy on one page with reviews only from a previous month!
Is this possible :confused: :confused:


With the second solution I get EOF and BOF error.

Thanks all for your help,

I really appreciate

NancyJ
09-02-2005, 05:01 PM
apologies - use getdate() rather than date() in the sql statement.

to give you any more info on what you want to do I'd need to see your data structure.

urko
09-02-2005, 05:09 PM
apologies - use getdate() rather than date() in the sql statement.

to give you any more info on what you want to do I'd need to see your data structure.
At the moment I only have rs and filed items like Name, date, and description on that page.

I have made relationships in Access DB to the table Items so that users could write reviews(this works without problems) :D .
But because I don't know much of asp coding I have problems that I can't solve.
So I really appreciate your help on this one.

miranda
09-02-2005, 06:23 PM
To only get 1 record you need to change your sql select statement. Try using the TOP keyword to get the record.


<%
Dim caliber
Dim caliber_numRows

Set caliber = Server.CreateObject("ADODB.Recordset")
caliber.ActiveConnection = MM_search_STRING
caliber.Source = "SELECT TOP 1 * FROM [Submit_load Poizvedba] WHERE DatePart("m",[Submit_load Poizvedba].[date]) = DatePart("m",Date()) AND DatePart("yyyy",[Submit_load Poizvedba].[date]) = DatePart("yyyy",Date()) ORDER BY date DESC"
caliber.CursorType = 0
caliber.CursorLocation = 2
caliber.LockType = 1
caliber.Open()

caliber_numRows = 0
%>

Are the reviews in the same table? or are they in a different table? To get the reviews you will need to use a JOIN if in a different table and you could use a sub query if they are in the same table.

urko
09-02-2005, 07:03 PM
To only get 1 record you need to change your sql select statement. Try using the TOP keyword to get the record.

Are the reviews in the same table? or are they in a different table? To get the reviews you will need to use a JOIN if in a different table and you could use a sub query if they are in the same table.

I have to Db tables
1. Items
2. Reviews

In relationships I have connected these two tables so that users can write reviews for a different type of product. Now this works ok in browser as I have tested and have no problem with this.

NancyJ
09-02-2005, 07:45 PM
I'm at home now so I dont have anything to test this on but off the top of my head


caliber.Source = "SELECT top 1 count(distinct reviews.itemID) as numreviews, reviews.* FROM reviews WHERE DateDiff("m",[Submit_load Poizvedba].[date], date()) = 0 ORDER BY numreviews DESC"

or something like that... sorry if its not 100% accurate, I dont have an SQL synax checker at home

miranda
09-03-2005, 08:25 PM
here is how to get the results from the reviews table This is assuming that [Submit_load Poizvedba] is the table that holds the items. Just replace the name of the table and the name of the field.


"SELECT * FROM the_table_with_reviews WHERE the_field_name = (SELECT TOP 1 * FROM [Submit_load Poizvedba] WHERE DatePart("m",[Submit_load Poizvedba].[date]) = DatePart("m",Date()) AND DatePart("yyyy",[Submit_load Poizvedba].[date]) = DatePart("yyyy",Date())) ORDER BY date DESC"

urko
09-04-2005, 08:45 AM
here is how to get the results from the reviews table This is assuming that [Submit_load Poizvedba] is the table that holds the items. Just replace the name of the table and the name of the field.


"SELECT * FROM the_table_with_reviews WHERE the_field_name = (SELECT TOP 1 * FROM [Submit_load Poizvedba] WHERE DatePart("m",[Submit_load Poizvedba].[date]) = DatePart("m",Date()) AND DatePart("yyyy",[Submit_load Poizvedba].[date]) = DatePart("yyyy",Date())) ORDER BY date DESC"

Submit_load Poizvedba is a Query that I'm using to display reviews.

Submit_load is a table in DB where records are described.

So I need that reviews from Sumbit_load Poizvedba would be visible for previous month ( ie August ), but only reviews from a record which had most reviews!
example:
If I get 10 reviews for record #2 and 12 reviews for record # 5 I would like that only reviews from record #5 would be shown!

So that means that from September 1st to September 30th this page would show only records from August ( ID Record and all reviews for this record ) and then on October 1st it will shown record with most reviews in September.

Hope you know what I mean :)

miranda
09-04-2005, 03:37 PM
To get the previous months reviews use this DatePart("m",DateAdd("m",-1,Date())). This is a function within a function. The inner function gets the date of 1 month previous to today(August 04, 2005, or 8/4/2005 as m/d/yyyy) the outer function says to just get the value of the month for this (August = 8). So this month using DatePart("m",DateAdd("m",-1,Date())) will return 8 and next month it will return 9 and so on

You will need to designate a unique field to key off of. For example say that items table has a column with data type autonumber that is used as a primary key to signify a unique record. Your reviews table then have a column with data type number that holds this value, so that you know which item the review is for. (obviously this doesnt have to be numeric values, you could have set it up so that these are data type text. The point is you will have some field used as a unique identifier in the items table that is then listed in the rviews table to show which record they relate to) In this case you want to grab that value of that column as the TOP.

uniqueIdentifier = the column in the query that designates which record in the items table the reviews correpond to. All you need to do is change that to whatever you named this column.



<%
Dim caliber
Dim caliber_numRows

Set caliber = Server.CreateObject("ADODB.Recordset")
caliber.ActiveConnection = MM_search_STRING
caliber.Source = "SELECT TOP 1 [Submit_load Poizvedba].[uniqueIdentifier], [Submit_load Poizvedba].* FROM [Submit_load Poizvedba] WHERE DatePart("m",[Submit_load Poizvedba].[date]) = DatePart("m",DateAdd("m",-1,Date())) AND DatePart("yyyy",[Submit_load Poizvedba].[date]) = DatePart("yyyy",Date()) ORDER BY date DESC"
caliber.CursorType = 0
caliber.CursorLocation = 2
caliber.LockType = 1
caliber.Open()

caliber_numRows = 0
%>

urko
09-04-2005, 04:22 PM
To get the previous months reviews use this DatePart("m",DateAdd("m",-1,Date())). This is a function within a function. The inner function gets the date of 1 month previous to today(August 04, 2005, or 8/4/2005 as m/d/yyyy) the outer function says to just get the value of the month for this (August = 8). So this month using DatePart("m",DateAdd("m",-1,Date())) will return 8 and next month it will return 9 and so on

You will need to designate a unique field to key off of. For example say that items table has a column with data type autonumber that is used as a primary key to signify a unique record. Your reviews table then have a column with data type number that holds this value, so that you know which item the review is for. (obviously this doesnt have to be numeric values, you could have set it up so that these are data type text. The point is you will have some field used as a unique identifier in the items table that is then listed in the rviews table to show which record they relate to) In this case you want to grab that value of that column as the TOP.

uniqueIdentifier = the column in the query that designates which record in the items table the reviews correpond to. All you need to do is change that to whatever you named this column.



<%
Dim caliber
Dim caliber_numRows

Set caliber = Server.CreateObject("ADODB.Recordset")
caliber.ActiveConnection = MM_search_STRING
caliber.Source = "SELECT TOP 1 [Submit_load Poizvedba].[uniqueIdentifier], [Submit_load Poizvedba].* FROM [Submit_load Poizvedba] WHERE DatePart("m",[Submit_load Poizvedba].[date]) = DatePart("m",DateAdd("m",-1,Date())) AND DatePart("yyyy",[Submit_load Poizvedba].[date]) = DatePart("yyyy",Date()) ORDER BY date DESC"
caliber.CursorType = 0
caliber.CursorLocation = 2
caliber.LockType = 1
caliber.Open()

caliber_numRows = 0
%>

Ok this works partly as it shown a record with 3 reviews, but I have 1 record that has 5 reviews and that one didn't show. but please don't be mad as I would like something different.
Unique key is "ID" and so this part is ok and works like u decsribed.

What I would like is to dispaly headings of each revirew for most reviewed record, so hat users can click on a specific review.
So what i made is a repeated region to display all reviews(title only) ( not Records name ) and made a link on title to read review.

So users can browse or see all reviews click on a desired review title and popup opens with that review.


<%If (Repeat1__numRows Mod 2) Then%>
<tr style="background-color:#f4f4f4">
<%Else%>
<tr style="background-color:#ffffff">
<%End If%>
<td width="20%"><%= DoDateTime((caliber.Fields.Item("date").Value), 1, 4105) %></td>
<td width="20%"><a href="#" onClick="MyWindow=window.open('http://myserver/myfolder/loads_display.asp?load_id=<%=(caliber.Fields.Item("Load_id").Value)%>','MyWindow','toolbar=no,location=no,directories=no,status=no,menubar=no,scrollbars=no,resizable=no, width=500,height=620'); return false;"><%=(caliber.Fields.Item("Title").Value)%></a></td>
<td width="20%"><%=(caliber.Fields.Item("bullet").Value)%></td>
<td width="20%"><%=(caliber.Fields.Item("powder").Value)%></td>
</tr>
</table>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
caliber.MoveNext()
Wend
%>

miranda
09-04-2005, 05:38 PM
so what part do you need help with?

urko
09-04-2005, 05:52 PM
so what part do you need help with?

The last RS that you send me is ok, only thing is, that I want all reviews to be shown not just 1, and also that all reviews are from record ID which had most reviews in previous month. All other reviews from different Record ID should not be shown in that page.

miranda
09-04-2005, 06:01 PM
then remove the TOP 1 part of the sql statement


"SELECT * FROM [Submit_load Poizvedba] WHERE DatePart("m",[Submit_load Poizvedba].[date]) = DatePart("m",DateAdd("m",-1,Date())) AND DatePart("yyyy",[Submit_load Poizvedba].[date]) = DatePart("yyyy",Date()) ORDER BY date DESC"

NancyJ
09-04-2005, 06:14 PM
This is what you're trying to do as I understand it...
You have a table of items and you have a table of reviews linked to those items in a one to many relationship.
You want to determine the which item had the most reviews within the last month and display all reviews for that item within that timeframe.

Sorry I cant help while I'm at home but maybe my explaination (if it is correct) will help miranda (or anyone else) understand what you need to do

urko
09-04-2005, 06:16 PM
Ok I did that.
But the thing is that all reviews entered in August are displayed! I would like that only reviews from a record ID which had most reviews would be displayed

urko
09-04-2005, 06:17 PM
TYou want to determine the which item had the most reviews within the last month and display all reviews for that item within that timeframe.

Yes that's what i was trying to say :D , sorry to you all ,but english is not my native language so i had some difficulties explaining what I really want ;)

miranda
09-05-2005, 03:27 PM
Can you zip the structure of these database tables and include it? that way I can better understand and can help you with the correct sql statement

urko
09-05-2005, 04:09 PM
Can you zip the structure of these database tables and include it? that way I can better understand and can help you with the correct sql statement

I will send u PM with link where can you download DB. The link will be working today. In case you will try to download it tomorrow, please PM me, and I will send you another link.

Thanks,



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum