Post
Ask Question
Akhter HUssain

Akhter HUssain

  • 653
  • 1.3k
  • 95.6k

Query required in with opening and closing

Jul 6 2020 1:53 PM
i have data below
 
  1. CREATE TABLE #ConIssuance (CID INT,iWeight int,QTY int,EntryDate  date)    
  2. CREATE TABLE #Bigbalprd (BID INT,Bweight int,Bpqty int,EntryDate date)   
  3.   
  4. INSERT INTO #Probale VALUES(10010,100,1,'01-06-2020')   
  5. INSERT INTO #Probale VALUES(10011,100,1,'01-06-2020')    
  6. INSERT INTO #Probale VALUES(10012,300,1,'02-06-2020')   
  7. INSERT INTO #Probale VALUES(10016,200,1,'02-06-2020')    
  8. INSERT INTO #Probale VALUES(10013,110,1,'03-06-2020')    
  9. INSERT INTO #Probale VALUES(10014,150,1,'03-06-2020')    
  10. INSERT INTO #Probale VALUES(10015,100,1,'04-06-2020')    
  11. INSERT INTO #Probale VALUES(10016,150,1,'04-06-2020')    
  12. INSERT INTO #Probale VALUES(10017,800,1,'05-06-2020')   
  13. INSERT INTO #Probale VALUES(10018,800,1,'05-06-2020')    
  14. INSERT INTO #Probale VALUES(10019,900,1,'05-06-2020')    
  15. INSERT INTO #Probale VALUES(10018,900,1,'06-06-2020')   
  16.   
  17. INSERT INTO #Bigbalprd VALUES(20010,500,1,'01-06-2020')   
  18. INSERT INTO #Bigbalprd VALUES(20011,600,1,'01-06-2020')    
  19. INSERT INTO #Bigbalprd VALUES(20012,700,1,'02-06-2020')   
  20. INSERT INTO #Bigbalprd VALUES(20013,200,1,'02-06-2020')    
  21. INSERT INTO #Bigbalprd VALUES(20014,410,1,'03-06-2020')    
  22. INSERT INTO #Bigbalprd VALUES(20015,250,1,'03-06-2020')    
  23. INSERT INTO #Bigbalprd VALUES(20016,200,1,'04-06-2020')    
  24. INSERT INTO #Bigbalprd VALUES(20017,250,1,'04-06-2020')    
  25. INSERT INTO #Bigbalprd VALUES(20018,400,1,'05-06-2020')   
  26. INSERT INTO #Bigbalprd VALUES(20019,200,1,'05-06-2020')    
  27. INSERT INTO #Bigbalprd VALUES(20020,300,1,'05-06-2020')    
  28. INSERT INTO #Bigbalprd VALUES(20021,350,1,'06-06-2020')    
  29.   
  30.   
  31. INSERT INTO #ConIssuance VALUES(1111,1000,1,'01-06-2020')    
  32. INSERT INTO #ConIssuance VALUES(1112,2000,1,'01-06-2020')   
  33. INSERT INTO #ConIssuance VALUES(1113,800,1,'02-06-2020')    
  34. INSERT INTO #ConIssuance VALUES(1114,600,1,'02-06-2020')    
  35. INSERT INTO #ConIssuance VALUES(1115,400,1,'03-06-2020')    
  36. INSERT INTO #ConIssuance VALUES(1116,100,1,'03-06-2020')    
  37. INSERT INTO #ConIssuance VALUES(1117,300,1,'04-06-2020')    
  38. INSERT INTO #ConIssuance VALUES(1118,110,1,'04-06-2020')    
  39. INSERT INTO #ConIssuance VALUES(1119,100,1,'05-06-2020')    
  40. INSERT INTO #ConIssuance VALUES(1120,800,1,'05-06-2020')    
  41. INSERT INTO #ConIssuance VALUES(1121,900,1,'05-06-2020')    
  42. INSERT INTO #ConIssuance VALUES(1122,1900,1,'06-06-2020')    

Output

#Probale table columns Pweight and prdqty Sum date wise.

#Bigbalprd table  Columns Bweight and Bpqty sum date wise.

#ConIssuance Columns Iweight and QTY sum date wise

Floor= (Opening + Iweight),First day opening will be 0

Closing = Floor-Bweight-Pweight

Opening = Closing will forward next day


Answers (1)