November 24, 2015 at 3:59 am
I have the following tables in my query:
CREATE TABLE #StudyCode (
studycode nvarchar(100)
SpecimimenCount INT
)
The output of table #StudyCode looks like :
studycode
-----------
S15-02057
S15-02058
S15-02059
S15-02060
S15-02062
S15-02063
S15-02064
S15-02065
SpecimimenCount
---------------
0
0
0
0
0
0
0
0
CREATE TABLE #Content(
containerCode nvarchar(25),
contentCode nvarchar(25),
StudyCode nvarchar(100),
isSpecimen bit
)
The output of table #Contentlooks like :
containerCode
------------
LOC-UKWI-000019
LOC-UKWI-000019
LOC-UKWI-000023
LOC-UKWI-000023
LOC-UKWI-000023
LOC-UKWI-000023
contentCode
-------------
201509180406
201509150004
201505210451
201505210450
201504160178
201504160177
StudyCode
----------
S15-02058
NULL
S15-02057
S15-02057
S15-02059
S15-02059
isSpecimen
-----------
1
1
1
1
1
1
My query is that how to show the result below, when for a particular studycode, when matching studycode is there in
#Content table and meeting the condition
'where isSpecimen <> 0.'
The result I require is like this:
studycode
-----------
S15-02057
S15-02058
S15-02059
S15-02060
S15-02062
S15-02063
S15-02064
S15-02065
SpecimimenCount
---------------
2
1
2
0
0
0
0
0
November 24, 2015 at 4:32 am
Please, in future post decent test data in a format that can easily be used by others - http://www.sqlservercentral.com/articles/Best+Practices/61537/.
Usually, when I see a question posted like this I just ignore it, because there is too much work required to set it all up.
Today I'm feeling generous - I had a nice long weekend.
I won't go over the missing indexes, primary keys and suchlike and just assume this is a simple exercise. Normally I'd like to see what you had attempted, but by the time I typed all of the code in that you should have done, it was only a few seconds more of effort to finish it off.
It isn't quite in the format your specified, as I've listed both columns together, where you have one above the other. If you want it that way then it is a small matter, with what you now have.
CREATE TABLE #StudyCode (
studycode nvarchar(100),
SpecimimenCount INT
);
GO
INSERT INTO #StudyCode
( studycode, SpecimimenCount )
VALUES ( 'S15-02057',0),
('S15-02058',0),
('S15-02059',0),
('S15-02060',0),
('S15-02062',0),
('S15-02063',0),
('S15-02064',0),
('S15-02065',0);
GO
CREATE TABLE #Content(
containerCode nvarchar(25),
contentCode nvarchar(25),
StudyCode nvarchar(100),
isSpecimen bit
) ;
GO
INSERT INTO #Content
( containerCode ,
contentCode ,
StudyCode ,
isSpecimen
)
VALUES ('LOC-UKWI-000019','201509180406','S15-02058',1),
('LOC-UKWI-000019','201509150004',NULL,1),
('LOC-UKWI-000023','201505210451','S15-02057',1),
('LOC-UKWI-000023','201505210450','S15-02057',1),
('LOC-UKWI-000023','201504160178','S15-02059',1),
('LOC-UKWI-000023','201504160177','S15-02059',1);
GO
SELECT s.studycode, COUNT(c.isSpecimen) AS [SpecimenCount]
FROM #StudyCode s
LEFT OUTER JOIN #Content c ON c.StudyCode = s.studycode
GROUP BY s.studycode
ORDER BY s.studycode;
There are always other ways to solve these things, but at least now other people will be more tempted to look, because the material is prepared for them.
November 24, 2015 at 5:14 am
With in excess of 600 posts, you really ought to know how to post questions by now.
Please take a look at the best-practice link for advice.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply