Author |
Topic |
steve_joecool
Starting Member
21 Posts |
Posted - 2014-07-02 : 17:05:52
|
Hello, I am having a little trouble trying to figure this one out. My query is this SELECT DISTINCT fcompany,SUM(fnetprice) As "TotalPrice",MAX(CONVERT(DATE,fduedate)) AS "LastPurchase"FROM SalesHistoryGroup by fcompanyI would like to filter out of the results 2 things.. #1 I would like ONLY the results where "TotalPrice" > 1000 #2 I would like "LastPurchase" only to be the system sate -365 days (I know I can achieve that like This: CONVERT(DATE, fduedate) > dateadd(day,-365,getdate())But neither of the 2 conditions I can add in a WHERE / AND clauses.Is there any other way I can achieve what I'm looking for? |
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-07-02 : 17:08:40
|
SELECT DISTINCT fcompany,SUM(fnetprice) As "TotalPrice",MAX(CONVERT(DATE,fduedate)) AS "LastPurchase"FROM SalesHistorywhere fduedate>getdate()-365Group by fcompanyHAVING SUM(fnetprice) > 1000Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
steve_joecool
Starting Member
21 Posts |
Posted - 2014-07-02 : 17:11:33
|
Thank you :-) |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2014-07-02 : 17:20:26
|
Why both DISTINCT and GROUP BY? Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-07-02 : 17:21:12
|
quote: Originally posted by SwePeso Why both DISTINCT and GROUP BY?
I usually catch those and remove the DISTINCT. Missed it this time!Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-07-02 : 17:21:21
|
SELECT fcompany,SUM(fnetprice) As "TotalPrice",MAX(CONVERT(DATE,fduedate)) AS "LastPurchase"FROM SalesHistorywhere fduedate>getdate()-365Group by fcompanyHAVING SUM(fnetprice) > 1000Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
steve_joecool
Starting Member
21 Posts |
Posted - 2014-07-02 : 19:03:42
|
Question:Seems like this query is filtering all the INDIVIDUAL records where fduedate>getdate()-365 and HAVING SUM(fnetprice) > 1000 But! I really would like to just not show that fcompany in the summary. Example: What I did, I took the summaries (SUM, Group by, MAX) and took a sample of the data fcompany custno fprice date TCI POWDER COATING 50565 532.6 8/1/2013TCI POWDER COATING 50565 532.6 8/1/2013TCI POWDER COATING 50565 406.3 7/17/2013TCI POWDER COATING 50565 406.3 7/17/2013TCI POWDER COATING 50565 14 7/11/2013TCI POWDER COATING 50565 532.6 7/9/2013TCI POWDER COATING 50565 325.04 7/2/2013TCI POWDER COATING 50565 325.04 7/2/2013and it's filterin out the records in red... I would like for that company not show at all in the summary (when I insert again the group by, etc)Does that make sense? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
steve_joecool
Starting Member
21 Posts |
Posted - 2014-07-03 : 10:45:50
|
OK, let me rephrase my question... The ultimate goal is to only show accounts that don't have sales history in over one year and have bought more than $1000. When I run this, the query is only taking out the individual records in the past year, but it's still showing the account in the summarySo, in the small data sample I took, it's Showing that TCI POWDER COATING has bought only $325.04 x 2 (the last 2 rows, because the fduedate>getdate()-365 condition holds true) but in reality, all I want is to discard that account from my report altogether because it does have sales history in the past year. Does it make sense now? |
|
|
steve_joecool
Starting Member
21 Posts |
Posted - 2014-07-03 : 10:48:01
|
I was thinking a subquery might work here? I'm having trouble finding a logic for this :-( |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-07-03 : 12:03:27
|
http://www.sqlservercentral.com/articles/Best+Practices/61537/Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|