queries help....confused - 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

queries help....confused

Author

Topic

rgjonathan
Starting Member

2 Posts

Posted - 2010-11-11 : 22:18:02

i have 4 tables:
txShipment
ID IDSALESCONTRACT QTY DATE POSITION
---------------------------------------------------
1 SALES001 10000 11/05/2010 EARLY
2 SALES002 20000 11/06/2010 EARLY
3 SALES003 3000 12/13/2010 MID
4 SALES004 15000 11/17/2010 MID
5 SALES005 9000 12/25/2010 END

txSalesContract
IDSALESCONTRACT IDFABRIC
-------------------------
SALES001 FABRIC001
SALES002 FABRIC002
SALES003 FABRIC001
SALES004 FABRIC003
SALES005 FABRIC003

mstFabric
IDFABRIC MACHINETYPE
----------------------
FABRIC001 A
FABRIC002 A
FABRIC003 B

mstWeft
IDFABRIC IDWEFT NEWEFT
---------------------------
FABRIC001 TC10 10
FABRIC001 PE10 10
FABRIC002 PE35 25
FABRIC003 CB40 35
FABRIC003 TC10 15

*AS YOU CAN SEE, in table txShipment the column date and position are related if the date < 10, then position = EARLY, date < 20 position = MID and so on.... (i know i can use iif function, but ill do that later)

what I am trying to get is

SUMMARY:

MACHINETYPE POSITION DATE SUMOFQTY MACHINEQTY
------------------------------------------------------------------
A EARLY NOVEMBER2010 30000 1300
A MID DECEMBER2010 3000 120
B EARLY NOVEMBER2010 15000 300
B END DECEMBER2010 9000 180

MACHINEQTY is a new column, which is the sum of (qty/sum of neweft)
for example the first row (10000/20)+(20000/25) = 1300

please helpppppp....my head is about to explode....

- Ray -

sreekanth939
Starting Member

12 Posts

Posted - 2010-11-12 : 01:18:28

select MACHINETYPE,POSITIOn,

datename(mm, substring(DATE,4,2)+ '/1/'+substring(DATE,7,4)) + substring(DATE,7,4) as [DATE],

sum(isnull(QTY,0)) as [SUMOFQTY],
(sum(isnull(QTY,0)) /sum(isnull(NEWEFT,0)) ) as [Machine QTY]

from txshipment
inner join txsalescontract on IDSALESCONTRACT=IDSALESCONTRACT
inner join mstfabric on IDFABRIC=IDFABRIC
left join mstweft on IDFABRIC=IDFABRIC
group by MACHINETYPE,POSITIOn,datename(mm, substring(DATE,4,2)+ '/1/'+substring(DATE,7,4)) + substring(DATE,7,4)

then DATE : i believ your date format in the table txshipment(DATE) is dd/mm/yyyy ,
so i think we cant use any date functions to getmonth name directly.

rgjonathan
Starting Member

2 Posts

Posted - 2010-11-12 : 02:17:57

thx for your help, but for some reason, it doesnt give me the right value...by the way my date format is mmddyyyy

- Ray -

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources