SQLTeam.com | Weblogs | Forums

Help Needed in Date Logic


#1

Hi,

I will need to create query for uservisit tracking. Below are the sample data to play with

[Code]
Declare @UserVisit table(userid bigint,VisitedDate datetime)

insert into @UserVisit

select 1410089 , '2017-06-28 21:35:37' union all
select 1606546 , '2017-06-28 21:35:37' union all
select 1606546 , '2017-06-28 21:35:37' union all
select 1590814 , '2017-06-28 21:35:37' union all
select 1581326 , '2017-06-28 21:35:37' union all
select 1582858 , '2017-06-28 21:35:37' union all
select 1590814 , '2017-06-28 21:35:37' union all
select 1582858 , '2017-06-28 21:35:37' union all
select 1606546 , '2017-06-28 21:35:37' union all
select 1606546 , '2017-06-28 21:35:37' union all
select 1606546 , '2017-06-28 21:35:37' union all
select 1601256 , '2017-06-28 21:35:37' union all
select 1601256 , '2017-06-28 21:35:37' union all
select 1601256 , '2017-06-28 21:35:37' union all
select 1601256 , '2017-06-28 21:35:37' union all
select 1601256 , '2017-06-28 21:35:37' union all
select 1582858 , '2017-06-28 21:35:37' union all
select 1590814 , '2017-06-28 21:35:37' union all
select 1590814 , '2017-06-28 21:35:37' union all
select 1590814 , '2017-06-28 21:35:37' union all
select 1582858 , '2017-06-28 21:35:37' union all
select 1410130 , '2017-06-28 21:35:37' union all
select 1601082 , '2017-06-28 21:35:37' union all
select 1581326 , '2017-06-28 21:35:37' union all
select 1581326 , '2017-06-28 21:35:37' union all
select 1957484 , '2017-06-28 21:35:37' union all
select 1606546 , '2017-06-28 21:35:37' union all
select 1597714 , '2017-06-28 21:35:37' union all
select 1957484 , '2017-06-28 21:35:37' union all
select 1597714 , '2017-06-28 21:35:37' union all
select 1410089 , '2017-06-28 21:35:37' union all
select 1606546 , '2017-06-28 21:35:37' union all
select 1606546 , '2017-06-28 21:35:37' union all
select 1582858 , '2017-06-28 21:35:37' union all
select 1581326 , '2017-06-28 21:35:37' union all
select 1590814 , '2017-06-28 21:35:37' union all
select 1606546 , '2017-06-28 21:35:37' union all
select 1582469 , '2017-06-28 21:35:37' union all
select 1957484 , '2017-06-28 21:35:37' union all
select 1597714 , '2017-06-28 21:35:37' union all
select 1597714 , '2017-06-28 21:35:37' union all
select 2348169 , '2017-06-28 21:35:37' union all
select 1579074 , '2017-06-28 21:35:37' union all
select 1570589 , '2017-06-28 21:35:37' union all
select 2212302 , '2017-06-28 21:35:37' union all
select 2597386 , '2017-06-28 21:35:37' union all
select 1563847 , '2017-06-28 21:35:37' union all
select 2642440 , '2017-06-28 21:35:37' union all
select 1587938 , '2017-06-28 21:35:37' union all
select 1565146 , '2017-06-28 21:35:37' union all
select 1410071 , '2017-06-28 21:35:37' union all
select 1587938 , '2017-06-28 21:35:37' union all
select 1592794 , '2017-06-28 21:35:37' union all
select 2255913 , '2017-06-28 21:35:37' union all
select 1603226 , '2017-06-28 21:35:37' union all
select 2200971 , '2017-06-28 21:35:37' union all
select 2212812 , '2017-06-28 21:35:37' union all
select 2179954 , '2017-06-28 21:35:37' union all
select 1588403 , '2017-06-28 21:35:37' union all
select 1410135 , '2017-06-28 21:35:37' union all
select 1584246 , '2017-06-28 21:35:37' union all
select 1709745 , '2017-06-28 21:35:37' union all
select 1410083 , '2017-06-28 21:35:37' union all
select 2597386 , '2017-06-28 21:35:37' union all
select 2212470 , '2017-06-28 21:35:37' union all
select 1605289 , '2017-06-28 21:35:37' union all
select 1570871 , '2017-06-28 21:35:37' union all
select 1594321 , '2017-06-28 21:35:37' union all
select 1594298 , '2017-06-28 21:35:37' union all
select 2705791 , '2017-06-28 21:35:37' union all
select 1594321 , '2017-06-28 21:35:37' union all
select 2597386 , '2017-06-28 21:35:37' union all
select 1583968 , '2017-06-28 21:35:37' union all
select 2219054 , '2017-06-28 21:35:37' union all
select 1579074 , '2017-06-28 21:35:37' union all
select 1599920 , '2017-06-28 21:35:37' union all
select 2597386 , '2017-06-28 21:35:37' union all
select 2597386 , '2017-06-28 21:35:37' union all
select 2597386 , '2017-06-28 21:35:37' union all
select 2142079 , '2017-06-28 21:35:37' union all
select 1910443 , '2017-06-28 21:35:37' union all
select 1594321 , '2017-06-28 21:35:37' union all
select 1867062 , '2017-06-28 21:35:37' union all
select 2255621 , '2017-06-28 21:35:37' union all
select 2597386 , '2017-06-28 21:35:37' union all
select 1570641 , '2017-06-28 21:35:37' union all
select 2597386 , '2017-06-28 21:35:37' union all
select 1410099 , '2017-06-28 21:35:37' union all
select 1410083 , '2017-06-28 21:35:37' union all
select 1606065 , '2017-06-28 21:35:37' union all
select 2222276 , '2017-06-28 21:35:37' union all
select 2142352 , '2017-06-28 21:35:37' union all
select 2060266 , '2017-06-28 21:35:37' union all
select 1953392 , '2017-06-28 21:35:37' union all
select 1576642 , '2017-06-28 21:35:37' union all
select 1830106 , '2017-06-28 21:35:37' union all
select 2253198 , '2017-06-28 21:35:37' union all
select 1830741 , '2017-06-28 21:35:37' union all
select 1585251 , '2017-06-28 21:35:37' union all
select 1839987 , '2017-06-28 21:35:37' union all
select 2212762 , '2017-06-28 21:35:37' union all
select 1583968 , '2017-06-28 21:35:37' union all
select 1867062 , '2017-06-28 21:35:37' union all
select 1839987 , '2017-06-28 21:35:37' union all
select 1606065 , '2017-06-28 21:35:37' union all
select 1606065 , '2017-06-28 21:35:37' union all
select 1606065 , '2017-06-28 21:35:37' union all
select 1410077 , '2017-06-28 21:35:37' union all
select 1581591 , '2017-06-28 21:35:37' union all
select 1598823 , '2017-06-28 21:35:37' union all
select 2106773 , '2017-06-28 21:35:37' union all
select 1839987 , '2017-06-28 21:35:37' union all
select 2211859 , '2017-06-28 21:35:37' union all
select 2102342 , '2017-06-28 21:35:37' union all
select 1588403 , '2017-06-28 21:35:37' union all
select 1591403 , '2017-06-28 21:35:37' union all
select 1410071 , '2017-06-28 21:35:37' union all
select 1579896 , '2017-06-28 21:35:37' union all
select 1570871 , '2017-06-28 21:35:37' union all
select 1579007 , '2017-06-28 21:35:37' union all
select 1957484 , '2017-06-28 21:35:37' union all
select 1570108 , '2017-06-28 21:35:37' union all
select 1565146 , '2017-06-28 21:35:37' union all
select 1597278 , '2017-06-28 21:35:37' union all
select 2219054 , '2017-06-28 21:35:37' union all
select 2705892 , '2017-06-28 21:35:37' union all
select 1410083 , '2017-06-28 21:35:37' union all
select 1953392 , '2017-06-28 21:35:37' union all
select 2212762 , '2017-06-28 21:35:37' union all
select 1580026 , '2017-06-28 21:35:37' union all
select 1580666 , '2017-06-28 21:35:37' union all
select 2642440 , '2017-06-28 21:35:37' union all
select 2597386 , '2017-06-28 21:35:37' union all
select 2212302 , '2017-06-28 21:35:37' union all
select 1588403 , '2017-06-28 21:35:37' union all
select 1839987 , '2017-06-28 21:35:37' union all
select 1585251 , '2017-06-28 21:35:37' union all
select 2102342 , '2017-06-28 21:35:37' union all
select 1581800 , '2017-06-28 21:35:37' union all
select 1576723 , '2017-06-28 21:35:37' union all
select 1572014 , '2017-06-28 21:35:37' union all
select 1579007 , '2017-06-28 21:35:37' union all
select 2597386 , '2017-06-28 21:35:37' union all
select 2597386 , '2017-06-28 21:35:37' union all
select 1570589 , '2017-06-28 21:35:37' union all
select 2146588 , '2017-06-28 21:35:37' union all
select 1589534 , '2017-06-28 21:35:37' union all
select 2254272 , '2017-06-28 21:35:37' union all
select 2255098 , '2017-06-28 21:35:37' union all
select 1410083 , '2017-06-28 21:35:37' union all
select 1588403 , '2017-06-28 21:35:37' union all
select 2597563 , '2017-06-28 21:35:37' union all
select 2222276 , '2017-06-28 21:35:37' union all
select 2212388 , '2017-06-28 21:35:37' union all
select 1579074 , '2017-06-28 21:35:37' union all
select 1587707 , '2017-06-28 21:35:37' union all
select 2255621 , '2017-06-28 21:35:37' union all
select 1588403 , '2017-06-28 21:35:37' union all
select 1588403 , '2017-06-28 21:35:37' union all
select 1580026 , '2017-06-28 21:35:37' union all
select 2711168 , '2017-06-28 21:35:37' union all
select 1410083 , '2017-06-28 21:35:37' union all
select 1580420 , '2017-06-28 21:35:37' union all
select 1592209 , '2017-06-28 21:35:37' union all
select 1584770 , '2017-06-28 21:35:37' union all
select 1579007 , '2017-06-28 21:35:37' union all
select 2347865 , '2017-06-28 21:35:37' union all
select 1607480 , '2017-06-28 21:35:37' union all
select 2211859 , '2017-06-28 21:35:37' union all
select 1589534 , '2017-06-28 21:35:37' union all
select 1410077 , '2017-06-28 21:35:37' union all
select 1597129 , '2017-06-28 21:35:37' union all
select 1923653 , '2017-06-28 21:35:37' union all
select 1410032 , '2017-06-28 21:35:37' union all
select 2597386 , '2017-06-28 21:35:37' union all
select 1580026 , '2017-06-28 21:35:37' union all
select 1599750 , '2017-06-28 21:35:37' union all
select 1580026 , '2017-06-28 21:35:37' union all
select 1410135 , '2017-06-28 21:35:37' union all
select 1565146 , '2017-06-28 21:35:37' union all
select 1581591 , '2017-06-28 21:35:37' union all
select 1587938 , '2017-06-28 21:35:37' union all
select 2212812 , '2017-06-28 21:35:37' union all
select 1410071 , '2017-06-28 21:35:37' union all
select 1599920 , '2017-06-28 21:35:37' union all
select 1570108 , '2017-06-28 21:35:37' union all
select 2212302 , '2017-06-28 21:35:37' union all
select 2212408 , '2017-06-28 21:35:37' union all
select 1594321 , '2017-06-28 21:35:37' union all
select 1594321 , '2017-06-28 21:35:37' union all
select 1579074 , '2017-06-28 21:35:37' union all
select 2106773 , '2017-06-28 21:35:37' union all
select 1579074 , '2017-06-28 21:35:37' union all
select 2211365 , '2017-06-28 21:35:37' union all
select 2255057 , '2017-06-28 21:35:37' union all
select 2142079 , '2017-06-28 21:35:37' union all
select 2255621 , '2017-06-28 21:35:37' union all
select 1579074 , '2017-06-28 21:35:37' union all
select 1588403 , '2017-06-28 21:35:37' union all
select 1410083 , '2017-06-28 21:35:37' union all
select 1594321 , '2017-06-28 21:35:37' union all
select 1603226 , '2017-06-28 21:35:37' union all
select 2211365 , '2017-06-28 21:35:37' union all
select 2255621 , '2017-06-28 21:35:37' union all
select 2222276 , '2017-06-28 21:35:37' union all
select 2212302 , '2017-06-28 21:35:37' union all
select 2211555 , '2017-06-28 21:35:37' union all
select 1584146 , '2017-06-28 21:35:37' union all
select 1598823 , '2017-06-28 21:35:37' union all
select 2212762 , '2017-06-28 21:35:37' union all
select 2597386 , '2017-06-28 21:35:37' union all
select 2597386 , '2017-06-28 21:35:37' union all
select 1579074 , '2017-06-28 21:35:37' union all
select 2711168 , '2017-06-28 21:35:37' union all
select 2142079 , '2017-06-28 21:35:37' union all
select 2597386 , '2017-06-28 21:35:37' union all
select 1910443 , '2017-06-28 21:35:37' union all
select 1598823 , '2017-06-28 21:35:37' union all
select 1584246 , '2017-06-28 21:35:37' union all
select 2219363 , '2017-06-28 21:35:37' union all
select 2255621 , '2017-06-28 21:35:37' union all
select 1581591 , '2017-06-28 21:35:37' union all
select 2597386 , '2017-06-28 21:35:37' union all
select 1594321 , '2017-06-28 21:35:37' union all
select 1582811 , '2017-06-28 21:35:37' union all
select 1582814 , '2017-06-28 21:35:37' union all
select 2102342 , '2017-06-28 21:35:37' union all
select 2255345 , '2017-06-28 21:35:37' union all
select 1599750 , '2017-06-28 21:35:37' union all
select 1565146 , '2017-06-28 21:35:37' union all
select 2200971 , '2017-06-28 21:35:37' union all
select 2255498 , '2017-06-28 21:35:37' union all
select 1710430 , '2017-06-28 21:35:37' union all
select 1410083 , '2017-06-28 21:35:37' union all
select 2219363 , '2017-06-28 21:35:37' union all
select 1839987 , '2017-06-28 21:35:37' union all
select 2142352 , '2017-06-28 21:35:37' union all
select 1953392 , '2017-06-28 21:35:37' union all
select 2597386 , '2017-06-28 21:35:37' union all
select 2219054 , '2017-06-28 21:35:37' union all
select 2597386 , '2017-06-28 21:35:37' union all
select 2060266 , '2017-06-28 21:35:37' union all
select 1600070 , '2017-06-28 21:35:37' union all
select 1576588 , '2017-06-28 21:35:37' union all
select 2211365 , '2017-06-28 21:35:37' union all
select 1572014 , '2017-06-28 21:35:37' union all
select 2102342 , '2017-06-28 21:35:37' union all
select 2222441 , '2017-06-28 21:35:37' union all
select 2179954 , '2017-06-28 21:35:37' union all
select 1584770 , '2017-06-28 21:35:37' union all
select 2219363 , '2017-06-28 21:35:37' union all
select 2179954 , '2017-06-28 21:35:37' union all
select 1565146 , '2017-06-28 21:35:37' union all
select 1606065 , '2017-06-28 21:35:37' union all
select 2650093 , '2017-06-28 21:35:37' union all
select 2526259 , '2017-06-28 21:35:37' union all
select 2711168 , '2017-06-28 21:35:37' union all
select 1584246 , '2017-06-28 21:35:37' union all
select 1410083 , '2017-06-28 21:35:37' union all
select 1581800 , '2017-06-28 21:35:37' union all
select 1830106 , '2017-06-28 21:35:37' union all
select 1588403 , '2017-06-28 21:35:37' union all
select 1600070 , '2017-06-28 21:35:37' union all
select 1581860 , '2017-06-28 21:35:37' union all
select 2222276 , '2017-06-28 21:35:37' union all
select 2219054 , '2017-06-28 21:35:37' union all
select 1710557 , '2017-06-28 21:35:37' union all
select 1576642 , '2017-06-28 21:35:37' union all
select 1585251 , '2017-06-28 21:35:37' union all
select 1593397 , '2017-06-28 21:35:37' union all
select 2102342 , '2017-06-28 21:35:37' union all
select 1582427 , '2017-06-28 21:35:37' union all
select 1581591 , '2017-06-28 21:35:37' union all
select 2253926 , '2017-06-28 21:35:37' union all
select 2212302 , '2017-06-28 21:35:37' union all
select 2134940 , '2017-06-28 21:35:37' union all
select 2255621 , '2017-06-28 21:35:37' union all
select 1591403 , '2017-06-28 21:35:37' union all
select 2597386 , '2017-06-28 21:35:37' union all
select 1580666 , '2017-06-28 21:35:37' union all
select 2212408 , '2017-06-28 21:35:37' union all
select 2711168 , '2017-06-28 21:35:37' union all
select 2218752 , '2017-06-28 21:35:37' union all
select 1580030 , '2017-06-28 21:35:37' union all
select 2642440 , '2017-06-28 21:35:37' union all
select 2212812 , '2017-06-28 21:35:37' union all
select 1584246 , '2017-06-28 21:35:37' union all
select 1589534 , '2017-06-28 21:35:37' union all
select 1576642 , '2017-06-28 21:35:37' union all
select 1829840 , '2017-06-28 21:35:37' union all
select 2211365 , '2017-06-28 21:35:37' union all
select 1570108 , '2017-06-28 21:35:37' union all
select 2212682 , '2017-06-28 21:35:37' union all
select 1410132 , '2017-06-28 21:35:37' union all
select 2597386 , '2017-06-28 21:35:37' union all
select 2597386 , '2017-06-28 21:35:37' union all
select 2597386 , '2017-06-28 21:35:37' union all
select 1953464 , '2017-06-28 21:35:37' union all
select 1582814 , '2017-06-28 21:35:37' union all
select 1410071 , '2017-06-28 21:35:37' union all
select 2597386 , '2017-06-28 21:35:37' union all
select 2597386 , '2017-06-28 21:35:37' union all
select 2597386 , '2017-06-28 21:35:37' union all
select 2597386 , '2017-06-28 21:35:37' union all
select 1593907 , '2017-06-28 21:35:37' union all
select 1601256 , '2017-06-28 21:35:37' union all
select 2597386 , '2017-06-28 21:35:37' union all
select 1584146 , '2017-06-28 21:35:37' union all
select 1597313 , '2017-06-28 21:35:37' union all
select 2597386 , '2017-06-28 21:35:37' union all
select 1410077 , '2017-06-28 21:35:37' union all
select 1410077 , '2017-06-28 21:35:37' union all
select 1592794 , '2017-06-28 21:35:37' union all
select 2597386 , '2017-06-28 21:35:37' union all
select 1581594 , '2017-06-28 21:35:37' union all
select 2219363 , '2017-06-28 21:35:37' union all
select 1593397 , '2017-06-28 21:35:37' union all
select 1580666 , '2017-06-28 21:35:37' union all
select 2597386 , '2017-06-28 21:35:37' union all
select 2597386 , '2017-06-28 21:35:37' union all
select 1570108 , '2017-06-28 21:35:37' union all
select 1579074 , '2017-06-28 21:35:37' union all
select 2255057 , '2017-06-28 21:35:37' union all
select 1587707 , '2017-06-28 21:35:37' union all
select 2597386 , '2017-06-28 21:35:37' union all
select 2597386 , '2017-06-28 21:35:37' union all
select 1957484 , '2017-06-28 21:35:37' union all
select 1600070 , '2017-06-28 21:35:37' union all
select 1596992 , '2017-06-28 21:35:37' union all
select 1708958 , '2017-06-28 21:35:37' union all
select 2238789 , '2017-06-28 21:35:37' union all
select 1839987 , '2017-06-28 21:35:37' union all
select 2252986 , '2017-06-28 21:35:37' union all
select 2642440 , '2017-06-28 21:35:37' union all
select 1585251 , '2017-06-28 21:35:37' union all
select 1599750 , '2017-06-28 21:35:37' union all
select 1572309 , '2017-06-28 21:35:37' union all
select 1576642 , '2017-06-28 21:35:37' union all
select 2255621 , '2017-06-28 21:35:37' union all
select 1599750 , '2017-06-28 21:35:37' union all
select 1581594 , '2017-06-28 21:35:37' union all
select 1576642 , '2017-06-28 21:35:37' union all
select 1957484 , '2017-06-28 21:35:37' union all
select 2597386 , '2017-06-28 21:35:37' union all
select 1830648 , '2017-06-28 21:35:37' union all
select 1597313 , '2017-06-28 21:35:37' union all
select 1597313 , '2017-06-28 21:35:37' union all
select 1579074 , '2017-06-28 21:35:37' union all
select 2642440 , '2017-06-28 21:35:37' union all
select 1588347 , '2017-06-28 21:35:37' union all
select 1600070 , '2017-06-28 21:35:37' union all
select 1594321 , '2017-06-28 21:35:37' union all
select 1593397 , '2017-06-28 21:35:37' union all
select 1607480 , '2017-06-28 21:35:37' union all
select 1579074 , '2017-06-28 21:35:37' union all
select 2252986 , '2017-06-28 21:35:37' union all
select 2597386 , '2017-06-28 21:35:37' union all
select 1593397 , '2017-06-28 21:35:37' union all
select 1582858 , '2017-06-28 21:35:37' union all
select 2211365 , '2017-06-28 21:35:37' union all
select 1410023 , '2017-06-28 21:35:37' union all
select 2212470 , '2017-06-28 21:35:37' union all
select 1585251 , '2017-06-28 21:35:37' union all
select 2212762 , '2017-06-28 21:35:37' union all
select 2255621 , '2017-06-28 21:35:37' union all
select 1582469 , '2017-06-28 21:35:37' union all
select 1607480 , '2017-06-28 21:35:37' union all
select 1593149 , '2017-06-28 21:35:37' union all
select 2219363 , '2017-06-28 21:35:37' union all
select 1580026 , '2017-06-28 21:35:37' union all
select 1923653 , '2017-06-28 21:35:37' union all
select 2597386 , '2017-06-28 21:35:37' union all
select 1599117 , '2017-06-28 21:35:37' union all
select 1596992 , '2017-06-28 21:35:37' union all
select 1598823 , '2017-06-28 21:35:37' union all
select 1923653 , '2017-06-28 21:35:37' union all
select 1923653 , '2017-06-28 21:35:37' union all
select 1593149 , '2017-06-28 21:35:37' union all
select 1597313 , '2017-06-28 21:35:37' union all
select 1607480 , '2017-06-28 21:35:37' union all
select 2597386 , '2017-06-28 21:35:37' union all
select 1580026 , '2017-06-28 21:35:37' union all
select 1923653 , '2017-06-28 21:35:37' union all
select 1565146 , '2017-06-28 21:35:37' union all
select 1588347 , '2017-06-28 21:35:37' union all
select 1570025 , '2017-06-28 21:35:37' union all
select 2211859 , '2017-06-28 21:35:37' union all
select 1867062 , '2017-06-28 21:35:37' union all
select 2642440 , '2017-06-28 21:35:37' union all
select 1867062 , '2017-06-28 21:35:37' union all
select 1580420 , '2017-06-28 21:35:37' union all
select 1570871 , '2017-06-28 21:35:37' union all
select 1588137 , '2017-06-28 21:35:37' union all
select 1957484 , '2017-06-28 21:35:37' union all
select 1576588 , '2017-06-28 21:35:37' union all
select 2212762 , '2017-06-28 21:35:37' union all
select 2219363 , '2017-06-28 21:35:37' union all
select 2597386 , '2017-06-28 21:35:37' union all
select 1596784 , '2017-06-28 21:35:37' union all
select 1582466 , '2017-06-28 21:35:37' union all
select 1578444 , '2017-06-28 21:35:37' union all
select 1578444 , '2017-06-28 21:35:37' union all
select 1578444 , '2017-06-28 21:35:37' union all
select 1582466 , '2017-06-28 21:35:37' union all
select 2052987 , '2017-06-28 21:35:37' union all
select 1578444 , '2017-06-28 21:35:37' union all
select 2252785 , '2017-06-28 21:35:37' union all
select 1579032 , '2017-06-28 21:35:37' union all
select 1579032 , '2017-06-28 21:35:37' union all
select 2212105 , '2017-06-28 21:35:37' union all
select 2212105 , '2017-06-28 21:35:37' union all
select 2212105 , '2017-06-28 21:35:37' union all
select 2212105 , '2017-06-28 21:35:37' union all
select 2212105 , '2017-06-28 21:35:37' union all
select 1596784 , '2017-06-28 21:35:37' union all
select 1579481 , '2017-06-28 21:35:37' union all
select 2526137 , '2017-06-28 21:35:37' union all
select 2212324 , '2017-06-28 21:35:37' union all
select 1580245 , '2017-06-28 21:35:37' union all
select 2211637 , '2017-06-28 21:35:37' union all
select 2211637 , '2017-06-28 21:35:37' union all
select 2211637 , '2017-06-28 21:35:37' union all
select 2211637 , '2017-06-28 21:35:37' union all
select 2211637 , '2017-06-28 21:35:37' union all
select 2211637 , '2017-06-28 21:35:37' union all
select 2211637 , '2017-06-28 21:35:37' union all
select 2211637 , '2017-06-28 21:35:37' union all
select 2211637 , '2017-06-28 21:35:37' union all
select 1589671 , '2017-06-28 21:35:37' union all
select 1597196 , '2017-06-28 21:35:37' union all
select 1597196 , '2017-06-28 21:35:37' union all
select 1597196 , '2017-06-28 21:35:37' union all
select 2252785 , '2017-06-28 21:35:37' union all
select 2219164 , '2017-06-28 21:35:37' union all
select 2219164 , '2017-06-28 21:35:37' union all
select 2219164 , '2017-06-28 21:35:37' union all
select 1581944 , '2017-06-28 21:35:37' union all
select 1581944 , '2017-06-28 21:35:37' union all
select 2252785 , '2017-06-28 21:35:37' union all
select 1600058 , '2017-06-28 21:35:37' union all
select 2052987 , '2017-06-28 21:35:37' union all
select 2566788 , '2017-06-28 21:35:37' union all
select 2212105 , '2017-06-28 21:35:37' union all
select 1563059 , '2017-06-28 21:35:37' union all
select 1563059 , '2017-06-28 21:35:37' union all
select 1582466 , '2017-06-28 21:35:37' union all
select 2566806 , '2017-06-28 21:35:37' union all
select 2566806 , '2017-06-28 21:35:37' union all
select 2566806 , '2017-06-28 21:35:37' union all
select 2566806 , '2017-06-28 21:35:37' union all
select 2566806 , '2017-06-28 21:35:37' union all
select 2566806 , '2017-06-28 21:35:37' union all
select 2348167 , '2017-06-28 21:35:37' union all

select 2252986 , '2017-06-28 21:35:37'

[/Code]
Basically I need to pull the report with following data

  1. UserId
  2. No.of.Visits in last two months
  3. No.of visits in last one month
  4. No.of visits in last 3 weeks
  5. No.of visits in last 2 weeks
  6. No.of Visits in Last one week
  7. Visited every 2 days? ‘Y’ or ‘N’
  8. Visited every 3 days? ‘Y’ or ‘N’
  9. Visited every 1 days? ‘Y’ or ‘N’
  10. Visited every 4 days? ‘Y’ or ‘N’
  11. Visited every 5 days? ‘Y’ or ‘N’
  12. Visited every 6 days? ‘Y’ or ‘N’
  13. Visited every 7 days? ‘Y’ or ‘N’
  14. Visited Every 10days ? ‘Y’ or ‘N’
  15. Last visitedDate

How to pull this data using given sample data. Any sample query please.

[Note: there is a body limitation on this forum post,i gave minimal data to play with]


#2

I am not quite sure of the time frame for 'Visited every 2 days' etc.
The following should get you started with SQL2012 or above:

WITH PreviousVisits
AS
(
	SELECT UserID, VisitedDate
		,LAG(VisitedDate, 1) OVER (PARTITION BY userid ORDER BY VisitedDate) AS LastVisitedDate
	FROM @UserVisit
)
SELECT V.UserID
	,SUM(CASE WHEN V.VisitedDate > DATEADD(month, -2, CURRENT_TIMESTAMP) THEN 1 ELSE 0 END) AS Last2Months
	,MAX(CASE WHEN DATEDIFF(day, LastVisitedDate, VisitedDate) = 2 THEN 'Y' ELSE 'N' END) AS Visits2DaysApart
	,MAX(V.VisitedDate) AS LastVisitedDate
FROM PreviousVisits V
GROUP BY V.UserID;

#3

The Number of visits in the last N days and last visit date are easy enough. The "Visited every N days? ‘Y’ or ‘N’", is a little bit ambiguous... I assumes an ongoing pattern but it doesn't stipulate how long the pattern has to persist in order to qualify...
If I visited for the last 3 days in a row but hadn't visited for 3 months prior to that, have I visited every 1 day?

Also, ~2/3rds of your test data is duplicate data (only 157 unique rows) are you really suggesting that the same user can make multiple visits at the exact same time??? If so, are the counted as separate visits or the same visit?