1

Im trying to create the derived attribute availability in the item table, in order to do that do i need to create a join?

Availability Checks if todays date is between checkoutDate and returndate.

Here is the ER diagram:

enter image description here

Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
Dynamiite
  • 1,419
  • 5
  • 21
  • 28

2 Answers2

2

If you want to use this as a computed column, then I would recommend using a UDF. In your diagram, you have Availability as a boolean. SQL Server does not have a boolean data type, so I assume you mean a bit. This should be pretty close, but you'd need to test it:

CREATE FUNCTION dbo.GetValue
(
    @barcode int
)
RETURNS bit
AS
BEGIN
    DECLARE @field bit
     SELECT @field = CASE WHEN GETDATE() 
                BETWEEN L.LastReturnDate AND R.ReturnDate THEN 1 ELSE 0 END
       FROM [Item] I
        JOIN [Load] L ON I.barcode = L.barcode
        JOIN [Return] R ON I.barcode = R.barcode
       WHERE I.barcode = @barcode
      RETURN @field
END
GO

And then using that as your computed column:

ALTER TABLE dbo.Item
ADD Availability AS dbo.GetValue(barcode)

With that said, this might be better suited for a View.

sgeddes
  • 62,311
  • 6
  • 61
  • 83
  • ALTER TABLE dbo.Item ADD Availability AS dbo.GetValue(barcode) Gives me the following error Msg 2705, Level 16, State 5, Line 1 Column names in each table must be unique. Column name 'availability' in table 'dbo.Item' is specified more than once. – Dynamiite Feb 24 '13 at 00:52
  • 1
    @Sebastian -- unfortunately you can't alter an existing column -- the error you're receiving is because the above is trying to add a new column. You'll need to drop and readd that column -- check this post out: http://stackoverflow.com/questions/2372655/how-can-i-alter-this-computed-column-in-sql-server-2008 -- best of luck! – sgeddes Feb 24 '13 at 00:58
1

Yes, you need to do a join.

I would recommend that you use a view to put together the data from the different tables.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786