March 19, 2018 at 12:50 pm
Good afternoon all,
I have some data that I need to query by craft_group and return job status totals grouped by rundate. The craft_group can either be equal to aspecific group or not equal to 'OTHER'. I'm trying to do this in one fell swoop. I've tried common tableexpression with dynamic sql. The craft_groupparameter is what gets me. Does anyonehave any insight on how to do this? Please let me know if I've not been clear on the specifics.
TIA
Cosandra
declare @jobdata table
(rundate datetime,
jobnbr varchar(8),
tasknbrvarchar(3),
jobstatusint,
craft_groupvarchar(15) )
insert into @jobdata (rundate, jobnbr, tasknbr, jobstatus,craft_group)
values
('2018/03/14','17000992','500',130,'BHI'),
('2018/03/14','17000985','500',130,'BHI'),
('2018/03/14','17001013','505',250,'BHI'),
('2018/03/14','10513000','870',130,'DIESEL GEN'),
('2018/03/14','16506040','0',250,'ELEC'),
('2018/03/14','17514015','0',250,'IC'),
('2018/03/14','18500401','600',130,'MECH'),
('2018/03/14','16506119','0',250,'MECH'),
('2018/03/14','17512936','950',375,'IC'),
('2018/03/14','17004587','895',250,'BHI'),
('2018/03/21','18500101','514',250,'DIESEL GEN'),
('2018/03/21','14511167','0',250,'IC'),
('2018/03/21','12509375','500',250,'IC'),
('2018/03/21','16505416','0',130,'MECH'),
('2018/03/21','13505086','920',130,'OPS'),
('2018/03/21','17511392','490',250,'ELEC'),
('2018/03/21','18500705','514',250,'ELEC'),
('2018/03/21','17511396','0',250,'OPS'),
('2018/03/21','16506195','500',250,'DIESEL GEN'),
('2018/03/21','17500321','0',130,'OTHER')
Results if craft_group <> 'OTHER'
|
|
|
|
|
|
|
| rundate
| craft group
| total_130
| total_250
| total_375
|
|
| 03/14/2018
|
| 4
| 5
| 1
|
|
| 03/21/2018
|
| 2
| 7
|
|
|
Results if craft_group = 'MECH'
|
|
|
|
|
|
| rundate
| craft group
| total_130
| total_250
| total_375
|
| 03/14/2018
| MECH
| 1
| 1
|
|
| 03/21/2018
| MECH
| 1
|
|
|
Results if craft_group = 'IC'
|
|
|
|
|
|
| rundate
| craft group
| total_130
| total_250
| total_375
|
| 03/14/2018
| IC
|
| 1
| 1
|
| 03/21/2018
| IC
|
| 2
|
|
March 19, 2018 at 1:37 pm
I'm not sure of what you want you output to be. Did you want 1 query that returned all the different craft_groups and also what is <> to 'OTHER'? Or do you want just one of the craft_groups for a given query?
select rundate, craft_group, jobstatus, Count(*)
from @jobdata
where craft_group = 'MECH' -- <> 'OTHER' -- gives all the craft_groups except 'OTHER'
group by rundate, craft_group, jobstatus
order by rundate, jobstatus
The above query returns the results for one group. If you comment out the where clause, it will return all the groups.
When you want to query for <> 'OTHER' (Total Counts), it is a slightly different query:
select rundate, jobstatus, Count(*)
from @jobdata
where craft_group <> 'OTHER'
group by rundate, jobstatus
Getting the output in the form that you want would require either cross tabs, pivot or formatting the output in your front end.
You could do this with a SP and pass in the group that you are looking for and then return a table of results.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 19, 2018 at 1:53 pm
This is the data behind several SSRS reports. The user selects the craft group from a dropdown. If they select the word "ALL", then the SQL would be craft_group <> 'OTHER'. If they select a craft_group, then the SQL would be craft_group = 'MECH'. What I was hoping I could do is something that would take into account whether the craft_group value was 'OTHER' or a craft_group name and it be done in one SQL statement. I know I could use an if statement such as "If craft_group <> 'OTHER' then do this else do that". But there will be duplicate SQL and I was trying to avoid that. Thanks for responding!
March 19, 2018 at 1:58 pm
I got something like this to return all, including a rollup of all the non other entries:
declare @jobdata table
(rundate datetime,
jobnbr varchar(8),
tasknbr VARCHAR(3),
jobstatus INT,
craft_group VARCHAR(15) );
INSERT into @jobdata (rundate, jobnbr, tasknbr, jobstatus,craft_group)
values
('2018/03/14','17000992','500',130,'BHI'),
('2018/03/14','17000985','500',130,'BHI'),
('2018/03/14','17001013','505',250,'BHI'),
('2018/03/14','10513000','870',130,'DIESEL GEN'),
('2018/03/14','16506040','0',250,'ELEC'),
('2018/03/14','17514015','0',250,'IC'),
('2018/03/14','18500401','600',130,'MECH'),
('2018/03/14','16506119','0',250,'MECH'),
('2018/03/14','17512936','950',375,'IC'),
('2018/03/14','17004587','895',250,'BHI'),
('2018/03/21','18500101','514',250,'DIESEL GEN'),
('2018/03/21','14511167','0',250,'IC'),
('2018/03/21','12509375','500',250,'IC'),
('2018/03/21','16505416','0',130,'MECH'),
('2018/03/21','13505086','920',130,'OPS'),
('2018/03/21','17511392','490',250,'ELEC'),
('2018/03/21','18500705','514',250,'ELEC'),
('2018/03/21','17511396','0',250,'OPS'),
('2018/03/21','16506195','500',250,'DIESEL GEN'),
('2018/03/21','17500321','0',130,'OTHER');
SELECT
[j].[craft_group]
, [j].[rundate]
, SUM(CASE WHEN [j].[jobstatus] = 130 THEN 1 ELSE 0 END) [Total130]
, SUM(CASE WHEN [j].[jobstatus] = 250 THEN 1 ELSE 0 END) [Total250]
, SUM(CASE WHEN [j].[jobstatus] = 375 THEN 1 ELSE 0 END) [Total375]
FROM
@jobdata AS [j]
--WHERE
-- [j].[craft_group] = 'MECH'
GROUP BY
[j].[craft_group],
[j].[rundate]
UNION ALL
SELECT
[craft_group] = ''
, [j].[rundate]
, SUM(CASE WHEN [j].[jobstatus] = 130 THEN 1 ELSE 0 END) [Total130]
, SUM(CASE WHEN [j].[jobstatus] = 250 THEN 1 ELSE 0 END) [Total250]
, SUM(CASE WHEN [j].[jobstatus] = 375 THEN 1 ELSE 0 END) [Total375]
FROM
@jobdata AS [j]
WHERE
[j].[craft_group] <> 'OTHER'
GROUP BY
[j].[rundate]
ORDER BY
[j].[craft_group],
[j].[rundate]
;
March 19, 2018 at 3:02 pm
cwatson 81945 - Monday, March 19, 2018 1:53 PMBut there will be duplicate SQL and I was trying to avoid that.
You can modify what I gave you and put a UNION between the 2 queries and it returns everything in 1 query. Or you can use Lynn's suggestion which uses Cross Tabs. Either one, though, "duplicates" the query with the Union clause. Queries that use union do not automatically translate into "slow". If a union is what is required, then you use a union and tune the query like any other.
select rundate, 'TOTAL' craft_group, jobstatus, Count(*)
from @jobdata
where craft_group <> 'OTHER'
group by rundate, jobstatus
union
select rundate, craft_group, jobstatus, Count(*)
from @jobdata
where craft_group <> 'OTHER'
group by rundate, craft_group, jobstatus
order by rundate, jobstatus, craft_group
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply