Order by particale date

[telford99]

Can someone please help me with this. I'm very new to SQL

I've got a DB with a couple of field workstationID Date added, notes.

I need to collate the information into days.

ie we added X workstation on this day.

So for each day I can get the total workstations added

Is this possible, please help.

Thank you in advance.

 

[r937]

yes, it's possible

whay database system are you using?

r937.com | rudy.ca

 

[telford99]

MS Access

Don't judge me ;-)
Forced to!

 

[Golom]

In Access dialect

Select DateAdded, Count(*) As [WorkStations Added]
From TheTable
Group By DateAdded

Assuming that "DateAdded" is a "Date Only" field and not a "Date + Time" field.

 

[telford99]

Thats great, however the date field is date and time, and I only want to organise by day and months not time.

Thank you in advance.

 

[PHV]

SELECT Format(DateAdded, 'dd/mm/yyyy'), Count(*) As [WorkStations Added]
FROM TheTable
GROUP BY Format(DateAdded, 'dd/mm/yyyy')

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[telford99]

OK Thanks for all the great answers.

I need to count the numbers of machines between two times on a range of days.

ie. Between 21:00 and 06:00 (next day) X computers were added and I need this for a range of dates ie between 21/07/2006 - 30/08/2006.

Is this possible? Don't know if this helps but the date format is - dd/mm/yyyy hh:mm:ss

Thanks you in advance.

 

[Golom]

Haven't tested this but give it a try

Select A.TheDate, Count(*) As [Machines Added]

From (Select DISTINCT cDate(Int(A.DateAdded)) As TheDate From TheTable) As A 
     INNER JOIN
     TheTable As B
     ON A.TheDate = cDate(Int(B.DateAdded))

WHERE B.DateAdded BETWEEN
      A.TheDate     + TimeValue("21:00:00")
  AND A.TheDate + 1 + TimeValue("06:00:00"))

Group By A.TheDate

 

[Golom]

Not quite I guess ... Maybe a cross join

Select A.TheDate, Count(*) As [Machines Added]

From (Select DISTINCT cDate(Int(A.DateAdded)) As TheDate From TheTable) As A, 
     TheTable As B

WHERE B.DateAdded BETWEEN
      A.TheDate     + TimeValue("21:00:00")
  AND A.TheDate + 1 + TimeValue("06:00:00"))

Group By A.TheDate

 

[telford99]

Not sure if i'm replacing everything correctly with the correct names.

------------------
From (Select DISTINCT cDate(Int(A.DateAdded)) As TheDate From TheTable) As A,
TheTable As B
------------------

but A.DateAddedd, was this supposed to be TheTable.DateAdded, because thats the only way i can get it to work.

Also the results were completly spurious.

Thank you in advance

 

[Golom]

Yes it should have been TheTable.DateAdded (sorry about that.)

What results are you getting? Too many records returned? Too few? Incorrect counts? The wrong records? No records at all?

 

[telford99]

Thank you for quick responce.

The count seems very high.

Cheers

 

[Golom]

Pick a specific date that looks suspicious and run this

Select DateAdded

FROM   TheTable 

WHERE cDate(Int(DateAdded)) = #[red]???[/red]#
  AND DateAdded BETWEEN
      cDate(Int(DateAdded)) + TimeValue("21:00:00")
  AND cDate(Int(DateAdded)) + TimeValue("06:00:00") + 1

Substitute the date you want to look at for [red]???[/red].

It should show you all the records for the date you selected that are between 9 PM on that day and 6 AM the following day. Compare that with the results from the first query.

 

[telford99]

No results are appearing after midnight.

 

[Golom]

OK. It's a bit more convoluted than I thought. Try this

Select G.[The Date], Count(*) As [Number of Machines Added]

FROM

(

SELECT DateValue(A.DateAdded) AS [The Date],  Q.DateAdded

FROM aDates A INNER JOIN

(Select DateValue(DateAdded) As TempDate, X.DateAdded From aDates X
Where TimeValue(X.DateAdded) BETWEEN TimeValue("21:00:00") AND TimeValue("23:59:59")) As Q

ON DateValue(A.DateAdded) = Q.TempDate

UNION

SELECT DateValue(A.DateAdded) AS [The Date], Q.DateAdded

FROM aDates A INNER JOIN

(Select DateValue(DateAdded)-1 As TempDate, X.DateAdded  From aDates X
Where TimeValue(X.DateAdded) BETWEEN TimeValue("00:00:00")  and TimeValue("06:00:00")) As Q

ON DateValue(A.DateAdded)  = Q.TempDate

) As G

GROUP BY G.[The Date]