Combine two queries and change a value of a specific row

billj · May 25, 2015, 9:12pm

Hello SQL Expert,

How to combine to queries in order to come up with a result which is explained in the bottom of the page.
---Query1:
SELECT
CASE WHEN T1.Name = 'Apple' THEN 'AA'
WHEN T1.Name = 'Cherry' THEN 'CC'
ELSE 'EE' END AS Fruits,

CASE WHEN T2.Date <= '1/31/2011' THEN SUM(T2.D- T2.C) .001 END AS 'Jan'
CASE WHEN T2.Date <= '2/28/2011' THEN SUM(T2.D- T2.C) .001 END AS 'Feb' ----- 'Dec'

FROM T2 INNER JOIN T1 ON T1.A=T2.B

WHERE T2.Date <= getdate()
GROUP BY T1.Name, T2.Date

--------Query1 result:
Fruits Jan Feb ... Dec
AA 2 1 8
CC 3 4 10
EE 1 0 2

---Query2:
SELECT
CASE WHEN T3.Date <= '1/31/2011' THEN SUM(T3.A) -.001 End AS 'Jan'
CASE WHEN T3.Date <= '2/28/2011' THEN SUM(T3.A) -.001 End AS 'Feb' ----- 'Dec'

FROM T3

WHERE T3.Date <= getdate()
GROUP BY T3.Date

--------Query2 result:
Jan Feb ... Dec
2 3 5
9 4 0
1 1 2

I need to combine Query2 and Query1 and change the value of (CC) row.

I would like to add all of the values for each month (example for Jan.: 2+9+1 = 12) from Query2 and add to the CC row in Query for the same month (result in CC row for Jan.: 12 + 3 = 15).
Result after combination of two queries:

Fruits Jan Feb ... Dec
AA 2 1 8 // No change
CC 15 12 17 // Notice the change in CC row for each month
EE 1 0 2 // No Change

Thank you very much.

bitsmed · May 25, 2015, 9:47pm

A couple of things are puzzling me:

the queries don't seem to be producing the results (are they working examples?)
has the T3 table a name field like the T1 table to distingush the fruits?
or are all T3 table records cherry?
please explain "SUM(T2.D- T2.C) .001" and "SUM(T3.A) -.001"

Also, please provide sample data and your expected result from these sample data.

billj · May 25, 2015, 11:16pm

Hi bitsmed,

They are just working examples.
There is no relationship between T1 and T3. T1 displays the total number of any fruit based on a targeted month. The T3 displays the SUM of numbers for a column using the same targeted month.
Sorry there was an error in my formula. It is "SUM(T2.D- T2.C) * .001" NOT "SUM(T2.D- T2.C) .001". This applies to the rest of the formulas.

Can we join these two queries? So every time we run the query for example for month of Jan. then the total number in column 1 of Q2 will be added to CC row in q1. In this case CC = 3 becomes 9 for Jan.

Q1:
Fruits Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
AA 1 2 8 5 0 0 1 2 8 5 0 0
CC 9 3 7 7 1 12 9 3 7 7 1 12
EE 7 1 2 12 9 4 7 1 2 12 9 4

Q2
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
2 3 5 1 2 3 4 5 6 7 8 5
9 4 3 8 0 2 0 3 0 0 0 0
1 1 0 0 7 0 0 0 0 0 0 2

Thanks.

billj · May 25, 2015, 11:20pm

I meant CC = 3 becomes 15 for Jan NOT 9.

bitsmed · May 26, 2015, 4:10am

Hmm, from your newly supplied sample data, cherry for Jan should be 9+2+9+1=21. Anyway, you could do something like:

select Fruits
      ,sum([Jan 11])*.001 as [Jan 11]
      ,sum([Feb 11])*.001 as [Feb 11]
       ...
  from (select case t1.name
                  then 'Apple'  then 'AA'
                  then 'Cherry' then 'CC'
                  else 'EE'
               end as Fruits
              ,sum(case when year(t2.[date])=2011 and month(t2.[date])=1 then t2.d-t2.c else 0 end) as [Jan 11]
              ,sum(case when year(t2.[date])=2011 and month(t2.[date])=2 then t2.d-t2.c else 0 end) as [Feb 11]
               ...
          from t1
               inner join t2
                       on t2.b=t1.a
         where t2.[date]<getdate()
         group by t1.name
        union all
        select 'CC' as Fruits
              ,sum(case when year([date])=2011 and month([date])=1 then a else 0 end) as [Jan 11]
              ,sum(case when year([date])=2011 and month([date])=2 then a else 0 end) as [Feb 11]
               ...
          from t3
         where t3.[date]<getdate()
       ) as t

billj · May 26, 2015, 3:41pm

Hello bitsmed,

Please see line 5 (a,b,c,d) for CASE/WHEN statement. I changed it to:
from (select case WHEN t1.name = 'Apple' then 'AA' WHEN t1.name = 'Cherry' THEN 'CC' ELSE 'EE'.

When running the query, an error occurred.

Msg 8120, Level 16, State 1, Line 1
Column 't.Fruits' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Thanks again.

select Fruits
,sum([Jan 11])*.001 as [Jan 11]
,sum([Feb 11])*.001 as [Feb 11]
...
from (select case t1.name
a. then 'Apple' then 'AA'
b. then 'Cherry' then 'CC'
c. else 'EE'
d. end as Fruits
,sum(case when year(t2.[date])=2011 and month(t2.[date])=1 then t2.d-t2.c else 0 end) as [Jan 11]
,sum(case when year(t2.[date])=2011 and month(t2.[date])=2 then t2.d-t2.c else 0 end) as [Feb 11]
a. ...
from t1
a. inner join t2
i. on t2.b=t1.a
where t2.[date]<getdate()
group by t1.name
union all
select 'CC' as Fruits
,sum(case when year([date])=2011 and month([date])=1 then a else 0 end) as [Jan 11]
,sum(case when year([date])=2011 and month([date])=2 then a else 0 end) as [Feb 11]
a. ...
from t3
where t3.[date]<getdate()
) as t

bitsmed · May 26, 2015, 3:51pm

Oops, forgot to end with "group by fruits"

billj · May 26, 2015, 7:12pm

Hi bitsmed,

Your query does not give me the right data. Please see below my real queries with the right result data.

The total result for query1 for month of May (2011) is equal to (-0.15107).
The total result for query2 for month of May (2011) is equal to (0.15107).

So when we add up these two numbers the answer is 0 (zero). This is what I am expecting.

    -----Query 1    
    SELECT CASE
            WHEN OACT.FatherNum = 'Apple' THEN 'AA' 
            WHEN OACT.FatherNum = 'Cherry THEN 'CC'
            ELSE 'EE' END AS Fruits,
    
        CASE WHEN JDT1.RefDate <= DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,'2011')+5,0)) THEN
        SUM(JDT1.Debit - JDT1.Credit) * .001 END AS 'May'
    
       FROM JDT1 INNER JOIN OACT ON OACT.AcctCode=JDT1.Account
       GROUP BY OACT.FatherNum, JDT1.RefDate
    /* Note: SUM of May =  (-0.15107)  */

    -----Query 2 
       SELECT  CASE		
       WHEN OINM.DocDate <= DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,'2011')+5,0)) 
       THEN SUM(OINM.TransValue) * -.001 End AS 'May' 
   		
       FROM OINM		
       WHERE ((DOffDecAcc = '_SYS3609' OR IOffIncAcc = '_SYS3609') OR
                  (DOffDecAcc = '_SYS3612' OR
                   IOffIncAcc = '_SYS3612')) AND
       OINM.DocDate <= getdate()  		
       GROUP BY OINM.DocDate
    /* Note: SUM of May =  (0.15107) */

Thanks again.

bitsmed · May 26, 2015, 8:11pm

What was the result of the my query?
What was the result of your query for jan, feb, mar, apr for the year in question?
Does the data go further back than jan for that given year?
Is the sum for each month a running total?
If so, try this:

select Fruits
      ,sum([Jan 11])*.001 as [Jan 11]
      ,sum([Feb 11])*.001 as [Feb 11]
       ...
  from (select case t1.name
                  then 'Apple'  then 'AA'
                  then 'Cherry' then 'CC'
                  else 'EE'
               end as Fruits
              ,sum(case when year(t2.[date])*100+month(t2.[date])<=201101 then t2.d-t2.c else 0 end) as [Jan 11]
              ,sum(case when year(t2.[date])*100+month(t2.[date])<=201102 then t2.d-t2.c else 0 end) as [Feb 11]
               ...
          from t1
               inner join t2
                       on t2.b=t1.a
         where t2.[date]<getdate()
         group by t1.name
        union all
        select 'CC' as Fruits
              ,sum(case when year([date])*100+month([date])<=201101 then a else 0 end) as [Jan 11]
              ,sum(case when year([date])*100+month([date])<=201102 then a else 0 end) as [Feb 11]
               ...
          from t3
         where t3.[date]<getdate()
       ) as t
 group by Fruits

I don't suppose you can provide sample data and the result of your queries performed on the sample data?
ps.:You'll have to change the field and table names yourself.

billj · May 26, 2015, 8:33pm

Yes, I did change the field and table names to original in order to run the query and followed your format still getting the wrong data.

billj · May 26, 2015, 9:59pm

Hi bitsmed,

Yes, I got it.... I am so glad you helped me otherwise there was no way I could have done it by myself.
Thank you so much. One more favor if you don't mind...

Now I got a value for 'CC'. Is that possible to escape the case 'CC' and add 'CC' to be part of ELSE 'EE'? And using the same queries.
> ELSE 'EE' + 'CC'

bitsmed · May 26, 2015, 10:13pm

Not sure if I understand correctly, but if you change

select case t1.name
         when 'Apple'  then 'AA'
         when 'Cherry' then 'CC'
         else 'EE'
       end as Fruits

to

select case t1.name
         when 'Apple'  then 'AA'
         else 'CC+EE'
       end as Fruits

and

select 'CC' as Fruits

to

select 'CC+EE' as Fruits

you will have merged CC and EE columns.

billj · May 26, 2015, 10:18pm

Hi bitsmed,

I removed WHEN T1.Name = 'Cherry' THEN 'CC'
Replace select 'CC' as Fruits, ..............To.......... SELECT 'T1.Name = Cherry' AS Fruits,

It seems is working. Does it make sense to you?

bitsmed · May 26, 2015, 10:32pm

No makes no sence.
That would produce lines with fruitname "AA", "EE" and "T1.Name = Cherry".
But if it works for you, then who am I to argue otherwise.

billj · May 26, 2015, 10:32pm

Hi bitsmed,

I just want to see 'CC' to be part of the ELSE 'EE' + 'CC' END AS Fruits and 'CC' does show in result report.

billj · May 27, 2015, 12:25am

I just wanted to thank you again for you time.

bitsmed · May 27, 2015, 4:41am

You're welcome

Combine two queries and change a value of a specific row

I would like to add all of the values for each month (example for Jan.: 2+9+1 = 12) from Query2 and add to the CC row in Query for the same month (result in CC row for Jan.: 12 + 3 = 15). Result after combination of two queries:

I would like to add all of the values for each month (example for Jan.: 2+9+1 = 12) from Query2 and add to the CC row in Query for the same month (result in CC row for Jan.: 12 + 3 = 15).
Result after combination of two queries: