Help writing a Script - 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

Help writing a Script

Author

Topic

jp2code
Posting Yak Master

175 Posts

Posted - 2008-09-12 : 11:16:23

We have two tables that I need to write a query for. I am a Software Developer, and we do not have any Database Admins here.

TableA:
|_varchar(50)_|_char(15)_|_char(10)_|_varchar(99)_|_DateTime_|
|_Employee____|_ModelNum_|_Station__|_Status______|_TestDay__|

TableB:
|_char(15)_|_varchar(50)_|
|_ModelNum_|_ModelName___|

Though no primary key exists for the two tables, TableA's ModelNum *does* map to TableB's ModelNum.

How would I get:
TableA.Status, TableA.ModelNum, and TableB.ModelName
WHERE (TableA.Station='Testing')
AND (TableA.TestDay between (Date1 and Date2))
AND (TableA.Employee from TableA.Station='Building')

Here is the scenario:

Employee 'John' assembles units at the 'Building' Station.

Sometime later, these uints arrive at the 'Testing' Station where they are tested by others.

For a given date range at the 'Testing' Station, we need the ModelNum, ModelName, and Status at the 'Testing' Station for all coils that 'John' built at any time in the 'Building' Station.

How would I write such a query?

I am interested in seeing ways to write this query - simple or elegant!

Bonus points if I can get this query to return a count of the distinct ModelNames! (though I can do this manually in my software)

Avoid Sears Home Improvement

pommguest99
Starting Member

16 Posts

Posted - 2008-09-12 : 15:56:43

Try the following please -

select a.employee, a.modelnum, a.station, a.status, b.modelname, count(*) as [Total Units] from tableA a
join tableB b on tablea.modelnum = tableb.modelnum
where station = 'Building' and (testDay between '12/1/2007 12:00:00 AM' and '12/31/2007 11:59:59.99 PM')
group by a.employee, a.station, b.modelname, a.modelnum, a.status

regards,
Anil

jp2code
Posting Yak Master

175 Posts

Posted - 2008-09-12 : 17:52:36

Hi Anil,

Your query returns the status for station='Building'.

I'm trying to figure out how to get the station="Testing" status with the station="Building" employee.

I'll see what I can do with it.

Avoid Sears Home Improvement

pommguest99
Starting Member

16 Posts

Posted - 2008-09-12 : 18:04:19

Another try, is there any way that you can write in here pictorially as what you want to see ...something on the following lines ...

employee modelnum station status modelname count
PURR ABC BUILDING ACTIVE BIKE 3
TOAD ABC BUILDING ACTIVE BIKE 3
John ABC BUILDING PENDING BIKE 3

Something like this ?

select t.Employee, tb.Modelnum, t.status, t.Modelname, count(*) as [Total Units]
from tablea t
join tableb tb on tb.modelnum = t.modelnum
join tablea b on b.employee = t.employee
where t.station = 'Testing' and b.station = 'Building'
and t.testday between '1/1/2007 11:59:59.99 PM' and '12/31/2007 11:59:59.99 PM'
group by t.Employee, tb.Modelnum, t.status, t.Modelname
having count(*) > 1

jp2code
Posting Yak Master

175 Posts

Posted - 2008-09-13 : 10:52:46

It's Saturday and the company is closed - but I'll test it the first chance I get on Monday morning!

Avoid Sears Home Improvement

jp2code
Posting Yak Master

175 Posts

Posted - 2008-09-15 : 09:38:21

This query seemed to work pretty good until I attempted to count the types - then it bombed with about 6 errors.

Any ideas what I did wrong or how to fix this?

Actual SQL:
SELECT B.[OP_ID], A.[System_ID], A.[Serial_Number], A.[Test_Result], A.[Date_Time], B.[CoilType], COUNT(B.[CoilType]) AS 'TypeCount'
FROM Test_Results A INNER JOIN (
SELECT B1.[OP_ID], B1.[System_ID], B1.[Serial_Number], B2.[CoilType]
FROM Test_Results B1 INNER JOIN (
SELECT C.[Serial_Number], C.[CoilType]
FROM ACP_Parts C
WHERE (C.[System_ID] Like '%Label%')
) B2 ON (B2.[Serial_Number]=B1.[Serial_Number])
WHERE (B1.[System_ID] Like '%Decay%')
) B ON (A.[Serial_Number]=B.[Serial_Number])
WHERE (B.[OP_ID] Like @FULLNAME) AND
(A.[System_ID] Like '%Chamber%') AND
(A.[Date_Time] BETWEEN @DateStart AND @DateEnd)
ORDER BY A.[Date_Time], A.[Serial_Number]

Actual SQL Errors:
Server: Msg 8118, Level 16, State 1, Line 8
Column 'B.OP_ID' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 8
Column 'A.System_ID' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 8
Column 'A.Serial_Number' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 8
Column 'A.Test_Result' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 8
Column 'A.Date_Time' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 8
Column 'B.CoilType' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

Avoid Sears Home Improvement

pommguest99
Starting Member

16 Posts

Posted - 2008-09-15 : 13:01:58

Hello !!!

Every "column" that is in the select clause should be in the group by clause except for the agg function and that is the error message that you are looking at.

SELECT B.[OP_ID], A.[System_ID], A.[Serial_Number], A.[Test_Result], A.[Date_Time], B.[CoilType], COUNT(B.[CoilType]) AS 'TypeCount'
FROM Test_Results A INNER JOIN (
SELECT B1.[OP_ID], B1.[System_ID], B1.[Serial_Number], B2.[CoilType]
FROM Test_Results B1 INNER JOIN (
SELECT C.[Serial_Number], C.[CoilType]
FROM ACP_Parts C
WHERE (C.[System_ID] Like '%Label%')
) B2 ON (B2.[Serial_Number]=B1.[Serial_Number])
WHERE (B1.[System_ID] Like '%Decay%')
) B ON (A.[Serial_Number]=B.[Serial_Number])
WHERE (B.[OP_ID] Like @FULLNAME) AND
(A.[System_ID] Like '%Chamber%') AND
(A.[Date_Time] BETWEEN @DateStart AND @DateEnd)
--
group by B.[OP_ID], A.[System_ID], A.[Serial_Number], A.[Test_Result], A.[Date_Time], B.[CoilType]
--

ORDER BY A.[Date_Time], A.[Serial_Number]

regards,
Anil

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-15 : 13:05:16

quote:
Originally posted by pommguest99

Hello !!!

Every "column" that is in the select clause should be in the group by clause except for the agg function and that is the error message that you are looking at.

SELECT B.[OP_ID], A.[System_ID], A.[Serial_Number], A.[Test_Result], A.[Date_Time], ~~B.[CoilType],~~ COUNT(B.[CoilType]) AS 'TypeCount'
FROM Test_Results A INNER JOIN (
SELECT B1.[OP_ID], B1.[System_ID], B1.[Serial_Number], B2.[CoilType]
FROM Test_Results B1 INNER JOIN (
SELECT C.[Serial_Number], C.[CoilType]
FROM ACP_Parts C
WHERE (C.[System_ID] Like '%Label%')
) B2 ON (B2.[Serial_Number]=B1.[Serial_Number])
WHERE (B1.[System_ID] Like '%Decay%')
) B ON (A.[Serial_Number]=B.[Serial_Number])
WHERE (B.[OP_ID] Like @FULLNAME) AND
(A.[System_ID] Like '%Chamber%') AND
(A.[Date_Time] BETWEEN @DateStart AND @DateEnd)
--
group by B.[OP_ID], A.[System_ID], A.[Serial_Number], A.[Test_Result], A.[Date_Time]~~, B.[CoilType]~~
--

ORDER BY A.[Date_Time], A.[Serial_Number]

regards,
Anil

you dont need to group on CoilType if you want its count. just remove it from group by as above and try

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources