Skip to main content

Manual Query Input

Answered

Comments

4 comments

  • Yoni Lerner

    Are you trying to create a table just from a SQL statement?

    Can you give an actual example of a statement you are trying to run?

    0
  • Zoltan Hajdu

    Hi Yoni, here an example:

    /*
    VErsion 3; Outside Cost added
    Version 2; Working Due India added
    Version 1 created at 23.02*/

    -- set transaction isolation level read uncommitted;
    WITH CTE_COST
    as
    (SELECT /*convert (date, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0))  firstOfCurrentMonth*/
    sum (ia.amount) sumAmount
    ,ISNULL (p2.inventionID,i2.InventionId) inventionID
      FROM [Anaqua].[dbo].[Invoice] invo
      inner join InvoiceAllocation IA on IA.InvoiceId =invo.invoiceID
      inner join currency cur on cur.[CurrencyId]=invo.CurrencyId
      left join invention i2 on i2.inventionID=ia.recordid
      left join patent p2 on p2.patentID=ia.recordid
      /*entweder hier schon ausgrenzen */
      where InvoiceDate > convert (date,dateadd (MONTH,-13, DATEADD(month, DATEDIFF(month,0 , GETDATE()), 0)))
      and InvoiceDate <convert (date, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0))
      and cur.[CurrencyCode]='EUR'
      group by   isnull (p2.inventionID,i2.InventionId))
    Select I.InventionId as 'InventionId
    ,R.ReferenceNumber as 'PF'
    ,i.InventionTitle as 'Title',
     CASE
     WHEN RST.IsActive = 1 -- StatusTypeName IN ('To Be Filed', 'Instructed', 'Filed', 'Published', 'Examination Requested') -- NOCH ZU KLÄREN s. deadorlive.sql
     THEN 'alive'
     ELSE 'dead'
     END as 'PF Status'
     ,STUFF ((SELECT ', '  + c.ClassificationCode
       FROM Classification c
       INNER JOIN LnkRecordClassification lrc ON LRC.ClassificationId = C.ClassificationId  AND C.ClassificationTreeId = 81024516  -- Legacy - Technical Classification (SGKZ)
       WHERE lrc.RecordId = i.InventionId
       FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS 'TechField(SGKZ)'
     ,STUFF ((SELECT ', '  + c.ClassificationCode
       FROM Classification c
       INNER JOIN LnkRecordClassification lrc ON LRC.ClassificationId = C.ClassificationId  AND C.ClassificationTreeId = 81025833  -- Technical Area
       WHERE lrc.RecordId = i.InventionId
       FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS 'TechArea'
     ,STUFF ((SELECT ', '  + c.ClassificationDescription
       FROM Classification c
       INNER JOIN LnkRecordClassification lrc ON LRC.ClassificationId = C.ClassificationId  AND C.ClassificationTreeId = 81025833  -- Technical Area
       WHERE lrc.RecordId = i.InventionId
       FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS 'TechArea Text'

     ,STUFF ((SELECT distinct ', '  + dgi.Item
       FROM LnkRecordClientBudget lrcb
       inner join LnkIpRecordFields lirf on lirf.Recordid = lrcb.ClientId
       inner join RefIpFields rif on rif.ipmapfieldid = lirf.ipmapfieldid and rif.FieldLabel = 'Division'
       inner join DataGroupItem DGI on DGI.DataGroupItemId=try_convert (int,lirf.FieldText)
       WHERE lrcb.RecordId = i.InventionId
       FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS 'Cost Center Division AGG'
    ,DGIDivision.item Division

    ,STUFF ((SELECT concat (', ' ,  lirfs.FieldText ,' ' , lbhc.LegalName , ': ' , CAST(lrcb.Stakeholder as VARCHAR) + '%')
       FROM LnkRecordClientBudget lrcb
       INNER JOIN LegalBody lbc ON lbc.LegalBodyId = lrcb.ClientId
       inner join LegalBodyHistory lbhc on lbhc.legalBodyHistoryId = lbc.LatestLegalBodyHistoryId
       left join (LnkIpRecordFields lirfs
       inner join RefIpFields rifs on rifs.ipmapfieldid = lirfs.ipmapfieldid and rifs.FieldLabel = 'Strategic Business Unit' ) on lirfs.Recordid = lrcb.ClientId
       WHERE lrcb.RecordId = i.InventionId
       FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS 'Cost Centers'
     ,STUFF ((SELECT ', '  + lirfs.FieldText
       FROM LnkRecordClientBudget lrcbs
       inner join LnkIpRecordFields lirfs on lirfs.Recordid = lrcbs.ClientId
       inner join RefIpFields rifs on rifs.ipmapfieldid = lirfs.ipmapfieldid and rifs.FieldLabel = 'Strategic Business Unit'
       WHERE lrcbs.RecordId = i.InventionId
       FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS 'Strategic Business Unit'
    ,(select convert (date, max(e.EvaluationDate)) from evaluation e
    where I.inventionid = e.recordid
    group by RecordId) as 'Latest Patent Rewue Meeting'
    ,UIr.LongName as 'BASF Attorney'
    ,I.ToBeFiledDate as 'PF Priority Date'
    ,RST.StatusTypeName as 'PF Final Status'
    ,STUFF ((SELECT ', '  + case when p.ApplicationTypeId=29 then    concat (replace (right (r.referencenumber,4),'01','') ,'-Prio') else replace (right (r.referencenumber,4),'01','') end
       FROM Patent p
       inner join record r on r.recordid = p.patentid  
       WHERE p.Inventionid = i.InventionId
       and p.DateValue is not null -- inactive Date vorhanden
       FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS 'no longer active'
    ,STUFF ((SELECT ', '  + case when p.ApplicationTypeId=29 then    concat (replace (right (r.referencenumber,4),'01','') ,'-Prio') else replace (right (r.referencenumber,4),'01','') end
       FROM Patent p
       inner join record r on r.recordid = p.patentid   
       inner join RefStatusType rsta on rsta.StatusTypeId = r.StatusTypeId
       WHERE p.Inventionid = i.InventionId
       and (rsta.StatusTypeName IN ('Granted', 'Granted (Final)')
       or (p.DateValue is null -- active Date vorhanden
       and p.GrantedDate is not null)      )
       FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS 'granted and active'
    ,sndSQL.[not granted yet incl. Foreign Filings Scheduled]
    --nur für patent nicht für filing plan !!! nachfragen
    ,FFSSQL.ForeignFilingsScheduled as 'Scheduled Application' --Bajohr fragen??,
     ,format (isnull (i.closeddate,
             (select max (p.ExpiryDate)  TermYear
       from patent p
    where p.DateValue is null -- inactive nicht gefüllt
          and p.InventionId=i.inventionID
       and p.ExpiryDate is not null)),'yyyy') as 'PF Term Year'
    /*,(SELECT /*convert (date, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0))  firstOfCurrentMonth*/
    sum (ia.amount) sumAmount
      FROM [Anaqua].[dbo].[Invoice] invo
      inner join InvoiceAllocation IA on IA.InvoiceId =invo.invoiceID
      inner join currency cur on cur.[CurrencyId]=invo.CurrencyId
      left join invention i2 on i2.inventionID=ia.recordid
      left join patent p2 on p2.patentID=ia.recordid
      /*entweder hier schon ausgrenzen */
      where InvoiceDate > convert (date,dateadd (MONTH,-13, DATEADD(month, DATEDIFF(month,0 , GETDATE()), 0)))
      and InvoiceDate <convert (date, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0))
      and cur.[CurrencyCode]='EUR'
      and  isnull (p2.inventionID,i2.InventionId)=i.inventionID) as 'Outside PF Costs'*/
      ,CTE_COST.sumAmount as 'Outside PF Costs'
    ,(select count (distinct AgreementId) from LnkAgreementSubject las
    where las.recordid = i.inventionid or las.recordid in (select patentid from patent p where p.InventionId=i.inventionid)) as'Licence Agreement Counts'
    ,
     STUFF((SELECT CONCAT(CHAR(10) , CHAR(13) , ProductName, ' (', ReportingPeriodName, ')' )       
    from (
    SELECT top 1 with ties
              p.ProductName, rrp.ReportingPeriodName , rrp.toDate ,RANK () over ( partition by   p.ProductName order by rrp.toDate desc , rrp.ReportingPeriodId desc )  rang
            from LnkProductRemunerationReportingPeriodInvention LPRRPI
      INNER JOIN ProductRemunerationReportingPeriod PRRP ON PRRP.ProductRemunerationReportingPeriodId = LPRRPI.ProductRemunerationReportingPeriodId
      inner join RefReportingPeriod rrp on rrp.ReportingPeriodId = PRRP.ReportingPeriodId
      inner join Product p on p.ProductId = PRRP.ProductId
      INNER JOIN Record R ON R.RecordId = PRRP.ProductId
      where R.StatusTypeId IN (11553, 20047)  -- 'In Use' or 'Single Use'
      AND LPRRPI.Inventionid =  I.InventionId  
        order by rang
        ) SQLProductUse
      FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS 'Products'


    --, (select format (max (latestUSE),'yyyy') latestuse from SQLProductUse)  as 'End if Inventors Remuneration'
    ,(select   format (max (rrp.toDate),'yyyy') latestuse
            from LnkProductRemunerationReportingPeriodInvention LPRRPI
      INNER JOIN ProductRemunerationReportingPeriod PRRP ON PRRP.ProductRemunerationReportingPeriodId = LPRRPI.ProductRemunerationReportingPeriodId
      inner join RefReportingPeriod rrp on rrp.ReportingPeriodId = PRRP.ReportingPeriodId
      inner join Product p on p.ProductId = PRRP.ProductId
      INNER JOIN Record R ON R.RecordId = PRRP.ProductId
      where R.StatusTypeId IN (11553, 20047)  -- 'In Use' or 'Single Use'
      AND LPRRPI.Inventionid =  I.InventionId  --81125279
      ) as 'End of Inventors Remuneration'
    ,
    STUFF ((SELECT distinct ', '  + LBH.LegalName
       FROM [dbo].[LnkRecordOwner] LRO
       inner join LegalBodyHistory LBH on LBH.LegalBodyHistoryId=LRO.LegalBodyHistoryOwnerId

       WHERE   LRO.RecordId=i.InventionId
       FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS 'InventionOwner'
    /*
    STUFF ((SELECT distinct ', '  + lbhy.LegalName
       FROM  patent P 
       inner Join RecordProprietorRecordal rpr on rpr.recordid = p.patentid
       inner join LnkRecordProprietorRecordalOwner lrpro on lrpro.RecordProprietorRecordalId = rpr.RecordProprietorRecordalId
       inner join LegalBodyHistory lbhy on lbhy.LegalBodyHistoryId=lrpro.LegalBodyHistoryOwnerId
       WHERE  p.InventionId=i.InventionId
       AND  rpr.IsCurrentLegalOwner = 1
       FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS 'PatentOwner'
       */
    ,STUFF ((SELECT distinct ', '  + lbhy.LegalName
       FROM LegalBodyHistory lbhy
       inner join LnkIpApplicant lipa on lipa.ApplicantLegalBodyHistoryId = lbhy.LegalBodyHistoryId
       INNER JOIN patent P on  lipa.RecordId = p.PatentId
       WHERE  p.InventionId=i.InventionId
       FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS 'Applicant'
    ,STUFF ((SELECT ', '  + uio.LongName
       FROM UserInformation uio
       inner join LnkInventionInventor lii on lii.Inventorid = uio.userId
       WHERE lii.InventionId = i.InventionId
       FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS 'Inventors'
    ,WorkinDue.[IN-Working-Due]
    --Start From Clause
    from Invention I
    Join Record R ON I.InventionId = R.RecordId
    Join RefStatusType RST On R.StatusTypeId = RST.StatusTypeId

    LEFT Join (LnkIpContact LIPR
               inner join UserInformation UIR ON LIPR.UserId = UIR.UserId)
         ON i.InventionId = LIPR.RecordId  AND LIPR.IpContactTypeId = '21'    -- responsible Manager
    left join CTE_Cost on CTE_COST.inventionID=i.inventionID
    outer apply
    (select STUFF ((SELECT ', '  + case when INNERSQL.ApplicationTypeId=29 then    concat (INNERSQL.TerritoryCode ,'-Prio') else INNERSQL.TerritoryCode end  ld
    from
    (select TerritoryCode, 'FP' Sourc, case when ifp.IsFirstFiling=1 then 29 else null end  ApplicationTypeId
       FROM InventionFilingPlan IFP
       inner join territory t on t.TerritoryId = ifp.TerritoryId
       where ifp.PatentId  is null and ifp.ApprovedDate is not null -- geplant ohne patent Zuordnung
       and IFP.Inventionid = i.InventionId   
       union all
       select TerritoryCode, 'Patent' Sourc,  p.ApplicationTypeId
       FROM Patent p
       inner join record r on r.recordid = p.patentid
       inner join territory t on t.TerritoryId = r.TerritoryId   
       where p.InventionId=i.inventionID
       and p.DateValue is null
       and p.GrantedDate is  null
       ) as innerSQL
       FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS 'not granted yet incl. Foreign Filings Scheduled'
       ) as sndSQL
    outer apply
    (select STUFF ((SELECT ', '  + case when INNERSQL.ApplicationTypeId=29 then    concat (INNERSQL.TerritoryCode ,'-Prio') else INNERSQL.TerritoryCode end  ld
    from
    (select TerritoryCode, 'FP' Sourc, case when ifp.IsFirstFiling=1 then 29 else null end  ApplicationTypeId
       FROM InventionFilingPlan IFP
       inner join territory t on t.TerritoryId = ifp.TerritoryId
       where ifp.PatentId  is null and ifp.ApprovedDate is not null -- geplant ohne patent Zuordnung
       and IFP.Inventionid = i.InventionId   
       union all
       select TerritoryCode, 'Patent' Sourc,  p.ApplicationTypeId
       FROM Patent p
       inner join record r on r.recordid = p.patentid
       inner join territory t on t.TerritoryId = r.TerritoryId
       inner join RefIpLinkType rilt on rilt.IpLinkTypeId = p.ApplicationTypeId
       where p.InventionId=i.inventionID
       and p.DateValue is null
       and p.GrantedDate is  null
       /*??? Rückmeldung Bajohr, welche länder sind schon angemeldet?*/
       and (((p.FiledDate is not null or p.NationalFiledDate is not null) and rilt.SystemTypeName not in ('DesignatedEP','DesignatedPCT','DesignatedAP','DesignatedEA'))
       or  ((p.NationalPhaseEnteredDate is not null or p.SecondaryAgentAcknowledgedDate is not null or p.SecondaryAgentInstructedDate is not null)
               and rilt.SystemTypeName  in ('DesignatedEP','DesignatedPCT','DesignatedAP','DesignatedEA')))
       ) innerSQL
       FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS 'ForeignFilingsScheduled'
       ) as FFSSQL
    outer apply  ( select distinct  DGIDivision.Item
               from  LnkRecordClientBudget lrcb
       inner join LnkIpRecordFields lirf on lirf.Recordid = lrcb.ClientId
       inner join RefIpFields rif on rif.ipmapfieldid = lirf.ipmapfieldid and rif.FieldLabel = 'Division'
       inner join DataGroupItem DGIDivision on DGIDivision.DataGroupItemId=try_convert (int,lirf.FieldText)
        where   lrcb.RecordId = i.InventionId
       )  as DGIDivision
    --Working due India
    outer apply (select top 1 with ties
     rank () over (partition by p.inventionID order by case when actionDate <= getdate ()  and isclosed =0 then 1 else 2 end , isclosed,  actionDate desc ) rang ,
     concat (year (c.ActionDate), ' - task ', case when isclosed=0 then 'open' else 'closed' end  ) 'IN-Working-Due'
     from chaser c
     inner join patent p on p.patentid=c.RecordId
    where c.chasername like 'IN - Working Due'
    and p.inventionID=i.InventionID
    order by rang) as WorkinDue
    where r.ReferenceNumber is not null 
    and RST.StatusTypeName not in ('Change Inventorship – Submitted','Change Inventorship - Draft')

     

    0
  • Zoltan Hajdu

    Sincerely: Zoltán

    0
  • Permanently deleted user

    Hi Zoltan,

    In order to define an expression you have to select a table. The UI requires you to select a table or view first, and then you can define the SQL however you like. You could also define a stored procedure if that's your business need.

    Best,

    Orit

    0

Post is closed for comments.