October 22, 2012 at 3:26 pm
Hi Sean
I do apologize, I did think that I had copied the whole thing, not just the inserts, I certainly intended to post it all. and also thought that I had entered only courses in 2009 for 2115; I must have cut and pasted incorrectly. I'm terribly sorry. I'll delete 2115's exrtra rows as you did and make sure that my query does return no rows for her.
your assumptions were correct and your create table did the job perfectly as did your query. I'll have to study it for a bit to understand exactly what you did and why it has to be done in that way.
how were you able to do joins when foreign keys referring to temporary files aren't allowed; I got errors when I tried to do joins.
I'm gathering from your solution, that a top 1 clause in a nested query doesn't work and it's necessary to assign an incrementing # to each row and then filter on row 1 only; is this the case?
Thank you so much Sean; as I say, I think that you volunteers are very generous and wish that I knew more so I could contribute as well.
regards
kim
October 22, 2012 at 3:34 pm
Hi Ron
yes, quite correct, it's GM.
and your query does work, but it gives me multiple records for each contact and includes courses that are prior to 01 jan 2010. I need only one course and it has to be after 01 jan 2010.
it seems from Sean's solution, that a top 1 just doesn't work in a nested query which is where I started, but I guess I'll just have to give it up and move on with my life; how sad.
thanks for your reply
Kim
October 22, 2012 at 3:39 pm
ksharpe (10/22/2012)
Hi SeanI do apologize, I did think that I had copied the whole thing, not just the inserts, I certainly intended to post it all. and also thought that I had entered only courses in 2009 for 2115; I must have cut and pasted incorrectly. I'm terribly sorry. I'll delete 2115's exrtra rows as you did and make sure that my query does return no rows for her.
your assumptions were correct and your create table did the job perfectly as did your query. I'll have to study it for a bit to understand exactly what you did and why it has to be done in that way.
how were you able to do joins when foreign keys referring to temporary files aren't allowed; I got errors when I tried to do joins.
I'm gathering from your solution, that a top 1 clause in a nested query doesn't work and it's necessary to assign an incrementing # to each row and then filter on row 1 only; is this the case?
Thank you so much Sean; as I say, I think that you volunteers are very generous and wish that I knew more so I could contribute as well.
regards
kim
I am glad that my code helped. There is nothing that says a join has to be made of foreign keys. You can make joins on anything that is either the same datatype or can be implicitly converted to the same datatype.
To try to explain what I did...You said you only wanted the top 1. That is defined by an order so we can use ROW_NUMBER() to assign a position within each group. The partition clause means to restart counting when we change whatever we partition by. The order by portion defines what is "top".
If you run the inner query:
select c.AccountNo, c.ContactName, co.CourseCode, co.StartDate, ROW_NUMBER() over(partition by c.AccountNo order by co.StartDate desc) as RowNm
from #contact c
left join #contact_courses cc on cc.AccountNo = c.AccountNo
left join #Courses co on co.CourseCode = cc.CourseCode and co.StartDate >= @MinDate
You will see all the rows and RowNum will hold the "position" within each set. You can't use windowed functions like ROW_NUMBER in the where clause so I just wrapped this as a subquery so you can use the where clause. This could also be done as a cte.
;with cte as
(
select c.AccountNo, c.ContactName, co.CourseCode, co.StartDate, ROW_NUMBER() over(partition by c.AccountNo order by co.StartDate desc) as RowNm
from #contact c
left join #contact_courses cc on cc.AccountNo = c.AccountNo
left join #Courses co on co.CourseCode = cc.CourseCode and co.StartDate >= @MinDate
)
select * from cte where RowNm = 1
Either way you do this it will interpreted as the same query. There have been many posts of this over the years and the subquery vs cte in this case has always produce identical execution plans. Some people find the cte easier to understand and some prefer the subquery.
wish that I knew more so I could contribute as well.
Just keep hanging around reading the forums. Then start finding the "easy" ones that you can answer. You will be amazed at how quickly you learn from helping other people. Pretty soon you will be answering questions that today would leave you perplexed. This site is a great living example of pay it forward.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 22, 2012 at 4:03 pm
Your system is similar to mine -- you use "course name", I use "product" -- you have "course codes", I have nearly 200 distinct "product codes".
If you could submit a subset of the output rows from my query, I think I can figure out how to eliminate the "repeats". Part of the solution may require using CTE.
October 22, 2012 at 4:05 pm
You are so quick to respond! no waiting, how gratifying.
I'll have to study this, make sure I understand it and absorb it all, as well as add it to my sql how to file - growing all the time.
good to know about not needing a foreign key on a join too.
I know I should know, or @ least be able to take an educated guess, but alas ... what's a cte? confusing technical extra?
I did respond to one question in the GoldMine forum once but don't know if I helped or not as he didn't respond; I even asked a week or so later but no reply.
Henh.
thanks again.
kim
October 22, 2012 at 4:09 pm
thanks Ron, much appreciated; I'm always interested to learn more stuff.
I'll have to attend to that in the morning, I don't know where you're situated, but I'm EST and now an hour past 'going home' time - the most important time of the day - and things await.
I'll get back to you first thing.
btw, maincourses is a view I created to access contact1 for course type records. you probably have something similar for your product records.
thanks
kim
October 22, 2012 at 4:13 pm
what's a cte? confusing technical extra?
NOW THAT IS FUNNY!!!!
A cte is a common table expression. They were introduced in sql 2005. They are basically an inline view.
http://msdn.microsoft.com/en-us/library/ms190766%28v=sql.105%29.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 22, 2012 at 4:49 pm
I am EST too.
By the way, what are you using CONTSUPP.country for? In my system, that field retains the balance owed for a customer's particular product. A customer could have multiple active products -- and in some cases -- more than one product with the same code. You'd love my companies business rules -- maybe not.
In any case, I think you really need to concentrate on getting the query for CONTSUPP "correct" first before you join it to contact1. The only thing I think you are using from contact1 is contact -- the student's name?
October 23, 2012 at 6:39 am
I have a rather absurd sense of humour, to some that is, to others, not so much.
so, you were late out the door last night as well, that's IT for ya.
thanks for the link to the cte page; more stuff for me to learn, actually the best part of my job, well, then the application of same as well.
thanks again Sean for all of your time and help; I definitely learned stuff - now to remember it all.
kim
October 23, 2012 at 6:51 am
Hi Ron
contsupp.country is the course short name field. the registration component of the system underwent an evolution - as do most processes I suppose, but I really wish that they had added those extra four detail fields before the last iteration of registration; anyway one treads that convoluted path to customization as best one can.
I see what you mean about getting the contsupp query working first and then incorporating the student's name.
nonetheless, Sean's solution has given me what I need for now and much to my dismay I have to abandon this pursuit for the time being as I have just been handed a deadline which is impossible to keep and must attend to that rather than this mor fun stuff.
thanks for your help.
kim
October 23, 2012 at 8:12 am
OK, if you do return to continue this discussion, consider the following query -- it will return one and only one account number for each "contact" that has at least one "course name". This can be used as a derived table or a common table expression (CTE). It won't help you specifically get the first or last started course because contsupp does not have a datetime entered type field. contsupp does have a last updated date and time, but that may not be good enough because the smallest timepart is minutes, and it is possible that you could enter two courses within the same minute. As you may find out with GoldMine reporting, the overall structure of the database has a lot to be desired.
SELECT c.ACCOUNTNO AS 'AccountNo',
MIN(c.RECID) AS 'RecID'
FROM CONTSUPP c
WHERE c.CONTACT = 'course name'
GROUP BY c.ACCOUNTNO
October 23, 2012 at 8:18 am
Here is an example of using my previous example with a "derived table":
SELECT *
FROM (
SELECT c.ACCOUNTNO AS 'AccountNo',
MIN(c.RECID) AS 'RecID'
FROM CONTSUPP c
WHERE c.CONTACT = 'course name'
GROUP BY
c.ACCOUNTNO
) t1
JOIN CONTSUPP cs
ON t1.AccountNo = cs.ACCOUNTNO
AND t1.recid = cs.recid
October 23, 2012 at 8:28 am
And for those that like CTE:
USE GM_Main -- use your database name here
GO
WITH t1 (AccountNo, RecID) AS
(
SELECT c.ACCOUNTNO AS 'AccountNo',
MIN(c.RECID) AS 'RecID'
FROM CONTSUPP c
WHERE c.CONTACT = 'course name'
GROUP BY
c.ACCOUNTNO
)
SELECT *
FROM t1
JOIN CONTSUPP cs
ON t1.AccountNo = cs.ACCOUNTNO
AND t1.recid = cs.recid
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply