Date table - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Date table

Author

Topic

cidr
Posting Yak Master

207 Posts

Posted - 2011-03-15 : 09:22:31

Hi there,

I know this is old ground but I seem to be struggling with this.

I'm trying to find a way of counting the amount of records in a table, I'll call tblRecord, that are created in a week from Saturday to Friday.

So I'd be grouping the calls between these days of the week and having the call count showing against the last day, which is the Friday

Please see sample data examples below:

The table I'm taking the data from would look like this


RecordID    LoggedDate   
987         5 Feb 2011
933         6 Feb 2011
784         6 Feb 2011
128         7 Feb 2011
748         8 Feb 2011
784         9 Feb 2011
745         10 Feb 2011
968         11 Feb 2011    --(8 record)
984         12 Feb 2011
156         13 Feb 2011
747         13 Feb 2011
852         13 Feb 2011
189         14 Feb 2011
365         15 Feb 2011
111         15 Feb 2011
164         16 Feb 2011
845         16 Feb 2011
945         17 Feb 2011
555         18 Feb 2011
645         18 Feb 2011    --(12 records)

etc etc....

I'd like the result to look like this:


RecordCount Week(LastdayFri)  
8           11 Feb 2011 --see above example
12          18 Feb 2011 --see above example
15          18 Feb 2011

Each row has counted all calls from the previous Saturday to, and including, Friday

I'm usure if I need to create a calendar table for this and then join tblRecord using the dates.

If someone could help me with this I'd be most grateful

Cheers

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-03-15 : 10:59:29

Google a report period calendar table.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

cidr
Posting Yak Master

207 Posts

Posted - 2011-03-15 : 11:09:13

thanks for the reply, I've tried a calendar table and it hasn't worked. Had a startdate and enddate. Start date being on the saturday and end date being on the friday. I then joined tblRecord to the Calendar using
LoggedDate >= Startdate and LoggedDate <= Endate.

I could get a count but not for the Friday as it was null on Fridays date.

Is there anymore input on this?

Cheers

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-03-15 : 12:07:19

Bet you used DATETIME and not DATE. And might not know the ISO-8601 week name convention:

CREATE TABLE Weekly_Report_Periods
(week_period CHAR(7)NOT NULL PRIMARY KEY
CHECK (week_period LIKE '[12][0-9][0-9][0-9]W[0-5][0-9]')
week_start_date DATE NOT NULL,
week_end_date DATE NOT NULL,
CHECK (week_start_date < week_end_date));

SELECT week_period, COUNT(*) AS period_cnt
FROM Weekly_Report_Periods, Log
WHERE log_date BETWEEN week_start_date AND week_end_date;

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

mikgri
Starting Member

39 Posts

Posted - 2011-03-15 : 12:43:47

If you don't like calendar, you can use something like:

declare @date1 datetime, @date2 datetime
select @date1= min(loggeddate) from tablename--get first date
declare @tbl table (id int identity, loggeddate datetime)
insert @tbl (loggeddate)
select distinct convert(varchar,loggeddate,101) from tablename
where datename(weekday, loggeddate)='Friday' order by convert(varchar,loggeddate,101) --ignore time, take only mm/dd/yyyy
declare @curid int, @maxid int
set @curid=1
select @maxid=max(id) from @tbl

--create temp table to collect data
if object_id('tempdb.dbo.#tbl') is not null drop table #tbl;
create table #tbl (recordcount int, [week] datetime);

while @curid<=@maxid
begin
select @date2=loggeddate from @tbl where id=@curid
insert #tbl (recordcount, [week])
select count(*), @date2 from tablename where convert(varchar,loggeddate,101) between @date1 and @date2
set @curid=@curid+1
set @date1=dateadd(dd,1,@date2)
end

select recordcount, convert(varchar, [week], 106) from #tbl;

cidr
Posting Yak Master

207 Posts

Posted - 2011-03-16 : 07:21:28

@jcelko, Firstly, thanks for the help here.
I'm not sure I understand. Of course I used DATETIME. I wasn't aware that DATE datatype could be used in SQL 2000. I looked at ISO-8601 week name convention but I'm unsure what you mean.

@mikgri, I tested your suggestion out and with a little work I may have been able to use it It depends on a friday being in existance. If there's no friday for that week it seems to collect all records from the previous - this means it's possible that the count could span weeks, months or years. However, I'm aware it was just an example of what I could use so thanks for that.

In the end I used a Auxilary numbers table to get weekend date etc. And for tblRecord I previously added 2-3 years worth of dates (FullDate in example below). I done a CROSS JOIN and that seems to be good enough for my purpose. This will allow me to group either by Weekend date or WeekPeriod



DECLARE @StartDate datetime
DECLARE @EndDate   datetime             
SET @StartDate = '2 jan 2010' 
SET @EndDate = '28 dec 2012' 
SET
	I.WeekendDate = W.Weekend,
	I.WeekNumber = W.WeekPeriod,
	I.FullYear = YEAR(I.Datefull)
FROM	
	 dbo.tblRecord I CROSS JOIN 
		(
		SELECT 
			DATEPART(ww, DATEADD(d,-1,(DATEADD(d,(N*7),@StartDate)))) 'WeekNo',
			YEAR(DATEADD(d,-1,(DATEADD(d,(N*7),@StartDate)))) 'YearNo',
			DATEADD(d,((N-1)*7),@StartDate) 'WeekStart',
			DATEADD(d,-1,(DATEADD(d,(N*7),@StartDate))) 'WeekEnd',
			CONVERT(CHAR(6),CONVERT(varchar(4),YEAR(DATEADD(d,-1,(DATEADD(d,(N*7),@StartDate))))) + 
				right('00'+CONVERT(varchar(2),DATEPART(ww, DATEADD(d,-1,(DATEADD(d,(N*7),@StartDate))))  ),2))'WeekPeriod'
		FROM 
			dbo.Nums
		WHERE DATEADD(d,((N-1)*7),@StartDate) < @EndDate
		)W
	WHERE (I.Datefull >= W.WeekEnd-6 and I.Datefull <= W.WeekEnd)

Sample data


FullDate        WeekEndDate     WeekPeriod 
2010-01-02 	2010-01-08 	201002
2010-01-03 	2010-01-08 	201002
2010-01-04 	2010-01-08 	201002
2010-01-05 	2010-01-08 	201002
2010-01-06 	2010-01-08 	201002
2010-01-07 	2010-01-08 	201002
2010-01-08 	2010-01-08 	201002
2010-01-09 	2010-01-15 	201003
2010-01-10 	2010-01-15 	201003
2010-01-11 	2010-01-15 	201003
2010-01-12 	2010-01-15 	201003
2010-01-13 	2010-01-15 	201003
2010-01-14 	2010-01-15 	201003
2010-01-15      2010-01-15 	201003

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-03-16 : 08:36:45

[code]-- Prepare sample data
DECLARE @Sample TABLE
(
RecordID INT,
LoggedDate DATETIME
)

INSERT @Sample
(
RecordID,
LoggedDate
)
SELECT 987, '5 Feb 2011' UNION ALL
SELECT 933, '6 Feb 2011' UNION ALL
SELECT 784, '6 Feb 2011' UNION ALL
SELECT 128, '7 Feb 2011' UNION ALL
SELECT 748, '8 Feb 2011' UNION ALL
SELECT 784, '9 Feb 2011' UNION ALL
SELECT 745, '10 Feb 2011' UNION ALL
SELECT 968, '11 Feb 2011' UNION ALL
SELECT 984, '12 Feb 2011' UNION ALL
SELECT 156, '13 Feb 2011' UNION ALL
SELECT 747, '13 Feb 2011' UNION ALL
SELECT 852, '13 Feb 2011' UNION ALL
SELECT 189, '14 Feb 2011' UNION ALL
SELECT 365, '15 Feb 2011' UNION ALL
SELECT 111, '15 Feb 2011' UNION ALL
SELECT 164, '16 Feb 2011' UNION ALL
SELECT 845, '16 Feb 2011' UNION ALL
SELECT 945, '17 Feb 2011' UNION ALL
SELECT 555, '18 Feb 2011' UNION ALL
SELECT 645, '18 Feb 2011'

-- Solution by Peso starts here
SELECT DATEADD(DAY, 7 * theWeek, '19000105') AS theDay,
SUM(theCount) AS theSum
FROM (
SELECT DATEDIFF(DAY, '18991230', LoggedDate) / 7 AS theWeek,
COUNT(*) AS theCount
FROM @Sample
GROUP BY DATEDIFF(DAY, '18991230', LoggedDate) / 7
) AS d
GROUP BY theWeek
ORDER BY theWeek[/code]

N 56°04'39.26"
E 12°55'05.63"

cidr
Posting Yak Master

207 Posts

Posted - 2011-03-16 : 12:32:18

@Peso, haha, spot on... this is a lot more simplified than what I had... thanks for this!!