Create array from fields in a record

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Create array from fields in a record

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-08-18 : 06:28:32

I have a table (SQL Server) of records which contains the following fields

RoomRef, DateRequired, P1, P2, P3, P4.... to P24

The P1, P2 etc represent half hour time slots. eg P1 = 7.30 - 8.00, P2 = 8.00 - 8.30
They hold either 1 or 0, 1 meaning that time slot is booked ,0 that it isn't.

What I want to do is write a stored procedure which will take each record in turn and check P1 to P24 and if any of these are 0 display the record in a datagrid.How do I write the SQL code to step through each field to select the records ?

I did it in an Access module as below, passing the fields from a query. However this is now being rewritten as a web app and with the data stored on SQL Server.

Public Function Test_for_Null_Record(ParamArray strPs() As Variant) As Boolean

Dim I As Integer

For I = 0 To UBound(strPs())

If (strPs(I)) = -1 Then
Test_for_Null_Record = False
Exit Function
End If

Next I
Test_for_Null_Record = True

End Function

Kristen
Test

22859 Posts

Posted - 2005-08-18 : 06:44:59

Something like this?


SELECT *
FROM MyTable
WHERE P1 = 0
      OR P2 = 0
      OR P3 = 0
...

Kristen

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-08-18 : 06:48:12

[code]SELECT
mt.RoomRef,
mt.DateRequired,
mt.P1,
...
mt.P24
FROM
dbo.MyTable AS mt
WHERE
NOT(
mt.P1 = 1
AND mt.P2 = 1
...
AND mt.P24 = 1
)[/code]

Normalising this would be a good way to go!

Mark

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-08-18 : 06:50:39

d'oh!

Mark

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-08-18 : 07:02:59

Thanks for your replies. Since posting I was looking at creating a view where I could have an expression CheckIfBooked(P1,P2,P3,P4....P24) and in the criteria of this expression have 0. CheckIfBooked being a function similar to the one I used in Access.

I accept your versions, but was trying to cut down on the typing as I will use this check many times in this system. I don't know where to write the function nor how to adapt it to sql .... duhhhhh !

Kristen
Test

22859 Posts

Posted - 2005-08-18 : 07:03:24

How did you know the table was called MyTable?

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-18 : 07:13:47

Not sure whether you needed this


Declare @sql varchar(2000)
set @sql=''
select  @sql=isnull(@sql,'')+' or '+column_name + ' = 0 ' from information_schema.columns
where table_name='tableName' and column_name not in ('roomref','daterequired')
Set @sql='Select * from tableName where '+ substring(@sql,4,len(@sql))
Exec(@sql)

Madhivanan

Failing to plan is Planning to fail

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-08-18 : 07:36:59

Pure intuition

quote:
Originally posted by Kristen

How did you know the table was called MyTable?

Kristen

Mark

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-08-18 : 07:43:02

If you build a view, you'd only need to do this once, then always refer to the view.
The view could just present another column (probably of BIT type called something like 'Booked') that did the evaluation.
Your queries could then just contain a where clause something like

WHERE Booked = 0

quote:
Originally posted by Pinto

Thanks for your replies. Since posting I was looking at creating a view where I could have an expression CheckIfBooked(P1,P2,P3,P4....P24) and in the criteria of this expression have 0. CheckIfBooked being a function similar to the one I used in Access.

I accept your versions, but was trying to cut down on the typing as I will use this check many times in this system. I don't know where to write the function nor how to adapt it to sql .... duhhhhh !

Mark

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-08-18 : 09:40:41

How about this idea guys - I change my field type to numeric and then create an expression with adds together P1 to P24. I then check if the total is greater than 0 - if it is then some of the time slots must be booked,if it's 24 then they are all booked.

Kristen
Test

22859 Posts

Posted - 2005-08-18 : 11:43:56

That's just as hard as typing all those ORs isn't it?

Plus you'd have to watch out for any NULLS.

You COULD have a calculated field in the table that did that trick (or is that maybe what you meant?)

I hate calculated fields in tables, but.

If it was me I'd use a VIEW as mwjdavidson suggested

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources