[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 326
  • Last Modified:

modify query to run "yesterday's date"

I want this to run with yesterdays U_IIS_DDATE automatically rather than have a date inputted


Thanks in advance


SELECT T1.[SlpName], T2.CardName
, CONVERT(varchar(10), T0.U_iis_ddate, 120) Date
, sum(T0.[Quantity]) Total_fixes
, sum(T0.LineTotal) Invoicing_total
, sum(T3.[U_IIS_TIME]) TaskTime_Total
, T0.[u_iis_INS2], T0.[u_IIS_INS3]
FROM DLN1 T0  
INNER JOIN OSLP T1
  ON T0.SlpCode = T1.SlpCode
INNER JOIN ODLN T2
  ON T0.DocEntry = T2.DocEntry
left outer join OSCN T3
  on t3.ItemCode = t0.ItemCode
 and t3.Cardcode = SUBSTRING(t2.CardCode, 1, 8)
WHERE T2.[U_IIS_JTYPE] = '6'
  and T0.[U_iis_ddate] >= '[%0]'
  and T0.[U_iis_ddate] <= '[%1]'
group by T1.[SlpName], T2.CardName, CONVERT(varchar(10), T0.U_iis_ddate, 120), T0.[u_iis_INS2], T0.[u_IIS_INS3]

Open in new window

0
KeirMcCann
Asked:
KeirMcCann
1 Solution
 
Patrick MatthewsCommented:
WHERE T2.[U_IIS_JTYPE] = '6'
  and T0.[U_iis_ddate] >= (CONVERT(datetime, CONVERT(varchar, GETDATE(), 101)) - 1)
  and T0.[U_iis_ddate] < CONVERT(datetime, CONVERT(varchar, GETDATE(), 101))
0
 
KeirMcCannAuthor Commented:
superfast! Thanks
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now