What's the best way to do this

Transact-SQL
1

I have a table with name and visited date
I want to come out with a table that shows the last 30 days and each name - and then list for each name if they visited that day or not (for the last 30 days from today)

what's the best way to do this ?

2

Try:

SELECT
  [name]
, [visit date]
, CASE WHEN DATEPART(DD. [visit date])=DAY(DATEPART(DD. GETDATE) THEN 'TODAY' ELSE 'NOT TODAY' END AS TodayOrNot
FROM VISITS
WHERE [visit date] > DATEADD(GETDATE,-31);

By the way, it's better to post a script with create table and inserts of sample data so responders can post working code. It's a best practice to not name a column "name" or use spaces.

3

hi thanks - the thing is i want a calendar for the last 30 days where i can put a boolean field and put 1 if there are visits and 0 if no visits by that user

4

Usable sample data is needed, that is, CREATE TABLE and INSERT statements. [Not just a splat of data on the screen.]

5

Something like this.
Haven't tried it so could be typos but you should get the idea.

;with cte_visits as
(select distinct name, visitdate from tbl where visitdate >= getdate()-31
, cte_names as
(select distinct name from cte_visits)
, cte_dates as
(
select d = convert(datetime,convert(varchar(8),getdate(),112))
union all
select d = d-1 from cte_dates where d > getdate()-31
)
select n.name , d.d, visited = case when v.name is null then 'N' else 'Y' end
from cte_dates d
cross join cte_names n
left join cte_visits v
on v.name = b.name
and v.visitdate = d.d
order by d.d, n.name

6

hi

i tried to do this .. Hope this helps !!!! :slight_smile: :slight_smile:

its one way ( don't know if its the best way ) ....

please click arrow to the left for drop create sample data 
drop table #data
go  

create table #data 
(
name varchar(10),
visitdate date 
)
go 

insert into #data select 'harish',GETDATE() - 34
insert into #data select 'harish',GETDATE() - 22
insert into #data select 'harish',GETDATE() - 45
insert into #data select 'harish',GETDATE() - 40
insert into #data select 'harish',GETDATE() - 45
insert into #data select 'harish',GETDATE() - 37

go

insert into #data select 'prathima',GETDATE() - 34
insert into #data select 'prathima',GETDATE() - 22
insert into #data select 'prathima',GETDATE() - 20
insert into #data select 'prathima',GETDATE() - 46
insert into #data select 'prathima',GETDATE() - 54
insert into #data select 'prathima',GETDATE() - 40
go

select * from #data
go

image

please click arrow to the left for SQL 
;WITH cte_dates 
     AS (SELECT date = CONVERT(DATETIME, CONVERT(VARCHAR(8), Getdate(), 112)) 
         UNION ALL 
         SELECT date = date - 1 
         FROM   cte_dates 
         WHERE  date > Getdate() - 31) 
SELECT *, 
       CASE 
         WHEN b.visitdate IS NOT NULL THEN 'Y' 
         ELSE 'N' 
       END AS VisitedDate 
FROM   cte_dates a 
       LEFT JOIN #data b 
              ON a.date = b.visitdate 
ORDER  BY a.date

image
image619×536 7.95 KB