?
Solved

c# help Rejigging Sort order of an SQL query

Posted on 2009-05-14
4
Medium Priority
?
218 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 2000 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

770 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