SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 "moving" count of suppliers with cost<>0
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Junior Sqler
Starting Member

18 Posts

Posted - 06/20/2013 :  06:08:56  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 06/20/2013 :  06:18:42  Show Profile  Reply with Quote
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 - 06/20/2013 :  06:47:22  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 06/20/2013 :  06:50:38  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000