SQL - need to sum - not blocking on date

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SQL - need to sum - not blocking on date

Author

Topic

jewel
Starting Member

18 Posts

Posted - 2004-08-09 : 20:22:02

hi All

I have posted my sql code below: my problems with this are:

1. This is not blocking on the date selected
2. I need to sum on the Worktime.MaterialCharge - as at the moment if there is more than one worktime record for a call they show separately - so I only want the call to show once with total material charges per call. When I tried to put the word sum in front of (Worktime.MaterialCharge) it then errored telling me I had no GROUP by clause - so if I put all the select fields in the group by clause except the comments & worktime - it asks for them - so if I put them in I then get the error "The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator"

cheers

SELECT C.Call.Ref2, C.Call.CallSubject2, C.Call.CallNumber, C.Call.LogDatTim, C.Call.FirstName, C.Call.LastName, C.Call.Phone, C.Call.OrganizationLevelName, (Worktime.MaterialCharge), C.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 (C.Call.Ref2 = 'AVS') OR (C.Call.CallSubject2 = 'Supplies Avalon')AND (C.Call.LogDatTim BETWEEN '2003-06-01 00:00:00'AND '2003-06-30 00:00:00')

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-08-09 : 20:41:18

1. You might want to play around with your bracketing of the WHERE clauses. It looks like it's only checking the date if the CallSubject2 is 'Supplies Avalon'. Try adding brackets around the OR filters and see how you go.
2. If you want to do an aggregate, you'll need to either a) Convert your text fields to varchar's for the query or b) Create a separate view that contains only the CallNumber and total MaterialCharge. You can then include that view in your query as another join.

jewel
Starting Member

18 Posts

Posted - 2004-08-09 : 22:47:39

thanks Tim

If I change the OR to AND it blocks on my date. (only prob with this I guess is if someone didn't add the AVS where it should be, so both have to exist don't they?

I haven't done any convert fields as yet - can you give me an example?

and where you said create a separate view - is this like a sub report etc

I have sql query analyser and Studio.net

cheers
jewel

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-08-09 : 22:56:20

You need to break down your clauses and verify that each of them work correctly before building them up in this way:

WHERE (first clause) AND/OR (second clause)

and always use bracketing around the clauses if you're not sure of the execution order.

Here's an example of a convert:

CONVERT(varchar, dbo.Call_Comments.Comments)

Add this into your GROUP BY to get around that problem.

You're almost there on the view - it's like a sub-query. You create the view with something like:

Create View vwTotalMaterialCharges AS
 SELECT CallNumber, SUM(MaterialCharge) as TotalMaterialCharge
 FROM WorkTime
 GROUP BY CallNumber

Then you can reference vwTotalMaterialCharges as if it's another table.

jewel
Starting Member

18 Posts

Posted - 2004-08-10 : 19:07:31

thanks Tim

I appreciate your help but as I'm very new to this I need to take a few steps backwards first.

I have Visual Studio open - and have a report in the solution explorer - which has Data // Layout // preview, in data obviously is where my code is.

so when you ask me to create the view - do I put it at the top of my code then run into my current report code if not where do I do it??

sorry to appear dense but we all have to start somewhere!
thanks
jewel

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-08-10 : 19:57:26

You'd be much better of if you build all your SQL code etc in Query Analyser and test it before bringing it into VS.
The view would only be created once - it is then stored in the database for future use. Then you build your SQL statement in QA and verify that the data being returned is correct before moving onto creating the report. You should find this much easier than trying to build everything from within VS.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources