CODEWITHSUNDEEP
sqlsql-server
Johnathan M
Johnathan M

Reputation: 45

SQL multiple select with group by problems

I have a table with a list of work orders with creation dates and completion dates. I have found a GROUP BY statement on stack overflow but it doesn't seem to produce the results I expected. My goal is to have the following

TERM  |  OPENED  |  CLOSED
----------------------------
01-15  |    432   |  243
02-15  |    543   |  859
03-15  |    102   |  437
04-15  |    153   |  342
...    |    ...   |  ...
01-16  |    175   |  256
02-16  |     55   |   30

This is the code I am using

select COUNT(wo) AS opened FROM wkaw 
WHERE org_date >= DATEFROMPARTS(YEAR(GETDATE())-1,MONTH(GETDATE()),01)
GROUP BY MONTH(org_date) + '-' + YEAR(org_date)

select COUNT(wo) AS closed FROM wkaw 
WHERE cmpl_date >= DATEFROMPARTS(YEAR(GETDATE())-1,MONTH(GETDATE()),01) AND  cmpl_date IS NOT NULL
GROUP BY MONTH(cmpl_date) + '-' + YEAR(cmpl_date)

I originally was using a CROSS JOIN to try to join the two statements together but it didn't produce expected results either. My goal is that I want to eventually turn it into a VIEW, or Stored Procedure where it will select the results for the past year so that I can trend the data.

I'm not quite sure where to go from here.

UPDATED CODE BASED ON RESPONSE BELOW; it showed me how to fix my group by clause so I get the number of rows I expected now I just need to figure out how to use the join statement.

select MONTH(org_date) + (YEAR(org_date)*100) as dateinfo, COUNT(wo) AS opened FROM wkaw 
WHERE org_date >= DATEFROMPARTS(YEAR(GETDATE())-1,MONTH(GETDATE()),01)
GROUP BY MONTH(org_date) +  (YEAR(org_date)*100)
ORDER BY dateinfo

select MONTH(cmpl_date) + (YEAR(cmpl_date)*100) as dateinfo, COUNT(wo) AS closed FROM wkaw 
WHERE cmpl_date >= DATEFROMPARTS(YEAR(GETDATE())-1,MONTH(GETDATE()),01) AND cmpl_date IS NOT NULL
GROUP BY MONTH(cmpl_date) + (YEAR(cmpl_date)*100)
order by dateinfo

The order by isn't required was just using it to make it easier to read

Upvotes: 0

Views: 91

Answers (4)

Pavel Gatnar
Pavel Gatnar

Reputation: 4053

There is another possibility using sql analytic functions: 

WITH c(oterm,cterm) AS
(SELECT MONTH(org_date) + '-' + YEAR(org_date),MONTH(cmpl_date) + '-' + YEAR(cmpl_date) FROM wkaw),
a(term,cterm,opened,closed) AS
(SELECT DISTINCT oterm, cterm, COUNT(*) OVER (PARTITION BY oterm), COUNT(*) OVER (PARTITION BY cterm) FROM c)
SELECT term, opened, closed
FROM a
WHERE term = cterm

Upvotes: 0

Johnathan M
Johnathan M

Reputation: 45

In the end using both Shire's reply and Pavel's response I was able to come up with the following SQL code to produce the results I was looking for. I am not sure how this affects performance but it seems to work well.

select o.dateinfo, opened, closed FROM (
select MONTH(org_date) + (YEAR(org_date)*100) as dateinfo, COUNT(wo) AS opened FROM wkaw 
WHERE org_date >= DATEFROMPARTS(YEAR(GETDATE())-1,MONTH(GETDATE()),01)
GROUP BY MONTH(org_date) +  (YEAR(org_date)*100)) o
INNER JOIN (
select MONTH(cmpl_date) + (YEAR(cmpl_date)*100) as dateinfo, COUNT(wo) AS closed FROM wkaw 
WHERE cmpl_date >= DATEFROMPARTS(YEAR(GETDATE())-1,MONTH(GETDATE()),01) AND cmpl_date IS NOT NULL
GROUP BY MONTH(cmpl_date) + (YEAR(cmpl_date)*100)) c
ON o.dateinfo = c.dateinfo
ORDER BY o.dateinfo

Thanks for the help gentleman!

Cheers, Johnathan

Upvotes: 0

uncaught_exception
uncaught_exception

Reputation: 1078

create table wkaw (wo int, org_date date, cmpl_date date);

insert into wkaw values (1, '2015-02-01', '2015-02-28');
insert into wkaw values (1, '2015-02-01', '2015-02-28');
insert into wkaw values (1, '2015-02-01', '2015-03-31');
insert into wkaw values (1, '2015-12-01', '2015-12-21');
insert into wkaw values (1, '2015-12-01', '2015-12-21');
insert into wkaw values (1, '2015-11-01', '2015-12-21');

select coalesce(x.mth, y.mth) period, coalesce(opened, 0) opened, coalesce(closed, 0) closed from (
 (
    select RIGHT('0' + CAST(MONTH(org_date) AS varchar(2)), 2) + '-' + CAST(YEAR(org_date) AS CHAR(4)) as mth, COUNT(wo) AS opened FROM wkaw 
    WHERE org_date >= DATEFROMPARTS(YEAR(GETDATE())-1,MONTH(GETDATE()),01)
    GROUP BY RIGHT('0' + CAST(MONTH(org_date) AS varchar(2)), 2) + '-' + CAST(YEAR(org_date) AS CHAR(4))
  ) x
  full outer join
  (
    select RIGHT('0' + CAST(MONTH(cmpl_date) AS varchar(2)), 2) + '-' + CAST(YEAR(cmpl_date) AS CHAR(4)) as mth, COUNT(wo) AS closed FROM wkaw 
    WHERE cmpl_date >= DATEFROMPARTS(YEAR(GETDATE())-1,MONTH(GETDATE()),01)
    GROUP BY RIGHT('0' + CAST(MONTH(cmpl_date) AS varchar(2)), 2) + '-' + CAST(YEAR(cmpl_date) AS CHAR(4))
   ) y
   on x.mth = y.mth
 )
order by 1

Results

period  opened closed
02-2015  3      2
03-2015  0      1
11-2015  1      0
12-2015  2      3

NOTE: I haven't given any consideration to performance here.

Upvotes: 1

Pavel Gatnar
Pavel Gatnar

Reputation: 4053

Use CTE for both queries, add the term column and make full join on it: 

WITH
op(count,term) AS (
  SELECT COUNT(*), MONTH(org_date) + '-' + YEAR(org_date) FROM ...
),
cl(count,term) AS (
    SELECT COUNT(*), MONTH(cmpl_date) + '-' + YEAR(cmpl_date) FROM ...
)
SELECT ISNULL(op.term,cl.term) AS term, ISNULL(op.count,0) AS opened, ISNULL(cl.count,0) AS closed
FROM op
FULL JOIN cl ON op.term=cl.term

Upvotes: 1

Related Questions