April 8, 2009 at 7:18 am
I have a table A (CustID,CustCode), tableB(CustCode) and I was asked to query the following which i didnt follow, may need help.
Find top
100 frequently used custcode in a 150 day pre index period.
April 8, 2009 at 8:50 am
There would need to be a bit more information in those tables. You can't really get data based on date without having stored some date information.
Once you have the date information, you need to look at performing an aggregate using the GROUP BY clause and you need to do some date math. I'd try DATEDIFF to start. You can look both those up in the books online.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 8, 2009 at 9:14 am
Ok,i'll put in with some example.
I have tableA with CCode
ID CCode
1 98773
2 68790
3 62098
4 23899
and would like to create a resulting tableB something like this
CustID CCode Flag
dewr4454 62098 1
dewr4454 68790 1
dewr4454 98773 0
dewr4454 23899 0
rdwe8977 98773 1
rdwe8977 68790 0
rdwe8977 62098 1
rdwe8977 23899 1
I have 150 such codes and would like to indicate all of them for each customer??
April 8, 2009 at 9:18 am
I'm sorry, I guess I wasn't clear. I meant that you must have another column of data in the tables that references date. How else will you determine that you need 150 days worth of information?
As to the rest, what have you tried so far that hasn't worked?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 8, 2009 at 9:27 am
150 days worth of information will be calculated later but first i would to put these CCodes in a table with the flags in a proper way. I also have a table will CustId and CCodes, i need to pull from there and put them in a diffrent table with flags.
April 8, 2009 at 9:30 am
I'm not clear on where the Flag data is coming from. You say you want to create the data for tableB, but part of that is the Flag, and I don't see where you're getting that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 8, 2009 at 9:31 am
OK. Although if that information was in the table it would be easier to query.
Regardless. What TSQL have you tried for getting the data together so far?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 8, 2009 at 9:33 am
Tara (4/8/2009)
Ok,i'll put in with some example.I have tableA with CCode
ID CCode
1 98773
2 68790
3 62098
4 23899
and would like to create a resulting tableB something like this
CustID CCode Flag
dewr4454 62098 1
dewr4454 68790 1
dewr4454 98773 0
dewr4454 23899 0
rdwe8977 98773 1
rdwe8977 68790 0
rdwe8977 62098 1
rdwe8977 23899 1
I have 150 such codes and would like to indicate all of them for each customer??
I think you aren't providing us with all the information needed to assist you. I can't see how you get to tableB from tableA.
April 8, 2009 at 10:11 am
I have a table with all Cust Codes and CustId from there i have to create a 3 rd table as above, its not existign yet, i would like to populate the 3rd table as above with flags.
April 8, 2009 at 10:29 am
Have you tried using aggregation yet? I'm pretty sure you'll need to use within a derived table, but if you can get the aggregated values first, you'll be on your way.
Can you post what you've tried that hasn't worked for you?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 8, 2009 at 11:13 am
I havent tried anything so far. I dont know how to aggregate them
April 8, 2009 at 11:32 am
Lynn Pettis (4/8/2009)
Tara (4/8/2009)
Ok,i'll put in with some example.I have tableA with CCode
ID CCode
1 98773
2 68790
3 62098
4 23899
and would like to create a resulting tableB something like this
CustID CCode Flag
dewr4454 62098 1
dewr4454 68790 1
dewr4454 98773 0
dewr4454 23899 0
rdwe8977 98773 1
rdwe8977 68790 0
rdwe8977 62098 1
rdwe8977 23899 1
I have 150 such codes and would like to indicate all of them for each customer??
I think you aren't providing us with all the information needed to assist you. I can't see how you get to tableB from tableA.
I'm still waiting to see how you get from tableA above to tableB above.
April 8, 2009 at 11:48 am
Something like this will help get you going.
SELECT TOP (100)
a.CustCode
,a.COUNT(CustCode) CustCodeCount
FROM TableA a
GROUP BY a.CustCode
ORDER BY CustCodeCount DESC
I didn't use the other table becuase I don't see a need for it here. This takes the top 100 CustCode counts descending and groups by the CustCode. I'm not sure where the Flag comes from though. Is this enough to get you started?
If not, I'd suggest getting a TSQL fundamentals book such as Itzik Ben Gan's SQL Server T-SQL Fundamentals (well named, and it will apply to 2005 very well). I'm only saying that because, at least from what you've described, you're really just getting started with TSQL.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply