|
Thnkas Andy. Thats what I want. 
Mazy
"And the carpet needs a haircut, and the spotlight looks like a prison break
And the telephone's out of cigarettes, and the balcony is on the make
And the piano has been drinking, the piano has been drinking...not me...not me-Tom Waits
|
|
|
|
|
I'm working on a video rental system. I have a table of data that looks something like this:
DateRented RentalType
----------------------- -----------
11/11/2002 11:21:47 AM VHS
11/11/2002 11:44:28 AM VHS
11/13/2002 11:37:05 AM VHS
11/13/2002 10:54:14 PM DVD
11/14/2002 2:57:58 AM DVD
11/14/2002 2:58:31 AM DVD
11/14/2002 3:39:19 PM DVD
11/15/2002 10:38:32 AM VHS
11/15/2002 10:49:06 AM DVD
What I need is a query that will return three values--the date in question (day only), a count of all items that were rented, and a count of all DVDs that were rented. I have tried using the GROUP BY clause, but I can't seem to get it right. See, I don't care about the time in the date field. I just want a count for all items and DVD items for a particular day. So my output should look like this:
DateRented TotalRentals DVDRentals
-------------- --------------- ----------
11/11/2002 2 0
11/13/2002 2 1
11/14/2002 3 3
11/15/2002 2 1
Thanks in advance.
-Matt
------------------------------------------
The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall
|
|
|
|
|
Try something like:
SELECT
DateRented,
COUNT(1) As TotalRentals,
SUM(DVD) As DVDRentals
FROM
(
SELECT
CAST(
CAST(Year(DateRented) As varchar(4))
+ '/' + CAST(Month(DateRented) As varchar(2))
+ '/' + CAST(Day(DateRented) As varchar(2))
As datetime) As DateRented,
CASE
WHEN RentalType = 'DVD' THEN 1
ELSE 0
END As DVD
FROM
Rentals
) As R
GROUP BY
DateRented
ORDER BY
DateRented
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
|
|
|
|
|
Matt
The SQL statement is likely to depend upon the database that you are using. For Oracle, the statement would be:
select trunc(DateRented) DateRented,<br />
count(*) TotalRentals,<br />
sum(decode(RentalType, 'VHS', 1, 0)) VHSRentals,<br />
sum(decode(RentalType, 'DVD', 1, 0)) DVDRentals,<br />
from Rentals<br />
group by trunc(DateRented)
The trunc() function gets rid of the time portion of a date column. If there isn't a similar function for SQL-Server then I know that there is a function that allows you to convert the date-portion of a date column into a string.
The decode() function above returns 1 if the rental is a video, and 0 if the rental isn't. By summing the 1's, you get the total number of video rentals. Other database engines normally provide a case or iif function that would give you the same effect.
Hope this helps.
Andy
|
|
|
|
|
I'm actually toying with what database I should use. I was given the data in Access, though, so I'm starting there. The query functionality is not very powerful, so I don't think it has the functionality you describe. I will most likely end up moving this over to SQL Server, but I am trying to get something quick and dirty in the near term by just using what Access offers. Oy!!
Thanks for your help.
-Matt
------------------------------------------
The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall
|
|
|
|
|
Hi Matt
I got the follow statement to run through the MS-Access query painter:
SELECT DateValue(rentaldate) AS RentalDay,<br />
COUNT(*) AS TotalRentals,<br />
sum(IIf(rentaltype='VHS',1,0)) AS VhsRentals,<br />
sum(IIf(rentaltype='DVD',1,0)) AS DvdRentals<br />
FROM Rentals<br />
GROUP BY DateValue(rentaldate);
Andy
|
|
|
|
|
You are the man!!! Thank you. This is perfect!! I really appreciate it. By the way, do you know of any good source for SQL syntax in Access? I've found a few tutorial sites, but can't seem to find more of a guide on syntax and the differences between SQL Server SQL, ANSI SQL, and Access SQL. Any ideas?
Thanks again.
-Matt
------------------------------------------
The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall
|
|
|
|
|
The basic SQL syntax (select, insert, etc) is the same for all SQL databases. They start to differ in the following areas:
o Functions that can be used within SQL expressions (e.g. IIF).
o Outer joins.
o Available data-types.
o Whether stored-procedures and triggers are available.
o Security.
I tend to use the MS-Access query-painter to get the basic SQL syntax, then switch over to the SQL-Syntax view for the nitty-gritty stuff. The online help provides details of the available functions (if you look hard enough).
Regards
Andy
PS: If you start to use MS-Access in a production networked environment then make sure you have a copy of the JETCOMP.EXE utility available (from the MS support website). If your MDB database file get corrupted then JetComp may save your bacon.
PPS: If you want an alternative then the MSDE database engine is included on the MS-Access installation CD (you have to install it manually). This is a cut-down version of the SQL-Server product (so you should be able to use the .Net SQL-Server database classes).
|
|
|
|
|
You've used the term "MS-Access query-painter" a couple of times. Is this something different from the Design View of a query? I've used the expression builder a couple of times too, which is somewhat helpful. I just thought it would be nicer to have a decent reference. Seems like most of the books on the subject are more about the basic functionality available and less about the details of the SQL syntax that differs from other DBs.
As far as using this in a production environment, there's just no way. I don't trust Access. The company that built the application that generates and uses this DB does use access, but they provide no queries for reporting. I just decided that rather than maintaining two sets of data (the original in Access and then copying it to SQL Server), I would just learn how to use the query tool on Access. I will eventually be automating the generation of reporting (charting more than anything) in Excel with Office Automation. I guess I would call learning Access a necessary evil.
Thanks again.
-Matt
------------------------------------------
The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall
|
|
|
|
|
You're right - I ment the "Design View" and "SQL View" for Queries. I didn't have Access open when I wrote my previous email.
Andy
|
|
|
|
|
Hi all,
I am new in ASP and ADO
when I call recordset.open "select * from db where ID='aa'"
the returned record set seems wrong sometimes. i can not fetch the fields data. if i use this "rs.fields("PIN")" it gives an execption
sometimes it 's fine.
why?
|
|
|
|
|
Hi,
I was wondering whether it is possible to register for a DataTable events from a remote machine.
Lets say i have machine A , which has a DataSet , and let say also that i have machine B, which has a copy ( clone or cluster , i don’t know the right term for it ) and what I’m looking for is a way to know when something in the DataTable in machine A is occurred so i would update the DataTable in machine B.
I thought of registering machine B for the DadaTable events in machine A but i don't know how.
Do you think on a better solution for my problem?
Amir Harel
Why we always search for the generic solution for a specific problem...
|
|
|
|
|
I have trouble concatenating a SQL string and have it executed on the fly. The following is the code fragment extracted from inside a stored procedure:
...
... stored procedure ...
...
DECLARE @temp_table_name VARCHAR(50)
DECLARE @temp_table_sysname sysname
DECLARE @token VARCHAR(100)
SET @temp_table_name= '##SPVA12A4ACA2EBA74EC2B5856687E0FF42A8'
SET @temp_table_sysname = CAST( @temp_table_name AS sysname)
--OPTION 1: Does NOT work, error: "Must declare the variable '@temp_table_sysname'."
SELECT @token=strToken FROM @temp_table_sysname WHERE rowindex=1
--OPTION 2: Does NOT work, error: "Must declare the variable '@temp_table_name'."
SELECT @token=strToken FROM @temp_table_name WHERE rowindex=1
...
... stored procedure
...
NOTE:
1. I am aware of sp_executesql or EXEC(strSQL). But I need something that returns me @token.
2. I have to do this in stored procedure.. would avoid doing it in ASP - to avoid the messy scripts. Stored Procedure is more modular and framework offers more security (example: backup)...etc.
Hope to hear from you soon - I'm really stuck because of this and another ASP.NET problem.
norm
|
|
|
|
|
1)What must contain a ConnectionString?
2)How change a color in Excel table(cell, text) via VC++?
|
|
|
|
|
I don't have my development machine booted at the moment, but you may find the information on the following web-page useful:
http://support.microsoft.com/default.aspx?scid=KB;en-us;q278973[^]
It describes how to use the MS-Access jet OLE-DB database driver to access an Excel spreadsheet.
Note that it is not possible to change the colour of a spreadsheet cell via an OLE-DB driver. You would have to resort to OLE-Automation of the Excel application.
Hope this helps.
Andy
|
|
|
|
|
Hiya have just started to learn SQL and am having trouble with the INSERT and DELETE and EXECUTESQL statements. Can someone show me how to use these functions?? have tried but keep getting it wrong.
What I really need is a example program with some SQL statements being used.
I am using CDatabase and CRecordset for the database.
Thanks for your help.
grahamoj.
|
|
|
|
|
|
Thanks Alexandre, going to check those out now.
grahamoj.
|
|
|
|
|
I am trying to create a relational database for a product catalog. The problem is that there are two similar product types that have most of the same information but one product has two more fields of information than the other. What is the standard way to handle this situation? Do I create two seperate tables?
For example, let's say you have a catalog of books. But there are print books and e-books. Then let's say that the two book types have the same information except that the e-book also has a field for the number of downloads. Do you create a book table and an e-book table? This would seem to me to be extra work since the two book types only differ by one field. What do you think?
Thanks.
Mark Sanders
sanderssolutions.com
|
|
|
|
|
Mark Sanders wrote:
Do you create a book table and an e-book table?
Yes.
This would seem to me to be extra work since the two book types only differ by one field.
Still, does a "download count" have anything to do with the paper version? Of course not, hence it has nothing to do in the "book" table.
Possibly you'd have even three tables. One describing the "book", one describing the proprties of the paper version and one describing the properties of the "electronic only" version.
++luck;
|
|
|
|
|
Can you give me a little more info on modeling the three tables? Would a record in the "e-book properties" table have a FK which relates to the PK of the "book" table?
Mark Sanders
sanderssolutions.com
|
|
|
|
|
Mark Sanders wrote:
Would a record in the "e-book properties" table have a FK which relates to the PK of the "book" table?
Yep, that would have been my choice.
|
|
|
|
|
Since I would only want one record in the properties table to be associated with one record in the book table would the bookID actually be both a FK and a PK?
Mark Sanders
sanderssolutions.com
|
|
|
|
|
-= Lazy way =-
Since it is only one field, you can just have a nullable field for the number of downloads, and then add a constraint to validate that the download field is null if it is not an e-book.
-=Better way=-
But if you have many properties of a book, and many properties of an e-book some of which are the same,most of which are not. You may want to consider another table to hold these poperties, with a schema in the format: BookPK, property, value
-=Best way=-
Have three tables ... properties of paper books, properties of e-books, and a list of books
|
|
|
|
|
I think you would get different answers from different people. My personal tack on it is to keep all book data in a single table. Then you let your stored procedures or business rules layer handle figuring things out.
I would just create one table for all books and then create a second table that contained the book id and its download count. Every book won't have a download count, but that's ok. You let your app or stored proc figure that out. You could even keep a lookup of book types and a table of book IDs with their book type. Just do a join on the two tables to get all books of that type. That type will tell your app whether you should do a download count look up. This way, if you ever added a new book type, you don't have to add a new specific book table just to accomodate one or two new parameters. You already have one that suits your needs. You just create new tables with the book ID as the lookup mechanism.
This is just one guy's opinion on this. Take it or leave it. 
-Matt
------------------------------------------
The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
