Solved

c# help Rejigging Sort order of an SQL query

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

Expert Comment

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

Author Comment

by:andrewmilner
Comment Utility
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
Comment Utility
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
Comment Utility
Many Thanks.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.

763 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

7 Experts available now in Live!

Get 1:1 Help Now