More Efficient way to Concat Rows

Hi I have this problem with a query to process data concatenating
Units/Modules.
I have one View called Certificates thats lists Course IDs, Candidate
Names and Units. As each Certificate shows multiple Units achieved by
the Candidate I need to transform this data and combine the Units into
a single value - so I have 1 row per certificate rathan than 1 row per
unit.
I have been using this Query and several UDF's (Unit names, Unit
Grades, Unit Award dates etc) but as the database incresed in size the
query is timing out - Is there a more efficient way to do this ? Also
the function seems to create duplicates so i am using DISTINCT and
thisn seems to slow it down too. Help!
Thanks
hals_left
SELECT DISTINCT CourseID, CandidateID,dbo.GetUnits(CandidateID,
CourseID) AS Units
FROM Certificates
CREATE FUNCTION dbo.GetUnits
(@.CandidateID AS smallint, @.CourseID AS smallint )
RETURNS varchar(1000)
AS
BEGIN
DECLARE @.str AS varchar(1000)
SET @.str = ''
SELECT @.str = @.str + UnitTitle + char(13) + char(10)
FROM Certificates
WHERE CandidateID = @.CandidateID AND CourseID= @.CourseID
RETURN @.str
ENDIf this is just formatting for display then it isn't obvious why you
would need to do it in the database. The simplest and most likely
fastest method is in the client or presentation tier. See:
http://msdn.microsoft.com/library/d...
etstringmethod(recordset)ado.asp
David Portas
SQL Server MVP
--|||I agree but the client application (Word 2K Mailmerge) isnt capable of
this.|||Hello, hals_left
To make sure that the function is not called too many times, use a
query like this:
SELECT CourseID, CandidateID,
dbo.GetUnits(CandidateID, CourseID) AS Units
FROM (
SELECT DISTINCT CourseID, CandidateID,
FROM Certificates
) x
Razvan

More complicated use of aggregates...

Greetings all!
I am stuck with something at the moment, and that added to the cold thats got me in its icy grip is really batting my head :(
Basically I have a table of records:
Record_ID (id) | Employee_ID (pin) | Record_type (msgtype) | record_date | record_time (logtime)
What I am looking for is, for each employee_ID on a specific day, the Record_ID of the earliest record of type '5'. I've only got one date in there at the moment so thats not such a problem. My attempt was:

SELECT combined.ID
FROM combined
GROUP BY combined.pin
HAVING min(logtime);
but that didnt work, complaining that combined.id is not in the group by.

Can anyone provide any suggestions?

Thanks!
~Shiv

EDIT: Forgot to mention, I'm using MS Access...Perhaps something like this (or its variations - I'd say that interesting part is the use of a subquery):select c.employee_id, c.record_id
from combined c
where c.logtime = (select min(c1.logtime)
from combined c1
where c1.employee_id = c.employee_id
)
and c.record_type = 'S'
group by c.employee_id, c.record_id;|||Cool, I'm giving that a go so I'll let you know how it works out. It's gotta go through about just under a million records, so its taking a while!!!
~T

More bugs in SSRS 2k5 ....

Here is another one thats been bothering me for quite some time and I believe for user with regional and language settings to non-US English.

I have my regional settings set to Canadian English. I am using Calender controls to pick up Start, End dates in my report. When I pick a date from calender it automatically converts it behind the scenes to US English I believe. Therefore when I run the report, I get the result set, say from Oct 8 instead of Aug 10. Further, if I select the date as 13 Aug, the following error gets thrown (as a result that the SSRS is not able to identify 13 being any month of course)

"An error occured during local report processing. The value provided for the report parameter 'StartDate' is not valid for its type".

Is there a solution to this problem? The underlying date type is DateTime, so why should it matter which regional setting I use?

Comments anyone?

EDIT

The SSRS controls comes with Calendar property you need to use that, because if you use the NOW SSRS will run your report with current date time in your box, I have used it. But I think you have mistaken TimeZone for DateTime, you can get DateTime in SQL Server while TimeZone is .NET there are work around solutions now and it is implemented in VS2008. Try the thread below for details.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1860968&SiteID=1

|||

I never said I am using TimeZone. Let me try to explain once more. I have my regional settings in Control Panel >> Regional and Language Options>>Regional Options>>Standards and Formats - set to English(Canada) and Location set to Canada, further in the Advanced Tab its also set to English (Canada).

Now back in report, I have defined two report parameters: StartDate and EndDate. Their data type is set to DataTime. Therefore when I preview the report, the parameters display Calenders next to the boxes automatically. I pick a date, say 14 Aug 2007 from this calender. The date is formatted as 14/08/2007 in the text box (Canadian format dd/mm/yyyy). Then I hit the View Report button, and its then that the error gets thrown because for some reason report server/sql server does not accept this date format. Notice that I have not done any thing fancy with the date time parameter, simply out of the box usage.

So, I did this test. I switched the computer settings to English (United States) and wolla...everythings works fine. However thats not desirable as this would require the users to use US-English is all applications such as Word, Excel etc.

The thread you provided the link for does not address my problem unfortunately, it talks about TimeZone in .NET.

I suspect that users with English (UK, AUS, NZ) are also facing same problem. I would highly appreciate if some one can provide a solution to this problem.

|||

Moved to the reporting services forum...

|||? Dint I post in SSRS in first place?

|||

SQL Server can do what you want you just need the correct table design, 2005 also comes with GETUTDATE function you can use. Run a search in the BOL for GETUTCDATE and CAST and CONVERT, there is a guideline for SQL Server DateTime design and how to get datetime in the format you want in the links below.

http://www.karaszi.com/SQLServer/info_datetime.asp

http://sqljunkies.com/HowTo/6676BEAE-1967-402D-9578-9A1C7FD826E5.scuk

|||This is interesting as I have reports that are used by people with a variety of regional settings, probably at least half of them set to dd/mm/yyyy.

Doing it exactly as you describe works correctly for us, UK users set the date in the format that is consistent with their regional settings, US as mm/dd/yyyy, and both return the same results.

I will say that we have seen at odd times that if you pick too fast from the calendar control it doesn't seem to quite register (even though the correct date shows) and I've gotten that error. Don't know if that helps much.

What version number are you at?

|||To add to this discussion, I've noticed that Report Manager and Report Server behave differently when viewing the same report. One suffers from the bug and the other doesn't.

|||

Well, I do not have SQL Server SP2 installed. Not sure if installing that would make a diff and I can't do that without DBA's consent.

D Wall wrote:

This is interesting as I have reports that are used by people with a variety of regional settings, probably at least half of them set to dd/mm/yyyy.

Doing it exactly as you describe works correctly for us, UK users set the date in the format that is consistent with their regional settings, US as mm/dd/yyyy, and both return the same results.

I will say that we have seen at odd times that if you pick too fast from the calendar control it doesn't seem to quite register (even though the correct date shows) and I've gotten that error. Don't know if that helps much.

What version number are you at?

|||

Ok, so may be who ever wrote this Calender control had a grudge against us Canadians. Jus kidding

D Wall wrote:

This is interesting as I have reports that are used by people with a variety of regional settings, probably at least half of them set to dd/mm/yyyy.

Doing it exactly as you describe works correctly for us, UK users set the date in the format that is consistent with their regional settings, US as mm/dd/yyyy, and both return the same results.

I will say that we have seen at odd times that if you pick too fast from the calendar control it doesn't seem to quite register (even though the correct date shows) and I've gotten that error. Don't know if that helps much.

What version number are you at?

sql

More bugs in SSRS 2k5 ....

Here is another one thats been bothering me for quite some time and I believe for user with regional and language settings to non-US English.

I have my regional settings set to Canadian English. I am using Calender controls to pick up Start, End dates in my report. When I pick a date from calender it automatically converts it behind the scenes to US English I believe. Therefore when I run the report, I get the result set, say from Oct 8 instead of Aug 10. Further, if I select the date as 13 Aug, the following error gets thrown (as a result that the SSRS is not able to identify 13 being any month of course)

"An error occured during local report processing. The value provided for the report parameter 'StartDate' is not valid for its type".

Is there a solution to this problem? The underlying date type is DateTime, so why should it matter which regional setting I use?

Comments anyone?

EDIT

The SSRS controls comes with Calendar property you need to use that, because if you use the NOW SSRS will run your report with current date time in your box, I have used it. But I think you have mistaken TimeZone for DateTime, you can get DateTime in SQL Server while TimeZone is .NET there are work around solutions now and it is implemented in VS2008. Try the thread below for details.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1860968&SiteID=1

|||

I never said I am using TimeZone. Let me try to explain once more. I have my regional settings in Control Panel >> Regional and Language Options>>Regional Options>>Standards and Formats - set to English(Canada) and Location set to Canada, further in the Advanced Tab its also set to English (Canada).

Now back in report, I have defined two report parameters: StartDate and EndDate. Their data type is set to DataTime. Therefore when I preview the report, the parameters display Calenders next to the boxes automatically. I pick a date, say 14 Aug 2007 from this calender. The date is formatted as 14/08/2007 in the text box (Canadian format dd/mm/yyyy). Then I hit the View Report button, and its then that the error gets thrown because for some reason report server/sql server does not accept this date format. Notice that I have not done any thing fancy with the date time parameter, simply out of the box usage.

So, I did this test. I switched the computer settings to English (United States) and wolla...everythings works fine. However thats not desirable as this would require the users to use US-English is all applications such as Word, Excel etc.

The thread you provided the link for does not address my problem unfortunately, it talks about TimeZone in .NET.

I suspect that users with English (UK, AUS, NZ) are also facing same problem. I would highly appreciate if some one can provide a solution to this problem.

|||

Moved to the reporting services forum...

|||? Dint I post in SSRS in first place?|||

SQL Server can do what you want you just need the correct table design, 2005 also comes with GETUTDATE function you can use. Run a search in the BOL for GETUTCDATE and CAST and CONVERT, there is a guideline for SQL Server DateTime design and how to get datetime in the format you want in the links below.

http://www.karaszi.com/SQLServer/info_datetime.asp

http://sqljunkies.com/HowTo/6676BEAE-1967-402D-9578-9A1C7FD826E5.scuk

|||This is interesting as I have reports that are used by people with a variety of regional settings, probably at least half of them set to dd/mm/yyyy.

Doing it exactly as you describe works correctly for us, UK users set the date in the format that is consistent with their regional settings, US as mm/dd/yyyy, and both return the same results.

I will say that we have seen at odd times that if you pick too fast from the calendar control it doesn't seem to quite register (even though the correct date shows) and I've gotten that error. Don't know if that helps much.

What version number are you at?

|||To add to this discussion, I've noticed that Report Manager and Report Server behave differently when viewing the same report. One suffers from the bug and the other doesn't.|||

Well, I do not have SQL Server SP2 installed. Not sure if installing that would make a diff and I can't do that without DBA's consent.

D Wall wrote:

This is interesting as I have reports that are used by people with a variety of regional settings, probably at least half of them set to dd/mm/yyyy.

Doing it exactly as you describe works correctly for us, UK users set the date in the format that is consistent with their regional settings, US as mm/dd/yyyy, and both return the same results.

I will say that we have seen at odd times that if you pick too fast from the calendar control it doesn't seem to quite register (even though the correct date shows) and I've gotten that error. Don't know if that helps much.

What version number are you at?

|||

Ok, so may be who ever wrote this Calender control had a grudge against us Canadians. Jus kidding

D Wall wrote:

This is interesting as I have reports that are used by people with a variety of regional settings, probably at least half of them set to dd/mm/yyyy.

Doing it exactly as you describe works correctly for us, UK users set the date in the format that is consistent with their regional settings, US as mm/dd/yyyy, and both return the same results.

I will say that we have seen at odd times that if you pick too fast from the calendar control it doesn't seem to quite register (even though the correct date shows) and I've gotten that error. Don't know if that helps much.

What version number are you at?