How I Can Take Week Plan From Table

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

i selected my records from table i want to order it by week days for example
in first record
saturday
sunday
...
..

my code is in below:
week is field in tb1.

select *from tb1 where Scode='123' order by week

Posted 15-Jan-15 5:46am

Member 11356492

Add a Solution

Comments

jaket-cp 15-Jan-15 12:31pm

what is the datatype of the week field.

Member 11356492 15-Jan-15 12:36pm

nvarchar(50)

Tomas Takac 15-Jan-15 14:28pm

You didn't state what's the problem actually. Based on the above comment I assume that the query returns rows sorted by the day name alphabetically. So you need a sortable representation of the week days, preferably numbers. But there is another problem, if your query spans more than a week, or crosses the week boundary then the sorting would fail. So I would suggest you order by date, if there is one in your table. You can easily extract the week day out of a date using DATEPART[^] function.

BacchusBeale 15-Jan-15 15:29pm

Show some sample data rows, then we may better understand your problem.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

BacchusBeale · Answer 1 · 2015-01-15T10:08:00

Solution 1

Either you can create a reference table called Weekdays with ID and DayName and use it in your query or as I just discovered you can make pseudo tables:

http://sqlmag.com/t-sql/alternatives-arrays[^]

Posted 15-Jan-15 10:08am

BacchusBeale

Comments

Member 11356492 17-Jan-15 4:40am

i created a table with ID and DAyname how i can use it in my query?

jaket-cp · Answer 2 · 2015-01-16T01:05:00

Seeing the week column is of type nvarchar(50), the query can be formulated using order by case :

SQL

--setup test data
declare @tab table(id int identity(1,1), week nvarchar(50));
insert into @tab
select N'sunday' week
union all select N'saturday' week
union all select N'monday' week
union all select N'friday' week
union all select N'wednesday' week
union all select N'tuesday' week
union all select N'wednesday' week
union all select N'saturday' week
union all select N'thursday' week
union all select N'sunday' week
;

--query order by case
select * 
from @tab
order by 
  case lower(week)
    when 'saturday' then 0
    when 'sunday' then 1
    when 'monday' then 2
    when 'tuesday' then 3
    when 'wednesday' then 4
    when 'thursday' then 5
    when 'friday' then 6
  end
;
--modify the case statements to get the desired order of the week

Take Tomas Takac suggestion (comment above) about order by date if possible.