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)
 "moving" count of suppliers with cost<>0

Author  Topic 

Junior Sqler
Starting Member

18 Posts

Posted - 2013-06-20 : 06:08:56
hello!

I have a table Initial that includes costs for 3 years:2010-2012.
Columns are following:
1.Department (e.g A), 2.Transactiondate (eg 2012-03-01) , 3.Supplier 4.Cost ( e.g 5$)

For each day i would like to calculate the number of Suppliers per department who have Cost <>0 based on data from last 2 years (730 days). In case some supplier has more than 1 times cost <>0 his Name should be counted only once.

I did the following but it's not running:

select a.departmentcode,a.TransactionDate
,(count distinct(b.departmentcode, b.supplier) FROM INVOICE12 where cost<>0) as CountofSuppliers
from Initial a
left join Initial b
on a.departmentcode = b.departmentcode and
b.transactiondate>= DATEADD(DAY, -730, a.transactiondate) AND b.transactiondate< a.TransactionDate
group by a.departmentcode,a.TransactionDate
order by a.departmentcode,a.TransactionDate


Any ideas?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-20 : 06:18:42
something like

select a.departmentcode
,a.TransactionDate
,count(distinct case when cost <> 0 THEN supplier ELSE NULL END) AS CountOfSuppliers
from Initial a
where transactiondate>= DATEADD(DAY, DATEDIFF(dd,0,GETDATE())-730, 0)
group by a.departmentcode,a.TransactionDate
order by a.departmentcode,a.TransactionDate


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Junior Sqler
Starting Member

18 Posts

Posted - 2013-06-20 : 06:47:22
xm..it's partially working..
my initial table had 165 different transactiondates but these query shows only 89..
What could be the problem?
Should i do a left join?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-20 : 06:50:38
quote:
Originally posted by Junior Sqler

xm..it's partially working..
my initial table had 165 different transactiondates but these query shows only 89..
What could be the problem?
Should i do a left join?


it may not be a problem but it might be that there're only 89 dates which falls within your desired interval

ie transactiondate>= DATEADD(DAY, DATEDIFF(dd,0,GETDATE())-730, 0)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -