Prev: Problem with Update Trigger
Next: Transpose 1 datarow from Hor to vert w/o using tbl var or #tmp tbl
From: gv on 19 May 2010 15:02
Hi all,

SQL 2008 sp1

I have the following example, Having problems returning the correct results.


--If USID contains Blue,Green,Yellow then need to add Active
--Else need to add 3 lines that contain Blue,Green,Yellow

DECLARE @TABLE1 TABLE
(USGID INT NOT NULL,PCID INT NOT NULL )
INSERT INTO @TABLE1
VALUES (54675,1),(54675,2),(54675,3),(54675,4),(54675,5),(54682,1)

DECLARE @TABLE2 TABLE
(PCID INT NOT NULL,PCDESC VARCHAR(10))
INSERT INTO @TABLE2
VALUES (2,'Blue'),(3,'Green'),(4,'Yellow')

SELECT A.USGID,A.PCID,B.PCDESC,
CASE WHEN A.PCID IN(2,3,4) THEN 'Active'
WHEN A.PCID NOT IN(2,3,4) THEN 'Not Active' END AS [Status]
FROM @TABLE1 A
LEFT JOIN @TABLE2 B
ON A.PCID = B.PCID

Returns:

USGID PCID PCDESC Status
54675 1 NULL Not Active
54675 2 Blue Active
54675 3 Green Active
54675 4 Yellow Active
54675 5 NULL Not Active
54682 1 NULL Not Active


Needs to look like this:

USGID PCID PCDESC Status

54675 2 Blue Active
54675 3 Green Active
54675 4 Yellow Active

54682 1 Blue Active
54682 3 Green Active
54682 4 Yellow Active


Thanks
gv



From: Erland Sommarskog on 19 May 2010 18:10
gv (viator.gerry(a)gmail.com) writes:
> I have the following example, Having problems returning the correct
> results.

Well, adding

WHERE B.PCDESC IS NOT NULL

gives the desired result, but somehow, I think you were looking for
something more sophisticated.

While it is create that you posted tables and sample data, it would
also help if you explained the business rules a little more, and
explained what those USID, PCDESC etc mean.




--
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 20 May 2010 13:43
Thanks for your help,

I do and I apologise for not properly explaning the results I need.

Lets not worry about adding just filtering out for now.
I have changed the example a little to maybe make a little more since.

Service Number id's is what USID means and line description


DECLARE @TABLE1 TABLE
(USGID INT NOT NULL,ServiceNumber VARCHAR(10),PCID 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
(PCID INT NOT NULL,PCDESC VARCHAR(25))
INSERT INTO @TABLE2
VALUES (2,'IntraLata'),(3,'IntraState'),(4,'InterState')

SELECT A.USGID,A.ServiceNumber,A.PCID,B.PCDESC
FROM @TABLE1 A
LEFT JOIN @TABLE2 B
ON A.PCID = B.PCID

returns:

USGID ServiceNumber PCID PCDESC

54675 8004568787 1 NULL
54675 8004568787 2 IntraLata
54675 8004568787 3 IntraState
54675 8004568787 4 InterState
54675 8004568787 5 NULL
54682 8004565454 1 NULL

need it to return this:

USGID ServiceNumber PCID PCDESC

54675 8004568787 2 IntraLata
54675 8004568787 3 IntraState
54675 8004568787 4 InterState
54682 8004565454 1 NULL


This is telling me what service numbers have these values and which one's
don't

So USGD
54675 = has those values
and 54682 = doesn't have those values

Thanks
gv


Basicaly how do you return rows with

"Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message
news:Xns9D7E1CA6E5F8Yazorman(a)127.0.0.1...
> gv (viator.gerry(a)gmail.com) writes:
>> I have the following example, Having problems returning the correct
>> results.
>
> Well, adding
>
> WHERE B.PCDESC IS NOT NULL
>
> gives the desired result, but somehow, I think you were looking for
> something more sophisticated.
>
> While it is create that you posted tables and sample data, it would
> also help if you explained the business rules a little more, and
> explained what those USID, PCDESC etc mean.
>
>
>
>
> --
> 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: --CELKO-- on 20 May 2010 14:35
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: gv on 20 May 2010 15:50
Thanks for your help.

Not exactly:
2) if a (usg_id, service_nbr) group has >1 row, return all rows where
> (B.pc_descr IS NOT NULL)

also need B.pc_descr IS NULL but for the group doesn't contain thoose values pc_id =2,3,4

getting this error:

Msg 4108, Level 15, State 1, Line 26

Windowed functions can only appear in the SELECT or ORDER BY clauses.

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.
>
>
>
>
>
 |  Next  |  Last
Pages: 1 2
Prev: Problem with Update Trigger
Next: Transpose 1 datarow from Hor to vert w/o using tbl var or #tmp tbl