Query - Adding First Order

[former_member1050555]

I have created the below query which runs perfectly: -

SET DATEFIRST 1

SELECT YEAR(T0.[DocDate]) AS 'Year', YEAR(T0.[DocDate]) AS 'Year2', IIF(MONTH(T0.[DocDate])>=4,YEAR(T0.[DocDate])+1,YEAR(T0.[DocDate])) AS 'FYear', IIF(MONTH(T0.[DocDate])>=4,YEAR(T0.[DocDate])+1,YEAR(T0.[DocDate])) AS 'FYear2', MONTH(T0.[DocDate]) AS 'Month', MONTH(T0.[DocDate]) AS 'Month2', (((MONTH(T0.[DocDate])-4 % 12) + 12) % 12)+1 AS 'FMonth', (((MONTH(T0.[DocDate])-4 % 12) + 12) % 12)+1 AS 'FMonth2', DATEPART(ISO_WEEK,T0.[DocDate]) AS 'Week', DATEPART(ISO_WEEK,T0.[DocDate]) AS 'Week2', SUBSTRING(T1.[ItemCode],5,10) AS 'PID', SUM(T1.[Quantity]) AS Quantity, SUM(T1.[LineTotal]*((100-T0.[DiscPrcnt])/100)) AS 'Sales £', SUM(T1.[LineTotal]*((100-T0.[DiscPrcnt])/100))/SUM(T1.[Quantity]) AS 'ASP £', AVG(T1.[StockPrice]) AS 'ACP £', SUM(T1.[GrssProfit]) As 'GP £', CASE WHEN SUM(T1.[LineTotal]*((100-T0.[DiscPrcnt])/100)) > 0 THEN SUM(T1.[GrssProfit])/SUM(T1.[LineTotal]*((100-T0.[DiscPrcnt])/100))*100 ELSE 0 END AS 'GP %', T0.[U_WebReferrer]

FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OCRD T2 ON T0.[CardCode] = T2.[CardCode]

WHERE T0.[DocDate] BETWEEN '[%0]' and '[%1]' AND T2.[GroupCode] = 100 AND T0.Canceled = 'N' AND T1.[ItemCode] <> 'DELIVERY' AND T1.[ItemCode] <> 'DISCOUNT'

GROUP BY YEAR(T0.[DocDate]), MONTH(T0.[DocDate]), DATEPART(ISO_WEEK,T0.[DocDate]), T1.[ItemCode], T0.[U_WebReferrer]

ORDER BY YEAR(T0.[DocDate]), MONTH(T0.[DocDate]), DATEPART(ISO_WEEK,T0.[DocDate])

I want to add a field which shows if it is the first time that customer has ordered, I have successfully done this in another query which didn't have any inner joins by adding the below to the select statement: -

(SELECT IIF(MIN(T3.DocDate)=T0.DocDate,'Y','') FROM ORDR T3 WHERE T3.[CardCode] = T0.[CardCode] AND T3.[CANCELED] = (N'N')) [First Order]

so it now looks like the below: -

SET DATEFIRST 1

SELECT YEAR(T0.[DocDate]) AS 'Year', YEAR(T0.[DocDate]) AS 'Year2', IIF(MONTH(T0.[DocDate])>=4,YEAR(T0.[DocDate])+1,YEAR(T0.[DocDate])) AS 'FYear', IIF(MONTH(T0.[DocDate])>=4,YEAR(T0.[DocDate])+1,YEAR(T0.[DocDate])) AS 'FYear2', MONTH(T0.[DocDate]) AS 'Month', MONTH(T0.[DocDate]) AS 'Month2', (((MONTH(T0.[DocDate])-4 % 12) + 12) % 12)+1 AS 'FMonth', (((MONTH(T0.[DocDate])-4 % 12) + 12) % 12)+1 AS 'FMonth2', DATEPART(ISO_WEEK,T0.[DocDate]) AS 'Week', DATEPART(ISO_WEEK,T0.[DocDate]) AS 'Week2', SUBSTRING(T1.[ItemCode],5,10) AS 'PID', SUM(T1.[Quantity]) AS Quantity, SUM(T1.[LineTotal]*((100-T0.[DiscPrcnt])/100)) AS 'Sales £', SUM(T1.[LineTotal]*((100-T0.[DiscPrcnt])/100))/SUM(T1.[Quantity]) AS 'ASP £', AVG(T1.[StockPrice]) AS 'ACP £', SUM(T1.[GrssProfit]) As 'GP £', CASE WHEN SUM(T1.[LineTotal]*((100-T0.[DiscPrcnt])/100)) > 0 THEN SUM(T1.[GrssProfit])/SUM(T1.[LineTotal]*((100-T0.[DiscPrcnt])/100))*100 ELSE 0 END AS 'GP %', T0.[U_WebReferrer], (SELECT IIF(MIN(T3.DocDate)=T0.DocDate,'Y','') FROM ORDR T3 WHERE T3.[CardCode] = T0.[CardCode] AND T3.[CANCELED] = (N'N')) [First Order]

FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OCRD T2 ON T0.[CardCode] = T2.[CardCode]

WHERE T0.[DocDate] BETWEEN '[%0]' and '[%1]' AND T2.[GroupCode] = 100 AND T0.Canceled = 'N' AND T1.[ItemCode] <> 'DELIVERY' AND T1.[ItemCode] <> 'DISCOUNT'

GROUP BY YEAR(T0.[DocDate]), MONTH(T0.[DocDate]), DATEPART(ISO_WEEK,T0.[DocDate]), T1.[ItemCode], T0.[U_WebReferrer]

ORDER BY YEAR(T0.[DocDate]), MONTH(T0.[DocDate]), DATEPART(ISO_WEEK,T0.[DocDate])

However, this doesn't work and the query fails with 'Incorrect Syntax near First Order' and 'Blanket Agreement' (OOAT) (s) could not be prepared.

Could you help on this please.

Thanks.

[Johan_H · Accepted solution]

Hi Graham,

Please try with CASE statements instead of the IIF function. So for example:

CASE

     WHEN CAST(MONTH(T0.[DocDate]) AS INT) >= 4 THEN YEAR(T0.[DocDate])+1

     ELSE YEAR(T0.[DocDate]) END AS 'FYear'

instead of:

IIF(MONTH(T0.[DocDate])>=4,YEAR(T0.[DocDate])+1,YEAR(T0.[DocDate])) AS 'FYear'

Regards,

Johan

[former_member1050555 · Accepted solution]

Johan,

That part of the query works fine. It is when I add the below to the query when it breaks: -

(SELECT IIF(MIN(T3.DocDate)=T0.DocDate,'Y','') FROM ORDR T3 WHERE T3.[CardCode] = T0.[CardCode] AND T3.[CANCELED] = (N'N')) [First Order]

Thanks.

[Johan_H · Accepted solution]

Hi Graham,

That first one was just an example of how the CASE statement can do the same thing as IIF.

It is the IIF function that doesn't work. What I meant was to replace all instances of IIF with a CASE statement. Please give that a try.

Regards,

Johan

[former_member1050555 · Accepted solution]

Johan,

I tried the CASE statement but that didn't make any difference. I am using the same select statement in a different query as below which works fine: -

SELECT T0.[DocDate] AS 'Posting Date', T0.[DocTotal] - T0.[VatSum] AS 'Net Amount', T0.[U_WebReferrer] AS 'Referring site', T0.[U_PSPUsed] AS 'Payment Processor', T0.GrosProfit,

(SELECT IIF(MIN(T2.DocDate)=T0.DocDate,'Y','') FROM ORDR T2 WHERE T2.[CardCode] = T0.[CardCode] AND T2.[CANCELED] = (N'N')) [First Order]

FROM  [dbo].[ORDR] T0

WHERE T0.[DocDate] BETWEEN '[%startdate]' AND '[%enddate]' AND T0.[CANCELED] = (N'N') AND T0.[CardCode] <> 'AMAZON' AND T0.[CardCode] <> 'EBAY'

ORDER BY T0.[U_PSPUsed]

Any ideas?

Thanks.

Graham.

[Johan_H · Accepted solution]

Hi Graham,

Please try this:

SET DATEFIRST 1

SELECT YEAR(T0.[DocDate]) AS 'Year'

     , YEAR(T0.[DocDate]) AS 'Year2'

  , case when MONTH(T0.[DocDate]) >=4 then YEAR(T0.[DocDate])+1 else YEAR(T0.[DocDate]) end AS 'FYear'

  , case when MONTH(T0.[DocDate]) >=4 then YEAR(T0.[DocDate])+1 else YEAR(T0.[DocDate]) end AS 'FYear2'

  , MONTH(T0.[DocDate]) AS 'Month'

  , MONTH(T0.[DocDate]) AS 'Month2'

  , (((MONTH(T0.[DocDate])-4 % 12) + 12) % 12)+1 AS 'FMonth'

  , (((MONTH(T0.[DocDate])-4 % 12) + 12) % 12)+1 AS 'FMonth2'

  , DATEPART(WEEK,T0.[DocDate]) AS 'Week'

  , DATEPART(WEEK,T0.[DocDate]) AS 'Week2'

  , SUBSTRING(T1.[ItemCode],5,10) AS 'PID'

  , SUM(T1.[Quantity]) AS Quantity

  , SUM(T1.[LineTotal]*((100-T0.[DiscPrcnt])/100)) AS 'Sales £'

  , SUM(T1.[LineTotal]*((100-T0.[DiscPrcnt])/100))/SUM(T1.[Quantity]) AS 'ASP £'

  , AVG(T1.[StockPrice]) AS 'ACP £'

  , SUM(T1.[GrssProfit]) As 'GP £'

  , CASE

     WHEN SUM(T1.[LineTotal]*((100-T0.[DiscPrcnt])/100)) > 0 THEN SUM(T1.[GrssProfit])/SUM(T1.[LineTotal]*((100-T0.[DiscPrcnt])/100))*100

  ELSE 0

    END AS 'GP %'

  , T0.[U_WebReferrer]

  , (SELECT case when MIN(T3.DocDate)=T0.DocDate then 'Y' else '' end

     FROM ORDR T3

  WHERE T3.[CardCode] = T0.[CardCode]

   AND T3.[CANCELED] = (N'N')) [First Order]

FROM ORDR T0

     INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry

  INNER JOIN OCRD T2 ON T0.[CardCode] = T2.[CardCode]

WHERE T0.[DocDate] BETWEEN '[%0]' and '[%1]'

  AND T2.[GroupCode] = 100

  AND T0.Canceled = 'N'

  AND T1.[ItemCode] <> 'DELIVERY'

  AND T1.[ItemCode] <> 'DISCOUNT'

GROUP BY YEAR(T0.[DocDate])

       , MONTH(T0.[DocDate])

    , DATEPART(WEEK,T0.[DocDate])

    , T1.[ItemCode]

    , T0.[U_WebReferrer]

ORDER BY YEAR(T0.[DocDate])

       , MONTH(T0.[DocDate])

    , DATEPART(WEEK,T0.[DocDate])

Regards,

Johan

[former_member1050555 · Accepted solution]

Johan,

I have tried the above query, however, I receive the below error message: -

1). [Microsoft][SQL Server Native Client 11.0][SQL Server]Must specify table to select from.

2). [Microsoft][SQL Server Native Client 11.0][SQL Server]Statement 'Blanket Agreement' (OOAT) (s) could not be prepared.

Any ideas?

Thanks.

Graham.

[Johan_H · Accepted solution]

Hi Graham,

I forgot, because of the embedded queries, B1's basic parameter system breaks. Please try this:

/* SELECT * FROM ORDR x */

DECLARE @FROM AS DATETIME

DECLARE @TO AS DATETIME

SET @FROM = /* x.DocDate */ '[%0]'

SET @TO = /* x.DocDate */ '[%1]'

SELECT YEAR(T0.[DocDate]) AS 'Year'

     , YEAR(T0.[DocDate]) AS 'Year2'

  , case when MONTH(T0.[DocDate]) >=4 then YEAR(T0.[DocDate])+1 else YEAR(T0.[DocDate]) end AS 'FYear'

  , case when MONTH(T0.[DocDate]) >=4 then YEAR(T0.[DocDate])+1 else YEAR(T0.[DocDate]) end AS 'FYear2'

  , MONTH(T0.[DocDate]) AS 'Month'

  , MONTH(T0.[DocDate]) AS 'Month2'

  , (((MONTH(T0.[DocDate])-4 % 12) + 12) % 12)+1 AS 'FMonth'

  , (((MONTH(T0.[DocDate])-4 % 12) + 12) % 12)+1 AS 'FMonth2'

  , DATEPART(WEEK,T0.[DocDate]) AS 'Week'

  , DATEPART(WEEK,T0.[DocDate]) AS 'Week2'

  , SUBSTRING(T1.[ItemCode],5,10) AS 'PID'

  , SUM(T1.[Quantity]) AS Quantity

  , SUM(T1.[LineTotal]*((100-T0.[DiscPrcnt])/100)) AS 'Sales £'

  , SUM(T1.[LineTotal]*((100-T0.[DiscPrcnt])/100))/SUM(T1.[Quantity]) AS 'ASP £'

  , AVG(T1.[StockPrice]) AS 'ACP £'

  , SUM(T1.[GrssProfit]) As 'GP £'

  , CASE

     WHEN SUM(T1.[LineTotal]*((100-T0.[DiscPrcnt])/100)) > 0 THEN SUM(T1.[GrssProfit])/SUM(T1.[LineTotal]*((100-T0.[DiscPrcnt])/100))*100

  ELSE 0

    END AS 'GP %'

  , T0.[U_WebReferrer]

  , (SELECT case when MIN(T3.DocDate)=T0.DocDate then 'Y' else '' end

     FROM ORDR T3

  WHERE T3.[CardCode] = T0.[CardCode]

   AND T3.[CANCELED] = (N'N')) [First Order]

FROM ORDR T0

     INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry

  INNER JOIN OCRD T2 ON T0.[CardCode] = T2.[CardCode]

WHERE T0.[DocDate] BETWEEN @FROM and @TO

  AND T2.[GroupCode] = 100

  AND T0.Canceled = 'N'

  AND T1.[ItemCode] <> 'DELIVERY'

  AND T1.[ItemCode] <> 'DISCOUNT'

GROUP BY YEAR(T0.[DocDate])

       , MONTH(T0.[DocDate])

    , DATEPART(WEEK,T0.[DocDate])

    , T1.[ItemCode]

    , T0.[U_WebReferrer]

ORDER BY YEAR(T0.[DocDate])

       , MONTH(T0.[DocDate])

    , DATEPART(WEEK,T0.[DocDate])

Regards,

Johan

[former_member1050555 · Accepted solution]

Hi Johan,

Sorry for the late reply. Many thanks, your query has worked an absolute treat and was just what we were after.

Thanks again for all your help.

Regards

Graham