My table is test and I have an ID and DateTest columns

I would like to count the weeks with more then one record.

So far I got this and return the weeks with 1 record per week. How can I count the weeks with more then one record

select sum(c)
from (
    select c = count( id) over (partition by id, datepart(week, DateTest))
    from test  where id = '1' and DateTest >= '7-7-2015'
) a where c = 1

Can you post a sample of the table being used?

TableName test
id      DateTest
1       7-7-2015    
2       6-23-2015
3       6-12-2015
1       7-8-2015
1       7-14-2015    
1       7-21-2015    
1       7-27-2015
1       7-28-2015
1       7-29-2015
1       7-30-2015
1       8-3-2015     
1       8-11-2015 

• Edited by Lio1972 14 hours 41 minutes ago

create table test (id int,  DateTest date)
insert into test values 
(1,'7-7-2015'),(2,'6-23-2015'),(3,'6-12-2015'),(1,'7-8-2015')
,(1,'7-14-2015'),(1,'7-21-2015'),
(1,'7-27-2015'),(1,'7-28-2015'),
(1,'7-29-2015'),(1,'7-30-2015'),
(1,'8-3-2015'),(1,'8-11-2015')

   ;with mycte as ( select id, dense_rank() over (partition by id, year(Datetest),
    datepart(week, DateTest) Order by Datetest) rn
    from test 
    where id = '1' and DateTest >= '7-7-2015')
    Select count(*) from mycte where rn=2
--or

select count(*) cnt from (
select count(*) row from test
  
    where id = '1' and DateTest >= '7-7-2015'
    group by id,  year(Datetest)+ datepart(week, DateTest)*100
   Having(count( year(Datetest)+ datepart(week, DateTest)*100))>1
  ) t

• Proposed as answer by Naomi NModerator 4 hours 26 minutes ago

Hi Lio1972,

This should work for you :)

   WITH T1 as ( select id, DateTest, ROW_NUMBER() over (partition by id, year(Datetest),
    datepart(week, DateTest) Order by Datetest) ROWN
    from test 
    where id = '1' and DateTest >= '7-7-2015')
Select COUNT(1) from T1 where ROWN=2
    Select * from T1 where ROWN=2

Thank you very much

My goal is to create a stored procedure that is going to take a datetime parameter and is going to return true if there are four weeks without a record or with less then one record in the database. If I enter 6-22-2105 the procedure will return true. If I enter 6-29-2015 will return false. I was planning to calculate the weeks between the passed date and the today's date then subtract the weeks with more then one record. The highlighted dates are my records in the database. The weeks with a blue line are with less then two records.

Do you think that there is a better way

if there are four weeks without a record or with less then one record in the database.

You mean "less than two", I presume?

If I enter 6-22-2105 the procedure will return true. If I enter 6-29-2015 will return false.

Why? Assuming that you mean the four weeks following the date, both would return false since the week starting June 6th has two dates.

What is your exact definition of a week. If the given date is a Wednesday, is that weeks starting on Wednessay? Or does your week always start on a certain day of the week? In such case, which day? Sunday or Monday?

TableName test
id      DateTest
1       7-7-2015    
2       6-23-2015
3       6-12-2015
1       7-8-2015
1       7-14-2015    
1       7-21-2015    
1       7-27-2015
1       7-28-2015
1       7-29-2015
1       7-30-2015
1       8-3-2015     
1       8-11-2015 

• Edited by Lio1972 Saturday, August 22, 2015 4:26 PM

select count(*) cnt from (
select count(*) row from test
  
    where id = '1' and DateTest >= '7-7-2015'
    group by id,  year(Datetest)+ datepart(week, DateTest)*100
   Having(count( year(Datetest)+ datepart(week, DateTest)*100))>1
  ) t

• Proposed as answer by Naomi NModerator Sunday, August 23, 2015 2:41 AM

Thank you

My week definition is from Sunday to Sunday

And yes I meant less then two

• Edited by Lio1972 4 hours 32 minutes ago

Thank you

My week definition is from Sunday to Sunday

And yes I meant less then two

• Edited by Lio1972 Sunday, August 23, 2015 2:35 AM

My week definition is from Sunday to Sunday

But the parameter date you gave was a Monday...

Oh well, this may do:

SELECT CASE WHEN EXISTS (SELECT 1
                         FROM   tbl
                         WHERE  datepart(week, datecol) BETWEEN
                                datepart(week, @date) AND datepart(week, @date) + 3
                        GROUP   BY datecol
                        HAVING  COUNT(*) > 1)
            THEN 1
            ELSE 0
       END

Thank you for the help.

Your select returns 0 no matter what is the '@date' parameter.

I will convert the parameter to be Sunday :

dateadd(week, datediff(week, 0, @date), -1)

So Far I got this.

CREATE PROC spTest @DateInput datetime, @Confirm bit output
AS
	BEGIN
	DECLARE @MoreThenTwo INT, @WeeksTotal INT; 
	WITH t1 AS ( SELECT id, datetest, ROW_NUMBER() OVER (PARTITION BY id, YEAR(datetest), DATEPART(WEEK, datetest) 
	ORDER BY datetest) rown FROM test WHERE id = '1' AND datetest >= DATEADD(WEEK, DATEDIFF(WEEK, 0, @DateInput), -1) ) 
	SELECT @MoreThenTwo=COUNT(1) FROM t1 WHERE rown=2 
	SELECT @WeeksTotal = DATEPART(WEEK, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), -1)) - DATEPART(WEEK, DATEADD(WEEK, DATEDIFF(WEEK, 0,@DateInput), -1))
	IF @WeeksTotal - @MoreThenTwo >=4
		BEGIN
			SET @Confirm = 1
		END 
		ELSE
		BEGIN
			SET @Confirm = 0
		END 
	END

• Edited by Lio1972 20 hours 5 minutes ago

Your select returns 0 no matter what is the '@date' parameter.

I was a little too quick there. It should be:

SELECT CASE WHEN EXISTS (SELECT 1
                         FROM   tbl
                         WHERE  datepart(week, datecol) BETWEEN
                                datepart(week, @date) AND datepart(week, @date) + 3
                        GROUP   BY datepart(week, datecol)
                        HAVING  COUNT(*) > 1)
            THEN 1
            ELSE 0
       END

However, I realise that this will not work well around New Year. If we assume that @date is normalised to Sunday, we can fix the interval this way:

SELECT CASE WHEN EXISTS (SELECT 1
                         FROM   tbl
                         WHERE  datecol BETWEEN @date AND dateadd(DAY, 27, @date)
                         GROUP   BY datepart(week, datecol)
                         HAVING  COUNT(*) > 1)
            THEN 1
            ELSE 0
       END

However, since datepart(week) will return 53 or 54 for 1231 and 1 for 0101, the above will not produce correct result for the week of Jan 1st (except when Jan 1st is Sunday.) datepart(iso_week) does not have this problem, but with iso_week the week starts on Monday.

But if I my thinking is correct, this should work:

SELECT CASE WHEN EXISTS (SELECT 1
                         FROM   tbl
                         WHERE  datecol BETWEEN @date AND dateadd(DAY, 27, @date)
                         GROUP   BY datepart(iso_week, dateadd(DAY, 1, datecol))
                         HAVING  COUNT(*) > 1)
            THEN 1
            ELSE 0
       END

Please test this!