17623

Select SQL mapping query

Question:

I use SQL Server 2012 and I have these tables:

Table <strong>Tb1</strong> (ID Int, CodeID Int, Value Int)

ID CodeID Value ---------------------- 1 1 10 2 1 14 3 1 5 4 1 25 5 2 12 6 2 17 7 2 4 8 2 10 9 2 6

Table <strong>Tb2</strong> (CodeID Int Value Int)

CodeID Value --------------- 1 25 2 20

I want a query for get records from Tb1 that SUM(Tb1.Value) <= Tb2.Value

For example result for above records is :

Tb1.ID Tb1.CodeID Tb1.Value UsedValue Tb2.Value -------------------------------------------------------- 1 1 10 10 25 2 1 14 14 25 3 1 5 1 25 5 2 12 12 20 6 2 17 8 20

I use cursor for get above query. But my query, execute with low performance. I want a query without cursor.

<strong>EDIT 1</strong> UsedValue is Tb1.Value until sum of Tb1.Value was lowest than Tb2.Value.

Answer1:

Try this:

;WITH Subtotals AS ( SELECT T1.Id, T1.CodeId, SUM(T2.Value) SubTotal FROM Tb1 T1 JOIN Tb1 T2 ON T1.CodeId = T2.CodeId AND T1.Id >= T2.Id GROUP BY T1.Id, T1.CodeId ) SELECT S.ID, S.CodeID, T1.Value, CASE WHEN T2.value >= S.Subtotal THEN T1.value ELSE T1.value - (S.Subtotal - T2.value) END UsedValue, T2.Value T2Value FROM Subtotals S JOIN Tb2 T2 ON S.CodeId = T2.CodeId JOIN Tb1 T1 ON S.Id = T1.Id WHERE T2.Value >= S.SubTotal - T1.Value

See result on <a href="http://sqlfiddle.com/#!6/5c5a8/3" rel="nofollow"><strong>SQL FIDDLE</strong></a>

Answer2:

select T.Id , T.CodeId , T.Value , T.Value -(Total - T2.Value) as UsedValue , T2.Value as T2_Value from ( select a.id , a.codeid , a.Value , sum(b.value) as Total from Tb1 a join Tb1 b on a.Id >= b.id and a.CodeId = b.CodeId group by a.id, a.CodeId, a.Value ) T join Tb2 T2 on T.CodeId = T2.CodeId where Total < (T2.Value + T.Value)

there is only one assumption when i wrote this code . that rows are ordered by ID, if it is not true you have to add ordering column !

Answer3:

select id,codeid,value,case when vt>0 then value else value+vt end usedvalue, value2 from (select *, value2-runtot vt, max(case when value2-runtot<0 then value2-runtot else -runtot end) over(PARTITION BY codeid) usedvalue from (SELECT t1.id, t1.codeid, t1.value, t2.value value2, sum(t1.value) OVER(PARTITION BY t1.codeid ORDER BY t1.id) runtot FROM tb1 t1 INNER JOIN tb2 t2 ON t1.codeid = t2.codeid ) X )Y where vt>=usedvalue

Recommend

  • MongoDB - Updating only $ref from DBRef field type
  • how to select data between 2 dates
  • Conflict about required and valuechangeListener for
  • SQL SELECT ORDER BY multiple columns depending on value of other column
  • TypeScript Mapped Types: Get element type of array
  • Dereferenceing on casting the void pointer to float*/int*
  • I have a SQLite syntax error in DELETE statement
  • Unselect column after pasting data
  • Edge-case: When (only) reversing order of template parameters in specialization, can non-specialized
  • How does socketcan handle arbitration?
  • Ordering a Union Query in MS Access SQL
  • Mongodb update() vs. findAndModify() performace
  • It is possible use the same sql azure instance from two different cloud service of two different sub
  • R convert summary result (statistics with all dataframe columns) into dataframe
  • Approximate Order-Preserving Huffman Code
  • Grails calculated field in SQL
  • Row Count Is Returning the incorrect number using RaptureXML
  • Django: Count of Group Elements
  • Handling un-mapped Rest path
  • Is my CUDA kernel really runs on device or is being mistekenly executed by host in emulation?
  • What is the “return” in scheme?
  • Fetching methods from BroadcastReceiver to update UI
  • recyclerView does not call the onBindViewHolder when scroll in the view
  • Is possible to count alias result on mysql
  • JSON with duplicate key names losing information when parsed
  • Symfony2: How to get request parameter
  • How to make Safari send if-modified-since header?
  • ORA-29908: missing primary invocation for ancillary operator
  • How to get next/previous record number?
  • GridView Sorting works once only
  • How to pass list parameters for each object using Spring MVC?
  • How to delete a row from a dynamic generate table using jquery?
  • json Serialization in asp
  • Rails 2: use form_for to build a form covering multiple objects of the same class
  • WPF Applying a trigger on binding failure
  • How do you join a server to an Active Directory (domain)?
  • Setting background image for body element in xhtml (for different monitors and resolutions)
  • How to stop GridView from loading again when I press back button?
  • How does Linux kernel interrupt the application?
  • JaxB to read class hierarchy