Solved

c# help Rejigging Sort order of an SQL query

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
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.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

929 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now