[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 335
  • Last Modified:

Count, GroupBy and where SQL Statement, without GroupBy

Hi all

I am having trouble in writing a SQL statement.  Actually don't know if it is possible.

What I am trying to do is count a number of records but group by 1 record but also have a date from which to start counting.

So, Table 1 = UserDetails
StaffID (Numeric,Unique Identifyer), DateStarted (DateTime)

Table 2 = DataWritten
RecordNumber (Numeric, Inique Identifyer), DateEntered (DateTime)

Table 3 = RecordsRead
RecorNumber (Numeric, Unique Identifyer), StaffID (Numeric), DateRead (DateTime)

So now, I want to
Count = RecordsRead.RecordNumber
GroupBy = RecordsRead.StaffID
Where RecordsRead.DateRead = UserDetails.DateStarted (But not GroupBy DateRead).

As stated earlier, not sure, if it is possible, but would make live a lot easier.

I will be displaying this on a web page ASP Classic if this helps.

Baker
0
Bakersville
Asked:
Bakersville
  • 3
  • 3
2 Solutions
 
imran_fastCommented:
you mean this


select count(RecordsRead.RecordNumber),RecordsRead.StaffID
from RecordsRead
inner join UserDetails
on RecordsRead.DateRead = UserDetails.DateStarted
group by RecordsRead.StaffID
0
 
imran_fastCommented:
or this
select count(RecordsRead.RecordNumber),RecordsRead.StaffID
from RecordsRead
inner join UserDetails
on RecordsRead.DateRead = UserDetails.DateStarted
and UserDetails.StaffID=RecordsRead.StaffID
group by RecordsRead.StaffID
0
 
MacNuttinCommented:
is RecorNumber a typo?
What does this give you?

select DateStarted,dw.RecordNumber,DateEntered,count(rr.RecordNumber) as NumCount,rr.StaffID,DateRead FROM RecordsRead rr left join UserDetails ud  on ud.StaffID = rr.StaffID join DataWritten dw on dw.RecordNumber = rr.recordnumber
0
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
BakersvilleAuthor Commented:
Thank you both for your quick replies.  Yes RecorNumber is a typo.

I will give it a go and let you know how i get on.

Thank again
Baker
0
 
BakersvilleAuthor Commented:
imran_fast, thanks again for this but on the Second option, it does count, but it counts the Records Read,RecordNumber not DataWritten.RecordNumber where > DataWritten.DateStarted.
I've tried to change it but my head hurts now.

MacNuttin, i've tried your but it comes up with a GroupBy clause error and once that fixed a where clause error.

Baker
0
 
imran_fastCommented:
You mean this

select count(DataWritten.RecordNumber),UserDetails.StaffID
from DataWritten
inner join UserDetails
on DataWritten.DateStarted < UserDetails.DateStarted
group by UserDetails.StaffID
0
 
BakersvilleAuthor Commented:
Thank you imran_fast, works perfectly.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now