Prev: Problem with Update Trigger
Next: Transpose 1 datarow from Hor to vert w/o using tbl var or #tmp tbl
From: gv on 20 May 2010 16:30
I have the correct results retuning with this query but, seems like it can
be done better?


DECLARE @TABLE1 TABLE
(RowID INT IDENTITY(1,1)NOT NULL,usg_id INT NOT NULL,service_nbr
VARCHAR(10),pc_id INT NOT NULL )
INSERT INTO @TABLE1
VALUES
(54675,8004568787,1),(54675,8004568787,2),(54675,8004568787,3),(54675,8004568787,4),(54675,8004568787,5),(54682,8004565454,1)

DECLARE @TABLE2 TABLE
(pc_id INT NOT NULL,pc_descr VARCHAR(25))
INSERT INTO @TABLE2
VALUES (2,'IntraLata'),(3,'IntraState'),(4,'InterState')


SELECT A.usg_id, A.service_nbr,A.pc_id,b.pc_descr
FROM @TABLE1 AS A
LEFT JOIN
@TABLE2 AS B
ON A.pc_id = B.pc_id
WHERE B.pc_id IS NULL
AND NOT EXISTS ( SELECT A2.usg_id, A2.service_nbr,b2.pc_descr
FROM @TABLE1 AS A2
INNER JOIN
@TABLE2 AS B2
ON A2.pc_id = B2.pc_id
WHERE A2.usg_id = A.usg_id AND A2.service_nbr = A.service_nbr
GROUP BY A2.usg_id, A2.service_nbr,b2.pc_descr )
GROUP BY A.usg_id, A.service_nbr,A.pc_id,b.pc_descr
UNION
SELECT A2.usg_id, A2.service_nbr,A2.pc_id,b2.pc_descr
FROM @TABLE1 AS A2
INNER JOIN
@TABLE2 AS B2
ON A2.pc_id = B2.pc_id
GROUP BY A2.usg_id, A2.service_nbr,A2.pc_id,b2.pc_descr

Thanks
gv


"--CELKO--" <jcelko212(a)earthlink.net> wrote in message
news:3e28fc25-21bc-482d-a9c8-8a378594ae06(a)l6g2000vbo.googlegroups.com...
> Is this your business rule:
> 1) if a (usg_id, service_nbr) group has one row, return that row
> 2) if a (usg_id, service_nbr) group has >1 row, return all rows where
> (B.pc_descr IS NOT NULL)
>
> SELECT A.usg_id, A.service_nbr, A.pc_id, B.pc_descr
> FROM Alpha AS A
> LEFT OUTER JOIN
> Beta AS B
> ON A.pc_id = B.pc_id
> WHERE CASE WHEN COUNT(*) OVER (PARTITION BY usg_id, service_nbr) = 0
> THEN 'T'
> WHEN B.pc_descr IS NOT NULL
> THEN 'T' ELSE 'F' END = 'T';
>
>
> Untested.
>
>
>
>
>


From: --CELKO-- on 20 May 2010 17:13
>> Not exactly: ..<<

Mea Culpa; Standard SQL allows the ordinal function just about any
place any ohter function can appear. SQL Server is still behind the
curve. Okay, the rules again:

1) if a (usg_id, service_nbr) group has one row, return that row
2) if a (usg_id, service_nbr) group has >1 row, return all rows where
(B.pc_descr IS NOT NULL)  2) if a (usg_id, service_nbr) group has >1
row, return all rows where (B.pc_descr IS NOT NULL)
3) if ( B.pc_descr IS NULL) but for the group doesn't contain pc_id
IN (2 ,3, 4)

Did you mean the group as a whole has all three pc_id values or or
will any of them disqualify that row?

SELECT A.usg_id, A.service_nbr, A.pc_id,
CASE WHEN COUNT(*) OVER (PARTITION BY usg_id, service_nbr) = 1
THEN B.pc_descr
WHEN B.pc_descr IS NOT NULL -- count is >1 here
THEN B.pc_descr
WHEN B.pc_descr IS NULL -- count is >1 here
AND pc_id NOT IN (2,3,4)
THEN B.pc_descr ELSE NULL END;
FROM Alpha AS A
LEFT OUTER JOIN
Beta AS B
ON A.pc_id = B.pc_id

I am getting obsessed with using an ordinal function with a CASE
expression :)
From: gv on 21 May 2010 07:56

Thanks again

I beleive you have the rules correct but, rule 3 is still not in play.

The query you sent returns a row for (usg_id, service_nbr) group
that B.pc_descr IS NULL and already has returned rows for that group
that has B.pc_id IN (2 ,3, 4)

This query below does return the correct rows but, I think can be written
better?

SELECT A.usg_id, A.service_nbr,A.pc_id,b.pc_descr
FROM @TABLE1 AS A
LEFT JOIN
@TABLE2 AS B
ON A.pc_id = B.pc_id
WHERE B.pc_id IS NULL
AND NOT EXISTS ( SELECT *
FROM @TABLE1 AS A2
INNER JOIN @TABLE2 AS B2
ON A2.pc_id = B2.pc_id
WHERE A2.usg_id = A.usg_id AND A2.service_nbr = A.service_nbr )
GROUP BY A.usg_id, A.service_nbr,A.pc_id,b.pc_descr
UNION
SELECT A2.usg_id, A2.service_nbr,A2.pc_id,b2.pc_descr
FROM @TABLE1 AS A2
INNER JOIN
@TABLE2 AS B2
ON A2.pc_id = B2.pc_id
GROUP BY A2.usg_id, A2.service_nbr,A2.pc_id,b2.pc_descr

thanks
gv

"--CELKO--" <jcelko212(a)earthlink.net> wrote in message
news:fcbb4005-5ecc-4bc7-a806-ef415eab5bc2(a)z33g2000vbb.googlegroups.com...
>> Not exactly: ..<<

Mea Culpa; Standard SQL allows the ordinal function just about any
place any ohter function can appear. SQL Server is still behind the
curve. Okay, the rules again:

1) if a (usg_id, service_nbr) group has one row, return that row
2) if a (usg_id, service_nbr) group has >1 row, return all rows where
(B.pc_descr IS NOT NULL) 2) if a (usg_id, service_nbr) group has >1
row, return all rows where (B.pc_descr IS NOT NULL)
3) if ( B.pc_descr IS NULL) but for the group doesn't contain pc_id
IN (2 ,3, 4)

Did you mean the group as a whole has all three pc_id values or or
will any of them disqualify that row?

SELECT A.usg_id, A.service_nbr, A.pc_id,
CASE WHEN COUNT(*) OVER (PARTITION BY usg_id, service_nbr) = 1
THEN B.pc_descr
WHEN B.pc_descr IS NOT NULL -- count is >1 here
THEN B.pc_descr
WHEN B.pc_descr IS NULL -- count is >1 here
AND pc_id NOT IN (2,3,4)
THEN B.pc_descr ELSE NULL END;
FROM Alpha AS A
LEFT OUTER JOIN
Beta AS B
ON A.pc_id = B.pc_id

I am getting obsessed with using an ordinal function with a CASE
expression :)


From: Erland Sommarskog on 23 May 2010 06:09
gv (viator.gerry(a)gmail.com) writes:
> This query below does return the correct rows but, I think can be written
> better?
>
> SELECT A.usg_id, A.service_nbr,A.pc_id,b.pc_descr
> FROM @TABLE1 AS A
> LEFT JOIN
> @TABLE2 AS B
> ON A.pc_id = B.pc_id
> WHERE B.pc_id IS NULL
> AND NOT EXISTS ( SELECT *
> FROM @TABLE1 AS A2
> INNER JOIN @TABLE2 AS B2
> ON A2.pc_id = B2.pc_id
> WHERE A2.usg_id = A.usg_id AND A2.service_nbr = A.service_nbr )
> GROUP BY A.usg_id, A.service_nbr,A.pc_id,b.pc_descr
> UNION
> SELECT A2.usg_id, A2.service_nbr,A2.pc_id,b2.pc_descr
> FROM @TABLE1 AS A2
> INNER JOIN
> @TABLE2 AS B2
> ON A2.pc_id = B2.pc_id
> GROUP BY A2.usg_id, A2.service_nbr,A2.pc_id,b2.pc_descr

It looks overly complex, but with the small amount of sample data and
lack of keys, makes it difficult for me to say anything with certainty.

There is usg_id and service_nbr and in your sample data, they are 1:1
to each other. But is that always the case? And if there can be more
than one usg_id for once service number or vice versa, what applies in
those cases?

And I still have a very poor understanding of what these columns means.
You may know your business domain, but I don't.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: gv on 26 May 2010 09:05
Hi Erland,

I will try and get some more specifics soon, very busy...
thanks for your help

gv



"Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message
news:Xns9D817BB46671DYazorman(a)127.0.0.1...
> gv (viator.gerry(a)gmail.com) writes:
>> This query below does return the correct rows but, I think can be written
>> better?
>>
>> SELECT A.usg_id, A.service_nbr,A.pc_id,b.pc_descr
>> FROM @TABLE1 AS A
>> LEFT JOIN
>> @TABLE2 AS B
>> ON A.pc_id = B.pc_id
>> WHERE B.pc_id IS NULL
>> AND NOT EXISTS ( SELECT *
>> FROM @TABLE1 AS A2
>> INNER JOIN @TABLE2 AS B2
>> ON A2.pc_id = B2.pc_id
>> WHERE A2.usg_id = A.usg_id AND A2.service_nbr = A.service_nbr )
>> GROUP BY A.usg_id, A.service_nbr,A.pc_id,b.pc_descr
>> UNION
>> SELECT A2.usg_id, A2.service_nbr,A2.pc_id,b2.pc_descr
>> FROM @TABLE1 AS A2
>> INNER JOIN
>> @TABLE2 AS B2
>> ON A2.pc_id = B2.pc_id
>> GROUP BY A2.usg_id, A2.service_nbr,A2.pc_id,b2.pc_descr
>
> It looks overly complex, but with the small amount of sample data and
> lack of keys, makes it difficult for me to say anything with certainty.
>
> There is usg_id and service_nbr and in your sample data, they are 1:1
> to each other. But is that always the case? And if there can be more
> than one usg_id for once service number or vice versa, what applies in
> those cases?
>
> And I still have a very poor understanding of what these columns means.
> You may know your business domain, but I don't.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000:
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>


First  |  Prev  | 
Pages: 1 2
Prev: Problem with Update Trigger
Next: Transpose 1 datarow from Hor to vert w/o using tbl var or #tmp tbl