Access to SQL Server - 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

General SQL Server Forums

New to SQL Server Programming

Access to SQL Server

Author

Topic

aash
Starting Member

11 Posts

Posted - 2011-03-24 : 14:00:06

Hi,

I am not a programmer and I have been given a SQL query to fetch some data from the access DB for reporting purpose. Now the DB is migrated to SQL server and I cant use the same query for my reports.

Any help is highly appreicated. I dont have any programmers here at my office to help..

SELECT RDATE as REPORTDATE, VSON as VIER, Sum(TAIMS) as TDS, sum(NOKES) as TKS, (TKS/TDS)as KSPD, (TKS)/(sum(EMINS-SMINS))*60) as KSHR, (KSHR/KSPD) as DPH, (4100/KSHR)*DPH as NormalHR, (TKS/KSHR) as Hours, (Hours)*60 as Minutes FROM WINTPOP WHERE VSON = '333' and (RDATE Between '1/3/2011' and '1/7/2011' Group by VDATE,VSON

Error - Invalid column name KSPD

Can anyone help me please?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-24 : 14:46:18

That's because SQL Server doesn't recognize it as a column. You can't name a column in a select statement and then use that name in the same select statement. So every time you referred to KSPD, you'll have to replace it with (sum(NOKES)/sum(TAIMS). The below is untested and I didn't check my algebra, but it should help

SELECT ReportDate,VIER
,TKS/Mins as KSHR
,4110 / (TKS/MINS) as NormalHR
,Mins as Hours
,MINS * 60 as Minutes
FROM

(SELECT RDATE as REPORTDATE
, VSON as VIER
, Sum(TAIMS) as TDS
, sum(NOKES) as TKS
,sum(EMINS - SMINS)*60 as Mins
,sum(Taims)/sum(NOKES) as KSPD
FROM WINTPOP WHERE VSON = '333' and (RDATE Between '1/3/2011' and '1/7/2011' Group by RDATE,VSON
) t

Jim

Everyday I learn something that somebody else already knew

aash
Starting Member

11 Posts

Posted - 2011-03-24 : 15:12:17

Thanks very much for responding,

I tried what you said but its now giving a different error

Incorrect syntax near keyword 'as

I checked and made sure there is no additional cote or anything.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-03-24 : 15:14:03

Also, be careful if your fields are integer types when dividing them. You may need to cast, or force a cast.

For example, in SQL Server:
10/4 returns 2
10/4.0 return 2.500

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-03-24 : 15:17:22

I think Jim accidentally got a stray parenthesis in there

FROM WINTPOP WHERE VSON = '333' and (RDATE Between '1/3/2011' and '1/7/2011' Group by RDATE,VSON

remove that

aash
Starting Member

11 Posts

Posted - 2011-03-24 : 15:34:46

Still getting the same error

SELECT
t.REPORTDATE,
t.VER,
t.TKS/t.Mins as KSHR,
4100 /(t.TKS/t.Mins) as NormalDocsHr,
t.TotalKS/(t.TotalKS/t.Mins) as Hours
FROM
(SELECT
(RDATE as REPORTDATE,
VSON as VIER,
Sum(TAIMS) as TDs,
Sum(NOKES) as TKS,
(Sum(EMINS-SMINS)*60 ) as Mins,
Sum(TAIMS)/Sum(NOKES) as KSPerDoc
FROM WINTPOP WHERE VSON= '333' and (RDATE Between '1/4/2011' and '1/7/2011') Group by RDATE,VSON) t

aash
Starting Member

11 Posts

Posted - 2011-03-24 : 16:31:22

Am I doing anything wrong?

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-03-24 : 17:06:12

try:

SELECT
t.REPORTDATE,
t.VER,
t.TKS/t.Mins as KSHR,
4100 /(t.TKS/t.Mins) as NormalDocsHr,
t.TotalKS/(t.TotalKS/t.Mins) as Hours
FROM
(SELECT
(RDATE as REPORTDATE,
VSON as VIER,
Sum(TAIMS) as TDs,
Sum(NOKES) as TKS,
(Sum(EMINS-SMINS)*60 ) as Mins,
Sum(TAIMS)/Sum(NOKES) as KSPerDoc
FROM WINTPOP WHERE VSON= '333' and (RDATE Between '1/4/2011' and '1/7/2011') Group by RDATE,VSON) t

Corey

snSQL on previous signature "...Oh and by the way Seventhnight, your signature is so wrong! On so many levels, morally, gramatically and there is a typo!"

aash
Starting Member

11 Posts

Posted - 2011-03-25 : 10:22:24

Thanks Seventhnight, that resolves my issue

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources