Farms Management System - 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

General SQL Server Forums

New to SQL Server Programming

Farms Management System

Author

Topic

surezh
Starting Member

32 Posts

Posted - 2009-01-13 : 05:30:03

In farms management system, I am having tables such as below

Table - farm
Code FName
----------------------------------
802 SHOLAPUR FARM
813 MANANGATHAN FARM
815 CHENGALPET FARM
816 VIJYAPURAM FARM
817 CHOKKANATHAN PATTI FARM
819 SUBBIA PURAM FARM
820 VENKATARAYAPURAM - A FARM
821 MADURAI RO FARMS (PACL)
822 TIRUNELVELI RO FARM
829 KURIPANKULAM FARM

Table – Head
Code HName
----------------------------------
7301 COW PROJECT
7302 SHEEP PROJECT
7303 PIGGERY PROJECT
7304 POULTRY PROJECT
7305 RABBIT PROJECT
7306 VERMICULTURE PROJECT
7307 APICULTURE PROJECT
7308 AZOLLA PROJECT
7309 FODDER CROPS PROJECT
7310 BUFFALO PROJECT

Table – SubHead
Code SBHName HCode
---------------------------------------------
7301000001 COST OF COW 7301
7301000002 COST OF BULL 7301
7301000003 COST OF FEED 7301

7302000001 COST OF MALE SHEEP 7302
7302000002 COST OF FEMALE SHEEP 7302
7302000003 COST OF BUILDINGS 7302
7302000004 COST OF MEDICINE 7302

7303000001 COST OF FEMALE PIG 7303
7303000002 COST OF MALE PIG 7303
7303000004 COST OF MEDICINE 7303
7303000005 COST OF BUILDING 7303
7303000013 POST-MORTEM EXP. 7303

7304000001 COST OF MALE BIRDS 7304
7304000002 COST OF FEMALE BIRDS 7304
7304000005 COST OF EQUIPMENT 7304
7304000006 COST OF MEDICINE 7304

7305000001 COST OF MALE RABBIT 7305
7305000007 COST OF EQUIPMENT 7305

7306000001 COST UNIT CONSTRUCTION 7306
7306000002 COST OF EARTH WARMS 7306
7306000003 TRANSPORTATION 7306
7306000004 MARKETING EXP. 7306

7307000001 COST OF BEE BOXES 7307
7307000002 COST OF BEES 7307
7307000003 COST OF EQUIPMENTS 7307
7307000004 COST OF PROCESSING 7307

7308000001 COST OF SILPAULIN SHEET 7308
7308000002 COST OF NUTRIENTS 7308

7309000001 LAND PLOUGHING 7309
7309000002 COST OF SEEDS 7309
7309000003 SOWING & ALLIED OPERATION CHARGES 7309

7310000001 COST OF MALE BUFFALO 7310
7310000002 COST FEMALE BUFFALO 7310

Table-Sanction
budgetid fcode headcode subheadcode oct nov dec jan feb mar apr may jun jul aug sep total --------------------------------------------------------------------------------------------------------------------------------------------
8 838 7101 7101000001 .0000 .0000 .0000 .0000 .0000 6000.0000 .0000 .0000 .0000 .0000 .0000 .0000 6000.0000
9 838 7102 7102000007 300.0000 300.0000 300.0000 300.0000 300.0000 300.0000 300.0000 300.0000 300.0000 300.0000 300.0000 300.0000 3600.0000
10 838 7102 7102000002 50.0000 50.0000 50.0000 50.0000 50.0000 50.0000 50.0000 50.0000 50.0000 50.0000 50.0000 50.0000 600.0000
11 838 7104 7104000002 .0000 .0000 .0000 .0000 .0000 .0000 .0000 .0000 650.0000 .0000 .0000 .0000 650.0000
12 838 7105 7105000001 150.0000 150.0000 150.0000 150.0000 150.0000 150.0000 150.0000 150.0000 150.0000 150.0000 150.0000 150.0000 1800.0000
13 838 7106 7106000001 50.0000 50.0000 50.0000 50.0000 50.0000 50.0000 50.0000 50.0000 50.0000 50.0000 50.0000 50.0000 600.0000
14 838 7106 7106000002 50.0000 50.0000 50.0000 50.0000 50.0000 50.0000 50.0000 50.0000 50.0000 50.0000 50.0000 50.0000 600.0000

Table - Expense
[code] [DATE3] [Amount]
--------------------------------------------
802 2007-04-02 00:00:00 15000.0
802 2007-04-02 00:00:00 6357.0
802 2007-04-02 00:00:00 17.0
802 2007-04-04 00:00:00 150.0
802 2007-04-04 00:00:00 468.5
802 2007-04-07 00:00:00 20000.0
802 2007-04-06 00:00:00 100.0
802 2007-04-07 00:00:00 6846.0
802 2007-04-07 00:00:00 7700.0
802 2007-04-07 00:00:00 700.0
802 2007-04-08 00:00:00 1300.0
802 2007-04-08 00:00:00 400.0

Table - Income
invoiceid fcode hcode sbhcode unit qty rate_per_unit gross_amt commision_amt net_amt invoice_date
------------------------------------------------------------------------------------------------------------------------------------------------------
1 817 7902 7902000013 Kgs 35.0 3.0000 105.0000 9.0000 96.0000 2008-10-12 00:00:00.000
2 817 7901 7901000005 Kgs 25.0 5.0000 125.0000 .0000 125.0000 2008-10-12 00:00:00.000
3 817 7903 7903000009 Lts 328.39999389648437 12.5000 4105.0000 .0000 4105.0000 2008-10-12 00:00:00.000
4 817 7902 7902000013 Kgs 25.0 4.0000 100.0000 8.0000 92.0000 2008-10-12 00:00:00.000
5 817 7901 7901000005 Kgs 16.0 5.0000 80.0000 .0000 80.0000 2008-10-12 00:00:00.000
6 817 7901 7901000004 Nos 20.0 5.0000 100.0000 .0000 100.0000 2008-10-12 00:00:00.000
7 817 7902 7902000013 Kgs 28.0 4.5000 126.0000 11.0000 115.0000 2008-10-12 00:00:00.000
8 817 7902 7902000007 Kgs 141.0 2.5000 352.5000 31.5000 321.0000 2008-10-12 00:00:00.000
9 817 7903 7903000001 Nos 1.0 12750.0000 12750.0000 .0000 12750.0000 2008-10-12 00:00:00.000
10 817 7901 7901000005 Kgs 22.0 5.0000 110.0000 .0000 110.0000 2008-10-12 00:00:00.000

The Expected Output Should be like below:

<------------SANCTION----------------------------> <-------EXPENSE--------> <-----------------------INCOME--------------------->

HCode Head Name OCT NOV DEC TOTAL OCT NOV DEC TOTAL OCT NOV DEC JAN FEB MAR TOTAL
802 SHOLAPUR FARM
7312 LADY FINGERS 2300 2500 1000 5800 2300 2400 900 4600 0 2000 3400 4000 3500 3200 16100 2000
7313 PADDY 3000 5000 8000 16000 2800 5000 7200 15000 0 0 0 25000 30000 28000 83000 10000
7314 RICE 0 0 0 0 0 0 0 0 0 0 0 5000 8000 2000 15000 2000
7314 COCUMBER 1000 300 2700 4000 1000 300 2500 3800 0 2000 3000 2000 1000 500 8500 3000
7315 COW PROJECT 10000 12000 8000 30000 10000 10000 7000 27000 0 0 0 0 0 0 0 0
7316 MILK 0 0 0 0 0 0 0 0 3500 2500 6000 3000 2500 3000 20500 7500
TOTAL 16300 19800 19700 55800 16100 17700 17600 50400 3500 6500 12400 39000 45000 36700 143100
817 MANANGATHAN FARM
7311 GROUND NUTS 2300 2500 1000 5800 2300 2400 900 4600 0 2000 3400 4000 3500 3200 16100 2000

Please Help me....

Thanks

surezh
Starting Member

32 Posts

Posted - 2009-01-13 : 05:35:55

The Expected 2nd Quarter

<------------SANCTION----------------------------> <-------EXPENSE--------> <-----------------------INCOME--------------------->

HCode Head Name JAN FEB MAR TOTAL JAN FEB MAR TOTAL JAN FEB MAR APR MAY JUN TOTAL
802 SHOLAPUR FARM
7312 LADY FINGERS 2300 2500 1000 5800 2300 2400 900 4600 0 2000 3400 4000 3500 3200 16100 2000
7313 PADDY 3000 5000 8000 16000 2800 5000 7200 15000 0 0 0 25000 30000 28000 83000 10000
7314 RICE 0 0 0 0 0 0 0 0 0 0 0 5000 8000 2000 15000 2000
7314 COCUMBER 1000 300 2700 4000 1000 300 2500 3800 0 2000 3000 2000 1000 500 8500 3000
7315 COW PROJECT 10000 12000 8000 30000 10000 10000 7000 27000 0 0 0 0 0 0 0 0
7316 MILK 0 0 0 0 0 0 0 0 3500 2500 6000 3000 2500 3000 20500 7500
TOTAL 16300 19800 19700 55800 16100 17700 17600 50400 3500 6500 12400 39000 45000 36700 143100
817 MANANGATHAN FARM
7311 GROUND NUTS 2300 2500 1000 5800 2300 2400 900 4600 0 2000 3400 4000 3500 3200 16100 2000

surezh
Starting Member

32 Posts

Posted - 2009-01-13 : 06:29:05

Please Someone Help Me!!!!!!

vedjha
Posting Yak Master

228 Posts

Posted - 2009-01-13 : 06:34:30

not getting ,
u have defined all the tables..
What is your query????

Ved Prakash Jha

surezh
Starting Member

32 Posts

Posted - 2009-01-13 : 06:52:24

quote:
Originally posted by vedjha

not getting ,
u have defined all the tables..
What is your query????

Ved Prakash Jha

The expected output should be like this

<------------SANCTION----------------------------> <-------EXPENSE--------> <-----------------------INCOME--------------------->

HCode Head Name OCT NOV DEC TOTAL OCT NOV DEC TOTAL OCT NOV DEC JAN FEB MAR TOTAL
802 SHOLAPUR FARM
7312 LADY FINGERS 2300 2500 1000 5800 2300 2400 900 4600 0 2000 3400 4000 3500 3200 16100 2000
7313 PADDY 3000 5000 8000 16000 2800 5000 7200 15000 0 0 0 25000 30000 28000 83000 10000
7314 RICE 0 0 0 0 0 0 0 0 0 0 0 5000 8000 2000 15000 2000
7314 COCUMBER 1000 300 2700 4000 1000 300 2500 3800 0 2000 3000 2000 1000 500 8500 3000
7315 COW PROJECT 10000 12000 8000 30000 10000 10000 7000 27000 0 0 0 0 0 0 0 0
7316 MILK 0 0 0 0 0 0 0 0 3500 2500 6000 3000 2500 3000 20500 7500
TOTAL 16300 19800 19700 55800 16100 17700 17600 50400 3500 6500 12400 39000 45000 36700 143100
817 MANANGATHAN FARM
7311 GROUND NUTS 2300 2500 1000 5800 2300 2400 900 4600 0 2000 3400 4000 3500 3200 16100 2000

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2009-01-13 : 12:37:59

...and other than paste out what looks to be a homework exercise, have you any sample code that somebody can nudge you towards a correct solution??

....or are you expecting us to do your full degree for free?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-13 : 12:45:44

surezh, its just a matter of joining tables together on correct fields and group by HCode Head Name fields and cross tab it based on month value of invoice_date field. just search for cross tab here and you will get lots of examples. Try it out yourself rather than waiting for somebody spoonfeeding you.if you face any problem, just post here and we will help you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources