incorrect result with this stored procedure..

Question

incorrect result with this stored procedure..

sqlnewby

Right there with Babe

Points: 797
More actions
November 9, 2004 at 8:45 am

#61365

hi
i have this sp which returns any conflicts in my bookings and group
them together so it would be easy for me to manipulate them at my front
end
i am having couple of problems with this query which i couldn't solve
here i will explain with some data
sttime    edtime    GroupId
09.00    17.30   3
10:00    12:30   3
17.30    18:00   4
according to the data these three should be grouped together but my query is grouping the first two and giving them the same Groupid and leaving the last one with different GroupId
another problem is with dynamic sort which is not allowing as it is
saying columns doesn't match with distinct error
any thoughts would be very greatful
CREATE Procedure spconflictBK(@Startdate as varchar(8),@Enddate as varchar(8),@viewId as int,@SortOrder tinyint)
--with encryption
as
-- dynamic order by and checking with all the resources in the view
set nocount on
declare @errorcode int
select @errorcode = @@Error
if @errorcode = 0
begin
create table #groups ( [ID] int IDENTITY( 1, 1 ),
        col1 datetime,
         col2 datetime
&nbsp
insert into #groups ( col1, col2 )
  select b.col1, b.col2
from Bookings a
        inner join
        (
            select b1.BookingId,
                    ISNULL( MIN( b.Start ), CAST( '' AS datetime ) ) AS col1,
                    ISNULL( MAX( b.Finish ), CAST( '' AS datetime ) ) AS col2
             from Bookings b1
                inner join resources r1 on b1.ResourceId=r1.ResourceId
                left join Bookings b on b.Finish < b1.Start
                left join customers c1 on b1.customerId=c1.CustomerId
                left join viewItems vi on b1.ResourceId=vi.ResourceId
            where exists( select BookingId, start, finish, resourceID
                  from Bookings b2
                where b1.Bookingid <> b2.Bookingid and
                             b1.start <= b2.finish and
                             b1.finish >= b2.start AND
                             b2.ResourceID = b1.ResourceID )
            and b1.start >=@Startdate and b1.Finish <= @Enddate and vi.viewId=@viewId
            group BY b1.BookingId
        ) AS b on b.BookingId = a.BookingId
        where exists (
        select BookingId from Bookings b2
         where a.Bookingid <> b2.Bookingid and a.start <= b2.finish and a.finish >= b2.start AND b2.ResourceID = a.ResourceID
        )
group BY col1, col2
        order by col1, col2
        select distinct a.BookingId,
       convert( varchar(10),a.Start,120) AS Startdate,
  convert( varchar(10), a.Finish, 120 ) AS EndDate,
         convert( varchar(5),a.Start,108) AS StTime,
         convert( varchar(5),a.Finish,108) AS edtime,
  c1.Name AS Customer,
         r1.Name AS ResName,
         bt1.Name as BookingType,U1.Name Madeby,Jobs.JobName JobName,
         #groups.[ID] AS Group_ID
from Bookings a
inner join (
               select b1.BookingId,
                        ISNULL( MIN( b.Start ), CAST( '' AS datetime ) ) AS col1,
                        ISNULL( MAX( b.Finish ), CAST( '' AS datetime ) ) AS col2
                 from Bookings b1
                    inner join resources r1 on b1.ResourceId=r1.ResourceId
                    left join Bookings b on b.Finish < b1.Start
                    left join viewItems vi on b1.ResourceId=vi.ResourceId
                where exists( select BookingId
                              from Bookings b2
                              where b1.Bookingid <> b2.Bookingid and
                                    b1.start <= b2.finish and
                                    b1.finish >= b2.start AND
                                    b2.ResourceID = b1.ResourceID )
               and b1.start >=@Startdate and b1.Finish <= @Enddate and vi.viewId=@viewId
               group BY b1.BookingId
              ) AS b on b.BookingId = a.BookingId
       inner join #Groups on #Groups.col1 = b.col1 AND #Groups.col2 = b.col2
       inner join resources r1 on a.ResourceId=r1.ResourceId
       left join customers c1 on a.customerId=c1.CustomerId
       left join viewItems vi on a.ResourceId=vi.ResourceId
       inner join BookingTypes bt1 on a.BookingTypeId=bt1.BookingTypeId
       inner join Users u1 on a.MadeBy=u1.UserId
       left join JobBookings jb on a.BookingId=jb.BookingId
       left join centraltime.dbo.Jobs as Jobs on Jobs.JobCode = jb.JobCode
       order by Group_ID,case
  when @SortOrder=1 then convert(varchar(10),a.Start,120)
  when @SortOrder=6 then convert(varchar(5),a.Start,108)
  when @SortOrder=7 then convert(varchar(5),a.Finish,108)
  when @SortOrder=2 then r1.Name
  when @SortOrder=3 then bt1.Name
  when @SortOrder=4 then c1.Name
  when @SortOrder=5 then Jobs.JobName
  when @SortOrder=8 then U1.Name
  else convert(varchar(10),a.Start,120)
endselect @errorcode = @@Error
end
return @errorcode
GO

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736254 More actions · Answer 1

Wow, that's some code

Haven't read all through it, but the left joins always make me worry about non-matches. That could be the issue. Any chance you could break this into separate statements? Even ones you can UNION?

Is this real time or something that can be delayed? The reason I'm asking is maybe you want to "stage" some of this info in a temp table or perm table in your db to simplify the data cleansing.

sqlnewby Right there with Babe Points: 797 More actions · Answer 2

thanks for responding steve

i know it is a bit long and hard to go through but that's the way the sp is and i couldn't figure out what was wrong with it.

i can't go for seperate table solution and the only solution i found is to use the temp tables as i am doing now.

here is the entire sp which i have seperated the two select statements one will store the data to temp table and other select will inner join the temp table

CREATE Procedure spconflictBK(@Startdate as varchar(8),@Enddate as varchar(8),@viewId as int,@SortOrder tinyint)

--with encryption

as

-- dynamic order by and checking with all the resources in the view

set nocount on

declare @errorcode int

select @errorcode = @@Error

if @errorcode = 0

begin

/*#groups temp table */

create table #groups ( [ID] int IDENTITY( 1, 1 ),

col1 datetime,

col2 datetime

insert into #groups ( col1, col2 )

select b.col1, b.col2

from Bookings a

inner join

(

select b1.BookingId,

ISNULL( MIN( b.Start ), CAST( '' AS datetime ) ) AS col1,

ISNULL( MAX( b.Finish ), CAST( '' AS datetime ) ) AS col2

from Bookings b1

inner join resources r1 on b1.ResourceId=r1.ResourceId

left join Bookings b on b.Finish < b1.Start

left join customers c1 on b1.customerId=c1.CustomerId

left join viewItems vi on b1.ResourceId=vi.ResourceId

where exists( select BookingId, start, finish, resourceID

from Bookings b2

where b1.Bookingid <> b2.Bookingid and

b1.start <= b2.finish and

b1.finish >= b2.start AND

b2.ResourceID = b1.ResourceID )

and b1.start >=@Startdate and b1.Finish <= @Enddate and vi.viewId=@viewId

group BY b1.BookingId

) AS b on b.BookingId = a.BookingId

where exists (

select BookingId from Bookings b2

where a.Bookingid <> b2.Bookingid and a.start <= b2.finish and a.finish >= b2.start AND b2.ResourceID = a.ResourceID

)

group BY col1, col2

order by col1, col2

/*inner join the #groups temp table */

select distinct a.BookingId,

convert( varchar(10),a.Start,120) AS Startdate,

convert( varchar(10), a.Finish, 120 ) AS EndDate,

convert( varchar(5),a.Start,108) AS StTime,

convert( varchar(5),a.Finish,108) AS edtime,

c1.Name AS Customer,

r1.Name AS ResName,

bt1.Name as BookingType,U1.Name Madeby,Jobs.JobName JobName,

#groups.[ID] AS Group_ID

from Bookings a

inner join (

select b1.BookingId,

ISNULL( MIN( b.Start ), CAST( '' AS datetime ) ) AS col1,

ISNULL( MAX( b.Finish ), CAST( '' AS datetime ) ) AS col2

from Bookings b1

inner join resources r1 on b1.ResourceId=r1.ResourceId

left join Bookings b on b.Finish < b1.Start

left join viewItems vi on b1.ResourceId=vi.ResourceId

where exists( select BookingId

from Bookings b2

where b1.Bookingid <> b2.Bookingid and

b1.start <= b2.finish and

b1.finish >= b2.start AND

b2.ResourceID = b1.ResourceID )

and b1.start >=@Startdate and b1.Finish <= @Enddate and vi.viewId=@viewId

group BY b1.BookingId

) AS b on b.BookingId = a.BookingId

inner join #Groups on #Groups.col1 = b.col1 AND #Groups.col2 = b.col2

inner join resources r1 on a.ResourceId=r1.ResourceId

left join customers c1 on a.customerId=c1.CustomerId

left join viewItems vi on a.ResourceId=vi.ResourceId

inner join BookingTypes bt1 on a.BookingTypeId=bt1.BookingTypeId

inner join Users u1 on a.MadeBy=u1.UserId

left join JobBookings jb on a.BookingId=jb.BookingId

left join centraltime.dbo.Jobs as Jobs on Jobs.JobCode = jb.JobCode

order by Group_ID,case

when @SortOrder=1 then convert(varchar(10),a.Start,120)

when @SortOrder=6 then convert(varchar(5),a.Start,108)

when @SortOrder=7 then convert(varchar(5),a.Finish,108)

when @SortOrder=2 then r1.Name

when @SortOrder=3 then bt1.Name

when @SortOrder=4 then c1.Name

when @SortOrder=5 then Jobs.JobName

when @SortOrder=8 then U1.Name

else convert(varchar(10),a.Start,120)

end

select @errorcode = @@Error

end

return @errorcode

GO

thanks for any help

sqlnewby Right there with Babe Points: 797 More actions · Answer 3

i have found what causing this

here is what i have changed in my sp

b1.Start <=b.Finish

but still getting the error when i use dynamic order by clause in my sp

it is giving this error

ORDER BY items must appear in the select list if SELECT DISTINCT is specified

i am using this statement

CODE

order by Group_ID,case

when @SortOrder=1 then convert(varchar(10),a.Start,120)

when @SortOrder=6 then convert(varchar(5),a.Start,108)

when @SortOrder=7 then convert(varchar(5),a.Finish,108)

when @SortOrder=2 then r1.Name

when @SortOrder=3 then bt1.Name

when @SortOrder=4 then c1.Name

when @SortOrder=5 then Jobs.JobName

when @SortOrder=8 then U1.Name

else convert(varchar(10),a.Start,120)

end

can any one shed some light on to this

thanks

David Burrows SSC Guru Points: 65142 More actions · Answer 4

You cannot ORDER BY on a column alias

try changing GROUP_ID to #groups.[ID] in the ORDER BY clause

Far away is close at hand in the images of elsewhere.
Anon.

sqlnewby Right there with Babe Points: 797 More actions · Answer 5

thanks David Burrows but this didn't work

and also i said earlier that i had solved my problem but this statement b1.Start <=b.Finish is also not giving me correct results..any clue what i am doing wrong..

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

can you post the table data along with the expected results and the current results so we can work out the join?

sqlnewby Right there with Babe Points: 797 More actions · Answer 7

here is my data looks like

BookingId start finish

1 13/10/2004 09:00:00 13/10/2004 17:30:00

2 13/10/2004 10:00:00 13/10/2004 12:30:00

3 13/10/2004 17:30:00 13/10/2004 18:00:00

4 08/10/2004 09:00:00 08/10/2004 18:00:00

5 08/10/2004 13:30:00 08/10/2004 18:00:00

and expect

Bookignid start end sttime edtime GroupId

1 2004-10-11 2004-10-11 09:00 17:30 1

2 2004-10-11 2004-10-11 10:00 12:30 1

3 2004-10-11 2004-10-11 17:30 18:00 1

4 2004-10-11 2004-10-11 09:00 18:00 2

5 2004-10-11 2004-10-11 13:30 18:00 2

i almost got near to my result but

i think i am missing out on this line in my sp

left join Bookings b on b.Finish < b1.Start

thanks

sqlnewby Right there with Babe Points: 797 More actions · Answer 8

sorry haven't given the current result

here you go

actual data

BookingId start finish

1 13/10/2004 09:00:00 13/10/2004 17:30:00

2 13/10/2004 10:00:00 13/10/2004 12:30:00

3 13/10/2004 17:30:00 13/10/2004 18:00:00

4 08/10/2004 09:00:00 08/10/2004 18:00:00

5 08/10/2004 13:30:00 08/10/2004 18:00:00

current result

if i use b1.Start <=b.Finish then

i am missing the Grouping of 11/10/2004 and getting the result as

Bookignid start end sttime edtime GroupId

1 2004-10-11 2004-10-11 09:00 17:30 1

2 2004-10-11 2004-10-11 10:00 12:30 2

3 2004-10-11 2004-10-11 17:30 18:00 2

4 2004-10-08 2004-10-08 09:00 18:00 3

5 2004-10-08 2004-10-08 13:30 18:00 3

if i use b1.Finish >=b.start then

i am missing the Grouping of 08/10/2004 and getting the result as

Bookignid start end sttime edtime GroupId

1 2004-10-11 2004-10-11 09:00 17:30 1

2 2004-10-11 2004-10-11 10:00 12:30 1

3 2004-10-11 2004-10-11 17:30 18:00 1

4 2004-10-08 2004-10-08 09:00 18:00 2

5 2004-10-08 2004-10-08 13:30 18:00 3

expected result

Bookignid start end sttime edtime GroupId

1 2004-10-11 2004-10-11 09:00 17:30 1

2 2004-10-11 2004-10-11 10:00 12:30 1

3 2004-10-11 2004-10-11 17:30 18:00 1

4 2004-10-08 2004-10-08 09:00 18:00 2

5 2004-10-08 2004-10-08 13:30 18:00 2

thanks

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

I'm not expert at non-equal joins and I jut can't see the problem.. hopefully somebody else will find the solution.

Good luck.