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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tech_1
Posting Yak Master

129 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
2875 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

129 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
2875 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

129 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

129 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

129 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

129 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

129 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

129 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  
 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.67 seconds. Powered By: Snitz Forums 2000