Monthwise PO - 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 2012 Forums

Transact-SQL (2012)

Monthwise PO

Author

Topic

thiyait
Yak Posting Veteran

70 Posts

Posted - 2014-04-23 : 05:11:40

Hi everyone.

i have Purchase table and purcphase line table and the created date time is in purchase line table.
PurchTable table:
PONO DareaID
------ --------
P0001 FPC
P0002 FPC

Purchline table:
POno createddate DareaID
----------------------------
P001 25-11-2014 FPC
P001 25-11-2014 FPC
p002 28-12-2014 FPC

Want to see the data like below for given period and Number of orders should be on distince po number/purchase table

Month Number of PO's
----- ------------
10 32
11 82
12 12

can someone helpme with the query.

Thanks in advance

Thiya

thiyait
Yak Posting Veteran

70 Posts

Posted - 2014-04-23 : 05:19:22

i tried with below query but no luck

Select month(PL.CREATEDDATETIME) as Months,count(PT.PURCHID) as opernorder from purchtable PT left outer join PURCHLINE PL on PT.PURCHID = PL.PURCHID and PL.DATAAREAID = PT.DATAAREAID and PT.PURCHSTATUS = '3'
where PL.DATAAREAID = 'FPC' and (PL.CREATEDDATETIME >= '2010-10-31' and PL.CREATEDDATETIME <= '2010-12-31') group by month(PL.CREATEDDATETIME)

it gives number of records based on purchline but should be purchtable level(purchline will have po more than once.)

Thanks and regards,
Thiya

Thiya