summary data query - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

summary data query

Author

Topic

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2008-02-23 : 01:50:00

Hi,
I think this will be an easy one for you experts out there.
I am trying to summarise some data.
You can see in the grid below I have an accountno and two other fields. Please ignore the silly column headings (that's what they're actually called in this database) The Linkacct field shows the value of each transaction (and is a Nvarchar field) and the Country field shows the date of each transaction (and is also a Nvarchar field)

Accountno Linkacct Country
A8021217950 128813.8 12/07/07
A8022240320 222.21 21/03/07
A8022240320 3324.98 03/06/06
A8021217950 5689.00 30/01/08
A8022240320 2000.32 12/01/05
A8022240320 4505.32 25/10/07
A8021217950 3900.32 01/02/02

So far I have managed to do the following;

select accountno,count(accountno)as [Totaltrans], sum(cast(linkacct as money)) as [TotalSpend],AVG(cast(linkacct as money)) as [AVGSPend],
MAX(cast(linkacct as money)) as [MAXSPend] , MIN(cast(linkacct as money)) as [MAXSPend], MIN(country)as[1stBooking]

from table1 
group by Accountno

And this is working fine...but I have two problems
1.First problem is I also have to summarise the date fields and show the date of the 1stBooking and the date of the mostrecent booking. So I tried to do MIN(cast country as datetime) but this gives an outofbounds error.
2. The second problem is that users have the ability to edit the values manually, so as soon as a salesperson enters an incorrect value my query will bomb out. Is there a way I can say if linkacct is a monetary value then evaluate the field if not then leave it. And I'll also need to do the same testing for the date field if i can figure out how to turn it into a date field in the first place.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-23 : 02:51:30

Try like this for first part:-

SET DATEFORMAT dmy

select accountno,
count(accountno)as [Totaltrans], 
sum(cast(linkacct as money)) as [TotalSpend],
AVG(cast(linkacct as money)) as [AVGSPend],
MAX(cast(linkacct as money)) as [MAXSPend] , 
MIN(cast(linkacct as money)) as [MAXSPend], 
MIN(country) as [1stBooking],
MAX(cast (country as datetime)) AS LastBookingDate,
MIN(cast (country as datetime)) AS FirstBookingDate
from table1 
group by Accountno

For second part make use of functions ISNUMERIC(Linkacct) to check whether its a valid numeric data and also ISDATE(country) AND LEN(country) =8 to check if its a valid date. You might have to use an INSTEAD OF INSERT/UPDATE trigger if you want to perform the validation before inserting the value to table.

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2008-02-23 : 03:55:54

Thanks so much for the help, that's excellent. My query works now!

For the second bit, thanks for that too , i understand the reasoning and logic but am not sure if the syntax.
Should I use the CASE function somehow?
I just tried this but it didn't work

select accountno, 
sum(CASE WHEN ISNUMERIC(Linkacct) THEN (cast(linkacct as money)) ELSE 0 as [TotalSpend]
from table1
group by Accountno

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-23 : 04:01:55

quote:
Originally posted by icw

Thanks so much for the help, that's excellent. My query works now!

For the second bit, thanks for that too , i understand the reasoning and logic but am not sure if the syntax.
Should I use the CASE function somehow?
I just tried this but it didn't work
select accountno,
sum(CASE WHEN ISNUMERIC(Linkacct) THEN (cast(linkacct as money)) ELSE 0 as [TotalSpend]
from table1
group by Accountno

When do you want to perform validation checks? Before the value gets inserted to table or while retrieving from table for calculations?

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2008-02-23 : 04:16:44

Thanks again,
i only need to check it at time of querying. So I am not too worried about whether they mess up the data or not, i just need my query to work on the valid data.
==========================================
The following error appears

An expression of non-boolean type specified in a context where a condition is expected, near 'THEN'.

When I use

select accountno, 
sum(CASE WHEN ISNUMERIC(Linkacct) THEN (cast(linkacct as money)) ELSE 0 as [TotalSpend]
from table1
group by Accountno

However i did manage to get it to work by using

select accountno, 
sum(cast (linkacct as money))
from table1 where  isnumeric(linkacct) = 1
group by Accountno

Is it OK to do it that way or should I keep trying to get CASE to work?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-23 : 04:18:56

quote:
Originally posted by icw

Thanks again,
i only need to check it at time of querying. So I am not too worried about whether they mess up the data or not, i just need my query to work on the valid data.
==========================================
The following error appears
An expression of non-boolean type specified in a context where a condition is expected, near 'THEN'.

When I use
select accountno,
sum(CASE WHEN ISNUMERIC(Linkacct) THEN (cast(linkacct as money)) ELSE 0 as [TotalSpend]
from table1
group by Accountno

However i did manage to get it to work by using
select accountno,
sum(cast (linkacct as money))
from table1 where isnumeric(linkacct) = 1
group by Accountno

Is it OK to do it that way or should I keep trying to get CASE to work?

do like this. All case constructs shouls have an END clause

select accountno, 
sum(CASE WHEN ISNUMERIC(Linkacct) THEN cast(linkacct as money) ELSE 0 END) as [TotalSpend]
from table1
group by Accountno

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2008-02-23 : 04:52:46

I really appreciate your help.

Unfortunately, I am still getting the same error
even when i add the END keyword

An expression of non-boolean type specified in a context where a condition is expected, near 'THEN'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-23 : 05:01:13

quote:
Originally posted by icw

I really appreciate your help.

Unfortunately, I am still getting the same error
even when i add the END keyword
An expression of non-boolean type specified in a context where a condition is expected, near 'THEN'.

Ah i think i spoted it. you are not evaluating the result of function to a value. change like this & try

select accountno,
sum(CASE WHEN ISNUMERIC(Linkacct)=1 THEN cast(linkacct as money) ELSE 0 END) as [TotalSpend]
from table1
group by Accountno

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2008-02-23 : 05:11:15

Yes, of course I should have spotted that too.

Thanks so much Visakh and have a lovely day;o)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-23 : 05:58:39

quote:
Originally posted by icw

Yes, of course I should have spotted that too.

Thanks so much Visakh and have a lovely day;o)

thanks & wishing you the same

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources