Sql Count of Part_no sh - 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 2008 Forums

Transact-SQL (2008)

Sql Count of Part_no sh

Author

Topic

kond.mohan
Posting Yak Master

213 Posts

Posted - 2014-09-08 : 06:38:09

Hi ,

i am executing below query on Sql server 2008 i was getting below
below output.technically my output is correct but same part no is counting mutipletimes in the application
select count(distinct part_no) countPart_no,
part_no,count(distinct line_no),line_no,
case when datediff(dd,cast(latest_order_date as date),getdate()) between 0 and 30 then '0-30 days'
when datediff(dd,cast(latest_order_date as date),getdate()) between 31 and 60 then '31-60 days'
when datediff(dd,cast(latest_order_date as date),getdate()) between 61 and 90 then '61-90 days'
when datediff(dd,cast(latest_order_date as date),getdate()) > 90 then 'Above 90 days'
when datediff(dd,cast(latest_order_date as date),getdate()) between -30 and 0 then 'Future Within 30 days'
when datediff(dd,cast(latest_order_date as date),getdate()) between -60 and -31 then 'Future Within 60 Days'
when datediff(dd,cast(latest_order_date as date),getdate()) <=61 then 'Future More than 60 '
end "Ageing_bucket"
from bec_pr_line_rep a where a.project_id ='6020' and
a.EQUIPMENT_GROUP_DESC in ('Pipes & Fittings at Battery Proper (Utility) (Indoor)') and
upper(a.state1) = 'RELEASED'and cast(a.Bal_qty as decimal(15,2))>0and a.header_state not in ('Closed')
and a.PART_NO='0102010002'
group by part_no ,LATEST_ORDER_DATE,line_no

1 0102010002 1 20 Above 90 days
1 0102010002 1 11 61-90 days
1 0102010002 1 6 31-60 days

my required output is first column intead of1,1,1 it should show only 1

if anyone know pls explain the logic

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2014-09-08 : 07:49:58

Can U Plz Provide the sample data...........

Veera

kond.mohan
Posting Yak Master

213 Posts

Posted - 2014-09-08 : 08:11:55

Hi Veranjaneyulu,
below mentioned data is for one projectid data
partno lineno latestorderdate projectid
0102010002 11 14-jun-2014 6020
0102010002 20 09-jun-2014 6020
0102010002 6 19-jul-2014 6020

mohan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources