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.
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 CountofSuppliersfrom Initial aleft join Initial bon a.departmentcode = b.departmentcode andb.transactiondate>= DATEADD(DAY, -730, a.transactiondate) AND b.transactiondate< a.TransactionDategroup by a.departmentcode,a.TransactionDateorder by a.departmentcode,a.TransactionDateAny ideas? |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-20 : 06:18:42
|
something likeselect a.departmentcode,a.TransactionDate ,count(distinct case when cost <> 0 THEN supplier ELSE NULL END) AS CountOfSuppliersfrom Initial awhere transactiondate>= DATEADD(DAY, DATEDIFF(dd,0,GETDATE())-730, 0)group by a.departmentcode,a.TransactionDateorder by a.departmentcode,a.TransactionDate ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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? |
|
|
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 intervalie transactiondate>= DATEADD(DAY, DATEDIFF(dd,0,GETDATE())-730, 0)------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|