Report!

[manhthaodn]

Here is the link to download my database with sample data (Management Inventory): http://www.mediafire.com/?9o5ycrqot2risa8

I am making a query to access redundant or missing quatity of material.
Report

MatId CusMatName TDK NTK XTK ...
. .
. .
. .
. .

with: TDK:

select detail.MatId as ID_Nhap, SUM(Quantity) as SL_Nhap from InventoryTransDetail detail join InventoryTransHeader header
ON detail.InvTransHeaderId =  header.InvTransHeaderId 
where header.InvTransTypeId = 'IN1' and header.InvTransAccDate between '2011-02-01' and '2011-03-01' GROUP BY detail.MatId

[Condition: period in 2011-02-01 and 2011-03-01]
NTK:

select detail.MatId, SUM(Quantity) from InventoryTransDetail detail join InventoryTransHeader header
ON detail.InvTransHeaderId =  header.InvTransHeaderId 
where header.InvTransTypeId = 'OU1' and header.InvTransAccDate between '2011-02-01' and '2011-03-01' GROUP BY detail.MatId

[Condition: period in 2011-02-01 and 2011-03-01]

XTK: SUM(NTK)-SUM(XTK) [Condition: period before 2011-02-01] (Not done)

Then to do is save all the results on an object and then output the result (select statement) - resulting in the above table.

I have to do this how?

I wish to receiving your help!!!!

[urtrivedi]

to convert rows to column you need to do following query

select detail.MatId, 
SUM( case when header.InvTransTypeId = 'in1' then Quantity else 0 end) as IN1, 
SUM( case when header.InvTransTypeId = 'OU1' then Quantity else 0 end) as OU1,
SUM( case when header.InvTransTypeId = 'TK1' then Quantity else 0 end) as TK1,


SUM( case when header.InvTransTypeId = 'OU1' then Quantity else 0 end) 
-SUM( case when header.InvTransTypeId = 'IN1' then Quantity else 0 end) as OU_MINUS_IN,

SUM(QUANTITY) as TOTAL
from InventoryTransDetail detail join InventoryTransHeader header
ON detail.InvTransHeaderId =  header.InvTransHeaderId 
where  Header.InvTransAccDate between '2011-02-01' and '2011-03-01' GROUP BY detail.MatId

[manhthaodn]

First I would like to thank urtrivedi! I follow your instructions but there was some problems:
1. Query performance is very slow if I JOIN with other tables to get more information
2. I can not JOIN with other tables to get information

SELECT detail.MatId AS 'Mã số vật tư', custom.CusMatName

SUM(case when header.InvTransTypeId = 'IN1' and header.InvTransAccDate between '2011-04-02' and '2011-08-01' then Quantity else 0 end) AS 'Nhập trong kỳ',
SUM(case when header.InvTransTypeId = 'OU1' and header.InvTransAccDate between '2011-04-02' and '2011-08-01' then Quantity else 0 end) AS 'Xuất trong kỳ',     
 
SUM(case when header.InvTransTypeId = 'IN1' and header.InvTransAccDate between '2011-01-01' and '2011-04-01' then Quantity else 0 end) -
SUM(case when header.InvTransTypeId = 'OU1' and header.InvTransAccDate between '2011-01-01' and '2011-04-01' then Quantity else 0 end) AS 'Tồn đầu kỳ'

FROM InventoryTransDetail detail 
JOIN InventoryTransHeader header ON detail.InvTransHeaderId = header.InvTransHeaderId
JOIN Material material ON detail.MatId = material.MatId
JOIN CustomsMaterial custom ON material.CustMatId = custom.CustMatId
--WHERE Header.InvTransAccDate BETWEEN '2011-02-01' AND '2011-03-01' 
GROUP BY detail.MatId

[urtrivedi]

The query is simple so it is not responsible for slow performance.
check your joins, also you can add as many table you want to join for that you need to add new columns in group by as well

GROUP BY detail.MatId, custom.CusMatName

Also do not remove date condition, it will filter result and will give result faster

[manhthaodn]

urtrivedi, thanks you very much!

[manhthaodn]

If the request is to take the column [Tồn đầu kỳ]-[Nhập trong kỳ+Xuất trong kỳ] How should solve?

Thanks!

[urtrivedi]

Following query may work as you want.

I have to tell you one thing that you should not use single quote and space in alias column name {'Mã số vật tư'} it should be as ma_so_vat_tru, without space and quote

SELECT detail.MatId AS 'Mã số vật tư', custom.CusMatName,

 
(
SUM(case when header.InvTransTypeId = 'IN1' and header.InvTransAccDate between '2011-01-01' and '2011-04-01' 

then Quantity else 0 end) -
SUM(case when header.InvTransTypeId = 'OU1' and header.InvTransAccDate between '2011-01-01' and '2011-04-01' 

then Quantity else 0 end)

)
-
(
SUM(case when header.InvTransTypeId = 'IN1' and header.InvTransAccDate between '2011-04-02' and '2011-08-01' 

then Quantity else 0 end) +
SUM(case when header.InvTransTypeId = 'OU1' and header.InvTransAccDate between '2011-04-02' and '2011-08-01' 

then Quantity else 0 end) 
)


 AS 'Total'

FROM InventoryTransDetail detail 
JOIN InventoryTransHeader header ON detail.InvTransHeaderId = header.InvTransHeaderId
JOIN Material material ON detail.MatId = material.MatId
JOIN CustomsMaterial custom ON material.CustMatId = custom.CustMatId
WHERE Header.InvTransAccDate BETWEEN '2011-01-01' AND '2011-08-01' 
GROUP BY detail.MatId, custom.CusMatName

[manhthaodn]

Thanks! Best wishes to you!