|
I have two tables like this
Sales table Purchases table
--------------------- ------------------------------
SaleDateTime | Cost PurchaseDateTime | Cost
--------------------- ------------------------------
02-09-11 | 35 02-09-11 | 48
03-09-11 | 35 02-09-11 | 48
06-09-11 | 35 04-09-11 | 48
07-09-11 | 35 05-09-11 | 48
08-09-11 | 35 08-09-11 | 48
I want to produce result like this
Date | Sale_total | purchase_total
--------------------------------------------
02-09-11 | 35 | 96
03-09-11 | 35 | 0
04-09-11 | 0 | 48
05-09-11 | 0 | 48
06-09-11 | 35 | 0
07-09-11 | 35 | 0
08-09-11 | 35 | 48
how can I do this ?
Additional info:-
I am using MS Access.
---------------------------------------------
" Future Lies in Present "Manmohan Bishnoi
|
|
|
|
|
You can divide this task into three parts. The first part uses inner join to find the records from both tables with common date/time. The second part deals with the case that date/time only appears in the first table. The third part deals with the case that date/time only appears in the second table. The result is a union of the above three queries.
To show you what I mean in a cleaner example, let's suppose the first table is called "Table1" with the following two fields: a1 and a2 (corresponding to your "Sales" table's SaleDateTime and Cost columns), and the second table is called "Table2" with the following two fields: a1 and a2 (corresponding to your "Purchase" table's PurchaseDateTime and Cost columns). The following query should give you what you wanted.
SELECT a.a1, sum(a.aa2), sum(b.aa2)
FROM (SELECT a1, sum(a2) as aa2 FROM Table1 GROUP BY a1) as a,
(SELECT a1, sum(a2) as aa2 FROM Table2 GROUP BY a1) as b
WHERE (a.a1=b.a1)
GROUP BY a.a1;
UNION
SELECT a1, sum(a2), 0
FROM Table1
WHERE a1 not in (SELECT a1 FROM Table2)
GROUP BY a1
UNION
SELECT a1, 0, sum(a2)
FROM Table2
WHERE a1 not in (SELECT a1 FROM Table1)
GROUP BY a1
|
|
|
|
|
It is difficult to join datetime fields because of the time portion.
What appears as 09-10-11 may actually be stored as 09-10-11:09:30.00.00 in one field and 09-10-11:12:49.46.50 in another. So when you look at the date portion you would think they're the same value but they're not because of the time portion.
|
|
|
|
|
Actually I modified the fields
SaleDateTime and PurchaseDateTime. These fields contain only date values like 09-11-2011.
---------------------------------------------
" Future Lies in Present "Manmohan Bishnoi
|
|
|
|
|
You may have changed the display, that doesn't change the actual date value which still includes the time portion. So the date may display as 09-11-2011 but the real value may be 09/11/2011 08:30:20:15.
Anyway, try joining using those fields and see what you get.
|
|
|
|
|
yes you were right.
I had to change actual data of those fields.
thanks
---------------------------------------------
" Future Lies in Present "Manmohan Bishnoi
|
|
|
|
|
hi,
how to publish sql server database in server.
thanks in advance!
Have A Nice Day!
Murali.M
Blog
|
|
|
|
|
What do you mean by "publish"?
The data from a SQL Server database can be replicated many ways (backup/restore, detach/attach, ...). You need to have a SQL Server accessible by the client.
If you mean publish as with a .NET program that is a different problem.
|
|
|
|
|
Development phase.
1. Create one or more scripts that create the database
2. Create one or more scripts that populate the database.
3. CHECK them into source control.
4. Label them
5. Extract USING the label and store as a 'delivery' in the file system some where. Call this location X (it will be related to a delivery version number.)
Install phase
1. Notify the production personal what X is (related to the verifie version.)
2. Production personal use a dabase id SPECIFICALLY for installs, and nothing else, to run the scripts appropriately.
3. Verify that the database is 'working' using something methodology probably involving other applications.
There are all sorts of variations on the above.
|
|
|
|
|
Hi,
I have a stored proc, which converts a pdf to tiff files.
Now, I need to rename these tiff files. The main file name is maintained but the last part of the file name changes to xxx00001.tif - xxx is my file name.
How can I check if the main part of the file exists, just xxx part so I can rename it? When I use the fileexist, it looks for the entire filename and hence not finding the file.
any help is appreciated.
|
|
|
|
|
vanikanc wrote: How can I check if the main part of the file exists, just xxx part so I can rename it? When I use the fileexist, it looks for the entire filename and hence not finding the file.
Get the directory contents by using <a href="http://msdn.microsoft.com/en-us/library/aa175921%28v=sql.80%29.aspx">xp_cmdshell</a>[<a href="http://msdn.microsoft.com/en-us/library/aa175921%28v=sql.80%29.aspx" target="_blank" title="New Window">^</a>], and check whether there's a recently created file that meets the criteria.
Bastard Programmer from Hell 
|
|
|
|
|
Hello folks
say for example you have a company with various branches; each branch implements its own database (each DB has its own structure and data); one wants to make use of all the databases in the branches and their data;
how would you go about it then?
Some might go for heterogeneous databases tools; some others might suggest using SQLserver or any other DBMS for all DBs(will it handle that large data? isn't it very costly?); someone else might suggest going for a data warehouse?
How would you go on about it?
|
|
|
|
|
AbrahamMOKDAD wrote: each branch implements its own database (each DB has its own structure and data); one wants to make use of all the databases in the branches and their data;
AbrahamMOKDAD wrote: How would you go on about it?
Sql Express, one per branch, until they outgrow it. The "one" you're referring to could access the data it requires over some webservices from those database. Unless it's for reporting, then I'd simply go for a script that backups each database locally and then send that off once a day.
Bastard Programmer from Hell
|
|
|
|
|
Convince management to grow a set of balls and force through a decision on the data structure. The Branches do the same thing so make them do it the right way. Supporting this sort of wimpy management process is a complete disaster, you are either 1 organisation or you are not.
There is however a strategy I have used when management cop out completely (also used when merging disparate systems). That is to define a reporting structure, this structure is set by management and the branches decide what they want to put in the report elements. It is complex, difficult and hugely expensive in both time and development cost and is difficult to support but hey if they can't make a decision then they have to pay for it.
Sorry for the rant but I live with this every day, and yes I have called management on it!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
String or binary data would be truncated is the nice SqlException one gets from SQL Server when attempting to insert/update a record with a string value that is longer than the field is wide.
That raises 3 questions:
1.
what would be the recommended approach in general?
2.
what would be the recommended approach in a medium-size application (lots of insert/update statements, no SP), when the user doesn't care much and would be happy when the strings where truncated without notification?
3.
is there a simple way to identify which field is overflowing?
TIA
|
|
|
|
|
Luc Pattyn wrote: what would be the recommended approach in general?
Validate your data before you attempt to insert it.
Luc Pattyn wrote: what would be the recommended approach in a medium-size application (lots of
insert/update statements, no SP), when the user doesn't care much and would be
happy when the strings where truncated without notification?
I doubt that is a valid use case.
For example it would certainly never be valid to truncate the delivery address on an order.
So at most you might have one column where it might be acceptable to delete something. Say it is a 'comment' field but it that case your user experience is going to be that they typed a lot of stuff in and it just disappeared.
Again a much better idea is to validate the data first.
|
|
|
|
|
jschell wrote: a much better idea is to validate the data first
In theory sure. However it seems to imply the GUI knows all the database field widths, or alternatively the DB knows how to identify the failing field and pass that on to the GUI. Hence my third question.
|
|
|
|
|
Go to Query Analyser or SQL Server management studio... do your inserts in T-SQL and then query results. It should be easy to see which field is being truncated... also, SQL server may throw an error that tells you which field is being truncated. In general, when designing the table, you should have an idea about the field size. For example if the field is FirstName, I would give it VARCHAR(25).
|
|
|
|
|
Maybe I didn't explain well enough, but it isn't a one-off situation that I am trying to resolve.
My app holds a number of tables and dialogs; each dialog holds TextBoxes (and other Controls), and the strings entered in the TextBoxes is going to be inserted or updated in the tables. Think of people's first and last name, addresses, phone numbers, a comment line, some preferences, and many more. Right now there is no protection against overflow, as TextBoxes allow an unlimited amount of text to be entered (and the GUI is unaware of the table field widths anyway). So it is at run-time that the users might enter too much text in any of a large number of fields; the fields are quite sufficient for the intended use, but nothing is preventing abuse. So I want:
1. to make sure my app behaves well no matter what the input is;
2. provide detailed feedback where necessary (such as "Phone number cannot exceed 50 characters").
One way to remedy the situation would be to populate, at start-up, some data structures describing all relevant field widths, and pass this to the GUI stuff, so it can check all the lengths and warn the user, before attempting an insert/update. However that seems more effort than elegance, hence me asking about some best practices and/or easy solutions.
|
|
|
|
|
If you are in control of both the front end and the database, I don't see the issue with allowing the front end (be it through the actual front, or a "business layer" or such) to control the validation of your data.
|
|
|
|
|
_Damian S_ wrote: If you are in control of both the front end and the database
I am.
_Damian S_ wrote: I don't see the issue
I see it doable, in more than one way:
1. I could pass the DB field characteristics to the GUI and have the input checked against them by the GUI.
2. I could pass the Control to the back-end and let that decide whether the current content could be stored, and if not, signal that somehow to the user (e.g. by changing the BackColor).
Both seem pretty far from the ideas behind MVVM (which I do not master), and rather cumbersome.
So I'd like to get some real advice, if possible an example.
Note: a number of the text fields have sizes that haven't been fixed yet. Just setting a TextBox.MaxLength to a constant won't cut it. That would be good enough for phone numbers and ZIP codes and the like, but not for unstructured info, such as comment fields (which may change size upon the client's request).
|
|
|
|
|
Client side validation (in a web based system) saves you a postback for each field you are validating.
Personally I validate data in the gui through various means (whether that's through field validation as part of the control or functions or whatever), because that way you have granular control over how your program acts. Of course, you need to update and put out a new version if your data structures change, but you can't have everything!!
|
|
|
|
|
It is a WinForms app, but I tend to prefer "GUI side" validation too.
Here[^] is the best I could come up with so far. Please feel free to comment.
|
|
|
|
|
Luc Pattyn wrote: One way to remedy the situation would be to populate, at start-up, some data
structures describing all relevant field widths, and pass this to the GUI stuff,
so it can check all the lengths and warn the user, before attempting an
insert/update. However that seems more effort than elegance, hence me asking
about some best practices and/or easy solutions.
Generate the database API from the database schema.
At the same time, and in a different API, generate validation classes. That includes length validation and not null validations but does NOT include things like foreign key constraints.
The validation classes are used in the database API before attempting to apply the data to the database. They can also be used elsewhere, because they represent their own API.
This idiom probably works better in a standard distributed arch versus stand alone but the idea still applies.
At any rate that is how I do it and have done it that way for years in a variety of different languages.
But that is how I do it so it is "easy".
The "best-practice" is still - validate it. How you achieve the validation and insure its correctness is a different matter.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
