| Author |
 Topic  |
|
|
tech_1
Posting Yak Master
105 Posts |
 Posted - 12/04/2012 : 15:01:10
|
Note: The date format is dd/MM/yyyy (UK datetime format)
I have a table which contains the following fields:
AccessID, DateTime, URL, TrackID, IPAddress
TrackID is a FK to another table.
I want to be able to pull back data for a period of time/quarterly for example:
01/01/xx to 31/03/xx
01/04/xx to 30/06/xx
01/07/xx to 30/09/xx
01/10/xx to 31/12/xx
then within each of the periods (lets take the first one), I want to find the number of times a trackID has been stored/recorded in the table against the current date (01/01/xx, 02/01/xx, 03/01/xx, 04/01/xx)
typical output:
ID, Date, #, trackName
-- ---------- -- ----------
245, 01/01/2012, 4, trackB.mp3
312, 01/01/2012, 3, trackA.mp3
433, 01/01/2012, 9, trackF.mp3
100, 02/01/2012, 2, trackA.mp3
983, 02/01/2012, 10, trackD.mp3
The way the data is stored in the table is simple really:
AccessID, DateAccessed, TrackID
1, 01/01/2012, 1
2, 01/01/2012, 3
3, 01/01/2012, 5
9, 02/01/2012, 9
does this make sense? |
Edited by - tech_1 on 12/04/2012 15:03:29
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 12/04/2012 : 15:10:38
|
This will give you the first and last days of a quarter, if that's what you're looking for
SELECT DATEADD(q,DATEDIFF(q,0,'30/01/2012'),0),DATEADD(q,DATEDIFF(q,-1,'30/01/2012'),-1)
Jim
Everyday I learn something that somebody else already knew |
 |
|
|
tech_1
Posting Yak Master
105 Posts |
Posted - 12/04/2012 : 16:44:01
|
thanks Jim.
ok, going to re-write my question somewhat... its a little easier:
basically, for a given period (startdate/enddate I guess), I want to know:
the number of tracks being played within that period of time. |
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 12/04/2012 : 17:36:13
|
SELECT TrackId,DATEADD(q,DATEDIFF(q,0,DateAccessed),0),DATEADD(q,DATEDIFF(q,-1,DateAccessed),-1) as Qtr
, Count(*)
FROM yourTable
GROUP BY TrackID,DATEADD(q,DATEDIFF(q,0,DateAccessed),0),DATEADD(q,DATEDIFF(q,-1,DateAccessed),-1)
If the period of time is supplied be variables, say @FromDate, @ToDate, then maybe just this
SELECT TrackID,Count(*)
FROM yourTable
WHERE DateAccessed >= '@FromDate and DAteAccessed <= @ToDate
GROUP BY TrackID
Jim
Jim
Everyday I learn something that somebody else already knew |
|
|
|
tech_1
Posting Yak Master
105 Posts |
Posted - 12/05/2012 : 01:29:41
|
| thanks Jim! ill give that a bash |
|
|
|
tech_1
Posting Yak Master
105 Posts |
Posted - 12/09/2012 : 10:06:32
|
ok how about this:
for a given period (From and to), I want records showing the total (count) of tracks which are recorded for that period?
|
|
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 12/09/2012 : 10:21:44
|
| Did you look Jimf Solution? he has provided that |
|
|
|
tech_1
Posting Yak Master
105 Posts |
Posted - 12/09/2012 : 15:11:27
|
ah yes, sorry. i seemed to have missed that. but there are 2 tables which are needing to be joined, surely this would show all the entries in a duplicate manner?
TrackAudit: AuditID, DateAccessed, URL
Tracks: TrackID, TrackName, TrackURL
This does not quite work for me:
declare @startdate datetime
declare @enddate datetime
set @startdate = '2012-08-01'
set @enddate = '2012-08-31'
SELECT t.TrackID, t.TrackName, Count(t.trackName) AS [NumberOfPlays], t.TrackURL , ta.AuditID, ta.TimeOfEntry
FROM TrackAudit ta
INNER JOIN Tracks t ON
ta.URL = t.TrackURL
where ta.TimeOfEntry between @startdate and @enddate
group by t.trackID, t.TrackName, t.TrackURL, ta.AuditID, ta.TimeOfEntry
order by t.TrackURL asc
|
Edited by - tech_1 on 12/09/2012 15:17:47 |
|
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 12/09/2012 : 20:16:17
|
quote:
Originally posted by tech_1
ah yes, sorry. i seemed to have missed that. but there are 2 tables which are needing to be joined, surely this would show all the entries in a duplicate manner?
TrackAudit: AuditID, DateAccessed, URL
Tracks: TrackID, TrackName, TrackURL
Maybe this one:
This does not quite work for me:
declare @startdate datetime
declare @enddate datetime
set @startdate = '2012-08-01'
set @enddate = '2012-08-31'
SELECT t.TrackID, t.TrackName, Count(distinct t.trackName) AS [NumberOfPlays], t.TrackURL
FROM TrackAudit ta
INNER JOIN Tracks t ON ta.URL = t.TrackURL
Where ta.TimeOfEntry between @startdate and @enddate
Group by t.trackID, t.TrackName, t.TrackURL
Order by t.TrackURL asc
|
|
|
|
tech_1
Posting Yak Master
105 Posts |
Posted - 12/10/2012 : 05:29:55
|
thanks sodeep but not quite. it now does not show duplicates (which is good) but always shows "1" for NumberOfPlays.
I want to show the Number of times a track appears in the recordset.... so for example if the query (without distinct) gives me "Elton John" 4 times, i want it to show Elton John once and show "4" in the Number Of Plays column |
|
|
|
tech_1
Posting Yak Master
105 Posts |
Posted - 12/10/2012 : 05:32:14
|
I think this seems to work now:
declare @startdate datetime
declare @enddate datetime
set @startdate = '2012-08-01'
set @enddate = '2012-08-31'
SELECT t.TrackID, t.TrackName, Count(.trackName) AS [NumberOfPlays], t.TrackURL
FROM TrackAudit ta
INNER JOIN Tracks t ON ta.URL = t.TrackURL
Where ta.TimeOfEntry between @startdate and @enddate
Group by t.trackID, t.TrackName, t.TrackURL
Order by t.TrackURL asc |
|
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 12/10/2012 : 07:38:28
|
| Don't you have TrackId in TrackAudit Table? why joining based on url |
|
|
|
tech_1
Posting Yak Master
105 Posts |
Posted - 12/10/2012 : 07:51:29
|
because there is no trackID in the audit table unfortunately.
I will be adding this later - it is not my project/design :) |
|
|
| |
Topic |
|
|
|
Query help - dates and unique records
