Please help me understand SQL

[Former Member]

I am new to SQL, so I would like to understand the parts of this query from a previous thread:

declare @d1 datetime

declare @d2 datetime

set @d1= /* select t.docnum from oinv t where t.DocDate between*/ '[%0]'

set @d2=/* and */'[%1]'

SELECT T1.CardName,

sum(T0.Quantity*T0.PriceBefDi) 'Total Before Discounts',

sum((T0.Quantity*T0.PriceBefDi)-T0.LineTotal)'Line Discount',

(Select Sum(T.DiscSum) From OINV T

Where T.CardName = T1.CardName and T.DocDate between @d1 and @d2) 'Order Discount',

(sum((T0.Quantity*T0.PriceBefDi)-T0.LineTotal)+

(Select Sum(T.DiscSum) From OINV T

Where T.CardName = T1.CardName and T.DocDate between @d1 and @d2)) 'Total Discounts',

(Select Sum(T.TotalExpns) From OINV T

Where T.CardName = T1.CardName and T.DocDate between @d1 and @d2) 'Freight',

(Select Sum(T.DocTotal) From OINV T

Where T.CardName = T1.CardName and T.DocDate between @d1 and @d2) 'Invoice Total'

FROM INV1 T0 INNER JOIN OINV T1 ON T0.DocEntry = T1.DocEntry

WHERE T1.DocDate between @d1 and @d2

Group by T1.CardName

Order By T1.CardName

1) In the set clauses, what do /* and */ indicate?

2) Why refer to table 'oinv' as 't' when OINV is referred to later in the script as T1? Is the first reference creating a temporary table called 'oinv'?

I am trying to do a similar query that lists quantity and $ sales of items. I did this successfully. But when I add a date-range condition (see below) I get the error: conversion failed when converting datetime from character string.

Once I get the date range problem figured out, I have to somehow do the following:

1)when unitMsr = 'kg' convert to LBs

2)when Currency = 'CAN' convert to USD

3)summarize all by T2.U_APPFIELD

If you or anyone else can offer advise, I would appreciate it.

--Sales by Product Group in Detail by Date Range

SELECT T2.U_APPFIELD, T0.DocDate, T0.Quantity, T0.unitMsr, T0.PriceBefDi, T0.Currency

FROM INV1 T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode INNER JOIN dbo.@CHT_PRODGROUP T2 ON T1.U_CHT_PRODGROUP = T2.Code

WHERE T0.DocDate >=%0 AND <=%1

ORDER BY T2.U_APPFIELD

Thanks!

Lorna Bateman

[former_member186095 · Accepted solution]

Hi,

here is a forum relates to SQL server:

http://sql-server-performance.com/Community/forums/t/24533.aspx

you could also post your message there regarding with SQL query.

the code you provided is not worked well in my query editor.

SELECT T2.U_APPFIELD, T0.DocDate, T0.Quantity, T0.unitMsr, T0.PriceBefDi, T0.Currency
FROM INV1 T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode INNER JOIN dbo.@CHT_PRODGROUP T2 ON T1.U_CHT_PRODGROUP = T2.Code
WHERE T0.DocDate >='[%0]' AND <='[%1]'
ORDER BY T2.U_APPFIELD

it is caused by t0.docdate can't be applied to the user table. you must modify it.

Rgds,