Need to dynamically add grouping based on username

Question

Need to dynamically add grouping based on username

krypto69

SSChampion

Points: 13604
More actions
April 12, 2016 at 10:48 am

#326335

Hi
I have this simple query:
SELECT
CompanyName as Company,
Batch,
USERNAME as [User Name],
SQLUserName as [System User Name],
--claim_form_type_id as [Claim Form Type],
CASE WHEN [claim_form_type_id] IS NULL THEN ('N/A') END as [Claim Form Type],
Claim_type as [Claim Type],--altered
status_type as [Status Type],
each as [Each],
member_last as [Member Name],
fromdate as [From Date],
todate as [To Date],
CASE WHEN [Status_Type] = 'Entered' THEN 1 ELSE 0 END AS Entered,
CASE WHEN [Status_Type] = 'Approved' THEN 1 ELSE 0 END AS Approved,
CASE WHEN [Status_Type] = 'Pending' THEN 1 ELSE 0 END AS Pending
FROM Report_data
The results come in fine but I need to add columns based on the . So that I end up with the number of claims per user name - but as additional columns. In other words - if user XYZ has 55 approved I need a column that says XYZ 55 approved and XYZ has 66 pending, etc. I still need the 'entered' as 1 columns.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

I'm thinking you'll need to group by your username, and maybe some other columns, and use the SUM(CASE trick to count the items meeting your criteria.

my first guess , based on your post:

SELECT

CompanyName as Company,

Batch,

USERNAME as [User Name],

--SQLUserName as [System User Name],

----claim_form_type_id as [Claim Form Type],

--CASE WHEN [claim_form_type_id] IS NULL THEN ('N/A') END as [Claim Form Type],

--Claim_type as [Claim Type],--altered

--status_type as [Status Type],

--each as [Each],

--member_last) as [Member Name],

MIN(fromdate) as [From Date],

MAX(todate) as [To Date],

SUM(CASE WHEN [Status_Type] = 'Entered' THEN 1 ELSE 0 END) AS Entered,

SUM(CASE WHEN [Status_Type] = 'Approved' THEN 1 ELSE 0 END) AS Approved,

SUM(CASE WHEN [Status_Type] = 'Pending' THEN 1 ELSE 0 END) AS Pending

FROM Report_data

GROUP BY

CompanyName,

Batch,

USERNAME

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

krypto69 SSChampion Points: 13604 More actions · Answer 2

Okay my bad. I apologize. I didn't do a very good job of explaining what I am having trouble with.

I'm trying to get -

Number of APPROVED claims by username

Number of ENTERED claims by username

Number of PENDING claims by username

Lowell SSC Guru Points: 323518 More actions · Answer 3

yeah, i think my sum case will do exactly that for you.

what does this return? does it look right?

SELECT

USERNAME as [User Name],

SUM(CASE WHEN [Status_Type] = 'Entered' THEN 1 ELSE 0 END) AS Entered,

SUM(CASE WHEN [Status_Type] = 'Approved' THEN 1 ELSE 0 END) AS Approved,

SUM(CASE WHEN [Status_Type] = 'Pending' THEN 1 ELSE 0 END) AS Pending

FROM Report_data

GROUP BY

USERNAME

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

krypto69 SSChampion Points: 13604 More actions · Answer 4

I apologize Lowell.

I am not explaining this very well.

This query is going to be used for as part of a dashboard. Unfortunatley the dashboard software is kinda dumb and doesn't do aggreagations, groupings, etc.

So, what I need in my results is something like:

USER TOTAL OF APPROVED CLAIMS TOTAL OF ENTERED CLAIMS TOTAL OF PENDING CLAIMS

XYZ 100 5 3

RGC 44 22 22

Lowell SSC Guru Points: 323518 More actions · Answer 5

did you try the query at all?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

krypto69 SSChampion Points: 13604 More actions · Answer 6

Sure I did and the grouping is fine.

But after putting in the piece of junk dashboard software - I now think what I really have to have is dynamic column names so that the username and the number of approved, pending, etc for that user is actually the part of the column name.

XYZ_USER_APPROVED (column name) XYZ_USER_ENTERED

20 60

PDQ_USER_APPROVED PDQ_USER_ENTERED

22 33

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 7

krypto69 (4/13/2016)
Sure I did and the grouping is fine.
But after putting in the piece of junk dashboard software - I now think what I really have to have is dynamic column names so that the username and the number of approved, pending, etc for that user is actually the part of the column name.
XYZ_USER_APPROVED (column name) XYZ_USER_ENTERED
20 60
PDQ_USER_APPROVED PDQ_USER_ENTERED
22 33

Does this mean that you do not want a single result set, but individual single-row result sets for each user?

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 8

Probably easier to kill the dashboard and rewrite it yourself so that you don't have to continue to tolerate its nuances.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

DesNorton SSC-Insane Points: 24387 More actions · Answer 9

It's very difficult to attempt an answer without sample data and expected output.

That said, perhaps this will do the trick

SELECT src.*,

TotalEntered = SUM(Entered) OVER (PARTITION BY USERNAME),

TotalApproved = SUM(Approved) OVER (PARTITION BY USERNAME),

TotalPending = SUM(Pending) OVER (PARTITION BY USERNAME)

FROM (

SELECT

CompanyName as Company,

Batch,

USERNAME as [User Name],

SQLUserName as [System User Name],

--claim_form_type_id as [Claim Form Type],

CASE WHEN [claim_form_type_id] IS NULL THEN ('N/A') END as [Claim Form Type],

Claim_type as [Claim Type],--altered

status_type as [Status Type],

each as [Each],

member_last as [Member Name],

fromdate as [From Date],

todate as [To Date],

CASE WHEN [Status_Type] = 'Entered' THEN 1 ELSE 0 END AS Entered,

CASE WHEN [Status_Type] = 'Approved' THEN 1 ELSE 0 END AS Approved,

CASE WHEN [Status_Type] = 'Pending' THEN 1 ELSE 0 END AS Pending,

FROM Report_data

) AS src

How to post data/code on a forum to get the best help.