Query Layout
-
Could somebody check the uploaded photo of a screen print of a report I need to make?
I've never made something like this and I was wondering if it needs a UNION. It looks like it does. Not sure though.
The user requests details, for every claim officer as per the layout.
What method should I follow in SQL for this? As I said I think it's a UNION but not sure. Trying to grasp the logic behind it.
Thanks
-
You need to post DML and DDL to get an informed answer, with no knowledge of the datasets involved no one can guess the solution. Also post what you have tried so far.
...
- HappyGeek - Wednesday, September 5, 2018 11:38 PM
OK, I'll phrase it differently. I'll need a column called ClaimType (top left of photo) which I have and underneath that I'll need to get the ClaimOfficer which I have in my database, and underneath that, the claim officers records. So this:
This is what I've tried. A bit different but the requested information is present.
I'm not requesting any code related information. Just design opinions.
-
Without model and/or data, it's a bit difficult to give a definite answer.
But I think that this can be solved with Grouping on the ClaimsOfficer/Claimtype.
And for the totals I think a 'GROUPING SETS' might be the anwser.This might produce your rows, the next step is the presentation.
Greetings,
Ben - ben.brugman - Thursday, September 6, 2018 1:12 AM
Thanks Ben.
-
Do you have SSRS available to you? If so it looks like a fairly straightforward report to build using that.
See example attached on building a matrix report with grouping,
- mark.humphreys - Thursday, September 6, 2018 2:58 AM
Thanks Mark.
-
Without model and/or data, it's a bit difficult to give a definite answer.
Example:
--
-- Ben Brugman
-- 20180906
--
drop table if exists testgrcreate table testgr(
ClaimOfficer varchar(300)
,Claimtype varchar(300)
,New int
,Approved int
,Rejection int
)insert into testgr values
('a','b',1,2,3),
('a','c',1,2,3),
('a','d',1,2,3),
('a','d',1,2,3),
('p','b',1,2,3),
('p','d',1,2,3),
('p','c',1,2,3);
WITH
xx AS (
select
ClaimOfficer
,Claimtype
,sum(New) NEW
,sum(Approved) APPROVED
,sum(Rejection) REJECTION
from testgr group by grouping sets (ClaimOfficer, (ClaimOfficer, claimtype))
)
SELECT CASE WHEN Claimtype is NULL THEN 'Total' else Claimtype end CLAIMTYPE, * FROM xxdrop table if exists testgr
Data is fairly abstract here, but this might give you and others some handles to work on.
Greetings,
Ben Brugman -
NikosV,
Just to try and put some clarity on this. There are so many possible approaches to this that picking one over the other would absolutely require fairly detailed knowledge of the tables and the data. Design for any kind of grouping with both rows and columns is usually easiest with a matrix in a reporting tool, whether it's SSRS or Crystal Reports, or any other similarly capable presentation layer. That's why you got so many replies that asked for the details. Without the details, it's impossible to know which approaches might not even be possible or practical, much less which ones are easiest and/or perform well. Hopefully, you'll realize that with all things SQL Server, the answer is "it depends", the vast majority of the time. This is especially true when data volumes are larger. Just showing a picture and expecting a design approach isn't realistic, although in this particular case, it does seem likely that an SSRS matrix may be best, there's just no way to be sure that a query can actually be created that both performs well AND feeds the matrix appropriately. The nature of the existing data is the determining factor, and we didn't get that here.
Just an FYI...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply