Re: Search for a character in a column

On Fri, 15 Dec 2006 02:58:01 -0800, Rikard Knutsson wrote:

I have a column that shows one character for each day in a year, it is either
Y or N 365 times
eg. the 2 Januari will show NYNNNNNNNNN.....

I want to do a query that shows how many Y there are in a row and what place
it have.

Can someone help me with that?

Rikard

Hi Rikard,

The best you can do is probably to change your design. If this is, for
instance, for room reservations in a year and your current design is:

CREATE TABLE Rooms
(RoomID int NOT NULL,
RoomReserved char(365) NOT NULL DEFAULT(REPLICATE('N', 365)),
-- Other columns,
CONSTRAINT PK_Rooms
PRIMARY KEY (RoomID)
);

Then you should change it to

CREATE TABLE Rooms
(RoomID int NOT NULL,
-- Other columns,
CONSTRAINT PK_Rooms PRIMARY KEY (RoomID)
);
CREATE TABLE RoomReservations
(RoomID int NOT NULL,
DateReserved smalldatetime NOT NULL,
-- Other columns,
CONSTRAINT PK_RoomReservations
PRIMARY KEY (RoomID, DateReserved),
CONSTRAINT FK_RoomReservations_Rooms
FOREIGN KEY (RoomID) REFERENCES Rooms(RoomID)
);

Of course, since I know next to nothing about yoour real problem, I
can't fully exclude the possibility that your current design is actually
the best for your problem (however unlikely it may be). In that case,
the answer to your question is:

SELECT RoomID,
LEN(REPLACE(DateReserved, 'N', '')) AS NumberOfYs
FROM Rooms;

This relies on the data really only having Y or N on each of the 365
available positions. If there might be other characters as well and you
only want the number of Y's, change the query to

SELECT RoomID,
LEN(DateReserved) - LEN(REPLACE(DateReserved, 'Y', '')) AS
NumberOfYs
FROM Rooms;


--
Hugo Kornelis, SQL Server MVP
.

Relevant Pages

  • Re: Query using two columns
    ... >I now want to try an construct a query that will show a row for every ... CREATE TABLE Numbers (Number int not null primary key) ... DECLARE @MaxNumber int ... WHERE RoomID = 'Big Room' ...
    (microsoft.public.sqlserver.programming)
  • Re: Help updating table with data from another table
    ... What is the primary key in your table? ... In tblEmployee you have a field called tblEmployee? ... > tblEmployee has L_NAME, F_NAME, RoomID, Room, and tblEmployee. ... > tblSheet1 has the same except it doesn't have RoomID. ...
    (microsoft.public.access.queries)
  • Re: Search
    ... >> Each tables has a primary key called RoomID. ... Well, that doesn't sound right. ... Tim F ...
    (microsoft.public.access.modulesdaovba)
  • Re: Query Help Please - Consecutive Dates
    ... > CustomerID RoomID ArriveDateID DepartDateID ... into Query Analyzer. ... DROP TABLE bookedrooms ...
    (comp.databases.ms-sqlserver)
  • Re: Booking System - Vacant room report
    ... (SELECT DISTINCT RoomID ... If a room is booked for any one night in the range in the sub query, ... generate a report based on the top 30 records of any table but having ...
    (comp.databases.ms-access)