Clustered DB Server: value

Showing posts with label value. Show all posts

Thursday, March 22, 2012

Collation and views

Suppose you have your databases's collation as X but the value of collation
on the varchar fields of some of your tables as Y.
Suppose you create a view like this:
SELECT 'CostantString1', 'CostantString2', Field1, Field2 FROM
Table_A
UNIONA ALL
SELECT VarcharField1, VarcharField2, Field3, Field4 FROM
Table_B
You've got an error of incompatble collation on the first two columns of the
view. I think because on the constant string values the db assign the
collation X while the corresponding varchar fields of Table_B have
collation Y.
Is there any solution to this problem?
Thank you all
Andreayes, there is: use COLLATE clause in the select statement.
dean
"Andrea Temporin" <NOSPAM_temporin@.encopro.it> wrote in message
news:%232K2hKyGFHA.3108@.tk2msftngp13.phx.gbl...
> Suppose you have your databases's collation as X but the value of
collation
> on the varchar fields of some of your tables as Y.
> Suppose you create a view like this:
> SELECT 'CostantString1', 'CostantString2', Field1, Field2 FROM
> Table_A
> UNIONA ALL
> SELECT VarcharField1, VarcharField2, Field3, Field4 FROM
> Table_B
> You've got an error of incompatble collation on the first two columns of
the
> view. I think because on the constant string values the db assign the
> collation X while the corresponding varchar fields of Table_B have
> collation Y.
> Is there any solution to this problem?
> Thank you all
> Andrea
>

Monday, March 19, 2012

Collapsing Three Rows Into One with T-SQL Challange?

Hello,

I am wondering if someone has any good ideas how I could concatenate values in column 7:30- 9:50 so that I would have one row and a value: MTTHFW. Or even if it is possible having this in logical days of a week order : MTWTHF.
Thanks a lot for any help!
Building Time Room # 7:30- 9:50
Engeneering 7:30:00 AM - 9:50:00 AM 201 MTTH
Engeneering 7:30:00 AM - 9:50:00 AM 201 F
Engeneering 7:30:00 AM - 9:50:00 AM 201 Wahhh the old Database Systems 101 "course/room/schedule" problem

You will get nowhere until you normalize your tables!!!

Course(ID,CourseName)
Room(ID, Room)
Day(No, Name, Abbrv)
TimeSlot(ID, DayNo, StartTime, Length) -Different Days May have different Time Allotments
ScheduledCourse(CourseID, RoomID, TimeSlotID)

=======================================
Sample Data
=======================================
Course
ID CourseName
1 Engineering
2 Biology
3 Calculus

Room
ID Room
1 101
2 102
3 201
4 202

Day
No Name Abbrv
1 Monday M
2 Tuesday T
3 Wednesday W
4 Thursday Th
5 Friday F

TimeSlot
ID DayNo StartTime Length NOTE: StateTime and Length are DateTimes!!
1 1 7:30 2:20
2 2 7:30 2:20
3 3 7:30 2:20
4 4 7:30 2:20
5 5 7:30 2:20
6 1 7:30 2:20
7 2 10:10 2:20
8 3 10:10 2:20
9 4 10:10 2:20
10 5 10:10 2:20

ScheduledCourse
CourseID RoomID TimeSlot
1 3 1
1 3 2
1 3 3
1 3 4
1 3 5
=======================================
Try it out . . .
=======================================
create table Course(ID int identity primary key,CourseName sysname)
create table Room(ID int identity primary key, Room sysname)
create table ClassDay(Number int , DayName sysname primary key, Abbrv sysname)
create table TimeSlot(ID int identity primary key, DayNo int, StartTime dateTime, Length dateTime)
create table ScheduledCourse(CourseID int, RoomID int, TimeSlotID int, primary key(CourseID,RoomID, TimeSlotID ))
insert into Course (CourseName) values('Engineering')
insert into Course (CourseName) values('Biology')
insert into Course (CourseName) values('Calculus')
insert into Room (Room) values('101')
insert into Room (Room) values('102')
insert into Room (Room) values('201')
insert into Room (Room) values('202')
insert into ClassDay values(1, 'Monday','M')
insert into ClassDay values(2, 'Tuesday','T')
insert into ClassDay values(3, 'Wednesday','W')
insert into ClassDay values(4, 'Thursday','Th')
insert into ClassDay values(5, 'Friday','F')
insert into TimeSlot (DayNo, StartTime, Length ) values(1, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(2, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(3, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(4, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(5, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(1, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(2, '10:10', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(3, '10:10', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(4, '10:10', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(5, '10:10', '2:20')
insert into ScheduledCourse values(1, 3, 1)
insert into ScheduledCourse values(1, 3, 2)
insert into ScheduledCourse values(1, 3, 3)
insert into ScheduledCourse values(1, 3, 4)
insert into ScheduledCourse values(1, 3, 5)
insert into ScheduledCourse values(2, 1, 1)
insert into ScheduledCourse values(2, 2, 2)
insert into ScheduledCourse values(3, 2, 3)
insert into ScheduledCourse values(3, 1, 4)
=======================================
Now try this query:
=======================================
SELECT c.CourseName, r.Room, t.StartTime, t.Length, d.Abbrv
FROM ScheduledCourse s INNER JOIN Course c ON
s.CourseID = c.ID INNER JOIN Room r ON
s.RoomID = r.ID INNER JOIN TimeSlot t ON
s.TimeSlotID = t.ID INNER JOIN ClassDay d ON
t.DayNo = d.Number
ORDER BY d.Number, t.StartTime, c.CourseName
=======================================
Yields this:
=======================================
Biology 101 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 M
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 M
Biology 102 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 T
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 T
Calculus 102 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 W
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 W
Calculus 101 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 Th
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 Th
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 F
=======================================
almost there.... create this function
=======================================
create function DaysOfWeek(@.courseId int, @.roomId int) returns sysname
as begin
declare @.abbrv sysname
declare @.dayno int
declare @.temp sysname
declare curs cursor for
select distinct abbrv, number from classday where number in
(SELECT dayno
FROM ScheduledCourse INNER JOIN TimeSlot
ON ScheduledCourse.TimeSlotID = TimeSlot.ID
inner join ClassDay on TimeSlot.DayNo = ClassDay.Number
where ScheduledCourse.courseId = @.courseId and
ScheduledCourse.RoomId = @.RoomId)
order by number
open curs
fetch next from curs into @.abbrv, @.dayno
while @.@.fetch_status = 0
begin
fetch next from curs into @.temp, @.dayno
if @.@.fetch_status = 0
set @.abbrv = @.abbrv+@.temp
end
close curs
deallocate curs
return @.abbrv
end
=======================================
almost there. . .
change the previous query to include the function. . .
=======================================
SELECT distinct c.CourseName, r.Room, t.StartTime, t.StartTime+ t.Length, dbo.DaysOfWeek(s.courseId, s.roomId )
FROM ScheduledCourse s INNER JOIN Course c ON
s.CourseID = c.ID INNER JOIN Room r ON
s.RoomID = r.ID INNER JOIN TimeSlot t ON
s.TimeSlotID = t.ID INNER JOIN ClassDay d ON
t.DayNo = d.Number
=======================================
Yeilds this. . .
=======================================
Biology 101 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 M
Biology 102 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 T
Calculus 101 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 Th
Calculus 102 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 W
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 MTWThF
=======================================
Almost there. . . can you feel it? hold on!!! we need to format the times!!!!
=======================================
SELECT distinct c.CourseName, r.Room, cast(DatePart(hh, t.StartTime) as sysname) +':'+ cast(DatePart(mi, t.StartTime) as sysname) + ' - ' +
cast(DatePart(hh, t.StartTime+ t.Length) as sysname) +':'+
cast(DatePart(mi,t.StartTime+ t.Length) as sysname) ,
dbo.DaysOfWeek(s.courseId, s.roomId )
FROM ScheduledCourse s INNER JOIN Course c ON
s.CourseID = c.ID INNER JOIN Room r ON
s.RoomID = r.ID INNER JOIN TimeSlot t ON
s.TimeSlotID = t.ID INNER JOIN ClassDay d ON
t.DayNo = d.Number
=======================================
Yields. . .
=======================================
Biology 101 7:30 - 9:50 M
Biology 102 7:30 - 9:50 T
Calculus 101 7:30 - 9:50 Th
Calculus 102 7:30 - 9:50 W
Engineering 201 7:30 - 9:50 MTWThF
=======================================

BOO-YAH!
|||Thank you very much Allen. It looks like a great design but unfortunately I don't dba right to change schema of a table I pulling my data from. The table schema looks this:

ID, CSM_ID, CSM_FAC_ID_NAME, CSM_START_TIME, CSM_END_TIME, CSM_BLDG, CSM_ROOM, CSM_CAPACITY, CSM_MON, CSM_TUE, CSM_WED, CSM_THU, CSM_FRI, CSM_SAT, CSM_SUN, CSM_COURSE_ID, CSM_COURSE_SEC_MEETING_ID, CSM_TECH, CSM_TERM, TimeRuleID, ViolatesRules, FacultyConflict, IsFromImport, ModifiedBy, IsDeleted, CoursePlannerID, IsArranged, IsTBA

CSM_MON, CSM_TUE, CSM_WED, CSM_THU, CSM_FRI, CSM_SAT, CSM_SUN include Y if there is a class. Based on this I have a query that figures out if there is a class or not in the paricular time slot:
--
SELECT IDENTITY(int, 1,1) AS Custom_ID, A.* INTO #EarlyMorning FROM (
SELECT DISTINCT
'Bannan for 05FQ' AS Building,
'7:30-9:50' AS Time,
CSM_ROOM AS [Room Number],
[Tech] = CASE ISNULL(CSM_TECH, '')
WHEN '' THEN ''
ELSE 'X' END,
CSM_CAPACITY AS [Capacity],
[7:30-9:50] = CONVERT( VARCHAR (25), CASE ISNULL(CSM_MON, '') WHEN '' THEN '' ELSE 'M' END
+ CASE ISNULL(CSM_TUE, '') WHEN '' THEN '' ELSE 'T' END
+ CASE ISNULL(CSM_WED, '') WHEN '' THEN '' ELSE 'W' END
+ CASE ISNULL(CSM_THU, '') WHEN '' THEN '' ELSE 'TH' END
+ CASE ISNULL(CSM_FRI, '') WHEN '' THEN '' ELSE 'F' END
+ CASE ISNULL(CSM_SAT, '') WHEN '' THEN '' ELSE 'SA' END
+ CASE ISNULL(CSM_SUN, '') WHEN '' THEN '' ELSE 'SU' END) FROM
COURSESCH_MEET
WHERE CSM_BLDG = 'ENG'
AND CSM_TERM = '05FQ'
AND CAST(CSM_START_TIME AS datetime) BETWEEN '7:30:00 AM' AND '9:50:00 AM'
AND CAST(CSM_END_TIME AS datetime)BETWEEN '7:30:00 AM' AND '9:50:00 AM'
) AS A
ORDER BY A.[Room Number]

Since I may have several different classes for the particular time slot, I can get multiple rows. Looking at the example, instead of three rows, I would like to have one row that would conatenate values from [7:30- 9:50] column into one string. So I would have one row from Room#202 and a string MTTHFW. Do you think I could accamplish here?

Building Time Room # 7:30- 9:50
Engeneering 7:30:00 AM - 9:50:00 AM 201 MTTH
Engeneering 7:30:00 AM - 9:50:00 AM 201 F
Engeneering 7:30:00 AM - 9:50:00 AM 201 W|||

donni100 wrote:
Do you think I could accamplish here?

I don't think so . . . at least not in T-SQL without having rights to create a stored procedure / function.

Someone needs to grab the dba and have him redesign the database as the table is not in third normal form.

If a database isn't in (at least) third normal form, it makes doing things via sql extremely difficult.|||

Give this a shot.

--First dump the result set into the first temp table (#temp1)

CREATE TABLE #temp3 --Final result table
(Building varchar(30),
Time varchar(40),
[Room #] int,
[Day of week] varchar(10))

SET NOCOUNT on --don't want the row affected count displaying.
declare @.DayOfWeek varchar(10), @.Room varchar(20), @.Time varchar(40)
--Now we create the cursor get the distinct times and room numbers and well flip --threw them.

DECLARE cur_room_time CURSOR FOR
SELECT DISTINCT Room, Time
FROM #temp1

OPEN cur_room_time
FETCH NEXT FROM cur_room_time
INTO @.Room, @.Time

WHILE @.@.FETCH_STATUS = 0
BEGIN
--Creating the temp table that we will eveluate the day of the week
Select *
into #temp2
from #temp1
where Room = @.Room
and Time = @.Time

set @.DayOfWeek = ''

--Checking for Monday (M)
If exists(Select * from #temp2 where charindex('M', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek +'M'
end
--Checking for Tuesday (T) and insuring that it is not (TH)
If exists(Select * from #temp2 where charindex('T', DOW) > 0 and charindex('T', DOW)<> charindex('TH', DOW))
begin
Select @.DayOfWeek = @.DayOfWeek + 'T'
end
--Checking for Wednesday (W)
If exists(Select * from #temp2 where charindex('W', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek + 'W'
end
--Checking for Thursday (TH)
If exists(Select * from #temp2 where charindex('TH', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek + 'TH'
end
--Checking for Friday (F)
If exists(Select * from #temp2 where charindex('F', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek + 'F'
end
--Checking for Saturday (SA)
If exists(Select * from #temp2 where charindex('SA', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek + 'SA'
end
--Checking for Saturday (SA)
If exists(Select * from #temp2 where charindex('SU', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek + 'SU'
end

insert into #temp3
Select Building, Time, Room, @.DayOfWeek from #temp2 group by Building, Time, Room

Drop table #temp2

FETCH NEXT FROM cur_room_time
INTO @.Room, @.Time

END

--cleraning up the cursor

CLOSE cur_room_time
DEALLOCATE cur_room_time

--selecting the final result set

Select *
from #temp3

DROP TABLE #temp3
Hope this works for you!

Ron N

|||It should work. Thanks a lot for help!|||

i belive this can help

well use this function to get retrive the string that cotaians the rows of the specific ID.

CREATEFUNCTION dbo.ConRow(@.JID int)

RETURNSVARCHAR(8000)

BEGIN

DECLARE @.Output VARCHAR(8000)

SELECT @.Output =COALESCE(@.Output+', ','')+CONVERT(varchar(20), JP.a)

FROM [E_JobPending] JP

WHERE JP.JobID = @.JID

RETURN @.Output

END

select dbo.ConRow(jobid), vE_Job.*

from

vE_Job

DROPFUNCTION dbo.ConRow

|||

Untested, but should give a single row

SELECT IDENTITY(int, 1,1) AS Custom_ID, A.* INTO #EarlyMorning FROM (
SELECT
'Bannan for 05FQ' AS Building,
'7:30-9:50' AS Time,
CSM_ROOM AS [Room Number],
CASE ISNULL(CSM_TECH, '') WHEN '' THEN '' ELSE 'X' END AS [Tech],
CSM_CAPACITY AS [Capacity],
CONVERT( VARCHAR (25), CASE ISNULL(MAX(CSM_MON), '') WHEN '' THEN '' ELSE 'M' END
+ CASE ISNULL(MAX(CSM_TUE), '') WHEN '' THEN '' ELSE 'T' END
+ CASE ISNULL(MAX(CSM_WED), '') WHEN '' THEN '' ELSE 'W' END
+ CASE ISNULL(MAX(CSM_THU), '') WHEN '' THEN '' ELSE 'TH' END
+ CASE ISNULL(MAX(CSM_FRI), '') WHEN '' THEN '' ELSE 'F' END
+ CASE ISNULL(MAX(CSM_SAT), '') WHEN '' THEN '' ELSE 'SA' END
+ CASE ISNULL(MAX(CSM_SUN), '') WHEN '' THEN '' ELSE 'SU' END) AS [7:30-9:50]
FROM COURSESCH_MEET
WHERE CSM_BLDG = 'ENG'
AND CSM_TERM = '05FQ'
AND CAST(CSM_START_TIME AS datetime) BETWEEN '7:30:00 AM' AND '9:50:00 AM'
AND CAST(CSM_END_TIME AS datetime)BETWEEN '7:30:00 AM' AND '9:50:00 AM'
GROUP BY CSM_ROOM,CSM_TECH,CSM_CAPACITY
) AS A
ORDER BY A.[Room Number]

|||

Please post the version of SQL Server you are using so it is easier to suggest the correct solution. If you are using SQL Server 2005 you can use PIVOT operator and ROW_NUMBER in a query like below:

SELECT pt.Building, pt.Time, pt."Room #"

, pt.[1] + coalesce(pt.[2], '') + coalesce(pt.[3], '') + coalesce(pt.[4], '') as DaysOfWeek

FROM (

SELECT t.Building, t.Time, t."Room #", t."7:30- 9:50"

, ROW_NUMBER() OVER(PARTITION BY t.Building, t.Time, t."Room #" ORDER BY t."7:30- 9:50") as seq

FROM tbl as t

) AS t1

PIVOT (max(t1."7:30- 9:50") for t1.seq in ([1], [2], [3], [4] /*... as many maximum rows per grouping above*/)) as pt

You can do the same query above in older versions of SQL Server also. Use a temporary table to generate the sequence (possibly) or use correlated sub-query. And convert PIVOT to GROUP BY query with CASE expressions in SELECT list.

Collapsing Three Rows Into One with T-SQL Challange?

Hello,

You will get nowhere until you normalize your tables!!!

Course(ID,CourseName)
Room(ID, Room)
Day(No, Name, Abbrv)
TimeSlot(ID, DayNo, StartTime, Length) -Different Days May have different Time Allotments
ScheduledCourse(CourseID, RoomID, TimeSlotID)

=======================================
Sample Data
=======================================
Course
ID CourseName
1 Engineering
2 Biology
3 Calculus

Room
ID Room
1 101
2 102
3 201
4 202

Day
No Name Abbrv
1 Monday M
2 Tuesday T
3 Wednesday W
4 Thursday Th
5 Friday F

BOO-YAH!
|||Thank you very much Allen. It looks like a great design but unfortunately I don't dba right to change schema of a table I pulling my data from. The table schema looks this:

Building Time Room # 7:30- 9:50
Engeneering 7:30:00 AM - 9:50:00 AM 201 MTTH
Engeneering 7:30:00 AM - 9:50:00 AM 201 F
Engeneering 7:30:00 AM - 9:50:00 AM 201 W|||

donni100 wrote:
Do you think I could accamplish here?

I don't think so . . . at least not in T-SQL without having rights to create a stored procedure / function.

Someone needs to grab the dba and have him redesign the database as the table is not in third normal form.

If a database isn't in (at least) third normal form, it makes doing things via sql extremely difficult.|||

Give this a shot.

--First dump the result set into the first temp table (#temp1)

CREATE TABLE #temp3 --Final result table
(Building varchar(30),
Time varchar(40),
[Room #] int,
[Day of week] varchar(10))

DECLARE cur_room_time CURSOR FOR
SELECT DISTINCT Room, Time
FROM #temp1

OPEN cur_room_time
FETCH NEXT FROM cur_room_time
INTO @.Room, @.Time

WHILE @.@.FETCH_STATUS = 0
BEGIN
--Creating the temp table that we will eveluate the day of the week
Select *
into #temp2
from #temp1
where Room = @.Room
and Time = @.Time

set @.DayOfWeek = ''

insert into #temp3
Select Building, Time, Room, @.DayOfWeek from #temp2 group by Building, Time, Room

Drop table #temp2

FETCH NEXT FROM cur_room_time
INTO @.Room, @.Time

END

--cleraning up the cursor

CLOSE cur_room_time
DEALLOCATE cur_room_time

--selecting the final result set

Select *
from #temp3

DROP TABLE #temp3
Hope this works for you!

Ron N

|||It should work. Thanks a lot for help!|||

i belive this can help

well use this function to get retrive the string that cotaians the rows of the specific ID.

CREATE FUNCTION dbo.ConRow(@.JID int)

RETURNS VARCHAR(8000)

BEGIN

DECLARE @.Output VARCHAR(8000)

SELECT @.Output = COALESCE(@.Output+', ', '') + CONVERT(varchar(20), JP.a)

FROM [E_JobPending] JP

WHERE JP.JobID = @.JID

RETURN @.Output

END

select dbo.ConRow(jobid), vE_Job.*

from

vE_Job

DROP FUNCTION dbo.ConRow

|||

Untested, but should give a single row

|||

Please post the version of SQL Server you are using so it is easier to suggest the correct solution. If you are using SQL Server 2005 you can use PIVOT operator and ROW_NUMBER in a query like below:

SELECT pt.Building, pt.Time, pt."Room #"

, pt.[1] + coalesce(pt.[2], '') + coalesce(pt.[3], '') + coalesce(pt.[4], '') as DaysOfWeek

FROM (

SELECT t.Building, t.Time, t."Room #", t."7:30- 9:50"

, ROW_NUMBER() OVER(PARTITION BY t.Building, t.Time, t."Room #" ORDER BY t."7:30- 9:50") as seq

FROM tbl as t

) AS t1

PIVOT (max(t1."7:30- 9:50") for t1.seq in ([1], [2], [3], [4] /*... as many maximum rows per grouping above*/)) as pt

Collapsing Three Rows Into One with T-SQL Challange?

Hello,

You will get nowhere until you normalize your tables!!!

Course(ID,CourseName)
Room(ID, Room)
Day(No, Name, Abbrv)
TimeSlot(ID, DayNo, StartTime, Length) -Different Days May have different Time Allotments
ScheduledCourse(CourseID, RoomID, TimeSlotID)

=======================================
Sample Data
=======================================
Course
ID CourseName
1 Engineering
2 Biology
3 Calculus

Room
ID Room
1 101
2 102
3 201
4 202

Day
No Name Abbrv
1 Monday M
2 Tuesday T
3 Wednesday W
4 Thursday Th
5 Friday F

BOO-YAH!
|||Thank you very much Allen. It looks like a great design but unfortunately I don't dba right to change schema of a table I pulling my data from. The table schema looks this:

Building Time Room # 7:30- 9:50
Engeneering 7:30:00 AM - 9:50:00 AM 201 MTTH
Engeneering 7:30:00 AM - 9:50:00 AM 201 F
Engeneering 7:30:00 AM - 9:50:00 AM 201 W|||

donni100 wrote:
Do you think I could accamplish here?

I don't think so . . . at least not in T-SQL without having rights to create a stored procedure / function.

Someone needs to grab the dba and have him redesign the database as the table is not in third normal form.

If a database isn't in (at least) third normal form, it makes doing things via sql extremely difficult.|||

Give this a shot.

--First dump the result set into the first temp table (#temp1)

CREATE TABLE #temp3 --Final result table
(Building varchar(30),
Time varchar(40),
[Room #] int,
[Day of week] varchar(10))

DECLARE cur_room_time CURSOR FOR
SELECT DISTINCT Room, Time
FROM #temp1

OPEN cur_room_time
FETCH NEXT FROM cur_room_time
INTO @.Room, @.Time

WHILE @.@.FETCH_STATUS = 0
BEGIN
--Creating the temp table that we will eveluate the day of the week
Select *
into #temp2
from #temp1
where Room = @.Room
and Time = @.Time

set @.DayOfWeek = ''

insert into #temp3
Select Building, Time, Room, @.DayOfWeek from #temp2 group by Building, Time, Room

Drop table #temp2

FETCH NEXT FROM cur_room_time
INTO @.Room, @.Time

END

--cleraning up the cursor

CLOSE cur_room_time
DEALLOCATE cur_room_time

--selecting the final result set

Select *
from #temp3

DROP TABLE #temp3
Hope this works for you!

Ron N

|||It should work. Thanks a lot for help!|||

i belive this can help

well use this function to get retrive the string that cotaians the rows of the specific ID.

CREATE FUNCTION dbo.ConRow(@.JID int)

RETURNS VARCHAR(8000)

BEGIN

DECLARE @.Output VARCHAR(8000)

SELECT @.Output = COALESCE(@.Output+', ', '') + CONVERT(varchar(20), JP.a)

FROM [E_JobPending] JP

WHERE JP.JobID = @.JID

RETURN @.Output

END

select dbo.ConRow(jobid), vE_Job.*

from

vE_Job

DROP FUNCTION dbo.ConRow

|||

Untested, but should give a single row

|||

Please post the version of SQL Server you are using so it is easier to suggest the correct solution. If you are using SQL Server 2005 you can use PIVOT operator and ROW_NUMBER in a query like below:

SELECT pt.Building, pt.Time, pt."Room #"

, pt.[1] + coalesce(pt.[2], '') + coalesce(pt.[3], '') + coalesce(pt.[4], '') as DaysOfWeek

FROM (

SELECT t.Building, t.Time, t."Room #", t."7:30- 9:50"

, ROW_NUMBER() OVER(PARTITION BY t.Building, t.Time, t."Room #" ORDER BY t."7:30- 9:50") as seq

FROM tbl as t

) AS t1

PIVOT (max(t1."7:30- 9:50") for t1.seq in ([1], [2], [3], [4] /*... as many maximum rows per grouping above*/)) as pt

Collapse cell if empty and

Whoops. Sent too quick.
=Iif(IsNothing(Fields!ADDRESS3.Value),True,False)
Part 2 of question. I've got city, state, and zip columns evenly spaced in
a table. How do I get the columns to collapse to the width of their data?
"Colin" <legendsfan@.nospam.nospam> wrote in message news:...
> I'm creating a Reporting Services form within VS 2003. I can't figure out
> how to collapse a cell if there is no data returned. As an example I've
> got three lines for an Address field but most only use two lines. How can
> I collapse the third cell on my form so city, state, and zip will be next
> to last line of address? I figure I need to use an "expression" but this
> doesn't seem to be working. I applied this under visibility.
>Use something like this expression =IIF( Fields!STREET2.Value =nothing,True,False) as the Hidden property value under Visibility for
the row or cell you want to conditionally hide.
As for your part 2, I don't know the answer to that. SRS allows for
increased/decreased height based on data but not width. If anybody has
a fix for this, I'd like to see that too.|||How do I put static data in a field with something dynamically generated? I
tried "Street Name:" & =IIF( Fields!STREET2.Value => nothing,True,False)
That didn't work.
This will hide the field but it doesn't collapse it or make it not show on
the page. I have four rows in a table for the address field but usually
only 3 are used. This is what happens when the third row is nothing. Under
Willow there is a blank space because this field rarely has data associated.
You can also see the spacing problem between city and state.
Target Stores
TARGET #1167
2241 WILLOW
GLENVIEW IL 60025
"toolman" <toolman_2000@.yahoo.com> wrote in message
news:1139939869.370244.104680@.o13g2000cwo.googlegroups.com...
> Use something like this expression =IIF( Fields!STREET2.Value => nothing,True,False) as the Hidden property value under Visibility for
> the row or cell you want to conditionally hide.
> As for your part 2, I don't know the answer to that. SRS allows for
> increased/decreased height based on data but not width. If anybody has
> a fix for this, I'd like to see that too.
>|||Hello,
To understand the issue better, will you provide a simple sample of the
report with backend data?
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
>From: "Colin" <legendsfan@.nospam.nospam>
>References: <eHxiKZOMGHA.2416@.TK2MSFTNGP15.phx.gbl>
<1139939869.370244.104680@.o13g2000cwo.googlegroups.com>
>Subject: Re: Collapse cell if empty and
>Date: Tue, 14 Feb 2006 14:37:02 -0800
>Lines: 29
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
>X-RFC2646: Format=Flowed; Original
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
>Message-ID: <#c0tfbbMGHA.2124@.TK2MSFTNGP14.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.reportingsvcs
>NNTP-Posting-Host: mail.topics-ent.com 64.122.1.82
>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP14.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:68725
>X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>How do I put static data in a field with something dynamically generated?
I
>tried "Street Name:" & =IIF( Fields!STREET2.Value =>> nothing,True,False)
>That didn't work.
>This will hide the field but it doesn't collapse it or make it not show on
>the page. I have four rows in a table for the address field but usually
>only 3 are used. This is what happens when the third row is nothing.
Under
>Willow there is a blank space because this field rarely has data
associated.
>You can also see the spacing problem between city and state.
> Target Stores
> TARGET #1167
> 2241 WILLOW
> GLENVIEW IL 60025
>"toolman" <toolman_2000@.yahoo.com> wrote in message
>news:1139939869.370244.104680@.o13g2000cwo.googlegroups.com...
>> Use something like this expression =IIF( Fields!STREET2.Value =>> nothing,True,False) as the Hidden property value under Visibility for
>> the row or cell you want to conditionally hide.
>> As for your part 2, I don't know the answer to that. SRS allows for
>> increased/decreased height based on data but not width. If anybody has
>> a fix for this, I'd like to see that too.
>
>|||Are you using the =IIF( Fields!STREET2.Value = > nothing,True,False)
expression in the property for the entire row or just a cell in the
row? If not, try that. It should work for you. I have a couple
reports using it and it always draws the city, state zip row up to the
last visible address row.
The expression for Street: Fields!Street.value would be
="Street: " & Field!Street.Value
If Fields!Street.Value is Nothing you'll get 'Street: ' otherwise
you'll get 'Street: 123 Street Value' (without the quotes)|||Thanks toolman. I was setting the hidden value in the cell instead of row.
"toolman" <toolman_2000@.yahoo.com> wrote in message
news:1140038132.121332.42660@.g47g2000cwa.googlegroups.com...
> Are you using the =IIF( Fields!STREET2.Value = > nothing,True,False)
> expression in the property for the entire row or just a cell in the
> row? If not, try that. It should work for you. I have a couple
> reports using it and it always draws the city, state zip row up to the
> last visible address row.
> The expression for Street: Fields!Street.value would be
> ="Street: " & Field!Street.Value
> If Fields!Street.Value is Nothing you'll get 'Street: ' otherwise
> you'll get 'Street: 123 Street Value' (without the quotes)
>

Sunday, March 11, 2012

Code.SafeDivide Expression

What does =Code.SafeDivide mean in an expression? I have an example below:
=Code.SafeDivide (Sum(Fields!revenue.Value), Sum(Fields!volume.Value))
Thanks, DeborahThe only time I've seen something like that is when you write a custom
code function called "SafeDivide". This will do a check on the divisor
(the second parameter) and, if it is 0, it will not perform the
division. It will just return 0. Here is an example I found (this
example will actually take a 3rd parameter - the "value if undefined"
parameter):
The following was snipped from this thread. Read it to see the full
explanation:
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_frm/thread/b66002620ec40e52/ed9544519ff38234?q=safedivide&rnum=2#ed9544519ff38234
Public Function SafeDivide(pi_dblNumerator As Double, pi_dblDenominator
As
Double, pi_dblUndefined As Double)
If pi_dblDenominator = 0 Then
SafeDivide = pi_dblUndefined
Else
SafeDivide = pi_dblNumerator / pi_dblDenominator
End If
End Function
Regards,
Dan

Code table maintenance

Hello,
I am working on the logical model for a database. I need to use a number of
code tables (tables that keep typically name value pairs. I need maintain da
ta like products, services etc).
I am wondering if I increase the abstraction and use one table to represent
the name value pairs but use a category to identify each type is there is an
y value in doing this?
The advantage with this I think is consolidating the data and probably minim
izing the administration
The disadvantages may be too many joins that need to be qualified by the cat
egory type. Also, I may end up having too many self-joins.
Any suggestions'?doesnt seem to make sense to me.
I would keep them seperate.
Greg Jackson
PDX, Oregon

Code Table Maintenance

Hello,

I am working on the logical model for a database. I need to use a number of code tables (tables that keep typically name value pairs. I need maintain data like products, services etc).
I am wondering if I increase the abstraction and use one table to represent the name value pairs but use a category to identify each type. Is there is any value in doing this?

The advantage with this I think is consolidating the data and probably minimizing the administration
The disadvantages may be too many joins that need to be qualified by the category type. Also, I may end up having too many self-joins.

Any suggestions???Personally I like this approach because then I don't have a bunch of hash tables scattered around the database. Adding new groups of name value pairs becomes a lot easier.

I haven't found the need to perform self-joins, but yes the large number of joins to the same table tends to be a pain. But, you'd still have to have the joins regardless (just to different tables).

Honestly, I am not sure of the performance benefits. But I think the "compactness" of the solution has value.

My 2 cents, maybe only 1 cent.
Terri

Thursday, March 8, 2012

Code Question - Making a field Null

Hi,
I need to change some code and make a field always show the value NULL
moving forward. I need to keep the field there becuase at the end
result the value will be imported another way.
Currently, the field is coded like this: ISNULL(email_addr,'')
How can I change this to always just be NULL?
Thank you,
RayYou can update the column value to be NULL:
UPDATE MyTable
SET email_addr = NULL;
Then you simply select the column without using ISNULL:
SELECT email_addr
FROM MyTable;
HTH,
Plamen Ratchev
http://www.SQLStudio.com

Code Question - Making a field Null

Hi,
I need to change some code and make a field always show the value NULL
moving forward. I need to keep the field there becuase at the end
result the value will be imported another way.
Currently, the field is coded like this: ISNULL(email_addr,'')
How can I change this to always just be NULL?
Thank you,
Ray
You can update the column value to be NULL:
UPDATE MyTable
SET email_addr = NULL;
Then you simply select the column without using ISNULL:
SELECT email_addr
FROM MyTable;
HTH,
Plamen Ratchev
http://www.SQLStudio.com

Code Question

I have this in the Code section under Report->Properties:

Public Function GetSort() As String
Select Case Report.Parameters!SortBy.Value
Case "GM"
Return CStr(Fields!GM.Value)

Case "STYLECODE"
Return Fields!STYLECODE.Value

Case "CUSTOMER"
Return Fields!CUSTOMER.Value

End Select
End Function

I have a call to Code.GetSort() in the sorting tab of the report data table.

I get this error when I build:

Error 1 [rsCompilerErrorInCode] There is an error on line 4 of custom code: [BC30469] Reference to a non-shared member requires an object reference.

i think the problem is in the code body can't use report parameters.
so the code maybe is:
public Function GetSort(SortType as String) As String
Select Case SortType

then use it like GetSort(Report.Parameters!SortBy.Value) in the expression.
|||

Just pass the paremeter value to the function in the code and use that inside your code like this

Public Function GetSort(SortBy as String) As String

Select Case SortBy

........

End Function

Shyam

|||

Hi Sam,

You have to use GetSort= instead of Return (VB not accepts return keyword)

Public Function GetSort() As String
Select Case Report.Parameters!SortBy.Value
Case "GM"
GetSort=CStr(Fields!GM.Value)

Case "STYLECODE"
GetSort= Fields!STYLECODE.Value

Case "CUSTOMER"
GetSort= Fields!CUSTOMER.Value

End Select
End Function

Regards,

Nanda kumar R

|||

Mr Nanda Kumar,

Please get your basics right. You can use Return keyword in the code in report.

The error is because Parameter is being referenced in the code the way it is referenced in expressions.

Shyam

|||Looks like we all need to get our basics right.

Problem was not that the parameter was referenced in the body, but that fields were referenced in the header. So now I have this in Report->Properties ->Code:

Public Function GetSort(sortby As String, sc As String, cu As String) As String
Select Case sortby

Case "STYLECODE"
Return sc

Case "CUSTOMER"
Return cu

End Select
End Function

And this in the sort tab of the data table properties:

=IIF(Parameters!SortBy.Value="GM",
Fields!GM.Value,
Code.GetSort(Parameters!SortBy.Value,Fields!STYLECODE.Value,Fields!CUSTOMER.Value))

|||I have another question if you all will so indulge me: In Visual Studio Report Designer, Click File-> New -> New File -> VBScript File.

If I create a code file this way, how do I call a function in it from the report?

Thanks
|||

Dont you get the same error when you use Parameters!something.Value in code?

It will give that error!!!!!!

You can only add reference to .dll assemblies and use the methods in those assemblies.

Shyam

|||>>You can only add reference to .dll assemblies and use the methods in those assemblies.

Sorry for asking silly questions but what is the purpose of the vbscript file capability if you cant call functions in your script?

|||

For SSRS, the referenced code should be pre-compiled but vbscript can only be compiled to an exe or interpreted at runtime.

You can transform your vbscript code to .net code and place it under code section directly.

Can u please mark the previous issue as marked?

Shyam

Code Question

I have this in the Code section under Report->Properties:

Public Function GetSort() As String
Select Case Report.Parameters!SortBy.Value
Case "GM"
Return CStr(Fields!GM.Value)

Case "STYLECODE"
Return Fields!STYLECODE.Value

Case "CUSTOMER"
Return Fields!CUSTOMER.Value

End Select
End Function

I have a call to Code.GetSort() in the sorting tab of the report data table.

I get this error when I build:

Error 1 [rsCompilerErrorInCode] There is an error on line 4 of custom code: [BC30469] Reference to a non-shared member requires an object reference.i think the problem is in the code body can't use report parameters.
so the code maybe is:
public Function GetSort(SortType as String) As String
Select Case SortType

then use it like GetSort(Report.Parameters!SortBy.Value) in the expression.
|||

Just pass the paremeter value to the function in the code and use that inside your code like this

Public Function GetSort(SortBy as String) As String

Select Case SortBy

........

End Function

Shyam

|||

Hi Sam,

You have to use GetSort= instead of Return (VB not accepts return keyword)

Public Function GetSort() As String
Select Case Report.Parameters!SortBy.Value
Case "GM"
GetSort=CStr(Fields!GM.Value)

Case "STYLECODE"
GetSort= Fields!STYLECODE.Value

Case "CUSTOMER"
GetSort= Fields!CUSTOMER.Value

End Select
End Function

Regards,

Nanda kumar R

|||

Mr Nanda Kumar,

Please get your basics right. You can use Return keyword in the code in report.

The error is because Parameter is being referenced in the code the way it is referenced in expressions.

Shyam

|||Looks like we all need to get our basics right.

Problem was not that the parameter was referenced in the body, but that fields were referenced in the header. So now I have this in Report->Properties ->Code:

Public Function GetSort(sortby As String, sc As String, cu As String) As String
Select Case sortby

Case "STYLECODE"
Return sc

Case "CUSTOMER"
Return cu

End Select
End Function

And this in the sort tab of the data table properties:

=IIF(Parameters!SortBy.Value="GM",
Fields!GM.Value,
Code.GetSort(Parameters!SortBy.Value,Fields!STYLECODE.Value,Fields!CUSTOMER.Value))

I really wanted to avoid a mind bending iif statement hence the desire for the select case statement in the code. The GM field is numeric and it does not sort as a string so I have to handle that one separately.|||I have another question if you all will so indulge me: In Visual Studio Report Designer, Click File-> New -> New File -> VBScript File.

If I create a code file this way, how do I call a function in it from the report?

Thanks|||

Dont you get the same error when you use Parameters!something.Value in code?

It will give that error!!!!!!

You can only add reference to .dll assemblies and use the methods in those assemblies.

Shyam

|||>>You can only add reference to .dll assemblies and use the methods in those assemblies.

Sorry for asking silly questions but what is the purpose of the vbscript file capability if you cant call functions in your script?

|||

For SSRS, the referenced code should be pre-compiled but vbscript can only be compiled to an exe or interpreted at runtime.

You can transform your vbscript code to .net code and place it under code section directly.

Can u please mark the previous issue as marked?

Shyam

code problem..pls help me

i have put the following code under the code section to get the multi value.

Public Function ShowParameterValues(ByVal parameter As Parameter) As String
Dim s As String
If parameter.IsMultiValue Then
s = "Multivalue: "
For i As Integer = 0 To parameter.Count - 1
s = s + CStr(parameter.Value(i)) + " "
Next
Else
s = "Single value: " + CStr(parameter.Value)
End If
Return s
End Function

then i called that using =Code.ShowParameterValues(Parameters!BU). it gives error. what is the reason?

I wished I could help. Your code is working for me. .NET Framework problems?|||i dont have this reference. where can i download this reference?

Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Parameters

code problem

i have put the following code under the code section to get the multi value.

then i called that using =Code.ShowParameterValues(Parameters!BU). it gives error. what is the reason?

I wished I could help. Your code is working for me. .NET Framework problems?|||i dont have this reference. where can i download this reference?

Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Parameters

Wednesday, March 7, 2012

Code - Integer, Nothing vs Zero

The piece of code below attempts to convert minutes to a time string.
The problem I am having is if the value of Minutes is NULL, it treats it as
zero.
Has anyone got any suggestions ?
Thanks
Steve
---
Public Function MinutesToTime(ByVal Minutes AS Integer)
On Error GoTo errorhandler
Dim mins
Dim hrs
If IsNothing(Minutes) Then
MinutesToTime = ""
Else
hrs = Fix(Minutes / 60)
mins = Minutes - (hrs * 60)
If mins < 10 Then mins = "0" & mins
MinutesToTime = hrs & ":" & mins
End If
Exit Function
errorhandler:
MinutesToTime = "##:##"
End FunctionFirst off this is a function, you should be explicit on your return values.
You have two DIM's without specifing the type... then you have a check for
NULL (if nothing) you return a string... so which is it?
What happens if the minutes is actually 0 - what would you return? Would it
be an empty string or "00:00" ' If that is the case, then why not, for NULL
values, return "00:00" - the same as if you specified 0 minutes?
=-Chris
"SteveH" <SteveH@.discussions.microsoft.com> wrote in message
news:05257274-EDEA-438A-B6E5-054E122BC5C1@.microsoft.com...
> The piece of code below attempts to convert minutes to a time string.
> The problem I am having is if the value of Minutes is NULL, it treats it
> as
> zero.
> Has anyone got any suggestions ?
> Thanks
> Steve
> ---
> Public Function MinutesToTime(ByVal Minutes AS Integer)
> On Error GoTo errorhandler
> Dim mins
> Dim hrs
> If IsNothing(Minutes) Then
> MinutesToTime = ""
> Else
> hrs = Fix(Minutes / 60)
> mins = Minutes - (hrs * 60)
> If mins < 10 Then mins = "0" & mins
> MinutesToTime = hrs & ":" & mins
> End If
> Exit Function
> errorhandler:
> MinutesToTime = "##:##"
> End Function
>|||Thanks Chris for your reply
I am wanting to pass in an integer representing minutes and return a string
that looks like a time as per the following examples.
67 returns "1:07"
0 returns "0:00"
NULL returns ""
My problems is that when NULL is passed in I am currently getting "0:00"
instead of "".
I have tried using IsNull function but I get a compilation error "Name
'IsNull' is not declared."
"Chris Conner" wrote:
> First off this is a function, you should be explicit on your return values.
> You have two DIM's without specifing the type... then you have a check for
> NULL (if nothing) you return a string... so which is it?
> What happens if the minutes is actually 0 - what would you return? Would it
> be an empty string or "00:00" ' If that is the case, then why not, for NULL
> values, return "00:00" - the same as if you specified 0 minutes?
> =-Chris
> "SteveH" <SteveH@.discussions.microsoft.com> wrote in message
> news:05257274-EDEA-438A-B6E5-054E122BC5C1@.microsoft.com...
> > The piece of code below attempts to convert minutes to a time string.
> >
> > The problem I am having is if the value of Minutes is NULL, it treats it
> > as
> > zero.
> >
> > Has anyone got any suggestions ?
> >
> > Thanks
> > Steve
> > ---
> >
> > Public Function MinutesToTime(ByVal Minutes AS Integer)
> > On Error GoTo errorhandler
> >
> > Dim mins
> > Dim hrs
> >
> > If IsNothing(Minutes) Then
> > MinutesToTime = ""
> > Else
> > hrs = Fix(Minutes / 60)
> > mins = Minutes - (hrs * 60)
> >
> > If mins < 10 Then mins = "0" & mins
> >
> > MinutesToTime = hrs & ":" & mins
> > End If
> >
> > Exit Function
> >
> > errorhandler:
> > MinutesToTime = "##:##"
> >
> > End Function
> >
>
>|||Reporting Services doesn't support 'code reuse'
so I would reccomend just doing this in Microsoft Access; it would be a
simple format string there; right?
-Aaron
SteveH wrote:
> Thanks Chris for your reply
> I am wanting to pass in an integer representing minutes and return a string
> that looks like a time as per the following examples.
> 67 returns "1:07"
> 0 returns "0:00"
> NULL returns ""
> My problems is that when NULL is passed in I am currently getting "0:00"
> instead of "".
> I have tried using IsNull function but I get a compilation error "Name
> 'IsNull' is not declared."
> "Chris Conner" wrote:
> > First off this is a function, you should be explicit on your return values.
> > You have two DIM's without specifing the type... then you have a check for
> > NULL (if nothing) you return a string... so which is it?
> > What happens if the minutes is actually 0 - what would you return? Would it
> > be an empty string or "00:00" ' If that is the case, then why not, for NULL
> > values, return "00:00" - the same as if you specified 0 minutes?
> >
> > =-Chris
> >
> > "SteveH" <SteveH@.discussions.microsoft.com> wrote in message
> > news:05257274-EDEA-438A-B6E5-054E122BC5C1@.microsoft.com...
> > > The piece of code below attempts to convert minutes to a time string.
> > >
> > > The problem I am having is if the value of Minutes is NULL, it treats it
> > > as
> > > zero.
> > >
> > > Has anyone got any suggestions ?
> > >
> > > Thanks
> > > Steve
> > > ---
> > >
> > > Public Function MinutesToTime(ByVal Minutes AS Integer)
> > > On Error GoTo errorhandler
> > >
> > > Dim mins
> > > Dim hrs
> > >
> > > If IsNothing(Minutes) Then
> > > MinutesToTime = ""
> > > Else
> > > hrs = Fix(Minutes / 60)
> > > mins = Minutes - (hrs * 60)
> > >
> > > If mins < 10 Then mins = "0" & mins
> > >
> > > MinutesToTime = hrs & ":" & mins
> > > End If
> > >
> > > Exit Function
> > >
> > > errorhandler:
> > > MinutesToTime = "##:##"
> > >
> > > End Function
> > >
> >
> >
> >|||Finally found a solution and thought I would post it here for anyone who has
the same problem.
datatype needed to be Nullable(Of Integer) instead of Integer
Public Function MinutesToTime(ByVal Minutes As Nullabe(Of Integer)) AS
String
then use HasValue instead of IsNothing
If Minutes.HasValue Then
and Minutes.Value
"SteveH" wrote:
> Thanks Chris for your reply
> I am wanting to pass in an integer representing minutes and return a string
> that looks like a time as per the following examples.
> 67 returns "1:07"
> 0 returns "0:00"
> NULL returns ""
> My problems is that when NULL is passed in I am currently getting "0:00"
> instead of "".
> I have tried using IsNull function but I get a compilation error "Name
> 'IsNull' is not declared."
> "Chris Conner" wrote:
> > First off this is a function, you should be explicit on your return values.
> > You have two DIM's without specifing the type... then you have a check for
> > NULL (if nothing) you return a string... so which is it?
> > What happens if the minutes is actually 0 - what would you return? Would it
> > be an empty string or "00:00" ' If that is the case, then why not, for NULL
> > values, return "00:00" - the same as if you specified 0 minutes?
> >
> > =-Chris
> >
> > "SteveH" <SteveH@.discussions.microsoft.com> wrote in message
> > news:05257274-EDEA-438A-B6E5-054E122BC5C1@.microsoft.com...
> > > The piece of code below attempts to convert minutes to a time string.
> > >
> > > The problem I am having is if the value of Minutes is NULL, it treats it
> > > as
> > > zero.
> > >
> > > Has anyone got any suggestions ?
> > >
> > > Thanks
> > > Steve
> > > ---
> > >
> > > Public Function MinutesToTime(ByVal Minutes AS Integer)
> > > On Error GoTo errorhandler
> > >
> > > Dim mins
> > > Dim hrs
> > >
> > > If IsNothing(Minutes) Then
> > > MinutesToTime = ""
> > > Else
> > > hrs = Fix(Minutes / 60)
> > > mins = Minutes - (hrs * 60)
> > >
> > > If mins < 10 Then mins = "0" & mins
> > >
> > > MinutesToTime = hrs & ":" & mins
> > > End If
> > >
> > > Exit Function
> > >
> > > errorhandler:
> > > MinutesToTime = "##:##"
> > >
> > > End Function
> > >
> >
> >
> >

Coalesce Question

Hi.
I'm using Coalesce to change a NULL returned value from a Select statement.
Here's my code:
SELECT TOP 1 @.StartDate = Coalesce(Discount_Effect_Date, '01/01/1980') FROM
MyTable WHERE RTrim(Subtable_Id) = 'I03U'
Print @.StartDate
When I have a row in MyTable where the Subtable_Id = 'I03U' I get the
@.StartDate
printed out.
When I don't have a row in MyTable where the Subtable_Id = 'I03U' I don't
get the @.StartDate printed out. I was expecting to see 01/01/1980.
What am I missing?
TIA.
RitaRitaG wrote:
> Hi.
> I'm using Coalesce to change a NULL returned value from a Select statement
.
> Here's my code:
> SELECT TOP 1 @.StartDate = Coalesce(Discount_Effect_Date, '01/01/1980') FRO
M
> MyTable WHERE RTrim(Subtable_Id) = 'I03U'
> Print @.StartDate
> When I have a row in MyTable where the Subtable_Id = 'I03U' I get the
> @.StartDate
> printed out.
> When I don't have a row in MyTable where the Subtable_Id = 'I03U' I don't
> get the @.StartDate printed out. I was expecting to see 01/01/1980.
> What am I missing?
> TIA.
> Rita
The problem is that if the SELECT doesn't return any rows then the
assignment will never be made. For single value assignments use SET
instead of SELECT.
SET @.startdate =
(SELECT COALESCE(MIN(discount_effect_date),'1980
0101')
FROM mytable
WHERE RTRIM(subtable_id) = 'I03U');
I assume you meant to retrieve the minimum date but in the query you
posted you specified TOP without ORDER BY so in fact your results may
be unpredictable.
The YYYYMMDD format I've used is better for dates because it's
unambiguous and doesn't depend on any of your regional connection
settings.
Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Coalesce works on individual columns, for each row returned by your query.
If no row is returned, then there is no column value for coalesce to work
on. Coalesce is only useful when you have rows returned, but a particular
column is null, and you want to return a default value instead of the null.
You need to either change your SQL to insure that a row is always returned
(probably not what you want to do) or check to see if any rows were returned
(which appears to be your goal anyway).
Simply checking to see if @.StartDate is null AFTER your select may
accomplish what you want.
If you post the rest of your code, folks may be able to give some better
advice. Since this code is out of context, I can only guess at what you are
doing with it.
"RitaG" <RitaG@.discussions.microsoft.com> wrote in message
news:F739466D-0D03-4843-A46B-E9F3BF0AD5AB@.microsoft.com...
> Hi.
> I'm using Coalesce to change a NULL returned value from a Select
statement.
> Here's my code:
> SELECT TOP 1 @.StartDate = Coalesce(Discount_Effect_Date, '01/01/1980')
FROM
> MyTable WHERE RTrim(Subtable_Id) = 'I03U'
> Print @.StartDate
> When I have a row in MyTable where the Subtable_Id = 'I03U' I get the
> @.StartDate
> printed out.
> When I don't have a row in MyTable where the Subtable_Id = 'I03U' I don't
> get the @.StartDate printed out. I was expecting to see 01/01/1980.
> What am I missing?
> TIA.
> Rita|||"RitaG" <RitaG@.discussions.microsoft.com> wrote in message
news:F739466D-0D03-4843-A46B-E9F3BF0AD5AB@.microsoft.com...
> Hi.
> I'm using Coalesce to change a NULL returned value from a Select
> statement.
> Here's my code:
> SELECT TOP 1 @.StartDate = Coalesce(Discount_Effect_Date, '01/01/1980')
> FROM
> MyTable WHERE RTrim(Subtable_Id) = 'I03U'
> Print @.StartDate
> When I have a row in MyTable where the Subtable_Id = 'I03U' I get the
> @.StartDate
> printed out.
> When I don't have a row in MyTable where the Subtable_Id = 'I03U' I don't
> get the @.StartDate printed out. I was expecting to see 01/01/1980.
> What am I missing?
> TIA.
> Rita
Your WHERE clause only allows data to be printed out WHERE your Subtable ID
= I03U.
The other issue I see here is the use of your COALESCE... While this should
work for what you are doing, an ISNULL(Discount_Effect_Date, '01/01/1980')
may work better for you. COALESCE is generally used to find the first
non-null value in a list of columns. For example COALESCE(payrate_salary,
payrate_daily, payrate_hourly)
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks to all for your responses.
The dates are all the same that satisfy the WHERE clause so I could have
used a Distinct but I think Top 1 is faster.
The SET @.startdate =
(SELECT COALESCE(MIN(discount_effect_date),'1980
0101')
FROM mytable
WHERE RTRIM(subtable_id) = 'I03U');
will work for me. Thanks for the "YYYYMMDD format" suggestion.
Learn something every day! :-)
"David Portas" wrote:

> RitaG wrote:
> The problem is that if the SELECT doesn't return any rows then the
> assignment will never be made. For single value assignments use SET
> instead of SELECT.
> SET @.startdate =
> (SELECT COALESCE(MIN(discount_effect_date),'1980
0101')
> FROM mytable
> WHERE RTRIM(subtable_id) = 'I03U');
> I assume you meant to retrieve the minimum date but in the query you
> posted you specified TOP without ORDER BY so in fact your results may
> be unpredictable.
> The YYYYMMDD format I've used is better for dates because it's
> unambiguous and doesn't depend on any of your regional connection
> settings.
> Hope this helps.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>

COALESCE help

I have a pulldown menu which has like 4 options

producta productb productc and all

I am trying to retrieve the maximum build number value for these products and display on the gridview as per some other conditions like user selected OS etc

Now clicking on All, I want to display the maximum build number values for productA,ProductB ,ProductC

and I am trying to use coalesce but unable to get my result.

I end up seeing only one value which is the maximum of everything.Instead I want the maximums of A B and C and display them concatenated with commas.

If I do the following with no max funciton, i see all the values but i just want max from each branch.

DECLARE @.buildListvarchar(100)

select @.buildlist=COALESCE(@.buildList+', ','')+convert(varchar(10),build)from results

where branchin('ProductA','Product B','ProductC')

select @.buildList

Please let me know how to do this.

Please post some sample data from the table and expected output..

|||

Package Branch maxBuildNumber

Package1 Product A 2001

Package1 Product B 3004

Package1 Product C 4003

I want it as

Package buildList

Package1 2001,3004,4003

or better yet

Package1 ProductA.2001,ProductB.3004,ProductC.4003

|||

Close...

Declare @.TTable (Packagevarchar(10), Branchvarchar(10), maxBuildNumberint)Insert into @.TSelect'Package1','ProductA', 2001unionallSelect'Package1','ProductB', 3004unionallSelect'Package1','ProductC', 4003DECLARE @.buildListvarchar(100)SELECT @.buildlist=COALESCE(@.buildList +', ','') +convert(varchar(10),branch) +'.' +convert(Varchar, T.maxBuildNumber )FROM @.T TWHERE T.Package ='Package1'Select @.buildList

|||

Hi ,

Thank you for the coalesce help.Now I have a small problem with in that.

I do not want the whole of the branch name to be displayed in my buildlist(name of my branches are pretty long ..so want to display a short name instead)

I have this coalesce in a scalar function where I am returning it as a varchar.

Now before returning it, is it possible to check this buildlist for a pattern and replace it ?

suppose it is being displayed as productA/xy/ABCD.301 ....i want to display it as ABCD.301.

The value coming for Branch from my results table is something like productA/xy/ABCD.

I tried using Contains and replace but do not seem to work on declared variables?

This is what is in my coalesce

SELECT @.buildlist=COALESCE(@.buildList+' ','')+convert(varchar(50),Branch)+'.'+convert(Varchar, v_allbranchinfo.MAXBuildNumber)

FROM v_allbranchinfoWHERE /*some where conditions*/

IF @.buildlistcontains(@.buildlist,"Orcas/pu/DDE")

replace(buildlist,"Orcas/pu/DDE","DDE")

Can you please with this?

|||

After getting the @.Buildlist you can do a replace..

IF CHARINDEX(@.buildlist,'Orcas/pu/DDE') > 0SET @.BuildList =REPLACE(@.buildlist,'Orcas/pu/DDE','DDE' )

|||

You are great!

Thank you very much!

|||

Another problem now with coalese, I am getting the achived result as having all the branch build numbers in one row but on my webpage when I am displaying these results, I use a hyper link which would show some addition info from the same results table such as runid,total etc corresponding to each build.Now when I have the buildlist, I am not sure on how to handle this .

My query now looks like this:

(SELECT T1.*, T1.BuildListAS dataFROM v_BuildListerAS T1INNERJOIN

(SELECT SKU, OS, OSLang, ProductLang, Branch,MAX(Build)AS MaxBuild

FROM dbo.ResultsGROUPBY SKU, OS, OSLang, ProductLang, Branch)AS T2ON T1.SKU= T2.SKUAND

T1.OS= T2.OSAND T1.OSLang= T2.OSLangAND T1.ProductLang= T2.ProductLang)AS T3ON

dbo.Results.ID= T3.IDWHERE(dbo.Results.OSArch='Intel')AND(dbo.Results.OSLang='English - United States')AND(dbo.Results.ProductLang='ENU'))

v_Buildlister is a view on results table and the view doesn't have this runid etc information.

Even if it does, since it is a buildlist and not single build...does not give me proper information.

previously my T1 was results table .

Any idea will be appreciated.

coalesce does not seem to work

Hi,

I have the following table with some sample values, I want to return the first non null value in that order. COALESCE does not seem to work for me, it does not return the 3rd record. I need to include this in my select statement. Any urgent help please.

Mobile Business Private

NULL 345 NULL
4646 65464 65765
NULL 564
654654 564 6546

I want the following as my results:

Number

345
4646
564
654654

Select COALESCE(Mobile,Business,Private) as Number from Table returns:

345
4646

654654

(this is a test to see if private returns & it did with is not null but then how do i include in my select statement to show any one of the 3 fields)

select mobile,business,private where private is not null returns:

65765
564
6546

thanks

As you mentioned, COALESCE returns the first Non NULL value. You result is not what you want but the COALESCE is correct. You have a blank cell in your table. It is not NULL. You can use a CASE statement to check either NULL or blank to get the result you want. Or you can make sure your missing value cells are NULL.

HTH.

COALESCE and subquery

Hello,
I want to use a subquery within my main query but only dependant on a
parameter's value. I can do this with dynamic SQL but want to try to avoid
this
Here is mySQL without the parameter
SELECT Id
FROM tblAccounts a
WHERE EXISTS
(SELECT NULL FROM tblSales s
WHERE a.Id = s.AccountId)
Now if I have a parameter called @.chk_account and it evaluates to NULL - I
don't want to run the subquery at all.
Using dynamic sql would go something like:
DECLARE @.SQL varchar(1000)
SET @.SQL = 'SELECT Id FROM tblAccounts a'
IF @.chk_account IS NOT NULL
BEGIN
SET @.SQL = @.SQL +
' WHERE EXISTS
(SELECT NULL FROM tblSales s
WHERE a.Id = s.AccountId)
END
EXEC(@.SQL)
I thought maybe I could use the COALESCE function in some way?
DanWhy did you give the same attribute different names? There is no such
thing as an"id" -- it has to be some particular kind of identifier.
And stop using those redudant "tbl-" prefixes; there is one and only
one data strucutre in SQL; this is not an OO language. Read ISO-11179
for details:
SELECT account_id
FROM Accounts AS A
WHERE EXISTS
(SELECT * FROM Sales AS S
WHERE a.account_id = S.account_id)
OR @.chk_account IS NULL;|||Dan,
Depending on where this is used, one or another
solution may be more efficient. Here are a couple alternatives
to Joe's suggestion:
select Id
from tblAccounts a
where @.chk_account is not null
and exists (
select * from tblSales s
where a.Id = s.AccountId
)
union all
select Id
from tblAccounts a
where @.chk_account is null
--
if @.chk_account is null
select Id from tblAccounts a
else
select Id from tblAccounts a
where exists (...)
Steve Kass
Drew University
dan-cat wrote:

>Hello,
>I want to use a subquery within my main query but only dependant on a
>parameter's value. I can do this with dynamic SQL but want to try to avoid
>this
>Here is mySQL without the parameter
>SELECT Id
>FROM tblAccounts a
>WHERE EXISTS
>(SELECT NULL FROM tblSales s
>WHERE a.Id = s.AccountId)
>Now if I have a parameter called @.chk_account and it evaluates to NULL - I
>don't want to run the subquery at all.
>Using dynamic sql would go something like:
>DECLARE @.SQL varchar(1000)
>SET @.SQL = 'SELECT Id FROM tblAccounts a'
>IF @.chk_account IS NOT NULL
>BEGIN
>SET @.SQL = @.SQL +
>' WHERE EXISTS
>(SELECT NULL FROM tblSales s
>WHERE a.Id = s.AccountId)
>END
>EXEC(@.SQL)
>I thought maybe I could use the COALESCE function in some way?
>Dan
>
>
>
>
>|||Thanks CELKO for pointing out my naming conventions - you're right I come
from an OO language background and need to shake out of the habit.
Steve: I understand in your first selection how you are using @.chk_account
IS NOT NULL to filter the records correctly. However is the query still
processing the sub-query when it doesn't need to?
To get round this in your second solution you use if... else... to define
the SQL before beforehand
Are you saying its a toss-up between processing the if...else... arguments
or including the sub-query in the SQL and filtering down the result with
@.chk_account.
Dan
"Steve Kass" wrote:

> Dan,
> Depending on where this is used, one or another
> solution may be more efficient. Here are a couple alternatives
> to Joe's suggestion:
> select Id
> from tblAccounts a
> where @.chk_account is not null
> and exists (
> select * from tblSales s
> where a.Id = s.AccountId
> )
> union all
> select Id
> from tblAccounts a
> where @.chk_account is null
> --
> if @.chk_account is null
> select Id from tblAccounts a
> else
> select Id from tblAccounts a
> where exists (...)
> Steve Kass
> Drew University
>
> dan-cat wrote:
>
>|||Dan,
I can't say whether or not the query processor will recognize that it does
not have to process the exists clause when @.chk_account is null, so the
only thing you can do is try and see what happens. The if - then choice
may be good for an ad hoc query, but I believe that if you use it in a
stored procedure, the optimizer will try to generate a single query plan
that includes both queries. Depending on the first actual parameter it
receives, it may not produce the best plan.
I wish I could say more, but it's not cut and dried, as far as I know.
SK
dan-cat wrote:
>Thanks CELKO for pointing out my naming conventions - you're right I come
>from an OO language background and need to shake out of the habit.
>Steve: I understand in your first selection how you are using @.chk_account
>IS NOT NULL to filter the records correctly. However is the query still
>processing the sub-query when it doesn't need to?
>To get round this in your second solution you use if... else... to define
>the SQL before beforehand
>Are you saying its a toss-up between processing the if...else... arguments
>or including the sub-query in the SQL and filtering down the result with
>@.chk_account.
>Dan
>
>"Steve Kass" wrote:
>
>

Saturday, February 25, 2012

coalesce

Hi,
How can i display a integer value to it's description i a lookuptable? I did
something like this, but i still got the integer value.
select coalesce(myIntColumn, (select MyIntDescription from descriptions)
from payments
What is wrong with this?First of all, a parenth. is missing..

> select coalesce(myIntColumn, (select MyIntDescription from descriptions))
> from payments
Second, you should know that you are only able to get back ONE Value. If
dont know if your query mentioned above can handle this, i think it will
return more than one value.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Jason" <jasonlewis@.hotrmail.com> schrieb im Newsbeitrag
news:ug%235OHlRFHA.3544@.TK2MSFTNGP12.phx.gbl...
> Hi,
> How can i display a integer value to it's description i a lookuptable? I
> did
> something like this, but i still got the integer value.
> select coalesce(myIntColumn, (select MyIntDescription from descriptions)
> from payments
>
> What is wrong with this?
>|||coalesce isnt designed for what you aare trying to do ( check BOL )
why not just join to your lookup table ? e.g.
select MYINTDESCRIPTION from PAYMENTS join DESCRIPTIONS on
PAYMENTS.MYINTCOLUMN = DESCRIPTIONS.INTCOLUMN
I would assume that the integer value in "descriptions" table is unique, so
put a clustered primary key on that...|||I assume you want a JOIN here:
SELECT P.myIntColumn, D.myIntDescription
FROM Payments AS P
INNER JOIN Descriptions AS D
ON P.myIntColumn = D.myIntColumn
David Portas
SQL Server MVP
--|||Jason
Use Northwind
select
coalesce(( select orderid from orders where orderid = 10248),
(select top 1 orderid from [order details]
where orderid = 10249
)
)
"Jason" <jasonlewis@.hotrmail.com> wrote in message
news:ug%235OHlRFHA.3544@.TK2MSFTNGP12.phx.gbl...
> Hi,
> How can i display a integer value to it's description i a lookuptable? I
did
> something like this, but i still got the integer value.
> select coalesce(myIntColumn, (select MyIntDescription from descriptions)
> from payments
>
> What is wrong with this?
>

Thursday, March 22, 2012

Collation and views

Monday, March 19, 2012

Collapsing Three Rows Into One with T-SQL Challange?

Collapsing Three Rows Into One with T-SQL Challange?

Collapsing Three Rows Into One with T-SQL Challange?

Collapse cell if empty and

Sunday, March 11, 2012

Code.SafeDivide Expression

Code table maintenance

Code Table Maintenance

Thursday, March 8, 2012

Code Question - Making a field Null

Code Question - Making a field Null

Code Question

Code Question

code problem..pls help me

code problem

Wednesday, March 7, 2012

Code - Integer, Nothing vs Zero

Coalesce Question

COALESCE help

coalesce does not seem to work

COALESCE and subquery

Saturday, February 25, 2012

coalesce

Clustered DB Server

Blog Archive

About Me