Query Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Query Problem

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-10-22 : 08:42:53

Mateen writes "/*
An item is received and issued. Receiving rate of the item is saved in the
table receive_det. When the item is issued then no issuance rate is save in
the issue_det table.

for a particular month say (Nov,2002 issuance rate or avg rate of an item is determined
as follows

total received amount / total received qty where receiving date is less than or equal
to the last date of November 2002.

The followin sql is returning correct result for 1 month.
But how to find issuance expense for a period of months say
from '2002-11-01' to '2003-03-31'

SQL:
*/
select yr=derived_table.yr,
mth,derived_table.mth,
sum(expense)
from
(

SELECT yr= year(issm.issue_date),
mth=month(issm.issue_date),
issd.item_code,
expense = sum(issd.issue_qty)*
( SELECT sum(recd.rate * recd.qty) / sum(recd.qty)
FROM receive_det recd,
receive_mst recm
WHERE ( recm.trans_no = recd.trans_no )
and ( recd.item_code = issd.item_code)
and ( recm.trans_date <= '2002-11-30'))

FROM issue_det issd,
issue_mst issm
WHERE ( issm.trans_no = issd.trans_no )
and ( issm.issue_date between '2002-11-01' and '2002-11-30' )
group by issd.item_code ,
issm.issue_date

) derived_table
group by derived_table.yr,
derived_table.mth

/*
table:receive_det
-----------------
trans_np item_code qty rate rec_date
D0210042 01140098 30.00 495.0000 2002-10-02 00:00:00
D0210042 01140100 120.00 425.6000 2002-10-02 00:00:00
D0210055 07201337 86.66 54.5000 2002-10-03 00:00:00
D0210055 20071025 86.66 54.5000 2002-10-03 00:00:00
D0210055 20071277 86.66 54.5000 2002-10-03 00:00:00
D0210237 07202208 50.00 25.0000 2002-10-31 00:00:00
D0211053 20070661 30.00 34.0000 2002-11-01 00:00:00
D0211053 20071625 25.00 32.0000 2002-11-01 00:00:00
D0211053 20071626 70.00 16.0000 2002-11-01 00:00:00
D0211024 22030223 1.00 275.0000 2002-11-02 00:00:00
D0212017 06210025 1300.00 11.4000 2002-12-01 00:00:00
D0212025 03220373 2.00 300.0000 2002-12-03 00:00:00
D0212025 04110007 2.00 100.0000 2002-12-03 00:00:00
D0212043 07200979 100.00 5.0000 2002-12-08 00:00:00
D0212043 07202846 10.00 55.0000 2002-12-08 00:00:00
D0212043 07202854 10.00 12.5000 2002-12-08 00:00:00
D0212194 09180077 2.00 1500.0000 2002-12-31 00:00:00
D0301010 09180616 1.00 1.0000 2003-01-01 00:00:00
D0301174 20070412 15.00 150.0000 2003-01-22 00:00:00
D0301189 07200793 1.00 1100.0000 2003-01-27 00:00:00
D0301189 07200794 1.00 220.0000 2003-01-27 00:00:00
D0301203 20070283 1000.00 1.2500 2003-01-30 00:00:00
D0301209 09180252 1.00 7500.0000 2003-01-31 00:00:00
D0302026 07200065 2.00 300.0000 2003-02-01 00:00:00
D0303025 07202584 3.00 145.0000 2003-03-01 00:00:00
D0303200 07200418 1.00 140.0000 2003-03-26 00:00:00
D0303200 07200419 1.00 80.0000 2003-03-26 00:00:00
D0303213 07200582 72.00 27.5000 2003-03-31 00:00:00
D0303213 07200588 24.00 2.5000 2003-03-31 00:00:00
D0303213 07200589 36.00 17.9000 2003-03-31 00:00:00
D0303213 07200911 48.00 3.0000 2003-03-31 00:00:00
D0303213 07200912 48.00 6.0000 2003-03-31 00:00:00
D0303213 07201505 6.00 38.0000 2003-03-31 00:00:00
D0303213 07201741 6.00 65.0000 2003-03-31 00:00:00
D0303213 07201857 24.00 17.9000 2003-03-31 00:00:00
D0303213 07230011 6.00 98.0000 2003-03-31 00:00:00
D0303213 09180384 24.00 15.0000 2003-03-31 00:00:00
D0303213 20070895 1.00 575.0000 2003-03-31 00:00:00
D0303213 20071053 3.00 525.0000 2003-03-31 00:00:00

table:issue_det
-----------------
trans_no item_code issue_qty iss_date
02110010 07230011 3.00 2002-11-01 00:00:00
02110009 07230011 3.00 2002-11-04 00:00:00
02110014 07200582 24.00 2002-11-04 00:00:00
02110014 07200912 6.00 2002-11-04 00:00:00
021

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-22 : 12:23:21

Duplicate post:

[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=29946&SearchTerms=mateen[/url]

Tara

Mateen
Starting Member

14 Posts

Posted - 2003-10-23 : 02:07:19

I am sorry: I frogot to remove lines which refers to receive_mst and issue_mst. Please ignore those lines.

So the query after correction will be

select yr=derived_table.yr,
mth,derived_table.mth,
sum(expense)
from
(

SELECT yr= year(issm.issue_date),
mth=month(issm.issue_date),
issd.item_code,
expense = sum(issd.issue_qty)*
( SELECT sum(recd.rate * recd.qty) / sum(recd.qty)
FROM receive_det recd
WHERE ( recd.item_code = issd.item_code)
and ( recm.trans_date <= '2002-11-30'))

FROM issue_det issd
WHERE ( issd.issue_date between '2002-11-01' and '2002-11-30' )
group by issd.item_code ,
issd.issue_date

) derived_table
group by derived_table.yr,
derived_table.mth

Mateen
Starting Member

14 Posts

Posted - 2003-10-23 : 06:04:44

I request ASKSQLTEAM to delete this question as it is duplicated.
I do apologise for duplication.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=29946&SearchTerms=mateen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources