SQL Command needed

[Lars]

Hi

It's been some years since I last coded in SQL. I need some help to recall
dome things.

I have a Access data table called installs with the fields

ID
uid
aid
program
release
installdate

ID is the primary key. I need to create a GridView in Visual Web Developer
using ASP.NET 3.5. The View should for a selected month show the daily
install number for every distinct/unique program. I need this to so our
affiliates can track the number of installs. Note there is no personal
information gathered from the users. I recall that I must create a data view
or some ting and then select from that one.

Can I create one SQL command for this or do I really need to create a data
table and for every ro make a special SQL command to add the number of
installs like "select count(*) from installs where program=@program,
aid=@aid, datum=@datum"


Lars

 

[Brendan Reynolds]

Hi

It's been some years since I last coded in SQL. I need some help to recall
dome things.

I have a Access data table called installs with the fields

ID
uid
aid
program
release
installdate

ID is the primary key. I need to create a GridView in Visual Web Developer
using ASP.NET 3.5. The View should for a selected month show the daily
install number for every distinct/unique program. I need this to so our
affiliates can track the number of installs. Note there is no personal
information gathered from the users. I recall that I must create a data
view or some ting and then select from that one.

Can I create one SQL command for this or do I really need to create a data
table and for every ro make a special SQL command to add the number of
installs like "select count(*) from installs where program=@program,
aid=@aid, datum=@datum"


Lars

If I understand the question correctly, I think the SQL part of your
solution will look something like this ...

SELECT Count(installs.ID) AS CountOfID, installs.program,
installs.installdate
FROM installs
GROUP BY installs.program, installs.installdate
HAVING (((installs.installdate)>=[Start Date] And
(installs.installdate)<CDate([End Date])+1));

When run in the Access UI, this query will prompt for the start date and end
date. To use it programatically, you'll need to assign values to the
parameters.

 

[John W. Vinson]

SELECT Count(installs.ID) AS CountOfID, installs.program,
installs.installdate
FROM installs
GROUP BY installs.program, installs.installdate
HAVING (((installs.installdate)>=[Start Date] And
(installs.installdate)<CDate([End Date])+1));

nitpick: I'd use WHERE (applied before the counting and summing) rather than
HAVING (applied after the counting and summing is all done).

 

[Brendan Reynolds]

SELECT Count(installs.ID) AS CountOfID, installs.program,
installs.installdate
FROM installs
GROUP BY installs.program, installs.installdate
HAVING (((installs.installdate)>=[Start Date] And
(installs.installdate)<CDate([End Date])+1));

nitpick: I'd use WHERE (applied before the counting and summing) rather
than
HAVING (applied after the counting and summing is all done).

Yes, good point, I agree that would be more efficient.