Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query Help Expand / Collapse
Author
Message
Posted Wednesday, November 6, 2013 11:23 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 1, 2014 2:28 AM
Points: 47, Visits: 110
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
Post #1512092
Posted Thursday, November 7, 2013 1:14 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 6:55 AM
Points: 941, Visits: 2,936
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
Post #1512127
Posted Thursday, November 7, 2013 2:08 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 1, 2014 2:28 AM
Points: 47, Visits: 110
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
Post #1512144
Posted Thursday, November 7, 2013 2:29 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 6:55 AM
Points: 941, Visits: 2,936
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
Post #1512155
Posted Thursday, November 7, 2013 2:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 1, 2014 2:28 AM
Points: 47, Visits: 110
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
Post #1512162
Posted Thursday, November 7, 2013 2:52 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 6:55 AM
Points: 941, Visits: 2,936
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
Post #1512165
Posted Thursday, November 7, 2013 3:46 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 1, 2014 2:28 AM
Points: 47, Visits: 110
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
Post #1512178
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse