I have used some SQL on an Informix DB and now want to transfer the SQL to work in Sql Server (code below).

----
select TRIM(wis_quarter),
wis_group[1,2],
round(avg(wis_cases_owned),2),
round(avg(wis_perc_closed),2),
round(avg(wis_perc_close_24),2),
round(avg(wis_perc_reopen),2),
round(avg(wis_ave_days_resolve),2),
round(avg(wis_ave_days_status),2),
round(avg(wis_ia),2),
round(avg(wis_gap),2),
round(avg(wis_ipi_score),2)

from web_indi_scores

where wis_group[1,2] in('NE','CE','SE')

and wis_from_date = (select max(wis_from_date) from web_indi_scores)

group by wis_quarter
       , wis_group[1,2]
order by wis_group[1,2]

----

Now I know that SQL Server does not have a TRIM function so can replace that with an RTRIM function. The rest of the SQL is used to gather statistics by using the AVG function and calculating stats based on the areas NE, SE and CE. Now in my table the areas are not specifically entered into a field, however groups are enetered into a field i.e. NECB, SECS. Now as you can see from the SQL used for the Informix DB i have used the first 2 characters ([1,2]) of the groups values which determins their area (i.e. NE, SE, CE etc) to calculate averages for those areas. HOw do i port this SQL to Sql Server?

I hope i have explained the problem well!

Yeeeeeha cowboy!

__________________
Yeeeeeha cowboy!

SQL Server doesn't have a TRIM function, but it does have RTRIM and LTRIM and you could combine them, as LTRIM(RTRIM(expression)) will trim the leading spaces from an expression which has trimmed the trailing spaces.

Use the SUBSTRING(expression, start,length) function, as WHERE SUBSTRING(wis_group,1,2) in ...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Firstly thankyou for the reply. Now I am by no means an expert with SQL so need a little help.

Ok firstly look at the original SQL for Informix. When run from the command line through Informix I get the following output:

----
q1 2003 CE 117.00 12.27 71.78 52.67 0.39 4.40 2.53 80.00 2.00
q1 2003 NE 117.78 6.30 85.61 40.24 0.09 3.18 2.60 80.00 2.00
q1 2003 SE 113.51 5.78 64.86 57.46 0.00 2.18 2.10 80.00 2.00
q4 2002 NE 138.82 6.67 84.61 42.27 0.22 3.40 2.66 80.00 0.62
----

You can see it extracts the data depending on the group (specified by first 2 characters in the data, e.g. groups NECB, NECSR etc belong to area NE, and groups CECSR, CECB belong to area CE and so on) - you can see that the data is sorted by QUARTER and AREA in the first column (e.g. q1 2003 NE, q1 2003 CE etc). The average of all the data is then displayed for each QUARTER BY AREA. Hence you get the output as above - i hope this makes sense.

Now obviously having duplicated the db in exactly the same way in SQL Server I would like to get the same output. I am using SQL Server with a web front end (ASP.NET with VB.NET), which I am designing using Visual Studio.NET. Now I run the following SQL in SQL Query Analyser:

----
select
LTRIM(RTRIM(wis_quarter))
, wis_group
, round(avg(wis_cases_owned),2)
, round(avg(wis_perc_closed),2)
, round(avg(wis_perc_close_24),2)
, round(avg(wis_perc_reopen),2)
, round(avg(wis_ave_days_resolv),2)
, round(avg(wis_ave_days_status),2)
, round(avg(wis_ia),2)
, round(avg(wis_gap),2)
, round(avg(wis_ipi_score),2)

FROM web_indi_scores

WHERE SUBSTRING(wis_group,1,2) in ('NE','CE','SE')
AND wis_from_date = (SELECT max(wis_from_date) FROM web_indi_scores)

GROUP BY wis_quarter,wis_group

ORDER BY wis_group
----

There are some noticable differences due to errors I received for example notice that [1,2] does not appear as it did on lines 2,15,20 and 21 of the original SQL i gave in my first post. This was because SQL Server did not like them. You can also see that I have used the LTRIM,RTRIM and SUBSTRING function as you specified.

The output of this SQL on SQL Query Analyser was:

----
q4 2002 NECB 7.500000 94.510000 38.110000 1.040000 3.660000 3.760000 80 2.010000 102.390000
q4 2002 NECBE 6.600000 87.860000 32.870000 .000000 5.650000 3.420000 80 .470000 119.010000
q4 2002 NECDK 5.730000 94.120000 52.080000 .000000 3.880000 5.030000 80 .580000 96.510000
q4 2002 NECE 6.580000 93.620000 45.530000 .520000 2.040000 2.240000 80 .730000 153.320000
q4 2002 NECFE 4.160000 70.380000 37.020000 .000000 4.000000 2.310000 80 .330000 149.270000
q4 2002 NECI 6.650000 90.800000 41.200000 .000000 1.320000 1.810000 80 .420000 165.820000
q4 2002 NECN 4.240000 82.140000 30.430000 .000000 1.410000 1.980000 80 .580000 155.900000
q4 2002 NECS 6.250000 84.800000 41.750000 .000000 3.340000 2.440000 80 .470000 136.360000
q4 2002 NECSR 11.970000 65.040000 75.590000 .280000 .840000 1.190000 80 .160000 174.490000
----
Apologies for the formatting - not much i could do but im sure u can cope!

From the information I have given you could you help in showing how I can get a similar result of the informix sql on my sql server? You can see the output just above is completely different from that further above. As i say im by no means an expert with sql and so any helpe would be much appreciated.

Thanks in advance, Luke

Yeeeeeha cowboy!

Why don't you try replacing every instance of wis_group[1,2] with SUBSTRING(wis_group,1,2), you have just replaced the one in the WHERE clause.

Yeah thanks for your reply, I did this just before I read your post and it worked! Overlooked that one. Problem solved (code below)

select
LTRIM(RTRIM(wis_quarter))
, substring(wis_group, 1,2)
, round(avg(wis_cases_owned),2)
, round(avg(wis_perc_closed),2)
, round(avg(wis_perc_close_24),2)
, round(avg(wis_perc_reopen),2)
, round(avg(wis_ave_days_resolv),2)
, round(avg(wis_ave_days_status),2)
, round(avg(wis_ia),2)
, round(avg(wis_gap),2)
, round(avg(wis_ipi_score),2)

FROM web_indi_scores

--WHERE SUBSTRING(wis_group,1,2) in ('NE','CE','SE')
--AND wis_from_date != (SELECT max(wis_from_date) FROM web_indi_scores)

GROUP BY wis_quarter, substring(wis_group, 1,2)

--ORDER BY substring(wis_group, 1,2)

Yeeeeeha cowboy!