Query help - dates and unique records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Query help - dates and unique records

Author

Topic

tech_1
Posting Yak Master

129 Posts

Posted - 2012-12-04 : 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?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-12-04 : 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

129 Posts

Posted - 2012-12-04 : 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
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-12-04 : 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

129 Posts

Posted - 2012-12-05 : 01:29:41

thanks Jim! ill give that a bash

tech_1
Posting Yak Master

129 Posts

Posted - 2012-12-09 : 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
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-09 : 10:21:44

Did you look Jimf Solution? he has provided that

tech_1
Posting Yak Master

129 Posts

Posted - 2012-12-09 : 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

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-09 : 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

129 Posts

Posted - 2012-12-10 : 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

129 Posts

Posted - 2012-12-10 : 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
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-10 : 07:38:28

Don't you have TrackId in TrackAudit Table? why joining based on url

tech_1
Posting Yak Master

129 Posts

Posted - 2012-12-10 : 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 :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources