Solved

c# help Rejigging Sort order of an SQL query

Posted on 2009-05-14
4
214 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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.
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
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.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

726 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