?
Solved

calculate total over one date and output to new field

Posted on 2008-10-29
19
Medium Priority
?
242 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 7
19 Comments
 
LVL 143

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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 60

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 60

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 60

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 60

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 60

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 60

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 60

Accepted Solution

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

Open in new window

0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
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.

777 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