CODEWITHSUNDEEP
sqlsql-servert-sqlbit-manipulationuser-defined-functions
Martin Lebel
Martin Lebel

Reputation: 507

Using t-sql bitwise when querying directly a function that returns a table

The following function returns a list of availability for a given employee.

Sometimes, the data was saved in the database under two lines for the same date so I would like to perform a BITWISE OR (|) for the dates that are present more than one time (eg: 2014-11-27 in the following results)

SELECT * FROM dbo.fnWebGetAvailability('10436837-7AA9-4012-BE66-CA33D0FA084D','2014-11-25','2014-11-30')

MyAvailabilityDate Night Day   Evening NA    Vacation
------------------ ----- ----- ------- ----- --------
2014-11-25         0     1     1       0     0
2014-11-26         0     0     0       0     0
2014-11-27         0     0     1       0     0
2014-11-27         1     0     0       0     0
2014-11-28         0     0     0       0     0
2014-11-29         0     0     0       0     0
2014-11-30         0     0     0       0     0

My current work arround was to GROUP BY MyAvailabilityDate and perform a SUM in the SELECT list. That's only working correctly when implemented directly in the function but not working when querying directly the function.

SELECT MyAvailabilityDate, SUM(CAST(Night AS int)),SUM(CAST(Day as int)),SUM(CAST(Evening as int)),NA,Vacation 
FROM dbo.fnWebGetAvailability('10436837-7AA9-4012-BE66-CA33D0FA084D','2014-11-25','2014-11-30') GROUP BY MyAvailabilityDate,CAST(Night AS int),CAST(Day as int),CAST(Evening as int),NA,Vacation

I don't like this implementation because the columns data type needed to be changed from BIT to INT and I'll later have to remember to use >= 1 instead of just = 1.

How can I use BITWISE OR when querying directly my function ?

Edit: I don't think it is necessary but here's the code of the function:

USE [MyDataBase]
GO

/****** Object:  UserDefinedFunction [dbo].[fnWebGetAvailability]    Script Date: 2014-07-14 10:51:38 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


ALTER FUNCTION [dbo].[fnWebGetAvailability] 
(
@MyUserID Uniqueidentifier
,@MyAvailabilityDateFrom DATE
,@MyAvailabilityDateTo DATE
)

RETURNS @MaTable TABLE
(
MyAvailabilityDate DATE,
Night INT,
Day INT,
Evening INT,
NA INT,
Vacation INT
)

AS
BEGIN

DECLARE @MyUserStreamID UNIQUEIDENTIFIER = (SELECT ID FROM UserStreams WHERE USerID = @MyUserID AND DefaultStream = 1)
INSERT INTO @MaTable 

SELECT dt.Date AS MyAvailabilityDate
    ,SUM(ISNULL(CAST(usd.N AS int),0)) AS Night
    ,SUM(ISNULL(CAST(usd.J AS int),0)) AS Day
    ,SUM(ISNULL(CAST(usd.S AS int),0)) AS Evening
    ,CASE WHEN EXISTS (SELECT NULL FROM UserHolidays as us1 WHERE us1.UserStreamID = @MyUserStreamID AND us1.FromDate = dt.date AND (Description IS NULL OR Description LIKE '')) THEN 1
        ELSE 0 END AS NA
    ,CASE WHEN EXISTS (SELECT NULL FROM UserHolidays as us2 WHERE us2.UserStreamID = @MyUserStreamID AND us2.FromDate = dt.date AND Description LIKE 'Vacation') THEN 1
        ELSE 0 END AS Vacation
    FROM dbo.fnDatesTable(@MyAvailabilityDateFrom,@MyAvailabilityDateTo) As dt
        LEFT JOIN UserSpecialDays as usd on dt.Date = usd.FromDate and usd.UserStreamId = @MyUserStreamID
        LEFT JOIN UserHolidays as uh on dt.Date = uh.FromDate and uh.UserStreamId = @MyUserStreamID 
    GROUP BY dt.Date
RETURN 
END
GO

Upvotes: 0

Views: 63

Answers (2)

limit
limit

Reputation: 31

BITWISE OR aggregate can be simulated using next trick:

SELECT 
   MAX(field&1)+MAX(field&2)+MAX(field&4)+...+MAX(field&1073741824)+MIN(field&0x80000000) AS bitwise_or_of_field
 FROM
    tbl

(replace ellipsis with other missing powers of two)

It's very redundantly but works.

Upvotes: 0

Anon
Anon

Reputation: 10908

Use MAX() instead of SUM()

SELECT MyAvailabilityDate, MAX(CAST(Night AS int)),MAX(CAST(Day as int)),MAX(CAST(Evening as int)),NA,Vacation 
FROM dbo.fnWebGetAvailability('10436837-7AA9-4012-BE66-CA33D0FA084D','2014-11-25','2014-11-30') GROUP BY MyAvailabilityDate,CAST(Night AS int),CAST(Day as int),CAST(Evening as int),NA,Vacation

Upvotes: 1

Related Questions