Solved

calculate total over one date and output to new field

Posted on 2008-10-29
19
237 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
 
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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

757 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

21 Experts available now in Live!

Get 1:1 Help Now