|
but I want to get order details as well?!
Technology News @ www.JassimRahma.com
|
|
|
|
|
Jassim Rahma wrote: but I want to get order details as well?! You'd also want the cleaners that don't have an order detail?
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
yes.. I want to display all
if cleaner has order then it will display the SUM but if not then I want to display ZERO, null, blank or whatever..
Technology News @ www.JassimRahma.com
|
|
|
|
|
|
|
Look into LEFT OUTER joins
|
|
|
|
|
Hie everyone I have the below code on my console to import data from an excel sheet to a mySql database but unfortunately I am having an error which reads:
NO CONNECTION TRYING TO RECONNECT....
ERROR 2005 (HY000): UNKNOWN MYSQL SERVER HOST '(ID,NAME):' (11001)
ERROR:
Can't connect to the server
My command is as follows:
LOAD DATA LOCAL INFILE `C:\\temp\\test.csv' INTO TABLE bd_george.test FIELDS TERMINATED BY `;' LINES TERMINATED BY `\r\n' (id,name);
Regards
George Tsiga
STUDENT
|
|
|
|
|
George Tsiga wrote: My command The command isn't executed if you can't even connect. Check your connectionstring.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Thanks a lot its now working I was using a backtick on rapping \r\n. The correct command is as follows.
LOAD DATA LOCAL INFILE 'C:\\temp\\test.csv' INTO TABLE bd_george.test FIELDS TERMINATED BY ';' LINES TERMINATED BY '\r\n' (id,name);
Regards
George Tsiga
STUDENT
|
|
|
|
|
I am working on a legacy app with a SqlServer DB
I am about to create 18 brand new table to support new functionality I'm going to add
Just for clarity sake I was thinking to create this table in a new schema (a bit like grouping my C# class by namespace / folder in my mind)
But as I'm reading about schema I read that it has security associated with it..
I don't want to make thing more complicated.. (quite the opposite)
So.. what's your take on shema? is it here for grouping things together for
1. clarity / purpose
2. security
|
|
|
|
|
This is a flavour of the month thing, some do some don't. Security allows you to assign specific users to a schema, really useful if your users have query access to the database but if you use a schema just for you to identify the functionality grouping simply give all users the rights to the schema.
Another issue will be your DAL, it will need to support multiple schemas, this one bit me many years ago as I assumed dbo only.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
The purpose of the schema is security, how you use it is up to you.
It's not an overly big deal to give the user associated with one schema rights on another schema.
Be excellent to each other. And... PARTY ON, DUDES!
Abraham Lincoln
|
|
|
|
|
After you CREATE SCHEMA you'll notice that the object, if I could call it that for a moment, is found in the Security folder of the Object Browser tree. But as far as using SCHEMA as one would an "idea" in the "scheme of things" ... it's nature is perfect for that.
I use it to isolate dev stages as I work on scripts. Testing procedures, isolating a vast array of data tables by filtering them via SCHEMA, in Object Viewer, is very easy. In fact one of the only downsides to using SQL Server Management Studio happens to be that only one SCHEMA at a time is filterable.
That and a very aggrevating lack of code editor collapsablilty (-/+) as in VS. But that's rant for another holiday. ARRRRGHHHH me buckoe.
|
|
|
|
|
Super Lloyd wrote: am about to create 18 brand new table ...I don't want to make thing more complicated
Then I suggest you might want to take another look at the first part.
|
|
|
|
|
I want to get data on the bases of selected dates i.e start date and endate it might be any dates between the week but the dates stored in the table are start date of the week and end date of the week and days of the week mon,tue,wed,thur,fri,sat,sun.
|
|
|
|
|
what database are you using? SQL Server, Oracle, MySQL or Access.
Also edit your question and expand on what you are after also include sample table stucture and/or even sample date so that people can give you a full answer.
Every day, thousands of innocent plants are killed by vegetarians.
Help end the violence EAT BACON
|
|
|
|
|
i'm using sql server 2008
i'm having table where timesheet is filled weekely i'e from monday to sunday and i'm saving start and end date of the week but when retrieving the data dates selected may be any day of the week.
example if selected date is 15/8/2013 and 30/8/2013
and dates saved in table is from
2013-08-12(mon) 2013-08-18(sun)
2013-08-19 2013-08-25
2013-08-26 2013-09-01
2013-09-02 2013-09-08
2013-07-08 2013-07-14
2013-06-03 2013-06-09
2013-07-22 2013-07-28
MON TUE WED THUR FRI SAT SUN
8 8 7 7 7 7 7
8 8 8 8 8 8 8
0 8 8 8 8 8 8
0 8 8 8 8 8 0
8 8 8 8 8 8 8
8 8 8 8 8 8 8
8 8 8 8 8 8 8
i need data between 15/8/2013 to 30/8/2013
|
|
|
|
|
You can get the data for any weeks which overlap the selected date range with:
WHERE
WeekStartDate <= @SelectedEndDate
And
WeekEndDate >= @SelectedStartDate
For your example, that would give you the data between 2013-08-12 and 2013-09-01 .
Since the data for a week appears to be on a single row, it's not obvious how you intend to return only part of a week.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
from this query we are getting data for whole week but selected startdate may be thur or any day i need data from that particular day to the particular enddate selected
|
|
|
|
|
As I said, since the data for the entire week appears to be on a single row, it's not obvious how you intend to return only part of a week.
Once you explain that, then we might be able to help you.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
any alternate solution to go with suggest please.
|
|
|
|
|
Since you haven't explained how you want the data to be returned, how is anyone supposed to suggest a solution?!
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
from selected date i need date,day of the week and no of hrs he worked on that day and same till the end date selected
|
|
|
|
|
So you essentially want one row per day?
DECLARE @SelectedStartDate date = '20130815';
DECLARE @SelectedEndDate date = '20130830';
WITH cteOverlappingWeeks As
(
SELECT
StartDate,
MON,
TUE,
WED,
THUR,
FRI,
SAT,
SUN
FROM
dbo.Timesheet
WHERE
StartDate <= @SelectedEndDate
And
EndDate >= @SelectedStartDate
),
ctePivotedWeeks (WorkDay, Hours) As
(
SELECT
StartDate,
MON
FROM
cteOverlappingWeeks
UNION ALL
SELECT
DateAdd(day, 1, StartDate),
TUE
FROM
cteOverlappingWeeks
UNION ALL
SELECT
DateAdd(day, 2, StartDate),
WED
FROM
cteOverlappingWeeks
UNION ALL
SELECT
DateAdd(day, 3, StartDate),
THUR
FROM
cteOverlappingWeeks
UNION ALL
SELECT
DateAdd(day, 4, StartDate),
FRI
FROM
cteOverlappingWeeks
UNION ALL
SELECT
DateAdd(day, 5, StartDate),
SAT
FROM
cteOverlappingWeeks
UNION ALL
SELECT
DateAdd(day, 6, StartDate),
SUN
FROM
cteOverlappingWeeks
)
SELECT
WorkDay,
Hours
FROM
ctePivotedWeeks
WHERE
WorkDay Between @SelectedStartDate And @SelectedEndDate
ORDER BY
WorkDay
;
Example: http://www.sqlfiddle.com/#!3/b8a7a/1[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Nice tool never knew about SQLFiddle Thanks
Every day, thousands of innocent plants are killed by vegetarians.
Help end the violence EAT BACON
|
|
|
|