need to list all software product names and versions with their current status

phman1275
phman1275 used Ask the Experts™
on
Hi, I have 2 tables and need to list all software product names and versions with their current status.

Software table

Name    Version
Tax         1988
Tax          1999
Tax          2010

SoftwareBuild Table
Version   CompID   ComponentName       BuildStatus
1998        1               Keyboard Driver          Ready
1998        3               Dbase Interface           Ready
1999        1               Keyboard Driver          Ready
1999        4               Dbase Interface           Ready
1999        6               Pen Driver                    Usable
2010        1               Keyboard Driver          Ready
2010        2               Touch Screen Driver   Ready
2010        5               Chart Genertor            Null

So the output should be like the following

Name     version     status
Tax          1998         Ready
Tax          1999         Usable (since one of the component status is usable)
Tax          2010         No Ready (since one of the component status is null)

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
AlexSenior Infrastructure Analyst

Commented:
What software are you doing this in?
ste5anSenior Developer

Commented:
Without the software name in the build table, this makes no sense. Consider

Name    Version
Tax          1988
Tax          1999
Tax          2010
Exce        2010

...

Author

Commented:
I am using SQL Server. OK I will add the software name to the build table but I still get stuck.
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
When you get the tables "fixed"  try this:
select name, b.version,
  case min(case  
              when buildstatus is null then 1
              when buildstatus='Usable' then 2
              when buildstatus='Ready' then 3
          end)
    when 1 then 'Not Ready'
    when 2 then 'Usable'
    when 3 then 'Ready'
    end status
from SoftwareBuild b
    join software s on b.version=s.version
group by name, b.version

Open in new window


working example:
https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=bb15fec147877b732f1341da8834be2e
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
SELECT S.name, S.version, SB.status
FROM #Software S
LEFT OUTER JOIN (
    SELECT
        Version,
        CASE WHEN SUM(CASE WHEN BuildStatus IS NULL THEN 1 ELSE 0 END) > 0 THEN 'Not Ready'
             WHEN SUM(CASE WHEN BuildStatus = 'Usable' THEN 1 ELSE 0 END) > 0 THEN 'Usable'
             ELSE 'Ready' END AS status
    FROM #SoftwareBuild
    GROUP BY Version
) AS SB ON SB.Version = S.Version
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
@Scott:

Cleaner than mine!  That's what I wanted to do but my mind is already in Thanksgiving mode!
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
Thanks!

Yeah, we can leave in 30 minutes today ... but we have to work on Fri.  Not a big deal to me at all.  Many others just take the day off.

Author

Commented:
Thank you so much Slightwv and Scott.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial