question

akhterhussain-3167 avatar image
0 Votes"
akhterhussain-3167 asked ErlandSommarskog answered

Query is not giving me ouput?

query is not giving me output

  Create table #floor (FID int,FName varchar(50))
  Create table #ItemMasterFile (Codeitem int,Descriptionitem varchar(50),Weight varchar(50))
  Create table #Bigbalprd (B_ID int,Codeitem int,Bpqty int,Bweight int,Entrydate date,FID int) 
         
   Insert into #floor values (1 ,'First')
  ,(2,'Second')
        
   INSERT INTO #ItemMasterFile VALUES
         (1,'A',111)
       , (2,'B',222)
       , (3,'C',331)
       , (4,'D',331)
       , (5,'e',441)
       , (6,'f',554)
       , (7,'g',114)
       , (8,'h',552)
       , (9,'K',553)
       , (10,'L',443)
       , (11,'M',223);
       
        
       INSERT INTO #Bigbalprd VALUES
       (111,1,1,500,'04-01-2021',1)
       ,(112,1,1,200,'04-01-2021',1)
       ,(113,6,1,300,'04-01-2021',2)
       ,(114,6,1,100,'04-01-2021',2)
     ,(115,1,1,300,'04-01-2021',2)
        
        
     CREATE TABLE #ConIssuance (CID INT,iWeight int,QTY int,EntryDate  date)  
        
      INSERT INTO #ConIssuance VALUES(1111,1000,1,'04-01-2021')  
   INSERT INTO #ConIssuance VALUES(1112,2000,1,'04-01-2021') 
   INSERT INTO #ConIssuance VALUES(1113,800,1,'04-01-2021')  
   INSERT INTO #ConIssuance VALUES(1114,600,1,'04-01-2021')  
    
    
   ;with B as (
  select a.Descriptionitem,sum(b.Bweight) Bweight,b.Entrydate, FName ,c.FID
  from #ItemMasterFile a
  inner join #Bigbalprd b on a.Codeitem=b.Codeitem
  inner join #floor c on c.FID=b.FID
  group by a.Descriptionitem,b.Entrydate,c.FName,c.FID)
  ,C as (
  select sum(a.iWeight) iWeight,EntryDate
  from  #ConIssuance a 
    
  group by EntryDate)
  select b.Entrydate [Date],upper(b.Descriptionitem) Description,B.Bweight,C.iWeight
  , cast(B.Bweight as float)/cast(C.iWeight as float)*100 [Percentage], B.FName [Floor]
  from B
  inner join C on B.Bweight=C.iWeight



sql-server-generalsql-server-transact-sql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Good day and welcome to the QnA forum

Can you please explain what is your expected result?

Using this sample table and data, what is the result which you want to get and what is the logic in getting it?

0 Votes 0 ·

@pituach
Good day and again welcome for my query - ):

Column Calculation.

Bweight =item wise Bweight sum form #Bigbalprd table date wise and Floor wise.
IWeight = sum from of #ConIssuance table date wise and floor wise
Percentage = Bweight divide by Iweight and multiply by 100 (Bweight/Iweight)*100

![109591-image.png][1]


0 Votes 0 ·
image.png (8.9 KiB)
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

Seems like that if you change

inner join C on B.Bweight=C.iWeight

to

inner join C on B.Entrydate=C.EntryDate

you get the result you are looking for.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

Any particular reason why the query would return any data? You are joining on two weight columns that have been computed through aggregations. This looks a little odd to me, but then again, I have no knowledge about the business problem you are trying to solve.

The best way to troubleshoot a query with multiple CTEs is to break it down in pieces and look at what each CTE return. This can help you to understand where you went wrong.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.