Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# calculate total over one date and output to new field

Posted on 2008-10-29
Medium Priority
244 Views
Hi,

Please see attached picture, i need an SQL query to work out the total task time per date for each sales employee entry.  SO rather than having 7 Charlie devines it would have 1 with his total task time for that day.  I understand there are a few empty task time fields but we are working on getting these completed.
The output would be:

Customer/Vendor Name, Site Address, Row Total for day, Sales Employee Name, WOrkign with (Installer 2/3), and Task Time total for day.
SELECT T2.CardCode, T2.CardName, T2.U_IIS_INADR, T0.[DocEntry], T0.[ItemCode], T0.[Dscription]
, T0.U_iis_ddate, T0.[Quantity], T0.LineTotal, T1.[SlpName], T0.[u_iis_INS2], T0.[u_IIS_INS3], T3.[U_IIS_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]'
untitled.JPG
0
Question by:KeirMcCann
• 10
• 7

LVL 143

Expert Comment

ID: 22829645
you want something like this:
, 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)
0

Author Comment

ID: 22829670
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.
0

Author Comment

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

Thanks
0

LVL 61

Expert Comment

ID: 22857838
>>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.
0

Author Comment

ID: 22865673
T0.Linetotal is numeric
T0.Quantity is numeric
T0.U_IIS_DDate is datetime

do i need to check any others?

Thanks

Keir
0

Author Comment

ID: 22865706
and T3.U_IIS_TIME is smallint

is this the problem?

Thanks

Keir
0

LVL 61

Expert Comment

ID: 22866707
>>>>>
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.
0

Author Comment

ID: 22876171
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
0

LVL 61

Expert Comment

ID: 22876241
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?
0

Author Comment

ID: 22876635
T2.CardCode  NVCHAR
T2.CardName  NVCHAR
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!
0

LVL 61

Expert Comment

ID: 22876763
Try this with Angel's query if you are using SQL Server 2005!

SELECT T2.CardCode, T2.CardName
, 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)
0

Author Comment

ID: 22876862
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
0

LVL 61

Expert Comment

ID: 22879142
You would have to remove that from the select list and group by.
SELECT T2.CardCode, T2.CardName
, 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)
0

Author Comment

ID: 22879436
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.
0

Author Comment

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

LVL 61

Expert Comment

ID: 22879492
Sorry, I put my end comment tag too far over...that was a type-o.
SELECT T2.CardCode, T2.CardName
, 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)
0

Author Comment

ID: 22879643
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! :)

0

LVL 61

Accepted Solution

Kevin Cross earned 2000 total points
ID: 22882109
I am sorry I saw that in your image, but thought I was looking at the raw data since the column names were different than in query.  Yes, the customer/supplier number is different over each of the records so will not group correctly.

I would just group on slpname and date if that is what you need.
SELECT 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 T1.[SlpName],  CONVERT(varchar(10), T0.U_iis_ddate, 120)
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides? Â Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff lâ€¦
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
###### Suggested Courses
Course of the Month10 days, 6 hours left to enroll