Solved

calculate total over one date and output to new field

Posted on 2008-10-29
19
238 Views
Last Modified: 2011-10-19
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]'

Open in new window

untitled.JPG
0
Comment
Question by:KeirMcCann
  • 10
  • 7
19 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22829645
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)

Open in new window

0
 

Author Comment

by:KeirMcCann
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

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

Thanks
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 59

Expert Comment

by:Kevin Cross
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

by:KeirMcCann
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

by:KeirMcCann
ID: 22865706
and T3.U_IIS_TIME is smallint

is this the problem?

Thanks

Keir
0
 
LVL 59

Expert Comment

by:Kevin Cross
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

by:KeirMcCann
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 59

Expert Comment

by:Kevin Cross
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

by:KeirMcCann
ID: 22876635
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!
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22876763
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)

Open in new window

0
 

Author Comment

by:KeirMcCann
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 59

Expert Comment

by:Kevin Cross
ID: 22879142
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)

Open in new window

0
 

Author Comment

by:KeirMcCann
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

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

Expert Comment

by:Kevin Cross
ID: 22879492
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)

Open in new window

0
 

Author Comment

by:KeirMcCann
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 59

Accepted Solution

by:
Kevin Cross earned 500 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)

Open in new window

0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now