-
March 31st, 2015, 09:22 AM
#1
The orders after 12 am to 7 am not show timer problem
Hi guys
I make program to select data every day automatically by timer every second in c#
In sql server 2005 i write the following query
ALTER proc [dbo].[JeddahSalesAll]
as
SELECT ROW_NUMBER() OVER (ORDER BY dbo.[Jeddah-Live$Sales Header].No_) AS [S.N], dbo.[Jeddah-Live$Sales Line].[Document No_] AS 'OrderNo',
dbo.[Jeddah-Live$Sales Header].[Bill-to Name] AS 'CustomerNo', dbo.[Jeddah-Live$Sales Line].Area AS 'Shippment Type', dbo.[Jeddah-Live$Sales Line].Description AS 'Description',
dbo.[Jeddah-Live$Sales Header].[Pump No_] AS 'PumpNo', CAST(ROUND(dbo.[Jeddah-Live$Sales Line].Quantity, 0, 1) AS int) AS 'Required Qunatity',
CAST(ROUND(dbo.[Jeddah-Live$Sales Line].[Quantity Shipped], 0, 1) AS int) AS 'Shipped Quantity', CAST(ROUND(dbo.[Jeddah-Live$Sales Line].[Outstanding Quantity], 0,
1) AS int) AS 'Outstanding Qunatity '
FROM dbo.[Jeddah-Live$Sales Header] INNER JOIN
dbo.[Jeddah-Live$Sales Line] ON dbo.[Jeddah-Live$Sales Header].No_ = dbo.[Jeddah-Live$Sales Line].[Document No_] AND
dbo.[Jeddah-Live$Sales Header].[Sell-to Customer No_] = dbo.[Jeddah-Live$Sales Line].[Sell-to Customer No_]
WHERE DATEDIFF(d,dbo.[Jeddah-Live$Sales Line].[Shipment Date],GETDATE()) = 0
IN C# WINDOWS FORM VISUAL STUDIO 2008 IN TIMER TICK EVENT INTERVAL IS 1000MILISECOND(EVERY SECOND)
ales.SalesClass SalesClass1 = new Sales.SalesClass();
DataTable dt = SalesClass1.ShowSalesData("Data Source=192.168.1.5;Initial Catalog=Altawi-last06-01-2015;User ID=admin;Password=123");
dataGridView1.DataSource = dt;
dataGridView1[0, dataGridView1.Rows.Count - 1].Value = "Total Sum";
dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells[0].Style.BackColor = Color.Yellow;
dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells[1].Style.ForeColor = Color.Red;
dataGridView1.Refresh();
CLASS CODE AS FOLLOWING
class SalesClass
{
public DataTable ShowSalesData(string ConnectionString)
{
SqlConnection con = new SqlConnection(ConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "JeddahSalesAll";
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
DataSet ds = new DataSet();
da.Fill(ds);
DataTable dt = ds.Tables[0];
return dt;
}
code above work success no any problem
only problem the order start 8 pm in date(31/03/2015) and finish 7 am in date(01/04/2015)
at next day
what i do to show the orders done from 12 am to 7 am to show
if you have solution or suggestion to solve problem help me if possible
thanks
-
March 31st, 2015, 09:27 PM
#2
Re: The orders after 12 am to 7 am not show timer problem
Are there any orders between 12am and 7am in the database?
Does the following where clause properly span the two days? WHERE DATEDIFF(d,dbo.[Jeddah-Live$Sales Line].[Shipment Date],GETDATE()) = 0
-
March 31st, 2015, 11:23 PM
#3
Re: The orders after 12 am to 7 am not show timer problem
Thank you for reply
yes there are more orders from 12 am to 7 am
and i need to show it with date start
the order start in 31/03/2015 8 pm and finish 7 am
so that the following statement retrieve only today
WHERE DATEDIFF(d,dbo.[Jeddah-Live$Sales Line].[Shipment Date],GETDATE()) = 0
how to change this statement to accept next day until 7 am if possible
-
March 31st, 2015, 11:38 PM
#4
Re: The orders after 12 am to 7 am not show timer problem
Meaning the statement above show from 8 pm to 12 am
but what i need to show data orders from 8 pm to 7 am
-
April 1st, 2015, 12:52 AM
#5
Re: The orders after 12 am to 7 am not show timer problem
Instead of DATEDIFF use something else like calculating the start and end date times and use between.
Debug this using a sql query before trying this in your C# code.
-
April 1st, 2015, 01:15 PM
#6
Re: The orders after 12 am to 7 am not show timer problem
Thank you for reply
how to calculating the start and end date times and use between
-
April 1st, 2015, 01:16 PM
#7
Re: The orders after 12 am to 7 am not show timer problem
to more show what i need
shipped date(user enter it) time dateof insert(server date)
31/03/2015 8 pm 31/03/2015
31/03/2015 10 pm 31/03/2015
31/03/2015 1 am 01/04/2015
31/03/2015 7 am 01/04/2015
query above return 8 pm and 10 pm
but not show 1 am and 7 am
so taht what i need to show is 8 pm,10 pm,1 am,7 am
only I need to show orderes that happen in same day and data shipped after 12 until 7 am
Meaning if order still work two days i need to show it based on date starting in it
we put 31/03/2015 in the day of 01/04/2015 because shipping date start in 31/03/2015
but problem happen when order still work to another day
-
April 1st, 2015, 06:11 PM
#8
Re: The orders after 12 am to 7 am not show timer problem
The following will give you a date time that is tomorrow's date at 7am.
SELECT CAST((CAST(CAST(GETDATE() + 1 AS DATE) AS NVARCHAR(10)) + ' 07:00:00') AS DATETIME)
As I mentioned, do this in SQL and get the query working right before you run it in C#.
Here's some test code:
Code:
DECLARE @ShipmentDates TABLE
(
Id INT IDENTITY(1,1)
, ShipmentDate DATETIME
)
INSERT INTO @ShipmentDates SELECT '03/31/2015 08:00:00'
INSERT INTO @ShipmentDates SELECT '03/31/2015 22:00:00'
INSERT INTO @ShipmentDates SELECT '03/31/2015 08:00:00'
INSERT INTO @ShipmentDates SELECT '04/01/2015 01:00:00'
INSERT INTO @ShipmentDates SELECT '04/01/2015 07:00:00'
INSERT INTO @ShipmentDates SELECT '04/01/2015 08:00:00'
INSERT INTO @ShipmentDates SELECT '04/01/2015 22:00:00'
INSERT INTO @ShipmentDates SELECT '04/01/2015 08:00:00'
INSERT INTO @ShipmentDates SELECT '04/02/2015 01:00:00'
INSERT INTO @ShipmentDates SELECT '04/02/2015 07:00:00'
INSERT INTO @ShipmentDates SELECT '04/02/2015 08:00:00'
INSERT INTO @ShipmentDates SELECT '04/02/2015 22:00:00'
INSERT INTO @ShipmentDates SELECT '04/02/2015 08:00:00'
INSERT INTO @ShipmentDates SELECT '04/03/2015 01:00:00'
INSERT INTO @ShipmentDates SELECT '04/03/2015 07:00:00'
SELECT [ShipmentDate] FROM @ShipmentDates
-- Shipment dates that are greater than 7am today and less than/equal to 7am tomorrow
WHERE [ShipmentDate] > CAST((CAST(CAST(GETDATE() AS DATE) AS NVARCHAR(10)) + ' 07:00:00') AS DATETIME)
AND [ShipmentDate] <= CAST((CAST(CAST(GETDATE() + 1 AS DATE) AS NVARCHAR(10)) + ' 07:00:00') AS DATETIME)
When run today, this correctly returns the 'middle' 5 records that are past 7am today and are before (and including) 7am tomorrow.
Code:
ShipmentDate
-----------------------
2015-04-01 08:00:00.000
2015-04-01 22:00:00.000
2015-04-01 08:00:00.000
2015-04-02 01:00:00.000
2015-04-02 07:00:00.000
Probably a SQL expert would be able to do this more cleanly or with fewer casts.
Last edited by Arjay; April 1st, 2015 at 06:16 PM.
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|