|
from the original data I created the result, if the contract in the month has LOAIHD = "FBAN" and between (#01/01/2019# and #31/01/2019#) then [BOOL] = 1 else [BOOL = 0, I will describe the condition of [BOOL] in C# code
[CODE]
for (int i = 0; i < [Original data].Count; i++)
{
bool BOOLEAN = 0;
string SOHD = "";
if([NGAYGIAO].[i]>=#1/1/2019# && [NGAYGIAO].[i] <=#1/31/2019#) //dd/MM/yyyy
{
for (int j = 0; j < [Original data].Count; j++)
{
if([LOAIHD].[j] == "FBAN")
{
BOOLEAN = 1;
SOHD = [Original data].[HD].[j];
}
}
}
if(SOHD == [Original data].[HD].[i] && SOHD != "")
{
[Result].[BOOL].[i] = BOOLEAN;
}
}
[/CODE]
|
|
|
|
|
Add a derived column to your selection where the definition is as follows:
=IIF(NGAYGIAO BETWEEN #01/01/2019# and #31/01/2019 23:59:59#,true,false)
You can then sort and group by this column
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
I had SQL Remote Connections working on a clien't server. For some reason I can no longer connect.
I'm getting
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)
The network path was not found
I can remote into their server, I just can't connect to the DB either with SSMS or through my app. The ports are open, and SQL is configured for remote access.
I've tried all the usual debugging techniques, like this, but I can't get in.
Anyone have any other ideas?
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
|
Named Pipes is disabled in SQL Config Mgr.
I followed this to set up Remote Access.
I also opened ports 1433 and 1434 in Windows Firewall.
Again, no joy
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
But your error message says you're using named pipes.
Have you checked the client protocol configuration, as well as the server config?
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Well, I have checked & rechecked all the settings.. Named Pipes s disabled everywhere that I can see.
Still getting this error
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
Presuming you are NOT using named pipes and in fact the server is remote (different box) then the way to test connectivity is as follows.
1. Log in to the client box
2. Use telnet to access the other box
3. If telnet works then you have connectivity. If not then no connectivity.
To use telnet you must have the 'host' (name or ip) and the 'port'. Telnet doesn't need to do anything other than connect. That is the entire test.
If you do NOT have connectivity then one or more of the following is true
1. host is wrong
2. port is wrong
3. server is not up
4. server is not configured for the port you used (but see 1.)
5. Network is blocking traffic. This can include firewalls.
If you do have connectivity then you have an incorrect assumption. For example your test used the wrong host/port or your application is using the wrong host/port.
Kevin Marois wrote: For some reason I can no longer connect.
If it fact this is true. It worked at the install site and no longer does, then it suggests something changed in the install. For example
1. Configuration changed
2. Someone changed the stack. For example installed or changed a firewall.
Don't forget the always wildly popular - the server just simply is not running.
|
|
|
|
|
Hi guys, just wanted to ask if anyone can help me determine whether a table should be:
1. Left in its traditional database form; and if not why? and what should it be normalised to.
2.< The detailed process of implementing the database using SQL, including the normalisation of the table (should that be required), the identification of the attributes, the Entity-Relationship Diagram, and the use of SQL commands in order to create and populate the tables with data
If you're willing to help please reply or message needed urgently. Many thanks
|
|
|
|
|
|
Member 14182925 wrote: what should it be normalised to.
BCNF (also 3.5NF). Steps are described on wikipedia.
Member 14182925 wrote: The detailed process of implementing the database using SQL
We're not going to write a complete custom script and explain it.
Member 14182925 wrote: including the normalisation of the table (should that be required),
Is always required for relational data.
Member 14182925 wrote: and the use of SQL commands in order to create and populate the tables with data
If you don't know them, stay away from the database 
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
Member 14182925 wrote: If you're willing to help
You are not asking for help, you are asking for someone to do your work for you. If you intend to learn this stuff then you are the one to do the research and find out how to meet the requirement.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
Reeks of homework to me....
Software rusts. Simon Stephenson, ca 1994. So does this signature. me, 2012
|
|
|
|
|
Almost certainly, any shortcuts taken now will mean you or I will be fixing his garbage in the future.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
In Oracle DB, what are the terms USER and SCHEMA?
From what I've read, schema refers to all the content a database has (tables, sequences, views ...) which belongs to a USER.
But what is a USER? Is it like an "internal database"? I mean, let say that I have 2 programs (Planet Help | City industries) that each need 2 separate databases. So, can I create a USER with the name planetDatabase, and another with the name cityDatabase, which would mean I have 2 "internal databases"? And in each "internal database" I can create specific users that have restricted access to certain functions in their respective "internal database"? Or I am way off what a USER actually is?
modified 13-Mar-19 9:06am.
|
|
|
|
|
USER in Oracle is a function that returns the name of the User for the current session - Oracle / PLSQL: USER function[^] - an analogy for that would be the "person" logged on (but a user is not necessarily a person)
Schemas can be User specific - within a database. They are not databases in themselves. Think of them as the "user account".
Well I made that as clear as mud!
The best explanations of the differences that I've found are on this link[^] which also includes a link to this post[^] .
General consensus of opinion seems to be that Oracle made a mess of the terminology!
|
|
|
|
|
So, using my example, if I have 2 programs, and if I need a DB for each of them "Planet Help DB" and "City industries DB" (random names, no connection to real ones), can I create two Database users, using Application Express, one with the name "PlanetHelpDB" and the other "CityIndustriesDB", and use those as a DB for each app? I see that in both of them I can have tables with the same names as in the other one, and also for each I can create new users using "Administration" tools (from Application Express), which will be linked/restricted to a default schema (PlanetHelpDB or CityIndustriesDB, in my example).
Is this a good or a bad way to do it? In case it is bad, what is the recommended way to do it (to have 1 database for each app)?
|
|
|
|
|
In my own experience, having tables with the same name in two different schemas on the same database has led to issues (when people writing queries have omitted the schema name when referring to the table).
If there is absolutely nothing in common between the databases then I would probably have two separate databases and manage them separately. It rather depends on how things like backups, transaction logs, etc will be handled and if there are benefits to having the schemas held on the same db.
However, if these apps are for the same corporation and use different tables then I would put them on the same database, no need for differentiating schemas and users unless you want to use them to control access, or might want to in the future.
We are currently using schemas in a corporate database to indicate which "area" (application if you like) "owns" the data in the table(s) and using the dbo schema for common data (i.e. no tables with the same name in different schema). I'm yet to discover if this will cause us problems.
As you can probably gather .. "good" or "bad" is quite subjective and depends greatly on what the overall infrastructure is going to look like
|
|
|
|
|
Quote: If there is absolutely nothing in common between the databases then I would probably have two separate databases and manage them separately.
They will have some stuff in common, like the user data (username, email, password ...) and other stuff too, but they will also have some data that will be needed only for them separatly. So I'm goin to use something like you said.
Quote: We are currently using schemas in a corporate database to indicate which "area" (application if you like) "owns" the data in the table(s) and using the dbo schema for common data (i.e. no tables with the same name in different schema).
I'm new to this whole area of DB, so comments/suggestions like this one are really useful (practical not theoretical).
Thanks!
|
|
|
|
|
As an example, let's see what Oracle says about "schema";
A schema is a collection of logical structures of data, or schema objects. A schema is owned by a database user and has the same name as that user. Each user owns a single schema. Schema objects can be created and manipulated with SQL and include the following types of objects: (list of stuff)
Introduction is here[^].
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
Hello Friends,
my requirement is, I have table with following fields EmpID,RequestNo,RequestDate,RequestType,RequestStatus.
Now, I need to fetch the top 2 rows of the employee based on the employee ID with request date desc, and should display data of these 2 rows into 1 row with the fields:
I have query to fetch only top 2 rows, now I should know how to display that 2 rows into single rows with columns.
EmpID
Current ReqNo
Current ReqDate
Current ReqType
Current ReqStatus
Previous ReqNo
Previous ReqDate
Previous ReqType
Previous ReqStatus
Kindly help, I googled and found Pivot, but I am not getting exact result.
Thank you so much,
Best Regards,
Priya.
|
|
|
|
|
You claim you are not getting exact result, so show us the code that you have used up to now. Some sample data and your expected results would also be useful
|
|
|
|
|
You could create a temp table with the "current" & "previous" columns and insert data into it from the fetched two top records.
And do it in a simple stored procedure... 
|
|
|
|
|
|
For SQL Server 2012 or later:
WITH cte As
(
SELECT
ROW_NUMBER() OVER (PARTITION BY EmpID ORDER BY RequestDate DESC) As RN,
EmpID,
RequestNo As CurrentReqNo,
RequestDate As CurrentReqDate,
RequestType As CurrentReqType,
RequestStatus As CurrentReqStatus,
LEAD(RequestNo) OVER (PARTITION BY EmpID ORDER BY RequestDate DESC) As PreviousReqNo,
LEAD(RequestDate) OVER (PARTITION BY EmpID ORDER BY RequestDate DESC) As PreviousReqDate,
LEAD(RequestType) OVER (PARTITION BY EmpID ORDER BY RequestDate DESC) As PreviousReqType,
LEAD(RequestStatus) OVER (PARTITION BY EmpID ORDER BY RequestDate DESC) As PreviousReqStatus
FROM
YourTable
)
SELECT
EmpID,
CurrentReqNo,
CurrentReqDate,
CurrentReqType,
CurrentReqStatus,
PreviousReqNo,
PreviousReqDate,
PreviousReqType,
PreviousReqStatus
FROM
cte
WHERE
RN = 1
;
LEAD (Transact-SQL) - SQL Server | Microsoft Docs[^]
For SQL Server 2008 or 2008 R2:
WITH cte As
(
SELECT
ROW_NUMBER() OVER (PARTITION BY EmpID ORDER BY RequestDate DESC) As RN,
EmpID,
RequestNo,
RequestDate,
RequestType,
RequestStatus
FROM
YourTable
)
SELECT
C.EmpID,
C.RequestNo As CurrentReqNo,
C.RequestDate As CurrentReqDate,
C.RequestType As CurrentReqType,
C.RequestStatus As CurrentReqStatus,
P.RequestNo As PreviousReqNo,
P.RequestDate As PreviousReqDate,
P.RequestType As PreviousReqType,
P.RequestStatus As PreviousReqStatus
FROM
cte As C
LEFT JOIN cte As P
ON P.EmpID = C.EmpID
And P.RN = C.RN + 1
WHERE
C.RN = 1
;
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
