Skip to Content
0
Former Member
Jul 20, 2010 at 03:54 AM

Query - Invoices posted yesterday excluding weekends. Drill arrows missing.

152 Views

Hi All,

I have a working query that returns all AR Invoices posted Yesterday excluding weekends.

There are however no drill down yellow arrows in the result for DocNum. Is there a way I can restructure my query so the drill own arrows appear?

declare @dt_today datetime,

@dt_yesterday datetime

select @dt_today = DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)

select @dt_yesterday = case DATEPART(DW,@dt_today)

when 2 then DATEADD(dd, -3, @dt_today)

when 1 then DATEADD(dd, -2, @dt_today)

else DATEADD(dd, -1, @dt_today) end

SELECT T0.[DocNum] As 'Invoice number', T0.[DocDate] As 'Posting Date',

T0.[DocTotal] As 'Invoice Total', T0.[GrosProfit] As 'Gross Profit',

((T0.[GrosProfit] / (T0.[DocTotal] - T0.[GrosProfit]))*100) As 'Profit %',

T1.[CardCode], T1.[CardName], T0.[NumAtCard] As 'Customer PO#', T2.[GroupName],

T1.[Phone1], T1.[CntctPrsn]

FROM OINV T0 INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode INNER JOIN OCRG

T2 ON T1.GroupCode = T2.GroupCode WHERE T0.[DocDate] = @dt_yesterday