|
|
You're in the
sql server mseq
group:sql query single table
sql server mseq:
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
[quoted text, click to view]
On Thu, 28 Sep 2006 13:52:01 -0700, madgame wrote: (snip)>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 Hi Madgame, First of all - thanks for posting the CREATE TABLE statement. Does your real table also not have a key and allow NULL in all columns? If that's the case, you should make improving the design your top priority. Unfortunately, even with the description and the sample output, I'm not sure if I really understand what you're trying to achieve. For isntance, where do the rows with central host C222222 and C333333 in the sample output come from? Is the sample inpus posted incomplete? I *think* that the query below will return the desired results. I didn't test is myself, since you didn't post the sample data in the form of INSERT statements and I'm too lazy to type them myself. <g> SELECT CASE WHEN agcprefix = 'DAC' THEN sysid ELSE '' END AS "Central Host", CASE WHEN agcprefix = 'DAC' THEN '' ELSE 'sysid' END AS "OffBoard", release AS "Release Level", platform AS "Platformm Type" FROM (SELECT DISTINCT sysid, agcprefix, release, platform FROM DsdaSysConfigProfile WHERE release LIKE 'DK%') AS D --
Thanks for the help Hugo. The real table does have a key and allow nulls as
far as I know. Though I'm not the db admin, I'm a grunt that uses it to pull reports. :-D. I'm still fairly new to SQL. I checked one of the pages you recommended for getting requests like this answered which is how I got the create table on here for ya. I seen mention of INSERT, but there's no example so I'm not sure how to go about that. Can you give me an example of how to use INSERT to post data which would be more helpful for you, to help me? Thanks again. [quoted text, click to view] "Hugo Kornelis" wrote:
> On Thu, 28 Sep 2006 13:52:01 -0700, madgame wrote: > > >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 > (snip) > > Hi Madgame, > > First of all - thanks for posting the CREATE TABLE statement. Does your > real table also not have a key and allow NULL in all columns? If that's > the case, you should make improving the design your top priority. > > Unfortunately, even with the description and the sample output, I'm not > sure if I really understand what you're trying to achieve. For isntance, > where do the rows with central host C222222 and C333333 in the sample > output come from? Is the sample inpus posted incomplete? > > I *think* that the query below will return the desired results. I didn't > test is myself, since you didn't post the sample data in the form of > INSERT statements and I'm too lazy to type them myself. <g> > > SELECT CASE WHEN agcprefix = 'DAC' > THEN sysid > ELSE '' > END AS "Central Host", > CASE WHEN agcprefix = 'DAC' > THEN '' > ELSE 'sysid' > END AS "OffBoard", > release AS "Release Level", > platform AS "Platformm Type" > FROM (SELECT DISTINCT sysid, agcprefix, release, platform > FROM DsdaSysConfigProfile > WHERE release LIKE 'DK%') AS D > > -- > Hugo Kornelis, SQL Server MVP
Hugo,
The query did not work as I need it to. I will try and give a better description of what's been asked along with the data from INSERT. Please stay tuned... Thanks again for taking the time to help. [quoted text, click to view] > Hi Madgame,
> > Sure: > > INSERT INTO SomeTable (Column1, Column2) > SELECT 1, 'First row' > UNION ALL > SELECT 2, 'Second row' > UNION ALL > SELECT 3, 'Third row' > > Replace table and columns names with names from your table, then use > apropriate data after each SELECT keyword. > > Or as an alternative, use Vyas's script to generate INSERT statements > from the data in your DB: http://vyaskn.tripod.com/code.htm#inserts. But > this will only work if you already have a good set of test data in your > database. > > BTW, did the quey I posted work for you? > > -- > Hugo Kornelis, SQL Server MVP
[quoted text, click to view]
On Mon, 2 Oct 2006 06:46:02 -0700, madgame wrote: > I seen mention of INSERT, but there's no >example so I'm not sure how to go about that. Can you give me an example of >how to use INSERT to post data which would be more helpful for you, to help >me? Thanks again. Hi Madgame, Sure: INSERT INTO SomeTable (Column1, Column2) SELECT 1, 'First row' UNION ALL SELECT 2, 'Second row' UNION ALL SELECT 3, 'Third row' Replace table and columns names with names from your table, then use apropriate data after each SELECT keyword. Or as an alternative, use Vyas's script to generate INSERT statements from the data in your DB: http://vyaskn.tripod.com/code.htm#inserts. But this will only work if you already have a good set of test data in your database. BTW, did the quey I posted work for you? --
Hugo,
Here are the inserts. INSERT INTO [mytable] ([serial],[sysid],[agcprefix],[release],[platform])VALUES('C111111','C222222','DAC','DC041B','platform2') INSERT INTO [mytable] ([serial],[sysid],[agcprefix],[release],[platform])VALUES('C111111','C111111','DAD','DK041B','platform1') INSERT INTO [mytable] ([serial],[sysid],[agcprefix],[release],[platform])VALUES('C111111','C222222','DAD','DK041B','platform2') INSERT INTO [mytable] ([serial],[sysid],[agcprefix],[release],[platform])VALUES('C111111','C222222','DAZ','DZ041B','platform2') INSERT INTO [mytable] ([serial],[sysid],[agcprefix],[release],[platform])VALUES('C111111','C222222','DAP','DP041B','platform2') INSERT INTO [mytable] ([serial],[sysid],[agcprefix],[release],[platform])VALUES('C222222','C111111','DAD','DK041B','platform1') INSERT INTO [mytable] ([serial],[sysid],[agcprefix],[release],[platform])VALUES('C222222','C222222','DAP','DP041B','platform2') INSERT INTO [mytable] ([serial],[sysid],[agcprefix],[release],[platform])VALUES('C222222','C222222','DAC','DC041B','platform2') INSERT INTO [mytable] ([serial],[sysid],[agcprefix],[release],[platform])VALUES('C222222','C222222','DAD','DK041B','platform2') INSERT INTO [mytable] ([serial],[sysid],[agcprefix],[release],[platform])VALUES('C222222','C222222','DAY','DY041B','platform2') INSERT INTO [dsdasysconfigprofile] ([serial],[sysid],[agcprefix],[release],[platform])VALUES('C333333','C333333','DAD','DK061B','platformtype2') INSERT INTO [dsdasysconfigprofile] ([serial],[sysid],[agcprefix],[release],[platform])VALUES('C333333','C333333','DAC','DC061B','platformtype2') INSERT INTO [dsdasysconfigprofile] ([serial],[sysid],[agcprefix],[release],[platform])VALUES('C333333','C333333','DAX','DX061B','platformtype2') INSERT INTO [dsdasysconfigprofile] ([serial],[sysid],[agcprefix],[release],[platform])VALUES('C333333','C333333','DAG','DG061B','platformtype2') INSERT INTO [dsdasysconfigprofile] ([serial],[sysid],[agcprefix],[release],[platform])VALUES('C444444','C444444','DAD','DK050','platformtype3') INSERT INTO [dsdasysconfigprofile] ([serial],[sysid],[agcprefix],[release],[platform])VALUES('C444444','C444444','DAC','DC050','platformtype3') INSERT INTO [dsdasysconfigprofile] ([serial],[sysid],[agcprefix],[release],[platform])VALUES('C444444','C444444','DAX','DX050','platformtype3') INSERT INTO [dsdasysconfigprofile] ([serial],[sysid],[agcprefix],[release],[platform])VALUES('C444444','C444444','DAG','DG050','platformtype3') 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 would look like this: Central Host Offboard Release Platform C222222 DK041B Platformtype2 C111111 DK041B Platformtype1 C333333 DK061B Platformtype2 C444444 DK050 Platformtype3 Totals: Total Systems 4 Total offboards 1 Total Onboards 2 Total DK041B 2 Total DK061B 1 Total DK050 1 Total Platform1 1 Total Platform2 2 Total Platform3 1 [quoted text, click to view] "madgame" wrote:
> Hugo, > The query did not work as I need it to. I will try and give a better > description of what's been asked along with the data from INSERT. Please > stay tuned... > > Thanks again for taking the time to help. > > > Hi Madgame, > > > > Sure: > > > > INSERT INTO SomeTable (Column1, Column2) > > SELECT 1, 'First row' > > UNION ALL > > SELECT 2, 'Second row' > > UNION ALL > > SELECT 3, 'Third row' > > > > Replace table and columns names with names from your table, then use > > apropriate data after each SELECT keyword. > > > > Or as an alternative, use Vyas's script to generate INSERT statements > > from the data in your DB: http://vyaskn.tripod.com/code.htm#inserts. But > > this will only work if you already have a good set of test data in your > > database. > > > > BTW, did the quey I posted work for you? > > > > -- > > Hugo Kornelis, SQL Server MVP
dsdasysconfigprofile = mytable
[quoted text, click to view] "madgame" wrote:
> Hugo, > Here are the inserts. > > INSERT INTO [mytable] > ([serial],[sysid],[agcprefix],[release],[platform])VALUES('C111111','C222222','DAC','DC041B','platform2') > INSERT INTO [mytable] > ([serial],[sysid],[agcprefix],[release],[platform])VALUES('C111111','C111111','DAD','DK041B','platform1') > INSERT INTO [mytable] > ([serial],[sysid],[agcprefix],[release],[platform])VALUES('C111111','C222222','DAD','DK041B','platform2') > INSERT INTO [mytable] > ([serial],[sysid],[agcprefix],[release],[platform])VALUES('C111111','C222222','DAZ','DZ041B','platform2') > INSERT INTO [mytable] > ([serial],[sysid],[agcprefix],[release],[platform])VALUES('C111111','C222222','DAP','DP041B','platform2') > INSERT INTO [mytable] > ([serial],[sysid],[agcprefix],[release],[platform])VALUES('C222222','C111111','DAD','DK041B','platform1') > INSERT INTO [mytable] > ([serial],[sysid],[agcprefix],[release],[platform])VALUES('C222222','C222222','DAP','DP041B','platform2') > INSERT INTO [mytable] > ([serial],[sysid],[agcprefix],[release],[platform])VALUES('C222222','C222222','DAC','DC041B','platform2') > INSERT INTO [mytable] > ([serial],[sysid],[agcprefix],[release],[platform])VALUES('C222222','C222222','DAD','DK041B','platform2') > INSERT INTO [mytable] > ([serial],[sysid],[agcprefix],[release],[platform])VALUES('C222222','C222222','DAY','DY041B','platform2') > INSERT INTO [dsdasysconfigprofile] > ([serial],[sysid],[agcprefix],[release],[platform])VALUES('C333333','C333333','DAD','DK061B','platformtype2') > INSERT INTO [dsdasysconfigprofile] > ([serial],[sysid],[agcprefix],[release],[platform])VALUES('C333333','C333333','DAC','DC061B','platformtype2') > INSERT INTO [dsdasysconfigprofile] > ([serial],[sysid],[agcprefix],[release],[platform])VALUES('C333333','C333333','DAX','DX061B','platformtype2') > INSERT INTO [dsdasysconfigprofile] > ([serial],[sysid],[agcprefix],[release],[platform])VALUES('C333333','C333333','DAG','DG061B','platformtype2') > INSERT INTO [dsdasysconfigprofile] > ([serial],[sysid],[agcprefix],[release],[platform])VALUES('C444444','C444444','DAD','DK050','platformtype3') > INSERT INTO [dsdasysconfigprofile] > ([serial],[sysid],[agcprefix],[release],[platform])VALUES('C444444','C444444','DAC','DC050','platformtype3') > INSERT INTO [dsdasysconfigprofile] > ([serial],[sysid],[agcprefix],[release],[platform])VALUES('C444444','C444444','DAX','DX050','platformtype3') > INSERT INTO [dsdasysconfigprofile] > ([serial],[sysid],[agcprefix],[release],[platform])VALUES('C444444','C444444','DAG','DG050','platformtype3') > > 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 would look like this: > > Central Host Offboard Release Platform > C222222 DK041B Platformtype2 > C111111 DK041B Platformtype1 > C333333 DK061B Platformtype2 > C444444 DK050 Platformtype3 > > Totals: > Total Systems 4 > Total offboards 1 > Total Onboards 2 > Total DK041B 2 > Total DK061B 1 > Total DK050 1 > Total Platform1 1 > Total Platform2 2 > Total Platform3 1 > > "madgame" wrote: > > > Hugo, > > The query did not work as I need it to. I will try and give a better > > description of what's been asked along with the data from INSERT. Please > > stay tuned... > > > > Thanks again for taking the time to help. > > > > > Hi Madgame, > > > > > > Sure: > > > > > > INSERT INTO SomeTable (Column1, Column2) > > > SELECT 1, 'First row' > > > UNION ALL > > > SELECT 2, 'Second row' > > > UNION ALL > > > SELECT 3, 'Third row' > > > > > > Replace table and columns names with names from your table, then use > > > apropriate data after each SELECT keyword. > > > > > > Or as an alternative, use Vyas's script to generate INSERT statements > > > from the data in your DB: http://vyaskn.tripod.com/code.htm#inserts. But > > > this will only work if you already have a good set of test data in your > > > database. > > > > > > BTW, did the quey I posted work for you? > > > > > > -- > > > Hugo Kornelis, SQL Server MVP
[quoted text, click to view]
On Tue, 3 Oct 2006 07:56:03 -0700, madgame wrote: >Hugo, >Here are the inserts. (snip) >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%'. Hi Madgame, Finally found some time to play with your data - thanks for posting it. You write that you only want to include rows with release level like 'DK%'. After applying that filter, I see only rows with agcprefix other than DAC. That means that, if I understand your requirements correct, there is no "Central Host"- only offboards. But you als write that an offboard must "have" a central host - whatever "have" means in this context. And you provide sample output that lists three central hosts and one offboard. So I guess I DON'T understand the requirements correctly. Could you try to clarify this a bit further? (snip) [quoted text, click to view] >Totals: >Total Systems 4 >Total offboards 1 >Total Onboards 2 >Total DK041B 2 >Total DK061B 1 >Total DK050 1 >Total Platform1 1 >Total Platform2 2 >Total Platform3 1 It's almost certainly a better idea to calculate these totals in the front-end. You'll have to pass over the output from the query row by row anyway - while doing that, you can keep track of the various totals listed here, then display them when the last row of the query has been processed. Let me know if you really need to get this part from SQL as well - but note that performance will probably suck. Oh, and by the way - are you using SQL Server 2000 or 2005? --
Hugo,
Thanks for the help. I'm going to see if I can get assistance from someone more familiar with the database whose sql skills are better than mine. Thanks again for your assistance. [quoted text, click to view] "Hugo Kornelis" wrote:
> On Tue, 3 Oct 2006 07:56:03 -0700, madgame wrote: > > >Hugo, > >Here are the inserts. > (snip) > >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%'. > > Hi Madgame, > > Finally found some time to play with your data - thanks for posting it. > > You write that you only want to include rows with release level like > 'DK%'. After applying that filter, I see only rows with agcprefix other > than DAC. That means that, if I understand your requirements correct, > there is no "Central Host"- only offboards. But you als write that an > offboard must "have" a central host - whatever "have" means in this > context. And you provide sample output that lists three central hosts > and one offboard. So I guess I DON'T understand the requirements > correctly. Could you try to clarify this a bit further? > > (snip) > >Totals: > >Total Systems 4 > >Total offboards 1 > >Total Onboards 2 > >Total DK041B 2 > >Total DK061B 1 > >Total DK050 1 > >Total Platform1 1 > >Total Platform2 2 > >Total Platform3 1 > > It's almost certainly a better idea to calculate these totals in the > front-end. You'll have to pass over the output from the query row by row > anyway - while doing that, you can keep track of the various totals > listed here, then display them when the last row of the query has been > processed. Let me know if you really need to get this part from SQL as > well - but note that performance will probably suck. > > Oh, and by the way - are you using SQL Server 2000 or 2005? > > -- > Hugo Kornelis, SQL Server MVP
Don't see what you're looking for? Try a search.
|
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |