SQL query

[jewel]

hi all
I hope I'm in the right forum here

I am using ms visual studio / sql query
ok I have a field as below
call field to Material Charge field is a 1 to many relationship

=Fields!MaterialCharge.Value

if a call has more than 1 worktime entry - this shows separately with a 0 balance - eg there are two entries for the same call.

If add sum in front with brackets - it seems to add all the amounts and each record ends up with the same amount eg the total of all the 7 records - what would my syntax or formula be to accomplish this.

eg so I need to end up with 7 entries - not 8 like I do now.

is the only way to fix this - use a sub report?

thanks
Dianne

 

[SkipVought]

Dianne,

What is your SQL code?

Please post your 8 rows of data.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[jewel]

thanks Skip

I have put 4 of the records here - you will see by record 3 and 4 what I mean (two worktime records were added for this call there are other fields relevant so two records do need to be added - but for this report I would only want them to show once with a total of the material charge.

1. 6/5/2003 3:33 01AC058321 Robyn BXXXX 5722 Country Mezz cartridge 92298 62.00
2. 6/23/2003 9:49 01AC059816 Brent SXXXX 5910 Finance Mezz cartridge 2200DTN 129.00
3. 6/23/2003 9:51 01AC059817 Dick GXXXX 5855 Services F1 cartridge S020036 0
4. 6/26/2003 9:51 01AC059817 Dick GXXXX 5855 Services F1
cartridge S020036 129.00

so my code is as follows:
SELECT Call.Ref2, Call.CallSubject2, Call.CallNumber, Call.LogDatTim, Call.FirstName, Call.LastName, Call.Phone, Call.OrganizationLevelName,
Worktime.MaterialCharge, Call.Location1, dbo.Call_Comments.Comments
FROM dbo.Worktime Worktime INNER JOIN
dbo.Call Call ON Worktime.CallNumber = Call.CallNumber INNER JOIN
dbo.Call_Comments ON Worktime.CallNumber = dbo.Call_Comments.CallNumber
WHERE (Call.Ref2 = 'AVS') AND (Call.LogDatTim >= '2003-06-01 00:00:00') AND (Call.LogDatTim <= '2003-06-30 00:00:00') OR
(Call.CallSubject2 = 'Supplies') AND (Call.LogDatTim >= '2003-06-01 00:00:00') AND (Call.LogDatTim <= '2003-06-30 00:00:00')

thanks
Dianne

 

[SkipVought]

Dianne,

You need to SUM the MaterialCharge column. But I think that you have OTHER problems as well. Your WHERE logic may be flawed -- I can't be sure. Why? I see an OR in amoungst the ANDs. Depending on how you GROUP your where clause elements using PARENTHESES, you can get vastly different results.

SELECT Call.Ref2, Call.CallSubject2, Call.CallNumber, Call.LogDatTim, 
       Call.FirstName, Call.LastName, Call.Phone, Call.OrganizationLevelName, 
       SUM(Worktime.MaterialCharge), Call.Location1, dbo.Call_Comments.Comments
FROM dbo.Worktime Worktime INNER JOIN
     dbo.Call Call ON Worktime.CallNumber = Call.CallNumber INNER JOIN
     dbo.Call_Comments ON Worktime.CallNumber = dbo.Call_Comments.CallNumber
WHERE (Call.Ref2 = 'AVS') AND (Call.LogDatTim >= '2003-06-01 00:00:00') AND (Call.LogDatTim <= '2003-06-30 00:00:00') OR
      (Call.CallSubject2 = 'Supplies') AND (Call.LogDatTim >= '2003-06-01 00:00:00') AND (Call.LogDatTim <= '2003-06-30 00:00:00') 
GROUP BY Call.Ref2, Call.CallSubject2, Call.CallNumber, Call.LogDatTim, 
       Call.FirstName, Call.LastName, Call.Phone, Call.OrganizationLevelName, 
       Call.Location1, dbo.Call_Comments.Comments

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[PHV]

Isn't the selection logic like this ?
WHERE (Call.Ref2 = 'AVS' OR Call.LogDatTim BETWEEN '2003-06-01 00:00:00' AND '2003-06-30 23:59:59')

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SkipVought]

If that's really what she wants...

WHERE (Call.Ref2 = 'AVS' OR Format(Call.LogDatTim, "yyyymm") = '200306')

Skip,

Be advised: Alcohol and Calculus do not mix!
If you drink, don't derive!

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[PHV]

Skip, I love your new sig.

 

[SkipVought]

I can't take credit for it. I listen to "Wheels with Ed Wallace" each Sat AM, and his announcer, threw that one out there!

I'm assembling a list of them and will be unveiling one each month, Lord willing. ;-)

Skip,

Be advised: Alcohol and Calculus do not mix!
If you drink, don't derive!

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[jewel]

hi Skip & PHV

thanks for your replies

I have replaced my query data with yours (Skip) - I had tried the sum(material field) before but ended up with all records having the same amount = 610.00 which is the total of all.

In replacing and trying to run your query I get the following:

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

not sure what this means to me!

thanks
Dianne

 

[jewel]

Is anyone else able to jump in here ??? thanks

 

[SkipVought]

Dianne,

Your claim, "I had tried the sum(material field) before but ended up with all records having the same amount = 610.00 which is the total of all." does not make sense if your Group By clause is corrrect or if the sum of MaterialCharge is indeed 610 for each GRUOP.

Skip,

[red]Be advised:[/red] Alcohol and Calculus do not mix!
If you drink, don't derive!

Want to get great answers to your Tek-Tips questions? Have a look at FAQ222-2244

 

[jewel]

thanks Skip

Remembering we added the Group clause afterwards on your advice, I didn't have one in, (only having the Select, From, Where) so it may work quite well now but I can't get a result from your code because I get the error mentioned.

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

How do I get around this?

thanks
Dianne

 

[lespaul]

you switch to the SQL view of the query and post the SQL here for someone to look at for you.

leslie

 

[jewel]

ok I was asked to repost my sql query - the following is what I have: error I am getting is: "The text, ntext and image data types cannot be compared or sorted except when using IS NULL or LIKE operator."



SELECT Call.Ref2, Call.CallSubject2, Call.CallNumber, Call.LogDatTim,
Call.FirstName, Call.LastName, Call.Phone, Call.OrganizationLevelName,
SUM(Worktime.MaterialCharge), Call.Location1, dbo.Call_Comments.Comments
FROM dbo.Worktime Worktime INNER JOIN
dbo.Call Call ON Worktime.CallNumber = Call.CallNumber INNER JOIN
dbo.Call_Comments ON Worktime.CallNumber = dbo.Call_Comments.CallNumber
WHERE (Call.Ref2 = 'AVS') AND (Call.LogDatTim >= '2003-06-01 00:00:00') AND (Call.LogDatTim <= '2003-06-30 00:00:00') OR
(Call.CallSubject2 = 'Supplies') AND (Call.LogDatTim >= '2003-06-01 00:00:00') AND (Call.LogDatTim <= '2003-06-30 00:00:00')
GROUP BY Call.Ref2, Call.CallSubject2, Call.CallNumber, Call.LogDatTim,
Call.FirstName, Call.LastName, Call.Phone, Call.OrganizationLevelName,
Call.Location1, dbo.Call_Comments.Comments

thanks
Dianne

 

[SkipVought]

If Call.LogDatTim is a REAL Date/Time field, then you need to use the Format function

WHERE (Call.Ref2 = 'AVS') AND (Format (Call.LogDatTim, "yyyy-mm-dd hh:mm:ss") Between  "2003-06-01 00:00:00" AND "2003-06-30 00:00:00") OR
      (Format(Call.CallSubject2 = 'Supplies') AND ("yyyy-mm-dd hh:mm:ss") Between "2003-06-01 00:00:00" AND "2003-06-30 00:00:00")

and the other issue is your boolean logic in the WHERE clause using OR. Is it
[tt]
(Ref2='AVS') AND (DatTim Between x & y) OR
(Subj='Sup') AND (DatTim Between x & y)
[/tt]
or is it [tt]
(Ref2='AVS' AND DatTim Between x & y) OR
(Subj='Sup' AND DatTim Between x & y)
[/tt]
???

BIG difference.



Skip,
[sub]
[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive! [/sub]

 

[jewel]

thanks Skip I will have a look at this later today.

thanks for your help
Dianne