|
After asking so many questions, i finally succeeded in tweaking out a query that is correct.
it is as follows;
Select Customer, Count, Credit, Debit, Balance, Date from
(
Select Customer_ID as Customer, (count(Credit) + count(Debit)) as Count, sum(Credit) as Credit, sum(Debit) as Debit
from ThriftsTrans Where Transaction_Date Between '15/Dec/2013' And '18/Dec/2013' Group By Customer_ID
) as a left join (
Select Customer_ID as Cust, Account_Balance as Balance,
Transaction_Date as Date From ThriftsTrans Where Transaction_Date In
(Select Max(Transaction_Date) From ThriftsTrans Where Transaction_Date
Between '15/Dec/2013' And '18/Dec/2013' Group By Customer_ID)
) as b on a.Customer = b.Cust
thanks to you all
|
|
|
|
|
Hi,
I have to write a query where there will be multiple filters selected by user. These filters will be selected randomly in any combination
(for example filters like: Co.name, deptId, Startdate, endDate, employee name, Assignment, Shifts, class)
Can anyone tell me how to write such type of query?
As there cn be n numbers of permutation & combination of all filters.
|
|
|
|
|
one solution that I have used before is
select *
from table
where ((@endDate is null) or (enddate = @enddate)) and
((@deptid is null) or (deptid = @deptid)) and
((@employeeName is null) or (employeeName = @employeeName))
Every day, thousands of innocent plants are killed by vegetarians.
Help end the violence EAT BACON
|
|
|
|
|
Thanks Simon..for ur reply will implement this today and let u know..hope this works for me.
|
|
|
|
|
This is not working in case I dont give deptid or any one of parameters.
pls help me.
|
|
|
|
|
What simon supplied is the idea/concept of how it should be done, writing the actual query is up to you.
The sample shows you that simon is testing the value of the variable to find out if it is null and if it is not null then applying the filter. You need to understand the concept and extend it to your needs. That is called learning!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Share your query first
thatrajaCode converters | Education Needed
No thanks, I am all stocked up. - Luc Pattyn
When you're wrestling a gorilla, you don't stop when you're tired, you stop when the gorilla is - Henry Minute
|
|
|
|
|
I run a query in SSMS. A date/time column displays correctly like "2013-12-15 12:19:43.583".
I get the data table object back into my app and iterate through the data rows. Converting the column using DateTime always "loses" the millisecond component. I tried converting it to a string just to see if it was coming through that way, but no, the millisecond is always "lost". I then changed the query to cast the datetime column into two columns, one for the date and one for the time. Converting the time column to a DateTime now correctly contains the millisecond component.
If the column in the database is defined as datetime why am I not getting the millsecond component? There's no problem converting the value in the row using DateTime but the loss of the millsecond puzzles me because I thought a database datetime column would be wholly compatible with a Dotnet DateTime object?
Are my pudgy fingers, and thus by association, my pudgy brain, doing something wrong in my code?
If there is one thing more dangerous than getting between a bear and her cubs it's getting between my wife and her chocolate.
|
|
|
|
|
Thankfully in all my years as a LOB developer I have not once had to utilise the millisecond information so I have no idea 
I'm assuming this is an intellectual exercise only!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Nothing to see here. 
Are you using a datareader?
If so, using DateTime myDate = (DateTime)reader["MyDateTimeColumn"]; will convert the DateTime from the DB into a string and then convert it back to DateTime, and thus losing precision.
Instead use reader.GetDateTime(reader.GetOrdinal("MyDateTimeColumn"));
<edit>
Instead use DateTime myDate = (DateTime)reader.GetSQLDateTime(reader.GetOrdinal("MyDateTimeColumn"));
or DateTime myDate = (DateTime)reader.GetSQLValue(reader.GetOrdinal("MyDateTimeColumn"));
</edit>
Otherwise you need to update the question with a bit of code.
modified 16-Dec-13 9:01am.
|
|
|
|
|
Jörgen Andersson wrote: (DateTime)reader["MyDateTimeColumn"]; will convert the DateTime from the DB into a string and then convert it back to DateTime
Er, no it won't. It will take the DateTime value, box it as an Object, and then un-box it to a DateTime. At no point will the value be converted to or from a string.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Correct me if I'm wrong here (it's quite possible)
But assuming SQLServer, it will return a SQLDateTime value, as GetValue will only return dotnet types the SQLDateTime will be internally converted to a dotnet type.
The Operator for converting from SQLDateTime to DateTime is an Explicit Operator, and as far as I know this internal conversion will not use explicit operators, so it will be an implicit conversion to String instead.
The solution is to either use GetSQLValue or GetSQLDateTime or GetDateTime. <edit>Apparently not GetDateTime either.</edit>
modified 16-Dec-13 7:50am.
|
|
|
|
|
No, the value will not be converted to a string.
Tracing through the code with Reflector, the GetDateTime method returns the value of the SqlBuffer.DateTime property. The indexer calls the GetValue method, which returns the value of the SqlBuffer.Value property, which contains a switch block based on the type returned from the database. For the datetime type, it returns the value of the SqlBuffer.DateTime property.
GetSqlDateTime returns the value of the SqlBuffer.SqlDateTime property. Both SqlBuffer.SqlDateTime and SqlBuffer.DateTime construct the value from two integers returned by SQL. Neither property converts the value to a string.
It might be slightly more efficient to call GetDateTime if you know that the value is a datetime type, but the indexer and GetValue method will return exactly the same value.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Had to download Reflector to check this.
Also had to update my understanding about SqlDataReader. Tonight I'll eventually deepfry an author of an article, if I can find it back.
|
|
|
|
|
How are you inspecting the value of the column? The debugger is probably using the default .ToString method, which will not display the full value. If you use the format string "yyyy-MM-dd HH:mm:ss.fff", you should see the full value.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Here is the the link of samples of ASP.NET's Membership, Roles, and Profile, where Displaying an Informative Message in the Face of an Invalid Login, but there has been used SQL database file, But I need as it is a MS Access Database file.
Can Anybody help please
http://www.4guysfromrolla.com/articles/050306-1.aspx[^]
|
|
|
|
|
Please don't use Access; consider SQL Server Express or SQL Server CE.
|
|
|
|
|
OK Good Suggestion, I tried and got success, Thanks
|
|
|
|
|
I am trying to pull all data from SQL Database A, Table 1 where column name is X, and the value in column X equals the value in Database B, Table 2, column name Y.
So I have 2 databases on the same server.
One named A, the other named B.
I need to get all the data from A, Table1, where the values of Column Name X are equal to the values in in Column Name Y in B, Table2, and insert/write them to a new table in Database B.
Any and all help is appreciated. My SQL writing skills are very sub par.
If the title, and the subject differ, I apologize. I am just trying to put the thought thru my head as I write.
|
|
|
|
|
This is the general gist of where I think you need to be heading:
insert into B.NewTable
select a1.*
from A.Table1 a1
join B.Table2 b2
on a1.X = b2.Y
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
a1 and b2 are columns? what is X and Y?
|
|
|
|
|
a1 and b2 are aliases for tables.
moordoom wrote: what is X and Y?
They are the columns you mentioned in your question.
I think you need to read a book on sql or get some basic training, since if you cannot make sense of my answer you need to start reading.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
|
Looking at the W3 school, it shows table to table joins, not database to database joins.
Here is what I have so far... (minus the insert, and it works)
SELECT * from Table1
join Database2.Table2
on ColumnX = ColumnY
X is a Column in Database1.Table1, and Y is a Column in Database2.Table2.
This gets me my info when I run the query on Database1.
But now I want write these info into Database2.Table3 and delete what was in there before.
So would my statement then be...??
DELETE * FROM Database2.Table3
INSERT INTO Database2.Table3
SELECT * from Table1
join Database2.Table2
on ColumnX = ColumnY
|
|
|
|
|
I am not able to more than 250 records insert into table in sql server 2008 through (Asp.net upload program)
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
