SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query Help


Query Help

Author
Message
gksharmaajmer
gksharmaajmer
SSC-Enthusiastic
SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)

Group: General Forum Members
Points: 184 Visits: 112
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')

.Net NULL NULL x
Book1 NBC 2 Mon
Book2 NAA 1 Wed
Book3 NBA 3 Tue/Mon
Oracle NULL NULL x
Book1 OB1 1 D
Book2 OXZ 1 Fri
Book3 OPS 2 Sun
Book4 OPR 1 Thu
Java NULL NULL x
Book1 JPS 1 D
Book2 JJJ 4 Sat
PHP NULL NULL x
Book1 PMW 3 Wed/Tue
Book2 PHY 2 D
Book3 PED 1 Fri



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
Sean Pearce
Sean Pearce
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4162 Visits: 3436
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

@SeanPearceSQL

About Me
gksharmaajmer
gksharmaajmer
SSC-Enthusiastic
SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)

Group: General Forum Members
Points: 184 Visits: 112
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
Sean Pearce
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4162 Visits: 3436
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

@SeanPearceSQL

About Me
gksharmaajmer
gksharmaajmer
SSC-Enthusiastic
SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)

Group: General Forum Members
Points: 184 Visits: 112
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
Sean Pearce
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4162 Visits: 3436
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

@SeanPearceSQL

About Me
gksharmaajmer
gksharmaajmer
SSC-Enthusiastic
SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)

Group: General Forum Members
Points: 184 Visits: 112
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search