Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users
--Declare Start and End variables (these can also be passed into a stored procedure) DECLARE @startdate smalldatetime, @endDate smalldatetime --this drops the hours and minutes and sets them to 00:00 Set @startdate = convert(varchar,'Nov 1, 2008' ,111) Set @endDate = convert(varchar,'Dec 31 2008' ,111) --Declare EnD Date tmp variable to be used in the loop DECLARE @tmpEndDate smalldatetime --Declare tmp table to act as a temporary holding place for your data DECLARE @tmpTable TABLE( StartDate smalldatetime, EndDate smalldatetime, noemp integer) --Set @startdate to beginning of month span Set @startdate = DATEADD(dd,-(DAY(DATEADD(m,1,@startdate))-1),@startdate) --set @endDate to end of month span Set @endDate = DATEADD(dd, -DAY(DATEADD(m,1,@endDate)), DATEADD(m,1,@endDate)) --loop through each month While @startdate < @endDate BEGIN --set @tmpEndDate to last minute of month Set @tmpEndDate = dateadd(n,-1,dateadd(m,1,@startdate)) --insert values in table Insert into @tmpTable(StartDate,EndDate,noemp) Select @startdate as startdate, @tmpEndDate as enddate, NoEmployees as noemp from CTContracts Where Fromdate <= @tmpEndDate And EndDate >= @startdate and LKContractTypeID = 8 --increment @startdate Set @startdate = dateadd(m,1,@startdate) END --Now you can do what you want with the results Select * from @tmpTable Select Min(StartDate) as StartDate, Max(EndDate) as EndDate, Sum(noemp) as noemp from @tmpTable
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
Join the community of 500,000 technology professionals and ask your questions.