Making QUErY from stored procedure to SQL

albert albert

13y1.8kSQL

HI everybody,

I have this:

[code]
OPEN @WeekdayCursor
--
--SET @Weekdays = ''
--FETCH NEXT FROM @WeekdayCursor
--INTO @PriceAddID, @StartDate, @EndDate, @Weekdays
--
--WHILE @@FETCH_STATUS = 0
--BEGIN
--DECLARE @Position int
-- SET @Position = 0
--
-- DECLARE @NumDays int
-- SET @NumDays = DATEDIFF(day, @StartDate, @EndDate)
--
-- DECLARE @CurrentDate datetime
-- DECLARE @CurrentBWeekday int
-- DECLARE @Weekday int
--
-- WHILE (@Position < @NumDays + 1)
-- BEGIN
-- SET @CurrentDate = DATEADD(day, @Position, @StartDate)
-- -- Remember, SET DATEFIRST 1 for Monday
-- SET @CurrentBWeekday = dbo.f_BWeekday(@CurrentDate)
-- -- Validate the weekday
-- --SET @Position = @Position + 1
-- IF (@Weekdays > 0 AND @CurrentBWeekday & @Weekdays <> @CurrentBWeekday)
-- BEGIN
-- SET @Position = @Position + 1
-- CONTINUE
-- END
-- SET @Weekday= (SELECT dbo.f_Bweekday(@CurrentDate))
-- INSERT INTO #weekdayprice VALUES(@PriceAddID, @StartDate, @EndDate, @Weekday)
-- SET @Position = @Position + 1
-- END
--
----Logic
--FETCH NEXT FROM @WeekdayCursor
--INTO @PriceAddID, @StartDate, @EndDate, @Weekdays
--END
--
--CLOSE @WeekdayCursor
--DEALLOCATE @WeekdayCursor
[/code]

But I want to make a query of it. Because If you execute the weekDayCursor then the performance is very slow, like 20 minutes.

THX

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

albert albert13y ago
I have it now like this:

[code]
SELECT
    DISTINCT(PriceAdd.PriceAddID),
dbo.f_Bweekday(
    DATEADD(day,
            ROW_NUMBER() OVER(Order by PriceAdd.PriceAddID),
            PriceAdd.DateFrom)) as 'Weekdays'

,
CASE
dbo.f_Bweekday(
        DATEADD(day,
            ROW_NUMBER() OVER(Order by PriceAdd.PriceAddID),
            PriceAdd.DateFrom)
    )

    WHEN 0 THEN 'none'
                WHEN 1 THEN 'mo'
                WHEN 2 THEN 'tu'
                WHEN 4 THEN 'we'
                WHEN 8 THEN 'th'
                WHEN 16 THEN 'fr'
                WHEN 32 THEN 'sa'
                WHEN 64 THEN 'su'
end

as 'Name of Day ',
datediff(day, DateFrom, DateTo)

    --PriceAdd.DateFrom,
    --PriceAdd.DateTo,
    --PriceAdd.Weekdays
FROM

(Cost INNER JOIN
                      TripElement INNER JOIN
                      PriceAdd ON TripElement.TripElementID = PriceAdd.TripElementID ON Cost.CostID = PriceAdd.CostID LEFT OUTER JOIN
                      TripEBoarding INNER JOIN
                      ANVRBoardingType ON TripEBoarding.BoardingTypeID = ANVRBoardingType.BoardingTypeID ON TripElement.TripElementID = TripEBoarding.TripElementID AND
                      PriceAdd.TripEBoardingID = TripEBoarding.TripEBoardingID LEFT OUTER JOIN
                      ANVRSegmentAttribType INNER JOIN
                      TripElementAttr ON ANVRSegmentAttribType.SegmentAttribTypeID = TripElementAttr.SegmentAttribTypeID ON
                      TripElement.TripElementID = TripElementAttr.TripElementID AND PriceAdd.TripElementAttrID = TripElementAttr.TripElementAttrID
FROM

(PriceAddID sc1
                             CROSS JOIN weekdayss sc2
                             ) x
)

WHERE

(TripElement.SegmentTypeCode = N'AR') AND (PriceAdd.IsExported = 0) AND (PriceAdd.PresentationLevel = 0)
AND PriceAdd.DateTo BETWEEN GETDATE() AND PriceAdd.DateTo

order by priceaddid
[/code]

But now I want to make a crossjoin.

But this doest work - see above.
Thanks
albert albert13y ago
No it is not working,

but do you have a concrete example?

Thanks
Suthish Nair13y ago
So, is it working now?
Jignesh Trivedi13y ago
hi,
I think, you are use cursor only for find out the value of @Weekday parameter value am I right?
In this case you can write common function that process your data and return week day data...
and that function is used with your select query....
in this way you can remove cursor.
is it make sense?
albert albert13y ago
Yes, I try something like this:

[code]
SELECT DISTINCT(weekdays), PriceAddID,
CASE weekdays    WHEN 0 THEN 'none'
                WHEN 1 THEN 'mo'
                WHEN 2 THEN 'tu'
                WHEN 4 THEN 'we'
                WHEN 8 THEN 'th'
                WHEN 16 THEN 'fr'
                WHEN 32 THEN 'sa'
                WHEN 64 THEN 'su'
END AS weekdayss
FROM
    Cost INNER JOIN
    TripElement INNER JOIN
    PriceAdd ON TripElement.TripElementID = PriceAdd.TripElementID ON Cost.CostID = PriceAdd.CostID LEFT OUTER JOIN
    TripEBoarding INNER JOIN
    ANVRBoardingType ON TripEBoarding.BoardingTypeID = ANVRBoardingType.BoardingTypeID ON TripElement.TripElementID = TripEBoarding.TripElementID AND
    PriceAdd.TripEBoardingID = TripEBoarding.TripEBoardingID LEFT OUTER JOIN
    ANVRSegmentAttribType INNER JOIN
    TripElementAttr ON ANVRSegmentAttribType.SegmentAttribTypeID = TripElementAttr.SegmentAttribTypeID ON
    TripElement.TripElementID = TripElementAttr.TripElementID AND PriceAdd.TripElementAttrID = TripElementAttr.TripElementAttrID
--ORDER BY PriceAddID
[/code]
Suthish Nair13y ago
One of the rule for better SQL Performance is that to avoid Cursors always. Try with simple Joins and CTE.
Jignesh Trivedi13y ago
hi,

Can please share cursor query also little bit of cursor logic...?