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 |
|
dreemweaver
Starting Member
7 Posts |
 Posted - 2007-11-15 : 20:53:04
|
| Hi, I am trying to do what seems like a very complex statement and need some help... here is the senario:I need to display sales data from the past 7 days based on if that company (sorted by an account number) HAS NOT ordered that product before in the past year example.=================================| account | product | sale date |=================================| 1 | A | 07-25-12 |---------------------------------| 1 | B | 07-01-01 |---------------------------------| 1 | A | 07-01-01 |---------------------------------| 2 | A | 07-23-12 |=================================the above table would result in only 2 results (rows 2 and 4) as row 1 does appear in the last 7 days BUT row 3 is the same account and product and appears in the year before the last 7 days.all data is store in a single table. and a company may have ordered multiple products anytime during the year.if you need more info please ask, this is an urgent request as I need to finish it by mid-day (GMT) on friday... Thanks in advance. |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-11-15 : 21:11:34
|
Every request is urgent, and we are volunteers.What does your query look like?
Poor planning on your part does not constitute an emergency on my part. |
 |
|
|
dreemweaver
Starting Member
7 Posts |
Posted - 2007-11-15 : 22:02:59
|
| Hi, thanks... tho that is part of the problem, I am not even sure how to start this query... I would normally pull 2 datasets and programatically loop through the results and build a new table... but I am learning and I believe SQL can do the work for me... I am just not certain how. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-11-15 : 22:11:18
|
First can you explain what is the format of the date '07-25-12' ? Is it YY-DD-MM ? And how does "row 1 does appear in the last 7 days" ?
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-11-15 : 22:16:20
|
quote:
Originally posted by dreemweaver
Hi, thanks... tho that is part of the problem, I am not even sure how to start this query... I would normally pull 2 datasets and programatically loop through the results and build a new table... but I am learning and I believe SQL can do the work for me... I am just not certain how.
Normally in SQL, this is bad practice. You can do that in at least 4 or 5 better ways that looping through anything.SELECT [Columns,] INTO [NEWABLE]FROM [Table1] INNER JOIN [TABLE2] on [TABLE1].[LinkCOlumn]= [TABLE2].[LinkColumn]
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
dreemweaver
Starting Member
7 Posts |
Posted - 2007-11-16 : 04:35:07
|
| Thank dataguru... it is the looping senario I am wanting to avoid... I was hoping that it could be acomplished using an IF statement or something similar within the SQL query itself... I am only an intermediate tho when it comes to SQL... maybe this would help:SELECT * FROM sales WHERE date BETWEEN (last 7 days) and (IF [same company has NOT ordered this product in the last year])and the data in the table I put in my initial post was just dummy data, sorry if it confused anyone... the date is stored as a varchar (not my doing) but is converted to timestamp when needed. |
|
|
|
evilDBA
Posting Yak Master
155 Posts |
Posted - 2007-11-16 : 05:25:05
|
| It is not SQL, just a pseudocodeselect from Sales ...condition... and not exists(select * from Sales B where B.Date in last year and B.CompanyId=Sales.CompanyIdand B.ProductId=Sales.ProductId) |
|
|
|
|
|
|
|
|
Urgent help needed... plz...