sql query single table
- From: madgame <madgame@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 28 Sep 2006 13:52:01 -0700
I am trying to run a report. The requestor has specifics on the way it
should be formatted on output. I'm going to try and explain what he's
looking for as well as give the example of output which was given me. The
"Central Host" is the system whose agcprefix = DAC. Each "Central Host"
record should be a unique sysid. An offboard is considered a sysid which
does not have an agcprefix = DAC. An offboard always has a central host, but
a central host does not always have an offboard. Also he only wants the
release level from either type to be like 'DK%'.
He would like the central host row of information, followed by any offboards
for that central hosts with the central host field blank. If there are no
offboards then that field would be blank. He also wants totals as specified
below.
Sample output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Central Host OffBoard Release Level Platform Type
C126606 DK041B r3
C126568 DK041B Linux
C222222 DK053B alpha
C333333 DK053 r3
Total Platforms 4
Total offboards 1
Total onboards 2 - Onboards in this case are systems with no offboards.
Total DK041B 2
Total DK053B 1
Total DK053 1
Total Linux 1
Total alpha 1
Total r3 2
Create table
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE [DsdaSysConfigProfile] (
[serial] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sysid] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[agcprefix] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[release] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[platform] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
) ON [PRIMARY]
GO
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
table data
serial,sysid,agcprefix,release,platform --- In the DB sysid is what the
agcprefix, release, and platform information refer to, not serial.
C126568,C126568,DAD,DK041B,linux
C126568,C126568,DAX,DX041B,linux
C126568,C126606,DAC,DC041B,r3
C126568,C126606,DAD,DK041B,r3
C126568,C126606,DAP,DP041B,r3
C126568,C126606,DAZ,DZ041B,r3
C126606,C126606,DAD,DK041B,r3
C126606,C126606,DAP,DP041B,r3
C126606,C126606,DAC,DC041B,r3
C126606,C126606,DAZ,DZ041B,r3
C126606,C126568,DAD,DK041B,linux
Thanks for any help.
.
- Prev by Date: Re: Break out Pipe Values
- Previous by thread: Re: Break out Pipe Values
- Index(es):
Relevant Pages
|
|
