Query for days - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Query for days

Author

Topic

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-23 : 01:18:49

I have one table


CREATE table ServerSchedule( ServerID, ServerName, ScheduleDays)
GO
INSERT INTO ServerSchedule
SELECT 1, 'Server1', '7,1'
SELECT 1, 'Server2', '1,2,3'
SELECT 1, 'Server3', '1,2,3,4,5,6'
SELECT 1, 'Server4', '6,7'
GO
SELECT * FROM ServerSchedule

In the column ScheduleDays we are saving the days.
1 for sunday, 2 for monday....7 for saturday

now we need to show report where i have to show the names of the day in place of digits
and but not sunday monday that should be sun,mon like this.

Please help me how can i show 7,1 as sat,sun

Vaibhav T

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-23 : 01:20:13

Sorry the code should be like


CREATE table ServerSchedule( ServerID int, ServerName varchar(20), ScheduleDays varchar(10))
GO
INSERT INTO ServerSchedule
SELECT 1, 'Server1', '7,1'
UNION ALL
SELECT 2, 'Server2', '1,2,3'
UNION ALL
SELECT 3, 'Server3', '1,2,3,4,5,6'
UNION ALL
SELECT 4, 'Server4', '6,7'
GO
SELECT * FROM ServerSchedule

Vaibhav T

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-03-23 : 01:29:36

Why can't you try it on your front End?

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-23 : 01:33:41

I asked to do that in front end but they are able to do that that is why i will have to that any how.

Vaibhav T

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-03-23 : 01:45:42

If we try in back end,cost will be high! front end advisable!

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-23 : 01:46:02

must be lazy front end programmer


SELECT 	*, replace(replace(replace(replace(replace(replace(replace(ScheduleDays, '1', 'sun'), '2', 'mon'), '3', 'tue'), '4', 'wed'), '5', 'thu'), '6', 'fri'), '7', 'sat')
FROM 	ServerSchedule

KH
[spoiler]Time is always against us[/spoiler]

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-03-23 : 01:51:51

"REPLACE" can Replace the front end???

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-23 : 02:04:48

Yeah really lazy

And thank you very much senthil it worked fine and solved my problem.
and i think this might be easiest and most manageable way to do.
but can we not have another method with case statement.
because i was thinking in that way so i dint get the clue of this method.

thanks again.

i love sql and i want to replace my front end programmers...
can u imagine in the result sets if i am not getting anything i need to use full outer join and give the null in result sets

Vaibhav T

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-03-23 : 02:24:20

Vaibhav,

Ofcourse we can use CASE,DATENAME,DATEPART.... ,but here the case is must choose simple way(backend friendly).

And thanks to khtan, the REPLACE can also done trick.

Any way Enjoy SQL

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2010-03-23 : 04:32:25

quote:

Originally posted by vaibhavktiwari83

Sorry the code should be like


CREATE table ServerSchedule( ServerID int, ServerName varchar(20), ScheduleDays varchar(10))
GO
INSERT INTO ServerSchedule
SELECT 1, 'Server1', '7,1'
UNION ALL
SELECT 2, 'Server2', '1,2,3'
UNION ALL
SELECT 3, 'Server3', '1,2,3,4,5,6'
UNION ALL
SELECT 4, 'Server4', '6,7'
GO
SELECT * FROM ServerSchedule

Vaibhav T

Sorry, but how can you insert the schedule for Server3 without getting a string truncation error?

Also, may I say that your design appears to be wrong? Why don't you have a dedicated table for Servers, one for ScheduleDays and then another table for the ServerSchedule? So, basically something like this:

DECLARE @Server TABLE (ServerID int, ServerName varchar(20))
INSERT INTO @Server
SELECT 1, 'Server1'          
UNION ALL 
SELECT 2, 'Server2'
UNION ALL 
SELECT 3, 'Server3'

DECLARE @ScheduleDays TABLE (DayID int, DayName varchar(10))
INSERT INTO @ScheduleDays
SELECT 1, 'Sunday'
UNION ALL
SELECT 2, 'Monday'
UNION ALL
SELECT 3, 'Tuesday'
UNION ALL
SELECT 4, 'Wednesday'
UNION ALL
SELECT 5, 'Thursday'
UNION ALL
SELECT 6, 'Friday'
UNION ALL
SELECT 7, 'Saturday'

DECLARE @ServerSchedule TABLE (Serverid int, DayID int)

INSERT INTO @ServerSchedule
SELECT 1, 7
UNION ALL
SELECT 1, 1
UNION ALL
SELECT 1, 2
UNION ALL
SELECT 2, 1

SELECT
    S.ServerID,
    S.ServerName,
    SD.DayName
FROM
    @Server S
    JOIN
    @ServerSchedule SS ON S.ServerID = SS.ServerID
    JOIN
    @ScheduleDays SD ON SS.DayID = SD.DayID

Which would give you

ServerID	ServerName	DayName
1	Server1	Saturday
1	Server1	Sunday
1	Server1	Monday
2	Server2	Sunday

(4 row(s) affected)

Or if you want it "more report-friendly":

SELECT
    S.ServerID,
    S.ServerName,
    SUBSTRING(x.MySchedule, 2, 8000) AS ServerSchedule
FROM
    @Server S
    CROSS APPLY
    (SELECT 
        ',' + SD.DayName
    FROM
        @ServerSchedule SS
        JOIN
        @ScheduleDays SD ON SS.DayID = SD.DayID
    WHERE
        S.ServerID = SS.ServerID
    ORDER BY
        SD.DayID
    FOR XML PATH ('')) AS x(MySchedule)

which gives:

ServerID	ServerName	ServerSchedule
1	Server1	Sunday,Monday,Saturday
2	Server2	Sunday
3	Server3	NULL

(3 row(s) affected)

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-03-23 : 04:50:22

Frank Kalis,

Your Design is absolutely correct,and it must be.But here the question is need to build a query for the already designed and implemented table

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2010-03-23 : 04:53:07

quote:
Originally posted by senthil_nagore

Frank Kalis,

Your Design is absolutely correct,and it must be.But here the question is need to build a query for the already designed and implemented table

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

...which arguably may or may not be changeable. OP didn't say that his design is fixed.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-03-23 : 04:59:48

Cool!
Considering his design was fixed!

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-23 : 05:23:38

quote:
Originally posted by Frank Kalis

quote:
Originally posted by senthil_nagore

Frank Kalis,

Your Design is absolutely correct,and it must be.But here the question is need to build a query for the already designed and implemented table

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

...which arguably may or may not be changeable. OP didn't say that his design is fixed.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org

....yes my table design is fixed and way of inserting data is also fixed and we can not normalize the data as such.
in future design i may refer this structure and query for reports.

and thanks to all and specially khtan, sorry khtan dont mind i missed to say u thanks.

Vaibhav T

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2010-03-23 : 05:47:05

So, now that the design is fixed, how do insert '1,2,3,4,5,6' into a varchar(10) without an error or loss of information?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-23 : 06:13:18

quote:
Originally posted by Frank Kalis

So, now that the design is fixed, how do insert '1,2,3,4,5,6' into a varchar(10) without an error or loss of information?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org

[My quess]
See his first two posts in this thread
First without datatype specification
Second without testing the code, he randomly put the length of varchar
[/My quess]

Madhivanan

Failing to plan is Planning to fail

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2010-03-23 : 06:23:40

Sounds reasonable...

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-23 : 09:29:58

I have a very big table for that with a lot of columns that was the sample data which i prepare to get the solution so i missed something but main idea was to getting the day name.

You will see a lot of people posts without the sample data. we always asked for sample data.

Vaibhav T

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2010-03-23 : 11:14:24

quote:
Originally posted by vaibhavktiwari83
You will see a lot of people posts without the sample data. we always asked for sample data.

...and for a working script including table structures, indexes, etc...
scnr

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources