1 Reply Latest reply on Aug 16, 2018 9:25 AM by patrick.byrne.0

    Convert SQL getdate() to a date picker in Tableau

    Surap Nami

      Hi,

       

      I'm new to Tableau, and apologize if this is a basic question. I have following sql query that gives me 2 columns 1) LossType 2) WeekToDate (displaying the total count of previous week) .

       

      Now I want to add two more columns along with the existing:- 3) MonthToDate (shows the count of the current month) 4) YearToDate (shows the count for the current year)

       

      By default, when I open the dashboard I want the result to show the values for last week, current month and current year. But I also want a date filter on my dashboard, from which I can choose the date. Then my results should calculate based on that date.

       

      This is my MS-SQL query.

      select *from (
      
      
      select a.LossType, count(*) as WTD
      from
      (select DerivedClaimStatus ,datepart(week,wk.claimopendate) as Week ,
      case when [UW_Peril] not in ('Fire','Theft','Hail','Liability Injuries','Liability Coverage/PD','Wind','Water Weather','Water Non Weather','Liability Coverage/PD') 
        then   'All Other' else [UW_Peril] end as LossType
      from  (select  a.ClaimID,claimopendate,[UW_Peril], b.DerivedClaimStatus
      from  tbu.Claims_Master a
      left join [ClaimsOperations].[tbu].[Snapshot] b
      on a.ClaimID = b.ClaimID 
      where b.RowIsCurrent = 'Y'
      
      ) wk
      where DATEDIFF(week,ClaimOpenDate,GETDATE()) = 1  and DerivedClaimStatus  not in ('Record Only','Opened in error','N/A') 
      )a
      group by a.LossType
      union
      select 'Litigation' as loss_type , count(*) 
      from [dbo].[Litigation]
      where DATEDIFF(week,convert(date, convert(varchar,ReceivedDate)),GETDATE()) = 1
      ) a
      where LossType <> 'NULL'