SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Query help - dates and unique records
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tech_1
Posting Yak Master

126 Posts

Posted - 12/04/2012 :  15:01:10  Show Profile  Reply with Quote
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
2869 Posts

Posted - 12/04/2012 :  15:10:38  Show Profile  Reply with Quote
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
Go to Top of Page

tech_1
Posting Yak Master

126 Posts

Posted - 12/04/2012 :  16:44:01  Show Profile  Reply with Quote
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.
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 12/04/2012 :  17:36:13  Show Profile  Reply with Quote
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
Go to Top of Page

tech_1
Posting Yak Master

126 Posts

Posted - 12/05/2012 :  01:29:41  Show Profile  Reply with Quote
thanks Jim! ill give that a bash
Go to Top of Page

tech_1
Posting Yak Master

126 Posts

Posted - 12/09/2012 :  10:06:32  Show Profile  Reply with Quote
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?

Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/09/2012 :  10:21:44  Show Profile  Reply with Quote
Did you look Jimf Solution? he has provided that
Go to Top of Page

tech_1
Posting Yak Master

126 Posts

Posted - 12/09/2012 :  15:11:27  Show Profile  Reply with Quote
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
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/09/2012 :  20:16:17  Show Profile  Reply with Quote
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


Go to Top of Page

tech_1
Posting Yak Master

126 Posts

Posted - 12/10/2012 :  05:29:55  Show Profile  Reply with Quote
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
Go to Top of Page

tech_1
Posting Yak Master

126 Posts

Posted - 12/10/2012 :  05:32:14  Show Profile  Reply with Quote
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
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/10/2012 :  07:38:28  Show Profile  Reply with Quote
Don't you have TrackId in TrackAudit Table? why joining based on url
Go to Top of Page

tech_1
Posting Yak Master

126 Posts

Posted - 12/10/2012 :  07:51:29  Show Profile  Reply with Quote
because there is no trackID in the audit table unfortunately.
I will be adding this later - it is not my project/design :)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.31 seconds. Powered By: Snitz Forums 2000