• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 63
  • Last Modified:

SQL Sub Query Needs to be modified to be more dynamic

I have a query that I need to reformat to account for the over 100 instances of a_AssignmentsIdName. This example shows only two possibilities(Full Swing and Media Fee) but there are many more.
Is there a way to reconstruct this query where I do not have to hardcode each and every instance of a_AssignmentsIdName.



  SELECT DISTINCT a_ActorID,
ap.a_ActorIdentifierName as Actor
,(select count(*) from a_partsandincrements as fs where fs.a_ActorIdentifierName=ap.a_ActorIdentifierName and fs.a_AssignmentsIdName='Full Swing'
) as [Full_Swing]
,(select count(*) from a_partsandincrements as mf where mf.a_ActorIdentifierName=ap.a_ActorIdentifierName and mf.a_AssignmentsIdName='Media Fee'
) as [Media Fee]
FROM a_partsandincrements ap
WHERE ap.a_ActorID IN ('143939','128473')

Open in new window


Output from query above
 a_ActorID	Actor	           Full_Swing	Media Fee
128473	                 Alv, Sandy	1	7
143939	                Arr, Andrew	5	6

Open in new window

0
swaggrK
Asked:
swaggrK
  • 6
  • 4
1 Solution
 
PortletPaulfreelancerCommented:
sorry... just realized what I proposed wasn't correct
0
 
PortletPaulfreelancerCommented:
This is not the full solution, but it will simplify what you have been doing. Placing a case expression inside the COUNT() enables you to count with conditions.
SELECT
      ap.a_ActorID
    , ap.a_ActorIdentifierName as Actor
    , COUNT( case when fs.a_AssignmentsIdName='Full Swing' then ap.a_ActorID end ) as [Full Swing]
    , COUNT( case when fs.a_AssignmentsIdName='Media Fee' then ap.a_ActorID end ) as [Media Fee]
FROM a_partsandincrements AS ap
WHERE ap.a_ActorID IN ('143939', '128473')
GROUP BY
      ap.a_ActorID
    , ap.a_ActorIdentifierName
;

Open in new window


To make this  "more dynamic" requires using "dynamic SQL" which is SQL that actually writes new SQL and that new SQL is then executed. I may have time for that later, unless someone does it first.
0
 
Scott PletcherSenior DBACommented:
It's easy enough to see one total per row.  If you need to, we can instead dynamically pivot the data to columns, although that will be more overhead.

SELECT a_ActorID,
ap.a_ActorIdentifierName as Actor,
ap.AssignmentsIdName,
count(*) AS Count
FROM a_partsandincrements ap
WHERE ap.a_ActorID IN ('143939','128473')
GROUP BY a_ActorID, ap.AssignmentsIdName, ap.a_ActorIdentifierName
--ORDER BY Actor --,AssignmentsIdName
--ORDER BY AssignmentsIdName, Actor
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
PortletPaulfreelancerCommented:
Here is an approach to your "more dynamic" query.  It is the same logic as before; using case expressions inside the COUNT(); but now the SQL is generated.
DECLARE @cols  AS NVARCHAR(MAX)
DECLARE @query AS NVARCHAR(MAX)

SET @cols =   (SELECT DISTINCT 
                      ',' 
                     + ' COUNT( case when ap.a_AssignmentsIdName='
                     + ''''
                     + a_AssignmentsIdName 
                     + ''''
                     + ' then ap.a_ActorID end ) as '
                     + QUOTENAME(a_AssignmentsIdName) 
                   FROM a_partsandincrements
                   WHERE a_ActorID IN ('143939', '128473')
                   FOR XML PATH(''), TYPE
                  ).value('.', 'NVARCHAR(MAX)') 

set @query = 
    + ' SELECT ap.a_ActorID , ap.a_ActorIdentifierName as Actor'
    +     @cols
    + ' FROM a_partsandincrements AS ap'
    + ' WHERE ap.a_ActorID IN (''143939'', ''128473'')'
    + ' GROUP BY ap.a_ActorID , ap.a_ActorIdentifierName'


execute(@query)
;

Open in new window


To expand, you will need to alter the 2 where clauses

                   WHERE a_ActorID IN ('143939', '128473')

    + ' WHERE ap.a_ActorID IN (''143939'', ''128473'')'
0
 
swaggrKAuthor Commented:
@PortletPaul
thanks for the dynamic strategy but the the WHERE clause expansion part is giving me problems. How do I add a_ActorID without hardcoding over a thousand a_ActorID's?
0
 
PortletPaulfreelancerCommented:
I was answring your soecific question:
        "reformat to account for the over 100 instances of a_AssignmentsIdName ..."

If you have a specific subset of id's to filter by, and that is the only viable method of filtering, then your options include:

1. insert those id's into a table (perhaps a temp table)
2. list those id's as a "union all" query
3. do 2 as a CTE
4. use 2 to do the insert for 1
5. use 3 to do the inserts for 1
6. hard code the query

I think that offering any more alternatives requires knowing both your data model and data.
0
 
swaggrKAuthor Commented:
@PortletPaul yes I understand. Are you able to assist or do I have to ASK another question?
0
 
PortletPaulfreelancerCommented:
It may help you to get more folks on to a new question.

My point before though was I can give you technical options, but without familiarity with the data I can't offer much more than that.

Right now the only "simpler" way I can offer is to place the id's into a table (or equivalent)

SELECT id
into #my_id_list
FROM (
    SELECT 'abc' AS [id] UNION ALL
    SELECT 'edf' UNION ALL
    SELECT 'ghi' UNION ALL
    SELECT 'kjl' UNION ALL
    SELECT 'mno'
)

select
*
from some_table
where id in #my_id_list
;
0
 
swaggrKAuthor Commented:
@PortletPaul Thanks for all of your help. Trully appreciate it.
0
 
swaggrKAuthor Commented:
Extremely knowledgeable and helpful. Also, offered additional guidance on how to expand his suggestion.
0
 
PortletPaulfreelancerCommented:
Thank you. Too kind.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now