sql server - Trying To Join Two Pivot SQL Query Queries -



sql server - Trying To Join Two Pivot SQL Query Queries -

i've created 2 sql queries, first sums qty of 12 months , sec sums value on 12 months. im trying merge these 1 query, no matter seek errors.

the expect output 24 columns on 1 row e.g. jan, janqty, feb, febqty, mar, marqty etc.

any help great!

first query (qty):

select sum(janqty) 'janqty', sum(febqty) 'febqty', sum(marqty) 'marqty', sum(aprqty) 'aprqty', sum(mayqty) 'mayqty', sum(juneqty) 'juneqty', sum(julyqty) 'julyqty', sum(augqty) 'augqty', sum(septqty) 'septqty', sum(octqty) 'octqty', sum(novqty) 'novqty', sum(decqty) 'decqty' ( select isnull([1],0) janqty, isnull([2],0) febqty, isnull([3],0) marqty, isnull([4],0) aprqty, isnull([5],0) mayqty, isnull([6],0) juneqty, isnull([7],0) julyqty, isnull([8],0) augqty, isnull([9],0) septqty, isnull([10],0) octqty, isnull([11],0) novqty, isnull([12],0) decqty (select sum(t0.quantity) qtybal, month(t1.docdate) month inv1 t0 inner bring together oinv t1 on t0.docentry = t1.docentry t1.docdate between '20140101' , '20141231' , year(t1.docdate) = 2014 grouping t0.quantity, t1.docdate) s pivot (sum(qtybal) month in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) p union select isnull([1],0) janqty, isnull([2],0) febqty, isnull([3],0) marqty, isnull([4],0) aprqty, isnull([5],0) mayqty, isnull([6],0) juneqty, isnull([7],0) julyqty, isnull([8],0) augqty, isnull([9],0) septqty, isnull([10],0) octqty, isnull([11],0) novqty, isnull([12],0) decqty (select sum(-t0.quantity) qtybal, month(t1.docdate) month rin1 t0 inner bring together orin t1 on t0.docentry = t1.docentry t1.docdate between '20140101' , '20141231' , year(t1.docdate) = 2014 grouping -t0.quantity, t1.docdate) s pivot (sum(qtybal) month in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) p ) sq

second query (value):

select sum(jan) 'jan', sum(feb) 'feb', sum(mar) 'mar', sum(apr) 'apr', sum(may) 'may', sum(june) 'june', sum(july) 'july', sum(aug) 'aug', sum(sept) 'sept', sum(oct) 'oct', sum(nov) 'nov', sum(dec) 'dec' ( select isnull([1],0) jan, isnull([2],0) feb, isnull([3],0) mar, isnull([4],0) apr, isnull([5],0) may, isnull([6],0) june, isnull([7],0) july, isnull([8],0) aug, isnull([9],0) sept, isnull([10],0) oct, isnull([11],0) nov, isnull([12],0) dec (select sum(t0.linetotal) bal, month(t1.docdate) month inv1 t0 inner bring together oinv t1 on t0.docentry = t1.docentry t1.docdate between '20140101' , '20141231' , year(t1.docdate) = 2014 grouping t0.linetotal, t1.docdate) s pivot (sum(bal) month in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) p union select isnull([1],0) jan, isnull([2],0) feb, isnull([3],0) mar, isnull([4],0) apr, isnull([5],0) may, isnull([6],0) june, isnull([7],0) july, isnull([8],0) aug, isnull([9],0) sept, isnull([10],0) oct, isnull([11],0) nov, isnull([12],0) dec (select sum(-t0.linetotal) bal, month(t1.docdate) month rin1 t0 inner bring together orin t1 on t0.docentry = t1.docentry t1.docdate between '20140101' , '20141231' , year(t1.docdate) = 2014 grouping -t0.linetotal, t1.docdate) s pivot (sum(bal) month in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) p ) sq

try using cross join:

select jan, janqty ( select sum(janqty) 'janqty', sum(febqty) 'febqty', sum(marqty) 'marqty', sum(aprqty) 'aprqty', sum(mayqty) 'mayqty', sum(juneqty) 'juneqty', sum(julyqty) 'julyqty', sum(augqty) 'augqty', sum(septqty) 'septqty', sum(octqty) 'octqty', sum(novqty) 'novqty', sum(decqty) 'decqty' ( select isnull([1],0) janqty, isnull([2],0) febqty, isnull([3],0) marqty, isnull([4],0) aprqty, isnull([5],0) mayqty, isnull([6],0) juneqty, isnull([7],0) julyqty, isnull([8],0) augqty, isnull([9],0) septqty, isnull([10],0) octqty, isnull([11],0) novqty, isnull([12],0) decqty ( select sum(t0.quantity) qtybal, month(t1.docdate) month inv1 t0 inner bring together oinv t1 on t0.docentry = t1.docentry t1.docdate between '20140101' , '20141231' , year(t1.docdate) = 2014 grouping t0.quantity, t1.docdate ) s pivot ( sum(qtybal) month in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]) ) p union select isnull([1],0) janqty, isnull([2],0) febqty, isnull([3],0) marqty, isnull([4],0) aprqty, isnull([5],0) mayqty, isnull([6],0) juneqty, isnull([7],0) julyqty, isnull([8],0) augqty, isnull([9],0) septqty, isnull([10],0) octqty, isnull([11],0) novqty, isnull([12],0) decqty ( select sum(-t0.quantity) qtybal, month(t1.docdate) month rin1 t0 inner bring together orin t1 on t0.docentry = t1.docentry t1.docdate between '20140101' , '20141231' , year(t1.docdate) = 2014 grouping -t0.quantity, t1.docdate) s pivot ( sum(qtybal) month in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]) ) p ) sqa ) qty cross bring together ( select sum(jan) 'jan', sum(feb) 'feb', sum(mar) 'mar', sum(apr) 'apr', sum(may) 'may', sum(june) 'june', sum(july) 'july', sum(aug) 'aug', sum(sept) 'sept', sum(oct) 'oct', sum(nov) 'nov', sum(dec) 'dec' ( select isnull([1],0) jan, isnull([2],0) feb, isnull([3],0) mar, isnull([4],0) apr, isnull([5],0) may, isnull([6],0) june, isnull([7],0) july, isnull([8],0) aug, isnull([9],0) sept, isnull([10],0) oct, isnull([11],0) nov, isnull([12],0) dec ( select sum(t0.linetotal) bal, month(t1.docdate) month inv1 t0 inner bring together oinv t1 on t0.docentry = t1.docentry t1.docdate between '20140101' , '20141231' , year(t1.docdate) = 2014 grouping t0.linetotal, t1.docdate ) s pivot ( sum(bal) month in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]) ) p union select isnull([1],0) jan, isnull([2],0) feb, isnull([3],0) mar, isnull([4],0) apr, isnull([5],0) may, isnull([6],0) june, isnull([7],0) july, isnull([8],0) aug, isnull([9],0) sept, isnull([10],0) oct, isnull([11],0) nov, isnull([12],0) dec ( select sum(-t0.linetotal) bal, month(t1.docdate) month rin1 t0 inner bring together orin t1 on t0.docentry = t1.docentry t1.docdate between '20140101' , '20141231' , year(t1.docdate) = 2014 grouping -t0.linetotal, t1.docdate ) s pivot ( sum(bal) month in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]) ) p ) sqb ) bal

sql-server sql-server-2012

Comments

Post a Comment

Popular posts from this blog

assembly - What is the addressing mode for ld, add, and rjmp instructions? -

assembly - What is the addressing mode for ld, add, and rjmp instructions? - hey guys know ldi (load immediate) addressing mode set immediate , st instruction addressing mode set index. however, have no thought others curious are! thanks :) the addressing modes explained in instruction set, in first few pages. in table of instructions see operands instruction determine addressing mode. mode indicated arguments assembly instruction. for example, ld had several different ways of operating, depending on arguments. 1 of them ld rd,x d number of register, such ld r3,x . ld r3,-z else (although both indirect addressing). rjmp k relative addressing, jumping pc + k + 1. add rr,rd direct register addressing. result contained in rd. assembly avr instructions
Read more

vowpalwabbit - Interpreting Vowpal Wabbit results: Why are some lines appended by "h"? -

vowpalwabbit - Interpreting Vowpal Wabbit results: Why are some lines appended by "h"? - below part of log training vw model. why of these lines followed h? you'll notice that's true of "average loss" line in summary @ end. i'm not sure means, or if should care. ... average since illustration illustration current current current loss lastly counter weight label predict features 1.000000 1.000000 1 1.0 -1.0000 0.0000 15 0.500000 0.000000 2 2.0 1.0000 1.0000 15 1.250000 2.000000 4 4.0 -1.0000 1.0000 9 1.167489 1.084979 8 8.0 -1.0000 1.0000 29 1.291439 1.415389 16 16.0 1.0000 1.0000 45 1.096302 0.901166 32 32.0 -1.0000 -1.0000 21 1.299807 1.503312 64 64.0 -1.0000 1.0000 ...
Read more

Is there a way to convert an HTML page styled with Bootstrap CSS into email-compatible html? -

Is there a way to convert an HTML page styled with Bootstrap CSS into email-compatible html? - is there way convert html page styled bootstrap css html email compatible form? see there exclusively different css frameworks, ink, creating email compatible html, there flow creating email doesn't require utilize of different css framework? there html -> pdf -> email html flow? or other flow? no, couple of reasons... first, there no reliable css or html email. clients on place in terms of css back upwards & how render html, structural/semantic problem. isn't converting classes much altering html structure. as explained in this css tricks post, typical solution ol-skool techniques table-based layouts, requiring exclusively restructure existing html. have add together client-specific hacks. second, , more importantly, shouldn't send everything on existing page email. if it's newsletter subscribers, people won't read it. instead, create ...
Read more