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
Go to Top of Page

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-04-23 : 05:22:32
Count(Distinct PT.PURCHID) as opernorder


sabinWeb MCP
Go to Top of Page

thiyait
Yak Posting Veteran

70 Posts

Posted - 2014-04-23 : 06:11:34
quote:
Originally posted by stepson

Count(Distinct PT.PURCHID) as opernorder


sabinWeb MCP



Thanks a lot stepson.
Go to Top of Page

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-04-23 : 06:19:06
Glad to help!


sabinWeb MCP
Go to Top of Page
   

- Advertisement -