Skip to Content

Query - Adding First Order

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.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Best Answer
    Posted on Jun 16, 2016 at 12:50 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.