Re: MS SQL problem
opps, should be datediff(hour,'1910-01-01',t1.QuizDate) = (select max(datediff(hour,'1910-01-01',QuizDate)) from ) ( notice hour is used as an example ) . On Dec 4, 2007 12:24 PM, hua waveland <[EMAIL PROTECTED]> wrote: > in MS SQL there is a function DATEDIFF(datepart, startdate, enddate), > which returns an integer value of the time difference betwen two > different datatime values. datepart is one of the following: year, > quarter, month, dayofyear, day, hour, minute, second or millisecond. > > and you can use > t1.QuizDate = (select max(datediff(hour,'1910-01-01',QuizDate)) from ) > > HTH > > Hua > > > On Dec 4, 2007 9:25 AM, [EMAIL PROTECTED] [EMAIL PROTECTED] <[EMAIL > PROTECTED]> wrote: > > i have the following sql: > > > > SELECT MAX(QuizDate) as QuizDate, QuizID, > > QuizPercent = (SELECT QuizPercent FROM test_module as t1 > > WHERE t1.Customer_ID = 21 > > AND t1.QuizID = test_module.QuizID > > AND t1.QuizDate = MAX(test_module.QuizDate) > > > > FROM test_module > > WHERE Customer_ID = 21 > > GROUP BY QuizID > > > > it works fine until the QuizPercent subquery returns more than one record > > when the user has, for example, multiple 100% scores. so i thought of > > isolating and looking for max date and max time since the same scores would > > be done at a different time by doing the following: > > > > SELECT MAX(QuizDate) as QuizDate, QuizID, > > QuizPercent = (SELECT QuizPercent FROM test_module as t1 > > WHERE t1.Customer_ID = 21 > > AND t1.QuizID = test_module.QuizID > > AND t1.QuizDate = MAX(Day(test_module.QuizDate)) > > AND t1.QuizDate = MAX(Time(test_module.QuizDate)) > > > > FROM test_module > > WHERE Customer_ID = 21 > > GROUP BY QuizID > > > > but in SQL there is no > > > > MAX(Day(test_module.QuizDate)) > > AND t1.QuizDate = MAX(Time(test_module.QuizDate)) > > > > and it generates an error. > > > > suggestions? > > > > sebastian > > > > > > > > ~| Get involved in the latest ColdFusion discussions, product development sharing, and articles on the Adobe Labs wiki. http://labs/adobe.com/wiki/index.php/ColdFusion_8 Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:294180 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: MS SQL problem
Could try using HAVING; , ( SELECT QuizPercent FROM test_module as t1 WHERE t1.Customer_ID = 21 AND t1.QuizID = test_module.QuizID HAVING t1.QuizDate = MAX(Day(test_module.QuizDate)) AND t1.QuizDate = MAX(Time(test_module.QuizDate)) QuizPerent > i have the following sql: > > SELECT MAX(QuizDate) as QuizDate, QuizID, > QuizPercent = (SELECT QuizPercent FROM test_module as t1 > WHERE t1.Customer_ID = 21 > AND t1.QuizID = test_module.QuizID > AND t1.QuizDate = MAX(test_module.QuizDate) > > FROM test_module > WHERE Customer_ID = 21 > GROUP BY QuizID > > it works fine until the QuizPercent subquery returns more than one > record when the user has, for example, multiple 100% scores. so i > thought of isolating and looking for max date and max time since the > same scores would be done at a different time by doing the following: > > SELECT MAX(QuizDate) as QuizDate, QuizID, > QuizPercent = (SELECT QuizPercent FROM test_module as t1 > WHERE t1.Customer_ID = 21 > AND t1.QuizID = test_module.QuizID > AND t1.QuizDate = MAX(Day(test_module.QuizDate)) > AND t1.QuizDate = MAX(Time(test_module.QuizDate)) > > FROM test_module > WHERE Customer_ID = 21 > GROUP BY QuizID > > but in SQL there is no > > MAX(Day(test_module.QuizDate)) > AND t1.QuizDate = MAX(Time(test_module.QuizDate)) > > and it generates an error. > > suggestions? > > sebastian > ~| Check out the new features and enhancements in the latest product release - download the "What's New PDF" now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:294163 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: MS SQL problem
in MS SQL there is a function DATEDIFF(datepart, startdate, enddate), which returns an integer value of the time difference betwen two different datatime values. datepart is one of the following: year, quarter, month, dayofyear, day, hour, minute, second or millisecond. and you can use t1.QuizDate = (select max(datediff(hour,'1910-01-01',QuizDate)) from ) HTH Hua On Dec 4, 2007 9:25 AM, [EMAIL PROTECTED] [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > i have the following sql: > > SELECT MAX(QuizDate) as QuizDate, QuizID, > QuizPercent = (SELECT QuizPercent FROM test_module as t1 > WHERE t1.Customer_ID = 21 > AND t1.QuizID = test_module.QuizID > AND t1.QuizDate = MAX(test_module.QuizDate) > > FROM test_module > WHERE Customer_ID = 21 > GROUP BY QuizID > > it works fine until the QuizPercent subquery returns more than one record > when the user has, for example, multiple 100% scores. so i thought of > isolating and looking for max date and max time since the same scores would > be done at a different time by doing the following: > > SELECT MAX(QuizDate) as QuizDate, QuizID, > QuizPercent = (SELECT QuizPercent FROM test_module as t1 > WHERE t1.Customer_ID = 21 > AND t1.QuizID = test_module.QuizID > AND t1.QuizDate = MAX(Day(test_module.QuizDate)) > AND t1.QuizDate = MAX(Time(test_module.QuizDate)) > > FROM test_module > WHERE Customer_ID = 21 > GROUP BY QuizID > > but in SQL there is no > > MAX(Day(test_module.QuizDate)) > AND t1.QuizDate = MAX(Time(test_module.QuizDate)) > > and it generates an error. > > suggestions? > > sebastian > > > > ~| ColdFusion is delivering applications solutions at at top companies around the world in government. Find out how and where now http://www.adobe.com/cfusion/showcase/index.cfm?event=finder&productID=1522&loc=en_us Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:294172 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: MS SQL problem
The following will do what you need, although it is a little long-winded. -- Prepare blank list declare @list varchar(4000) set @list = '' -- Get SOFTWARE list for COMPANY select @list = @list + ',' + SOFTWARE.SOFTWARE_NAME fromSOFTWARE inner join COMPANY_SOFTWARES ON COMPANY_SOFTWARES.SOFTWARE_ID = SOFTWARE.SOFTWARE_ID where COMPANY_SOFTWARES.COMPANY_ID = 10 -- Removes Extraneous , at beginning select @list = substring( @list , 2, len( @list ) ) -- Final Returned Query select COMPANY.COMPANY_ID, COMPANY.COMPANY_name, @list as SOFTWARES fromCOMPANY where COMPANY_ID = 10 The other option is do it through ColdFusion as suggested, which may be a tidier way. Gavin -Original Message- From: Nurettin Omer Hamzaoglu [mailto:[EMAIL PROTECTED] Sent: 03 November 2004 10:29 To: CF-Talk Subject: MS SQL problem Hi all, I've 3 tables COMPANY, COMPANY_SOFTWARES, SOFWARES. COMPANY_SOFTWARES table holds the data of the softwares used in the companies like; ~| Purchase from House of Fusion, a Macromedia Authorized Affiliate and support the CF community. http://www.houseoffusion.com/banners/view.cfm?bannerid=34 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183245 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: MS SQL problem
I don't think it is possible (but I don't use M$SQL that often, so I may be mistaken). If it is for CF, use grouped output: SELECT C.COMPANY_ID, C.COMPANY_NAME, S.SOFTWARE_NAME FROM COMPANY C COMPANY_SOFTWARES COS, SOFTWARES S WHERE S.SOFTWARE_ID = CS.SOFTWARE_ID AND COS.COMPANY_ID = C.COMPANY_ID AND C.COMPANY_ID = 10 ORDER BY C.COMPANY_NAME ID: #qCompanySoftware.COMPANY_ID# Name: #qCompanySoftware.COMPANY_NAME# Softwares: #qCompanySoftware.SOFTWARE_NAME#, If you are filtering on a unique ID, you can also do: ID: #qCompanySoftware.COMPANY_ID# Name: #qCompanySoftware.COMPANY_NAME# Softwares: #ValueList(qCompanySoftware.SOFTWARE_NAME)# Pascal > -Original Message- > From: Nurettin Omer Hamzaoglu [mailto:[EMAIL PROTECTED] > Sent: 03 November 2004 11:29 > To: CF-Talk > Subject: MS SQL problem > > Hi all, > > I've 3 tables COMPANY, COMPANY_SOFTWARES, SOFWARES. COMPANY_SOFTWARES > table holds the data of the softwares used in the companies like; > > I need a result like; > COMPANY_ID COMAPNY_NAME SOFTWARES > -- - > 10 My Company X, Y, Z > > ~| Purchase from House of Fusion, a Macromedia Authorized Affiliate and support the CF community. http://www.houseoffusion.com/banners/view.cfm?bannerid=36 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183243 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
