| Author |
Topic |
|
pdx_2188
Starting Member
12 Posts |
 Posted - 2011-03-01 : 16:19:02
|
| Hi,I wouldn't say I'm brand new to SQL; however, I'm no expert at the same time. On that note. I am a data analyst for a company, and the bulk of my job is running Point of Sale reports. I was asked earlier this week if it would be possible to create something that let me know which customers have not reported sales for a particular month. For instance January 2011AMD 32INTELSUN 40HPI'd like the results in my query to return Intel, AMDI tried to use a count function:SELECT [Company],Count(Company) Missing_Company,[DateSale] FROM [database].[dbo].[table]Where DateSale not between '1/1/2011' and '1/31/2011' Group by Company, DateSaleOrder by Company ASCI know that this is going to return results for all company's that have reported in the past up until then, so this includes those company's I'm trying to exclude. Am I totally off? I feel like this should be a pretty "simple" query which is more frustrating. Thanks,Jeff |
|
|
ajthepoolman
Constraint Violating Yak Guru
384 Posts |
Posted - 2011-03-02 : 11:15:45
|
| Is there more than one table involved here? A table of companies and a table of sales? select Companyfrom [database].[dbo].[table] Sleft outer join Sales T on T.CompanyID= S.CompanyIDwhere T.CompanyID is nullIf so, this might work.Hey, it compiles. |
 |
|
|
pdx_2188
Starting Member
12 Posts |
Posted - 2011-03-02 : 13:33:29
|
| But the issue, and correct me if I'm wrong, is that for a certain company (lets say AMD) a month that has not yet been processed will not show up as null since it has not actually been loaded into the database for a select month. I'm trying to figure out how to make it display companies that I have used in the past but are not currently in the system for the current month. Does that make senseEX> Jan Feb AMD 34 The result should display company = 'AMD' because it has no input, but is not null because that would refer to it already being loaded in. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
mandm
Posting Yak Master
120 Posts |
Posted - 2011-03-02 : 14:36:59
|
| What the others are trying to tell you is that you need more than one table to make this work. 1st you need a table of all of the companies. 2nd you need related table of sales by company by date. Then you can left join companies to sales for the date range you're interested in. The ones that come back with null sales are the ones you're looking for. Do you have this type of structure available? |
|
|
|
pdx_2188
Starting Member
12 Posts |
Posted - 2011-03-02 : 15:35:22
|
| ugh yes for some reason I was just so narrow minded on using my one table that I wasn't even listening to what they were saying. I will give that a shot and provide some feedback thank you! |
|
|
|
pdx_2188
Starting Member
12 Posts |
Posted - 2011-03-02 : 16:10:10
|
| I had to create a temp table adding company name and the Sale Dates associated with January. I merged the following tables and the output was blanks. Any suggestions from here? SELECT a.[Company], a.DateSale FROM [DB].[dbo].[a_Input] a right outer join [DB].[dbo].[a_MissingDate] b on a.company = b.companyWhere B.DateSale is null |
|
|
|
mandm
Posting Yak Master
120 Posts |
Posted - 2011-03-03 : 10:32:54
|
| If your a_input table is your list of companies then it needs to be a left join. This would be much easier if you would supply table definitions and some sample data. |
|
|
|
pdx_2188
Starting Member
12 Posts |
Posted - 2011-03-03 : 11:35:57
|
| Thank you, I really appreciate the help all of you have given me!! |
|
|
|
|
Query designed to show you what is not present