Solved

c# help Rejigging Sort order of an SQL query

Posted on 2009-05-14
4
216 Views
Last Modified: 2012-05-07
Okay, so I have a query like the attached.

I know today is MON so I want to put priority on my order by for tomorrows deliveries - (TUE)

But then when my script gets to TUE i want to put priority on WED and so on.

How can I do this with either SQL or C# to save me having to write out 7 different queries depending on the day of the week that the scripts is run?

Hope you can help.
"ORDER BY CASE bs.DeliveryDay" +
                                       "when 'TUE' then 1" +
                                       "when 'WED' then 2" +
                                       "when 'THU' then 3" +
                                       "when 'FRI' then 4" +
                                       "when 'MON' then 5" +
                                       "else 99" +
                                       "end" +
                                       ", bs.SOH DESC"

Open in new window

0
Comment
Question by:andrewmilner
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 24389417
Is this a stored procedure or just a query you are passing to server via code?
0
 

Author Comment

by:andrewmilner
ID: 24389549
yes just passing query to server through c# code.

I've just gone through and done..

if(DayToday == "MON")
{
MySortOrder
}
else if (DayToday =="TUE)
etc

Long winded or just stick with it?
0
 
LVL 2

Accepted Solution

by:
phead_2 earned 500 total points
ID: 24389636
change the:
                                       "when 'TUE' then 1" +
                                       "when 'WED' then 2" +
                                       "when 'THU' then 3" +
                                       "when 'FRI' then 4" +
                                       "when 'MON' then 5" +
to:

                                       "when 'MON' then @MON" +
                                       "when 'TUE' then @TUE" +
                                       "when 'WED' then @WED" +
                                       "when 'THU' then @THU" +
                                       "when 'FRI' then @FRI" +

add the following  code to set the variables and add the parameters, replace 'sqlCommand' with your SqlCommand object.






//Defalut is Friday
 
        int MON = 1;
        int TUE = 2;
        int WED = 3;
        int THU = 4;
        int FRI = 5;
 
        if (System.DateTime.Today.DayOfWeek == DayOfWeek.Monday)
        {
            MON = 5;
            TUE = 1;
            WED = 2;
            THU = 3;
            FRI = 4;
        }
        if (System.DateTime.Today.DayOfWeek == DayOfWeek.Tuesday)
        {
            MON = 4;
            TUE = 5;
            WED = 1;
            THU = 2;
            FRI = 3;
        }
        if (System.DateTime.Today.DayOfWeek == DayOfWeek.Wednesday)
        {
            MON = 3;
            TUE = 4;
            WED = 5;
            THU = 1;
            FRI = 2;
        }
        if (System.DateTime.Today.DayOfWeek == DayOfWeek.Thursday)
        {
            MON = 2;
            TUE = 3;
            WED = 4;
            THU = 5;
            FRI = 1;
        }
        SqlCommand sqlCommand = new SqlCommand();
        sqlCommand.Parameters.Add(new SqlParameter("@MON", MON));
        sqlCommand.Parameters.Add(new SqlParameter("@TUE", TUE));
        sqlCommand.Parameters.Add(new SqlParameter("@WED", WED));
        sqlCommand.Parameters.Add(new SqlParameter("@THU", THU));
        sqlCommand.Parameters.Add(new SqlParameter("@FRI", FRI));

Open in new window

0
 

Author Comment

by:andrewmilner
ID: 24389695
Many Thanks.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

690 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question