Difficulty outputting in right format. -Tricky one

Hi can anyone help me with this?
I have would like to be able to output some data in a very specific way
and unfortunately my SQL isn't quite up to it. The database is not
SQL Server so in this case only SQL can be used.
I have 2 Practically Identical tables that record test results from 2
separate test points in a process. They appear like this (this example
is cut down):
Table 1(T1)
STDATE PASSFA
20051101 P
20051101 P
20051101 F
20051102 F
20051102 P
20051102 F
...and so on.
Table 2(T2)
Identical to table 1(T1) in format, although results could differ.
I would like the data to output like:
STDATE PASS(T1) FAIL(T1) PASS(T2) FAIL(T
2)
20051101 2 1 1 1
20051102 1 2 1 2
The database is not SQL Server so in this case only SQL can be used.
This is driving me crazy, please help.Try:
select
coalesce (x.STDDATE, y.STDDATE)
, x.PASS as 'PASS (T1)'
, x.FAIL as 'FAIL (T1)'
, y.PASS as 'PASS (T2)'
, y.FAIL as 'FAIL (T2)'
from
(
select
STDDATE
, sum (case when PASSFA = 'P' then 1 else 0 end) PASS
, sum (case when PASSFA = 'F' then 1 else 0 end) FAIL
from
T1
group by
STDDATE
) x
full join
(
select
STDDATE
, sum (case when PASSFA = 'P' then 1 else 0 end) PASS
, sum (case when PASSFA = 'F' then 1 else 0 end) FAIL
from
T2
group by
STDDATE
) y on y.STDDATE = x.STDDATE
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"philipbennett25" <pbennett@.xyratex.com> wrote in message
news:1132696702.642862.158690@.g43g2000cwa.googlegroups.com...
> Hi can anyone help me with this?
> I have would like to be able to output some data in a very specific way
> and unfortunately my SQL isn't quite up to it. The database is not
> SQL Server so in this case only SQL can be used.
>
> I have 2 Practically Identical tables that record test results from 2
> separate test points in a process. They appear like this (this example
> is cut down):
> Table 1(T1)
> STDATE PASSFA
> 20051101 P
> 20051101 P
> 20051101 F
> 20051102 F
> 20051102 P
> 20051102 F
> ...and so on.
> Table 2(T2)
> Identical to table 1(T1) in format, although results could differ.
>
> I would like the data to output like:
> STDATE PASS(T1) FAIL(T1) PASS(T2) FAIL(T2)
> 20051101 2 1 1 1
> 20051102 1 2 1 2
> The database is not SQL Server so in this case only SQL can be used.
> This is driving me crazy, please help.
>|||Hi Tom,
Thanks for replying to my question. I have tried this out but I cannot
get it to work (more than likely to be my own lack of skill). To give a
bit of background the data is coming from an AS/400 DB which doesnt
support as many functions as SQL server.
Table 1 is actually called TESTER and Table 2 is called BURNIN.
I had been able the data for a single table in the fromat that I wanted
but not both tables. I had also managed to UNION the tables so that the
outputs appeared in the format but had a criteria that showed which
table the output had come from, but I had really wanted to be able to
have the 'inline' output from both tables by date (STDATE)
Thanks again for your help.
*** Sent via Developersdex http://www.examnotes.net ***|||SQL has different dialects on different boxes. You may be able to create a
linked server in SQL Server to point to the AS/400 and then use T-SQL to do
the query. Failing that, you could to a DTS data pump to haul the data from
AS/400 to SQL Server and to it that way.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Philip Bennett" <pbennett@.xyratex.com> wrote in message
news:u1CUUKB8FHA.1248@.TK2MSFTNGP14.phx.gbl...
> Hi Tom,
> Thanks for replying to my question. I have tried this out but I cannot
> get it to work (more than likely to be my own lack of skill). To give a
> bit of background the data is coming from an AS/400 DB which doesnt
> support as many functions as SQL server.
> Table 1 is actually called TESTER and Table 2 is called BURNIN.
> I had been able the data for a single table in the fromat that I wanted
> but not both tables. I had also managed to UNION the tables so that the
> outputs appeared in the format but had a criteria that showed which
> table the output had come from, but I had really wanted to be able to
> have the 'inline' output from both tables by date (STDATE)
> Thanks again for your help.
> *** Sent via Developersdex http://www.examnotes.net ***|||Tom,
I am sure that I am being stupid, That SQL you kindly wrote for me
seems so close. It keeps stopping and saying 'Table Y Undefined'. Being
able to do this in the code, without any hardware changes/improvements
would make my life much better.
Thanks|||Could you please post your DDL please the exact code you're using?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"philipbennett25" <pbennett@.xyratex.com> wrote in message
news:1132768693.786210.98530@.g14g2000cwa.googlegroups.com...
> Tom,
> I am sure that I am being stupid, That SQL you kindly wrote for me
> seems so close. It keeps stopping and saying 'Table Y Undefined'. Being
> able to do this in the code, without any hardware changes/improvements
> would make my life much better.
> Thanks
>|||Tom, I dont know much about DDL's (Data Definition Library). Simple SQL
seems to be what I can use.
select
coalesce (x.STDATE, y.STDATE)
, x.PASS as PASSa
, x.FAIL as FAILa
, y.PASS as PASSb *Alias dont need inverted commas for me*
, y.FAIL as FAILb
from
(
select
STDATE
, sum (case when PASSFA = 'P' then 1 else 0 end) PASS
, sum (case when PASSFA = 'F' then 1 else 0 end) FAIL
from
TESTER
group by
STDATE
) x
full join
(
select
STDATE
, sum (case when PASSFA = 'P' then 1 else 0 end) PASS
, sum (case when PASSFA = 'F' then 1 else 0 end) FAIL
from
BURNIN
group by
STDATE
) y on y.STDATE = x.STDDATE|||Tom, I just got it, It worked, So thank you so much. Final question if
you can bear it. STDATE appears as 20051122 format and is a string, so
how would I mod this query to only take in the first 6 characters and
therefore group it by month. Also, where do I add criteria?
Really, Thanks so much. -I owe you a Pint.|||On 24 Nov 2005 09:25:20 -0800, philipbennett25 wrote:

>Tom, I just got it, It worked, So thank you so much. Final question if
>you can bear it. STDATE appears as 20051122 format and is a string, so
>how would I mod this query to only take in the first 6 characters and
>therefore group it by month. Also, where do I add criteria?
>Really, Thanks so much. -I owe you a Pint.
Hi philipbennett25,
I guess the real question is why you use a string to store a date value.
But since that's what you're currently facing, here's how you could
change the query you posted in your previous post:
select
coalesce (x.YYYYMM, y.YYYYMM)
, x.PASS as PASSa
, x.FAIL as FAILa
, y.PASS as PASSb *Alias dont need inverted commas for me*
, y.FAIL as FAILb
from
(
select
LEFT(STDATE, 6) AS YYYYMM
, sum (case when PASSFA = 'P' then 1 else 0 end) PASS
, sum (case when PASSFA = 'F' then 1 else 0 end) FAIL
from
TESTER
group by
LEFT(STDATE, 6)
) x
full join
(
select
LEFT(STDATE, 6) AS YYYYMM
, sum (case when PASSFA = 'P' then 1 else 0 end) PASS
, sum (case when PASSFA = 'F' then 1 else 0 end) FAIL
from
BURNIN
group by
LEFT(STDATE, 6)
) y on y.YYYYMM = x.YYYYMM
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)sql