Advertisement
Guest User

Untitled

a guest
Mar 4th, 2015
181
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.88 KB | None | 0 0
  1. create table #lo
  2. (
  3. mnbr bigint
  4. )
  5. insert into #login (mnbr)
  6. select distinct (_ID)
  7. FROM [KDB].[am_LOGS].[dbo].[_LOG]
  8. WHERE time >= '2012-7-26 9:00:00
  9.  
  10. Select count(*) as countreject
  11. from #lo
  12.  
  13. create table #pffblo
  14. (
  15. mber_br
  16. )
  17. insert into #pffblo (mber_br)
  18. select distinct (mber_br)
  19. from individ ip with (nolock)
  20. join memb mp with (nolock)
  21. on( ip.i_id=mp.i_id and mp.p_type=101)
  22. where ip.times >= '2012-9-26 11:00:00.000'
  23.  
  24. select count(*) as countaccept
  25.  
  26. create table #final
  27. (
  28. countreject bigint
  29. , Countacceptbigint
  30. .....
  31. )
  32.  
  33. insert into #final (Countreject, Countaccept....more rows here...)
  34. select Countreject, Countaccept, ...more rows selected from temp tables.
  35. from #final
  36. union
  37. (select * from #lo)
  38. union
  39. (select * from #pffblo)
  40. select *
  41. from #final
  42.  
  43. drop table #lo
  44. drop table #pffblo
  45. drop table #final
  46.  
  47. CREATE TABLE #Table1
  48. (
  49. col1 BIGINT
  50. )
  51.  
  52. CREATE TABLE #Table2
  53. (
  54. col1 BIGINT
  55. )
  56.  
  57. --populate the temporary tables
  58.  
  59. CREATE TABLE #Final
  60. (
  61. col1 BIGINT
  62. )
  63.  
  64. INSERT INTO #Final (col1)
  65. SELECT *
  66. FROM #Table1
  67. UNION
  68. SELECT *
  69. FROM #Table2
  70.  
  71. drop table #table1
  72. drop table #table2
  73. drop table #Final
  74.  
  75. CREATE TABLE #Table1
  76. (
  77. col1 BIGINT
  78. )
  79.  
  80. CREATE TABLE #Table2
  81. (
  82. col1 BIGINT
  83. )
  84.  
  85. --populate the temporary tables
  86.  
  87. CREATE TABLE #Final
  88. (
  89. col1 BIGINT,
  90. col2 BIGINT
  91. )
  92.  
  93.  
  94.  
  95.  
  96. INSERT INTO #Final (col1, col2)
  97. select (SELECT Count(*) FROM #Table1) as a, (SELECT Count(*) FROM #Table2) as b
  98.  
  99. select * From #Final
  100.  
  101. drop table #table1
  102. drop table #table2
  103. drop table #Final
  104.  
  105. create table #final
  106. (
  107. countreject bigint
  108. , Countaccept bigint
  109. .....
  110. )
  111.  
  112. insert into #final (Countreject, Countaccept....more rows here...)
  113. select
  114. from
  115. (
  116. select count(*) value, 'Countreject' col -- your UNION ALL's here
  117. from #lo
  118. union all
  119. select count(*) value, 'countaccept' col
  120. from #pffblo
  121. ) x
  122. pivot
  123. (
  124. max(value)
  125. for col in ([Countreject], [countaccept])
  126. ) p
  127.  
  128. select count(*) value, 'Countreject' col
  129. from #lo
  130. union all
  131. select count(*) value, 'countaccept' col
  132. from #pffblo
  133.  
  134. insert into #final (Countreject, Countaccept....more rows here...)
  135. select max(case when col = 'Countreject' then value end) Countreject,
  136. max(case when col = 'countaccept' then value end) countaccept
  137. from
  138. (
  139. select count(*) value, 'Countreject' col -- your UNION ALL's here
  140. from #lo
  141. union all
  142. select count(*) value, 'countaccept' col
  143. from #pffblo
  144. ) x
  145.  
  146. insert into #final (Countreject, Countaccept....more rows here...)
  147. select isnull(lo.Countreject, 0) Countreject,
  148. isnull(pffblo.Countaccept, 0) Countaccept
  149. from
  150. (
  151. select count(*) Countreject,
  152. row_number() over(order by (SELECT 0)) rn
  153. from #lo
  154. ) lo
  155. left join
  156. (
  157. select count(*) Countaccept,
  158. row_number() over(order by (SELECT 0)) rn
  159. from #pffblo
  160. ) pffblo
  161. on lo.rn = pffblo.rn
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement