Displaying the number of valid results for a date range
I currently have a table with a creation date and an expiration date. I currently have a sql command to get the number of valid items for a given date.
select
count(id) ,CONVERT(date, getdate())
from
table
where
createDate < getdate() and expDate > getdate()
This returns the count and the current date.
Is it possible to write a sql query that will return a result for a range of dates, say if I want to plot the number of valid items over 15 days?
Thank!
+3
source to share
2 answers
Try the following:
create table #datelist (ValidDateCheck date, ValidResults int)
declare @startdate date = '1/1/2015'
declare @enddate date = '2/1/2015'
declare @interval int = 1 --Use 1 if you want every day between your dates, use 2 if you want every other day, etc.
declare @datecounter date = @startdate
declare @count int
while @datecounter <= @enddate
begin
set @count =
(select count(*)
from Table
where CrtDt <= @datecounter and ExpDt > @datecounter)
insert into #datelist values (@datecounter, @count)
set @datecounter = dateadd(dd, @interval, @datecounter)
end
select * from #datelist order by 1
It iterates over all the dates in your range, counting valid results for each one.
+2
source to share
Check it,
DECLARE @StartDate DATETIME,
@EndDate DATETIME;
SELECT @StartDate = '20110501'
,@EndDate = '20110801';
SELECT
DATEADD(d, x.number, @StartDate) AS MonthName1
,
x.number
FROM master.dbo.spt_values x
WHERE x.type = 'P'
AND x.number <= DATEDIFF(MONTH, @StartDate, @EndDate);
The above query gives a list of dates between two dates.
According to your table and question, check this as well.
declare @table table(id int,frdt datetime, todt datetime)
insert into @table values (1,GETDATE()-20, GETDATE()-19)
,(2,GETDATE()-9, GETDATE()-8)
,(3,GETDATE()+20, GETDATE()+18)
,(4,GETDATE(), GETDATE()-1)
,(5,GETDATE()-20, GETDATE())
,(6,GETDATE()-10, GETDATE()+10 )
select * from @table
declare @frdt datetime = null , @todt datetime = getdate()-10
select @frdt, @todt,* from @table
where
(@frdt is null or @frdt between frdt and todt)
and
(@todt is null or @todt between frdt and todt)
select @frdt = GETDATE()-15 , @todt = GETDATE()
select @frdt, @todt,* from @table
where
(@frdt is null or @frdt between frdt and todt)
and
(@todt is null or @todt between frdt and todt)
+2
source to share