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

[Former Member]

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

[former_member224918]

Hi John,

You could add the T0.[DocEntry] column to your query so that you get a drill down link. If you use AS in the query add FOR BROWSE at the end of the SQL query, however if you want to use just DocEntry without AS there is no need to add the FOR BROWSE.

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.[DocEntry] AS 'Invoice JE', 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

FOR BROWSE

Best,

Paul.

[Former Member]

Hi Zal,

I hope you don't leave the forum. I (along with others I assume) need all the help I can get

Gordon's select statement was more elegant than my original query attempt but your solution worked absolutely perfectly and offered a great heads up for my future attempts. Thanks again.

Best,

John

[Former Member]

Thanks to you both.

The For Browse solution works with the original query. This being said, Gordon's solution is a much more elegant select statement.

Many thanks to you both.

Regards,

John

[zal_parchem2]

Hello John - quite welcome...

LOL - Gordon always comes up with the best answers - that is why he has so many points!

Now that Gordon is here I know I can leave the forum with everyone in the best of hands...

Take care - Zal

[Former Member]

Hi,

Try this way:

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 DATEDIFF(DD, T0.DocDate, GetDate()) = 1 OR (DATEDIFF(DD, T0.DocDate, GetDate()) = 3 AND DATEPART(dw,GetDate()) in (1,2))

If still no arrow, add For Browse.

Thanks,

Gordon

[zal_parchem2]

Hello John -

I did not test your SQL but one thing I always do is put a FOR BROWSE at the end of each SQL that I write. In many instances, that will get those orange arrows back! Sometimes you cannot use the FOR BROWSE, due to sums and all, but that is a good final step to take each time you write SQL...

Hope that helps,

Zal