Count help based on between dates

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

Transact-SQL (2005)

Count help based on between dates

Author

Topic

learntsql

524 Posts

Posted - 2009-12-10 : 04:14:29

Hi..
I have two tables one is periodDates another is PubBooks

This is the logic used to build to generate weekdates for specified weekcounter selection.
-----------------------------------------------------------------
declare @start_week datetime
declare @end_week datetime
declare @tabDates table(ID int IDENTITY,StartDate datetime,EndDate datetime)
declare @iStartDay int,@iNextDay int,@iInitCtr int,@iWeekCtr int

SET @iInitCtr=1
SET @iWeekCtr =8
SET @iStartDay =0
SET @iNextDay=6

SET @start_week = dateadd(d, 1-DATEPART(dw, getdate()), getdate())
--set @end_week = dateadd(d,6,@start_week )
WHILE (@iInitCtr<=@iWeekCtr)
BEGIN
INSERT INTO @tabDates
SELECT @start_week+@iStartDay ,@start_week+@iNextDay
SELECT @iInitCtr=@iInitCtr+1,@iStartDay=@iStartDay+7,@iNextDay=@iNextDay+7
END

select * from @tabDates
----------------------------------------------------------------
And PubBooks table contains
ID||BookID||PublishDate
1 B1 06-Dec-2009
2 B2 09-Dec-2009
3 B3 20-Dec-2009
4 B2 18-Dec-2009
.....
.....
BookIDs are repeated.
My requirement is to count the number of books published for each row in PeriodDates(Startdate and EndDate) from PubBooks table.

output like as
06-Dec-2009 to 12-Dec-2009 || 13-Dec-2009 to 19-Dec-2009 etc...
2 1

....
plz. help...

Sachin.Nand

2937 Posts

Posted - 2009-12-10 : 05:07:17

Maybe u will have to maniupulate it a little the way I did


declare @start_week datetime
declare @end_week datetime
create table  #tabDates (ID int IDENTITY,StartDate datetime,EndDate datetime,WeekDates varchar(40))
declare @iStartDay int,@iNextDay int,@iInitCtr int,@iWeekCtr int

SET @iInitCtr=1
SET @iWeekCtr =8
SET @iStartDay =0
SET @iNextDay=6

SET @start_week = dateadd(d, 1-DATEPART(dw, getdate()), getdate()) 

WHILE (@iInitCtr<=@iWeekCtr)
BEGIN
INSERT INTO #tabDates

SELECT @start_week+@iStartDay ,@start_week+@iNextDay,NULL
SELECT @iInitCtr=@iInitCtr+1,@iStartDay=@iStartDay+7,@iNextDay=@iNextDay+7
END


update #tabDates set weekdates=convert(varchar(40),StartDate) + ' ' +  convert(varchar(40),EndDate)
select * from #tabDates
declare @colList varchar(8000)
select @colList = coalesce(@colList + ', [' + WeekDates + ']', '[' + WeekDates + ']') from #tabDates group by WeekDates
print @colList
exec('
select ' + @colList + '
from   (select  WeekDates ,id, row_number() over (partition by id order by id) as rn from #tabDates) t
pivot (max(id) for WeekDates in (' + @colList + ')) as p')

	drop table #tabDates

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources