Query Help

Question

Query Help

gksharmaajmer

Ten Centuries

Points: 1120
More actions
November 6, 2013 at 11:23 pm

#100166

Hi,
SQL Server Version 2000
create table books(book_name varchar(10),code varchar(3),qty int,day varchar(10))
insert into books values('.Net',null,null,'x')
insert into books values('Book1','NBC',2,'Mon')
insert into books values('Book2','NAA',1,'Wed')
insert into books values('Book3','NBA',3,'Tue/Mon')
insert into books values('Oracle',null,null,'x')
insert into books values('Book1','OB1',1,'D')
insert into books values('Book2','OXZ',1,'Fri')
insert into books values('Book3','OPS',2,'Sun')
insert into books values('Book4','OPR',1,'Thu')
insert into books values('Java',null,null,'x')
insert into books values('Book1','JPS',1,'D')
insert into books values('Book2','JJJ',4,'Sat')
insert into books values('PHP',null,null,'x')
insert into books values('Book1','PMW',3,'Wed/Tue')
insert into books values('Book2','PHY',2,'D')
insert into books values('Book3','PED',1,'Fri')
.NetNULLNULLx
Book1NBC2Mon
Book2NAA1Wed
Book3NBA3Tue/Mon
OracleNULLNULLx
Book1OB11D
Book2OXZ1Fri
Book3OPS2Sun
Book4OPR1Thu
JavaNULLNULLx
Book1JPS1D
Book2JJJ4Sat
PHPNULLNULLx
Book1PMW3Wed/Tue
Book2PHY2D
Book3PED1Fri
Required Output:
Suppose today is Thursday and when I select the data output should be like this :
Book Name Code QTY Day
Oracle
Book1 OB1 1 D
Book4 OPR 1 Thu
Java
Book1 JPS 1 D
PHP
Book2 PYH 2 D
Means, all those rows who have ('D' or first three characters of the system day or 'X' in the day) AND only those 'x' marked rows after which subsequent day column have either 'D' or first three characters i.e. in above example .Net category don't have 'D' and Thu in the day column but it have 'x' marked in the day column, so it should not be in the output because 'x' marked day rows will only appear if subsequent rows have 'D' or first three characters in the day column.
Actually, it is an excel question, which I have asked (http://stackoverflow.com/questions/19698572/excel-2003-advanced-filer), but if it is not possible in the excel, then I think sql server will do it for me.
I do understand that it is design flaw, because in the book_name column I am storing book name as well as its category, which is 100% wrong, but since this is my friend's question and I can not insist him to change the design or data, so if possible, is it possible by sql query in older version like 2000 please.
Kindly let me know, if I am unclear in my question or I need to provide more detail/explain.
Thanks and Regards
Girish Sharma

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Sean Pearce SSCoach Points: 15999 More actions · Answer 1

It's not possible with that design. You have no way to relate a book to a category at all. At the minimum you need a column to indicate the relationship between a book and a category.

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

gksharmaajmer Ten Centuries Points: 1120 More actions · Answer 2

Hi,

Thanks for your reply. I am fully agree with you that this is purely design flaw and since he can not do any changes in the table, so it looks like impossible, but what if I says :

alter table books add book_cat int

and then:

update books set book_cat=1 where code like 'N%'

update books set book_cat=2 where code like 'O%'

update books set book_cat=3 where code like 'J%'

update books set book_cat=4 where code like 'P%'

i.e. if his client allows to make change in the table then what will be SQL then ? Moreover, otherwise it will give me another good learning of sql.

Regards

Girish Sharma

Sean Pearce SSCoach Points: 15999 More actions · Answer 3

Even with a category your next problem is row order. You mention a requirement to identify subsequent rows but there is no column that will allow you to identify subsequent or preceding rows.

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

gksharmaajmer Ten Centuries Points: 1120 More actions · Answer 4

What if I forgets about row order too i.e. first I should get category name and then any order of books.

Regards

Girish Sharma

Sean Pearce SSCoach Points: 15999 More actions · Answer 5

This will work for your sample data

New table definition

-- Add an IDENTITY ID column and a CategoryID column

CREATE TABLE books

(ID INT IDENTITY(1, 1) NOT NULL,

CategoryID INT NULL,

book_name VARCHAR(10),

code VARCHAR(3),

qty INT,

day VARCHAR(10));

-- We need the category ID to insert the books

DECLARE @Category INT;

-- First insert the category

insert into books values(null,'.Net',null,null,'x')

-- And fetch the new ID into our variable

SELECT @Category = SCOPE_IDENTITY();

-- Then insert the variable value into the category column for all the books

insert into books values(@Category,'Book1','NBC',2,'Mon')

insert into books values(@Category,'Book2','NAA',1,'Wed')

insert into books values(@Category,'Book3','NBA',3,'Tue/Mon')

-- Repeat for each category

insert into books values(null,'Oracle',null,null,'x')

SELECT @Category = SCOPE_IDENTITY();

insert into books values(@Category,'Book1','OB1',1,'D')

insert into books values(@Category,'Book2','OXZ',1,'Fri')

insert into books values(@Category,'Book3','OPS',2,'Sun')

insert into books values(@Category,'Book4','OPR',1,'Thu')

insert into books values(null,'Java',null,null,'x')

SELECT @Category = SCOPE_IDENTITY();

insert into books values(@Category,'Book1','JPS',1,'D')

insert into books values(@Category,'Book2','JJJ',4,'Sat')

insert into books values(null,'PHP',null,null,'x')

SELECT @Category = SCOPE_IDENTITY();

insert into books values(@Category,'Book1','PMW',3,'Wed/Tue')

insert into books values(@Category,'Book2','PHY',2,'D')

insert into books values(@Category,'Book3','PED',1,'Fri')

Query

-- Fetch todays day into a variable

DECLARE @Day CHAR(5) = '%' + SUBSTRING(DATENAME(DW, GETDATE()), 1, 3) + '%'

-- Load all categories into a temp table. Keep the ID column for sorting and add 0 for a row number also for sorting

SELECT

book_name, code, qty, NULL AS day, ID, 0 AS RowNbr

INTO

#t

FROM

books b

WHERE

day = 'x'

AND EXISTS(SELECT * FROM books b2 WHERE b2.CategoryID = b.ID AND (day = 'D' OR day LIKE @Day))

UNION ALL

-- Load all books into the same temp table. Use the CategoryID column for sorting and the ID for a row number

SELECT

book_name, code, qty, day, CategoryID, ID

FROM

books

WHERE

day = 'D' OR day LIKE @Day

-- Select your data sorted by category followed by row number

SELECT

book_name,

code,

qty,

day

FROM

#t

ORDER BY

ID,

RowNbr

DROP TABLE #t;

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

gksharmaajmer Ten Centuries Points: 1120 More actions · Answer 6

Hi,

Thank you for your query. Kindly check is the below sql is correct or not :

update books set book_cat=1 where book_name='.Net'

update books set book_cat=2 where book_name='Oracle'

update books set book_cat=3 where book_name='Java'

update books set book_cat=4 where book_name='PHP'

and :

select * from books

where

(

day in ('D','x')

and

book_cat in

(

select distinct book_cat

from books where day in ('D')

or day like

'%' + SUBSTRING(DATENAME(DW, GETDATE()), 1, 3) + '%'

)

or day like

'%' + SUBSTRING(DATENAME(DW, GETDATE()), 1, 3) + '%'

)

It is giving me required output, I just request to please check and reply if it is right/wrong/it depend.

Regards

Girish Sharma