2 Replies Latest reply: Jul 27, 2015 12:11 PM by Thomas Burnson RSS

    Creating a Resident Load list for a SQL Query

    Thomas Burnson

      Hi I'm looking to sync up my calender with my code below, however I am not sure what I am missing in order for them to link:


      Script for Main:


      SQL SELECT rq.request_id as [Request ID],

             rq.form_id as [Form ID],

             rtrim(fh.short_name) +

             case when

             ( select max(x.user_type)

                 from requestuser x

                where x.request_id = rq.request_id ) = 'T' then ' (Transfer)'

               else ''

             end as [Form Name],

             rq.user_id as [Create User ID],

             rq.user_name as [Create User Name],

             rq.create_date as [Create Date],

             rq.date_submit as [Submit Date]

        from FormProd.dbo.request rq

             inner join FormProd.dbo.requestlist rl on

             ( rq.request_id = rl.request_id )

             inner join FormProd.dbo.formheader fh on

             ( rq.form_id = fh.form_id )

             left outer join FormProd.dbo.webuser u1 on

             ( rl.route_to = u1.user_id )

             left outer join FormProd.dbo.webuser u2 on

             ( rl.user_id = u2.user_id )

      where ( rq.form_id NOT IN ('SERF','TERM-REQ')) and

             ( rq.date_submit >= getdate() - 10000)




      Script for Calender:



        Min([Create Date]) as MinDate,

        Max([Create Date]) as MaxDate

      Resident ARRequestList;



      LET vMinDate = Num(Peek('MinDate', 0, 'MinMax'));

      LET vMaxDate = Num(Peek('MaxDate', 0, 'MinMax'));

      LET vToday = $(vMaxDate);





        Date($(vMinDate) + rowno() - 1) AS TempDate


        $(vMaxDate) - $(vMinDate) +1;



      DROP TABLE MinMax;





      TempDate as [Create Date],

      Week(TempDate) as Week,

      Year(TempDate) as Year,

      Day(TempDate) as Day,

      Month(TempDate) as Month,

      WeekDay(TempDate) as WeekDay,

      'Q' & ceil(month(TempDate)/3) as Quarter,

      Date(monthstart(TempDate),'MMM-YYYY') as MonthYear,

      Week(TempDate)&'-'&Year(TempDate) as WeekYear,

      InYearToDate(TempDate,$(vToday),0) * -1 as CurYTDFlag,

      InYearToDate(TempDate,$(vToday),-1) * -1 as LastYTDFlag



      Resident TempCal

      Order By TempDate ASC;

      DROP TABLE TempCal;