Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!
--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.
|Display SQL maintenance plan SQL Code||3||47|
|TSQL remove duplicates from different columns||14||52|
|I am writing a Stored Procedure using SQL Server 2008. Do you know how I can resolve the error: Must declare the scalar variable "@sBank" ?||7||39|
|SqlServer amend PK column||5||8|
Join the community of 500,000 technology professionals and ask your questions.