Create a loop for the Calender Form data

I have created a query using view which give me results for the Tags as '1' if the work is done as per the dates on that particular day or '0' if it didn't. I have created this for October only but I want it for the whole year & also for the next year.

I know I will have to use the loop or the query will be long enough & will not work efficiently & that will turn the worse conditions.

So I just want to ask you that is there anyone which can reduce the query of mine & give me the desired output as required.

The code is as below :

Use CNSData
select [tagno],
max(case when(datepart(day,DueDate)) ='1' and (datepart(month,DueDate))='10' and (datepart(year,DueDate)) = '2018' then 1
when(datepart(day,JobDoneDate)) ='1' and (datepart(month,JobDoneDate))='10' and (datepart(year,JobDoneDate)) = '2018' then 1 else 0 end)[2018-10-01],
max(case when(datepart(day,DueDate)) ='2' and (datepart(month,DueDate))='10' and (datepart(year,DueDate)) = '2018' then 1
when(datepart(day,JobDoneDate)) ='2' and (datepart(month,JobDoneDate))='10' and (datepart(year,JobDoneDate)) = '2018' then 1 else 0 end)[2018-10-02],
max(case when(datepart(day,DueDate)) ='3' and (datepart(month,DueDate))='10' and (datepart(year,DueDate)) = '2018' then 1
when(datepart(day,JobDoneDate)) ='3' and (datepart(month,JobDoneDate))='10' and (datepart(year,JobDoneDate)) = '2018' then 1 else 0 end)[2018-10-03],
max(case when(datepart(day,DueDate)) ='4' and (datepart(month,DueDate))='10' and (datepart(year,DueDate)) = '2018' then 1
when(datepart(day,JobDoneDate)) ='4' and (datepart(month,JobDoneDate))='10' and (datepart(year,JobDoneDate)) = '2018' then 1 else 0 end)[2018-10-04],
max(case when(datepart(day,DueDate)) ='5' and (datepart(month,DueDate))='10' and (datepart(year,DueDate)) = '2018' then 1
when(datepart(day,JobDoneDate)) ='5' and (datepart(month,JobDoneDate))='10' and (datepart(year,JobDoneDate)) = '2018' then 1 else 0 end)[2018-10-05],
max(case when(datepart(day,DueDate)) ='6' and (datepart(month,DueDate))='10' and (datepart(year,DueDate)) = '2018' then 1
when(datepart(day,JobDoneDate)) ='6' and (datepart(month,JobDoneDate))='10' and (datepart(year,JobDoneDate)) = '2018' then 1 else 0 end)[2018-10-06],
max(case when(datepart(day,DueDate)) ='7' and (datepart(month,DueDate))='10' and (datepart(year,DueDate)) = '2018' then 1
when(datepart(day,JobDoneDate)) ='7' and (datepart(month,JobDoneDate))='10' and (datepart(year,JobDoneDate)) = '2018' then 1 else 0 end)[2018-10-07],
max(case when(datepart(day,DueDate)) ='8' and (datepart(month,DueDate))='10' and (datepart(year,DueDate)) = '2018' then 1
when(datepart(day,JobDoneDate)) ='8' and (datepart(month,JobDoneDate))='10' and (datepart(year,JobDoneDate)) = '2018' then 1 else 0 end)[2018-10-08],
max(case when(datepart(day,DueDate)) ='9' and (datepart(month,DueDate))='10' and (datepart(year,DueDate)) = '2018' then 1
when(datepart(day,JobDoneDate)) ='9' and (datepart(month,JobDoneDate))='10' and (datepart(year,JobDoneDate)) = '2018' then 1 else 0 end)[2018-10-09],
max(case when(datepart(day,DueDate)) ='10' and (datepart(month,DueDate))='10' and (datepart(year,DueDate)) = '2018' then 1
when(datepart(day,JobDoneDate)) ='10' and (datepart(month,JobDoneDate))='10' and (datepart(year,JobDoneDate)) = '2018' then 1 else 0 end)[2018-10-10],
max(case when(datepart(day,DueDate)) ='11' and (datepart(month,DueDate))='10' and (datepart(year,DueDate)) = '2018' then 1
when(datepart(day,JobDoneDate)) ='11' and (datepart(month,JobDoneDate))='10' and (datepart(year,JobDoneDate)) = '2018' then 1 else 0 end)[2018-10-11],
max(case when(datepart(day,DueDate)) ='12' and (datepart(month,DueDate))='10' and (datepart(year,DueDate)) = '2018' then 1
when(datepart(day,JobDoneDate)) ='12' and (datepart(month,JobDoneDate))='10' and (datepart(year,JobDoneDate)) = '2018' then 1 else 0 end)[2018-10-12],
max(case when(datepart(day,DueDate)) ='13' and (datepart(month,DueDate))='10' and (datepart(year,DueDate)) = '2018' then 1
when(datepart(day,JobDoneDate)) ='13' and (datepart(month,JobDoneDate))='10' and (datepart(year,JobDoneDate)) = '2018' then 1 else 0 end)[2018-10-13],
max(case when(datepart(day,DueDate)) ='14' and (datepart(month,DueDate))='10' and (datepart(year,DueDate)) = '2018' then 1
when(datepart(day,JobDoneDate)) ='14' and (datepart(month,JobDoneDate))='10' and (datepart(year,JobDoneDate)) = '2018' then 1 else 0 end)[2018-10-14],
max(case when(datepart(day,DueDate)) ='15' and (datepart(month,DueDate))='10' and (datepart(year,DueDate)) = '2018' then 1
when(datepart(day,JobDoneDate)) ='15' and (datepart(month,JobDoneDate))='10' and (datepart(year,JobDoneDate)) = '2018' then 1 else 0 end)[2018-10-15],
max(case when(datepart(day,DueDate)) ='16' and (datepart(month,DueDate))='10' and (datepart(year,DueDate)) = '2018' then 1
when(datepart(day,JobDoneDate)) ='16' and (datepart(month,JobDoneDate))='10' and (datepart(year,JobDoneDate)) = '2018' then 1 else 0 end)[2018-10-16],
max(case when(datepart(day,DueDate)) ='17' and (datepart(month,DueDate))='10' and (datepart(year,DueDate)) = '2018' then 1
when(datepart(day,JobDoneDate)) ='17' and (datepart(month,JobDoneDate))='10' and (datepart(year,JobDoneDate)) = '2018' then 1 else 0 end)[2018-10-17],
max(case when(datepart(day,DueDate)) ='18' and (datepart(month,DueDate))='10' and (datepart(year,DueDate)) = '2018' then 1
when(datepart(day,JobDoneDate)) ='18' and (datepart(month,JobDoneDate))='10' and (datepart(year,JobDoneDate)) = '2018' then 1 else 0 end)[2018-10-18],
max(case when(datepart(day,DueDate)) ='19' and (datepart(month,DueDate))='10' and (datepart(year,DueDate)) = '2018' then 1
when(datepart(day,JobDoneDate)) ='19' and (datepart(month,JobDoneDate))='10' and (datepart(year,JobDoneDate)) = '2018' then 1 else 0 end)[2018-10-19],
max(case when(datepart(day,DueDate)) ='20' and (datepart(month,DueDate))='10' and (datepart(year,DueDate)) = '2018' then 1
when(datepart(day,JobDoneDate)) ='20' and (datepart(month,JobDoneDate))='10' and (datepart(year,JobDoneDate)) = '2018' then 1 else 0 end)[2018-10-20],
max(case when(datepart(day,DueDate)) ='21' and (datepart(month,DueDate))='10' and (datepart(year,DueDate)) = '2018' then 1
when(datepart(day,JobDoneDate)) ='21' and (datepart(month,JobDoneDate))='10' and (datepart(year,JobDoneDate)) = '2018' then 1 else 0 end)[2018-10-21],
max(case when(datepart(day,DueDate)) ='22' and (datepart(month,DueDate))='10' and (datepart(year,DueDate)) = '2018' then 1
when(datepart(day,JobDoneDate)) ='22' and (datepart(month,JobDoneDate))='10' and (datepart(year,JobDoneDate)) = '2018' then 1 else 0 end)[2018-10-22],
max(case when(datepart(day,DueDate)) ='23' and (datepart(month,DueDate))='10' and (datepart(year,DueDate)) = '2018' then 1
when(datepart(day,JobDoneDate)) ='23' and (datepart(month,JobDoneDate))='10' and (datepart(year,JobDoneDate)) = '2018' then 1 else 0 end)[2018-10-23],
max(case when(datepart(day,DueDate)) ='24' and (datepart(month,DueDate))='10' and (datepart(year,DueDate)) = '2018' then 1
when(datepart(day,JobDoneDate)) ='24' and (datepart(month,JobDoneDate))='10' and (datepart(year,JobDoneDate)) = '2018' then 1 else 0 end)[2018-10-24],
max(case when(datepart(day,DueDate)) ='25' and (datepart(month,DueDate))='10' and (datepart(year,DueDate)) = '2018' then 1
when(datepart(day,JobDoneDate)) ='25' and (datepart(month,JobDoneDate))='10' and (datepart(year,JobDoneDate)) = '2018' then 1 else 0 end)[2018-10-25],
max(case when(datepart(day,DueDate)) ='26' and (datepart(month,DueDate))='10' and (datepart(year,DueDate)) = '2018' then 1
when(datepart(day,JobDoneDate)) ='26' and (datepart(month,JobDoneDate))='10' and (datepart(year,JobDoneDate)) = '2018' then 1 else 0 end)[2018-10-26],
max(case when(datepart(day,DueDate)) ='27' and (datepart(month,DueDate))='10' and (datepart(year,DueDate)) = '2018' then 1
when(datepart(day,JobDoneDate)) ='27' and (datepart(month,JobDoneDate))='10' and (datepart(year,JobDoneDate)) = '2018' then 1 else 0 end)[2018-10-27],
max(case when(datepart(day,DueDate)) ='28' and (datepart(month,DueDate))='10' and (datepart(year,DueDate)) = '2018' then 1
when(datepart(day,JobDoneDate)) ='28' and (datepart(month,JobDoneDate))='10' and (datepart(year,JobDoneDate)) = '2018' then 1 else 0 end)[2018-10-28],
max(case when(datepart(day,DueDate)) ='29' and (datepart(month,DueDate))='10' and (datepart(year,DueDate)) = '2018' then 1
when(datepart(day,JobDoneDate)) ='29' and (datepart(month,JobDoneDate))='10' and (datepart(year,JobDoneDate)) = '2018' then 1 else 0 end)[2018-10-29],
max(case when(datepart(day,DueDate)) ='30' and (datepart(month,DueDate))='10' and (datepart(year,DueDate)) = '2018' then 1
when(datepart(day,JobDoneDate)) ='30' and (datepart(month,JobDoneDate))='10' and (datepart(year,JobDoneDate)) = '2018' then 1 else 0 end)[2018-10-30],
max(case when(datepart(day,DueDate)) ='31' and (datepart(month,DueDate))='10' and (datepart(year,DueDate)) = '2018' then 1
when(datepart(day,JobDoneDate)) ='31' and (datepart(month,JobDoneDate))='10' and (datepart(year,JobDoneDate)) = '2018' then 1 else 0 end)[2018-10-31]
from CNSData.dbo.vw_Schedule group by tagno
Go

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Kk7y ago
@Sreekanth yes exactly I need the dates as column names.
I have modified the code which is as under:
select * from (select tagno, d = cast(duedate as date), i = 1 from dbo.vw_Schedule
union all select tagno, d = cast(jobdonedate as date), i = 1 from vw_Schedule) as t
pivot (max(i) for d in ([2018-10-01], [2018-10-02],[2018-10-03],[2018-10-04],[2018-10-05],[2018-10-06],[2018-10-07],[2018-10-08],
                         [2018-10-09],[2018-10-10],[2018-10-11],[2018-10-12],[2018-10-13],[2018-10-14],[2018-10-15],[2018-10-16],
                         [2018-10-17],[2018-10-18],[2018-10-19],[2018-10-20],[2018-10-21],[2018-10-22],[2018-10-23],[2018-10-24],[2018-10-25],
                         [2018-10-26],[2018-10-27],[2018-10-28],[2018-10-29],[2018-10-30],[2018-10-31] )) as p order by tagno
This gives me the result for the month of Oct only but I need this to be dynamic as @StartDate and @EndDate so that whenever I pass the values for the @StartDate and @EndDate the column names between the dates must be displayed.
I have attached the image of the output for your reference purposes.
Sreekanth Reddy7y ago
Seems your dates are being as column names.
Sreekanth Reddy7y ago
@kk

You should get the result for my query I hope.
Anyhow what type of result you are facing with my query ?
Kk7y ago
@Sreekanth thanks for your reply but its not working as per what is required I have attached the output results for reference purpose. I want the results to be displayed in the exactly same form as shown in the image.
Sreekanth Reddy7y ago
@kk Kohil

Try the following :

SET @Startdt = '20180101'
--2018 Year
--01 Month
--01 Date
WHILE @Startdt < '20201201'
BEGIN
-- Do your logic
-- Your Logic
-- your logic

SET @Startdt = DATEADD(MONTH,1,@StartDT)
END