Sql using function

Question

how to add a analytical function to check if there is 7 days between the current record and the next one

i need to add it to the below query :

select w.user_id,count (w.weigh_in_date) from table_name1 w
--join mrana.users u
where w.COUNTRY_ID = 1
and user_id in (select user_id from table_name2 where country_id = 1
and USER_STATUS_TYPE_ID = 1
and MEMBER_TYPE_ID =1
and USER_TYPE_ID = 2)
group by (w.user_id)
having count (w.weigh_in_date) between 14 and 15

Answer 1

in my case i need to get the first 14 or 15 records for the member, where has 7 days differance between each 2 consecutive records
ex:
1/9
8/9
15/9
22/9
29/9
till record 14 or 15

Answer 2

So there are records on 2/9 and 3/9 but you do not want those because you need a 7 days interval in between records.

Do you need to do this totally in sql or can you use c# or another ide.

Which sql do you use ?

Answer 3

yes i dont want in between dates, and need to use only sql.

using sql server (Toad)

Answer 4

Does every day has a record or can there be an 6 or 8 day interval ?

Answer 5

possibly it will have skipped dates more than inbetween
like 1/9 - 15/9

Answer 6

This is a very difficult query.

Because you want to filter the database based upon data from other records.

In sql you can substract dates, compare dates, but everything is in avaiable in the row itself.
You cannot look to the previous or the next record.
As far a I know there are no loops in sql.

I can think of 1 work arround :

Where weekday is monday (or another day) using datepart
datepart(dw,[Date]) = 1 (1 = monday)
now you have all the records on monday, so there are with a 7 day interval

To get the last 15 records you can use TOP
SELECT TOP 15 FROM table

Answer 7

i will check this solution and inform you.
but i think there is an analytical functions to do such comparison.

Answer 8

Please do. I am really interrested in the answer.

Answer 9

You can subtract dates

I used the northwind database to test my statement

select * from Employees where HireDate-'1992-05-04' < 7

This is the result
EmployeeID LastName HireDate
1 Davolio 1992-05-01 00:00:00.000
3 Leverling 1992-04-01 00:00:00.000