calculate total over one date and output to new field

you want something like this:

SELECT T2.CardCode, T2.CardName, T2.U_IIS_INADR
, CONVERT(varchar(10), T0.U_iis_ddate, 120) u_iis_date
, sum(T0.[Quantity]) sum_quantity
, sum(T0.LineTotal) sum_linetotal
, T1.[SlpName]
, sum(T3.[U_IIS_TIME]) sum_uiis_time 
FROM DLN1 T0  
INNER JOIN OSLP T1 
  ON T0.SlpCode = T1.SlpCode 
INNER JOIN ODLN T2 
  ON T0.DocEntry = T2.DocEntry 
left outer join OSCN T3 
  on t3.ItemCode = t0.ItemCode 
 and t3.Cardcode = SUBSTRING(t2.CardCode, 1, 8) 
WHERE T2.[U_IIS_JTYPE] = '6' 
  and T0.[U_iis_ddate] >= '[%0]' 
  and T0.[U_iis_ddate] <= '[%1]'
group by T2.CardCode, T2.CardName, T2.U_IIS_INADR, T1.[SlpName], CONVERT(varchar(10), T0.U_iis_ddate, 120)

Select allOpen in new window

thanks for the swift reply, i took out the dates to test it in sql and it returned:

Msg 306, Level 16, State 2, Line 1
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

Could we also add row total TOTAL when we get it going!

Thanks

>>The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

I would check the datatypes of columns involved and make sure none are set to TEXT / NTEXT.  If any are, please report back on which ones.

T0.Linetotal is numeric
T0.Quantity is numeric
T0.U_IIS_DDate is datetime

do i need to check any others?

Thanks

Keir

and T3.U_IIS_TIME is smallint

is this the problem?

Thanks

Keir

>>>>>
Msg 306, Level 16, State 2, Line 1
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
<<<<<

Given that is the error message was more suggesting that you check the columns involved to see if the ones in the JOIN statements or WHERE clause being compared (field = something) are as the error describes in datatype TEXT, NTEXT, etc.  If so can fix by doing what it says and changing to field LIKE something.  Maybe I am wrong, was just a suggestion.

T0.Linetotal is numeric
T0.Quantity is numeric
T0.U_IIS_DDate is datetime
T3.U_iis_time is smallint

T3.slpcode is smallint
T0.U_IIS_INS2 is ntext
T0.U_IIS_INS3 is ntext


I am unsure what the error is or how i can get around it!! Help much appreicated

If we can find which fields it is talking about, you can use Angel Eyes' suggestion and just alter comparison with LIKE instead of = or use different string functions.  At least that is what the error message is leading me to believe.  You obviously have some NTEXT values in your table according to your last post; however, I don't see any of those fields in Angels suggestion.  Did you add those in?

What is the T-SQL you are working off of to get the error?

T2.CardCode  NVCHAR
T2.CardName  NVCHAR
T2.U_IIS_INADDR  NTEXT
T0.U_IIS_DATE  datetime
T0.quantity  numeric
T0.linetotal  numeric
T1.SlpName  nvarchar
T1.SlPCode - smallint
T3.U_IIS_TIME  smallint

I hope this can help us resolve it!  I am currently working off the Microsoft SQL server studio however the query is hopefully going to be implmented into SAP!

T2.U_IIS_INADDR  NTEXT
Try this with Angel's query if you are using SQL Server 2005!

SELECT T2.CardCode, T2.CardName
, CAST(T2.U_IIS_INADR AS NVARCHAR(MAX)) AS U_IIS_INADDR
, CONVERT(varchar(10), T0.U_iis_ddate, 120) u_iis_date
, sum(T0.[Quantity]) sum_quantity
, sum(T0.LineTotal) sum_linetotal
, T1.[SlpName]
, sum(T3.[U_IIS_TIME]) sum_uiis_time 
FROM DLN1 T0  
INNER JOIN OSLP T1 
  ON T0.SlpCode = T1.SlpCode 
INNER JOIN ODLN T2 
  ON T0.DocEntry = T2.DocEntry 
left outer join OSCN T3 
  on t3.ItemCode = t0.ItemCode 
 and t3.Cardcode = SUBSTRING(t2.CardCode, 1, 8) 
WHERE T2.[U_IIS_JTYPE] = '6' 
  and T0.[U_iis_ddate] >= '[%0]' 
  and T0.[U_iis_ddate] <= '[%1]'
group by T2.CardCode, T2.CardName, CAST(T2.U_IIS_INADR AS NVARCHAR(MAX)), T1.[SlpName], CONVERT(varchar(10), T0.U_iis_ddate, 120)

Select allOpen in new window

Thanks for your patience mwvisa it appears we are getting somewhere!!!

What i think is happening is if the plot numbers are the same it is adding the totals up.  However i want it to just do a line total irrespective of the plot numbers in U_IIS_INADDR

If we take Yusuf Ahmedabadi for example it would read on 1 line -

Sum_quantity - 10 , sum_linetotal - £373.75 (if my maths are right) and sum_uiis_time - 76

i hope this makes sense.
bonus-report.JPG

You would have to remove that from the select list and group by.

SELECT T2.CardCode, T2.CardName
--, CAST(T2.U_IIS_INADR AS NVARCHAR(MAX)) AS U_IIS_INADDR
, CONVERT(varchar(10), T0.U_iis_ddate, 120) u_iis_date
, sum(T0.[Quantity]) sum_quantity
, sum(T0.LineTotal) sum_linetotal
, T1.[SlpName]
, sum(T3.[U_IIS_TIME]) sum_uiis_time 
FROM DLN1 T0  
INNER JOIN OSLP T1 
  ON T0.SlpCode = T1.SlpCode 
INNER JOIN ODLN T2 
  ON T0.DocEntry = T2.DocEntry 
left outer join OSCN T3 
  on t3.ItemCode = t0.ItemCode 
 and t3.Cardcode = SUBSTRING(t2.CardCode, 1, 8) 
WHERE T2.[U_IIS_JTYPE] = '6' 
  and T0.[U_iis_ddate] >= '[%0]' 
  and T0.[U_iis_ddate] <= '[%1]'
group by T2.CardCode, T2.CardName, /* CAST(T2.U_IIS_INADR AS NVARCHAR(MAX)), T1.[SlpName], */ CONVERT(varchar(10), T0.U_iis_ddate, 120)

Select allOpen in new window

thanks again for your dedication to my idiocy!!!

this error i get now >,<

Msg 8120, Level 16, State 1, Line 1
Column 'OSLP.SlpName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

i understand that i could take it out but i need to see the slpname >,<

Sorry, I put my end comment tag too far over...that was a type-o.

SELECT T2.CardCode, T2.CardName
--, CAST(T2.U_IIS_INADR AS NVARCHAR(MAX)) AS U_IIS_INADDR
, CONVERT(varchar(10), T0.U_iis_ddate, 120) u_iis_date
, sum(T0.[Quantity]) sum_quantity
, sum(T0.LineTotal) sum_linetotal
, T1.[SlpName]
, sum(T3.[U_IIS_TIME]) sum_uiis_time 
FROM DLN1 T0  
INNER JOIN OSLP T1 
  ON T0.SlpCode = T1.SlpCode 
INNER JOIN ODLN T2 
  ON T0.DocEntry = T2.DocEntry 
left outer join OSCN T3 
  on t3.ItemCode = t0.ItemCode 
 and t3.Cardcode = SUBSTRING(t2.CardCode, 1, 8) 
WHERE T2.[U_IIS_JTYPE] = '6' 
  and T0.[U_iis_ddate] >= '[%0]' 
  and T0.[U_iis_ddate] <= '[%1]'
group by T2.CardCode, T2.CardName, /* CAST(T2.U_IIS_INADR AS NVARCHAR(MAX)), */ T1.[SlpName],  CONVERT(varchar(10), T0.U_iis_ddate, 120)

Select allOpen in new window

I still have the same problem however, i only want to see one employee per day i.e. one Yusuf Ahmedabadi but this outputs several entries for one engineer.  

It think maybe we are comparing the wrong fields? maybe we should compare only slpname and if it has the same name then work out line total for that day??

It is totalling up some however not all?

this stuff is so mind boggling when you dont understand! I appreciate your patience! :)