|
Hi
I have month and year values, I want to get last day of month. How it can be acheived in SQL server 2005?
Shahzad Aslam
Software Engineer
Softech Systems Limited
Cell: +92-321-4606036
Email: shehzadaslam@hotmail.com
|
|
|
|
|
Try one of them......
1.
SELECT Datepart(dd,DateAdd(day, -1, DateAdd(month, DateDiff(month, 0, Getdate())+1, 0)))
2.
Select datepart(dd,dateadd(mm,1,Getdate() - day(Getdate())))
have a nice time........
|
|
|
|
|
Thanx!!!
Shahzad Aslam
Software Engineer
Softech Systems Limited
Cell: +92-321-4606036
Email: shehzadaslam@hotmail.com
|
|
|
|
|
I have created 2 radio buttons on my form but using databinding how i can hendle them?????
Base table name = "Employeer"
Column to be binded = "Gender"
HELP????
|
|
|
|
|
try to set it manually...
radiobutton1.databinding.add("Value", bindinsource("Fields")
|
|
|
|
|
i want to know
when we use sqlserver as backend....
while using reporting
i heard people use msoffice
why is it?
|
|
|
|
|
Hi, I wrote a function in C# to be used as a scalar function in SQL2005:
[Microsoft.SqlServer.Server.SqlFunction(IsPrecise = true, IsDeterministic = true, DataAccess = DataAccessKind.Read)]
public static SqlDecimal GetTaxAmountFromTax(SqlString tax, SqlDecimal amount)
{
SqlConnection conn = new SqlConnection("context connection=true");
SqlDataAdapter adap = new SqlDataAdapter("SELECT * FROM TransactionTaxes", conn);
DataTable taxTable = new DataTable();
adap.Fill(taxTable);
DataRow[] rows = taxTable.Select("[ID] = '" + tax.ToString() + "'");
if (rows.Length == 0) return SqlDecimal.Null;
if (rows[0]["CalculatedOnTax"] != DBNull.Value)
{
if ((bool)rows[0]["Cumulative"])
{
DataRow[] rows1 = taxTable.Select("[ID] = '" + rows[0]["CalculatedOnTax"].ToString() + "'");
if (rows1.Length == 0) return SqlDecimal.Null;
return ((amount + (amount * (decimal)rows1[0]["Rate"]) / new SqlDecimal(100))) * (decimal)rows[0]["Rate"] / new SqlDecimal(100);
}
else return amount * (decimal)rows[0]["Rate"] / new SqlDecimal(100);
}
else return amount * (decimal)rows[0]["Rate"] / new SqlDecimal(100);
}
I want to publish this function making sure the return type has a precision of 2 and a scale of 18. I found that I have to use the SqlFacetAttributes but I am unsure how to do it for a function. I found examples only for a Strored Procedure parameter.
Any help appreciated. Txs
|
|
|
|
|
Hi
Using MS Query for Excel connecting to a SQL Server 2000 database through ODBC.
I am trying to get the last purchase Order price for all products
There are two tables containing the info I need:
Table: poheadm (Purchase Order Header)
order_no
date_entered
Table: podetm (Purchase Order Detail)
order_no
product
local_expect_cost (i.e. the price)
The following statement gives an error "Could not add the table "(SELECT."
SELECT podetm.product, podetm.cost
FROM podetm podetm,
(SELECT max(poheadm.date_entered) as maxdate, poheadm.order_no
FROM poheadm poheadm
GROUP BY order_no) maxresults
WHERE podetm.order_no = maxresults.order_no
The following statement runs, but I get multiple results for each product, not just the latest price
SELECT podetm.product, podetm.local_expect_cost,
(SELECT max(poheadm.date_entered)
FROM poheadm poheadm
WHERE poheadm.order_no = podetm.order_no)
FROM podetm podetm
|
|
|
|
|
Your tables are a little confusing. I'm assuming that this is a one to many relationship. That you have one header record and multiple details. Therefore an order could have multiple products on it. So what are you trying to pull back. The latest total order cost, or the latest cost of one product? Perhaps you could give us an example of what exactly you want outputted from the data.
|
|
|
|
|
HI
Yes, one order can have many products. E.g. One entry in the PoHeadm table can have many line Items in Podetm table
Header Table: Poheadm
Fields: |date_eneterd |order_no|
Data: |2007-05-28 |00001 |
Data: |2007-05-29 |00002 |
Detail Table: podetm
Fields: |order_no |local_expec_cost |product|
Data: |00001 | USD5.06 |AA01 |
Data: |00001 | USD1.00 |AA02 |
Data: |00001 | USD9.00 |AA03 |
Data: |00002 | USD7.00 |AA01 |
Data: |00002 | USD2.00 |AA04 |
So above we have TWO Purchase orders. Order 00001, has 3 line items. Order two has two line items.
As you can see, product AA01 is on two purchase orders, I want to create a list of ALL products, and I want the last price paid. So for the above, the data I want returned would look as follows (each product is ONLY ONCE, and has the latest price and date):
|product |local_expect_cost |date_enetered|
|AA01 |USD7.00 |2007-05-29 | (Data from Latest Date Order2)
|AA02 |USD1.00 |2007-05-28 | (Only Ordered once)
|AA03 |USD9.00 |2007-05-29 |
|AA04 |USD2.00 |2007-05-29 |
|
|
|
|
|
I think I got it to work with this:
SELECT product, local_expect_cost, max(date_entered) <br />
<br />
FROM podetm as mainDetail, poheadm as mainHeader<br />
<br />
WHERE mainDetail.order_no=mainHeader.order_no and date_entered=(SELECT max(date_entered) FROM poheadm as subHeader, podetm as subDetail WHERE subHeader.order_no=subDetail.order_no and subDetail.product=mainDetail.product)<br />
<br />
GROUP BY product, local_expect_cost
And just to note, I think you meant to put 2007-05-28 for the date_entered in the AA03 product line of your output.
I hope this works for you.
|
|
|
|
|
Hi Kschuler
I tried it, but it maxed out the processor, and seemed to hang.
I will only be able to look at it again in the morning, but thanks for your reply in the mean time
BTW, the podetm table has about 14000 records, and the poheadm table about 5000 records. Maybe thats why its slow?
-- modified at 11:03 Thursday 30th August, 2007
|
|
|
|
|
Try this.
select t.product
,t.last_date
,pod.local_expect_cost
from ( select pod.order_no
,pod.product
,max(poh.date_entered) last_date
from poheadm poh
,podetm pod
where poh.order_no = pod.order_no
group by pod.order_no, pod.product ) t
,podetm pod
where pod.order_no = t.order_no
and pod.product = t.productThe temporary query could also be established as a view.
Chris Meech
I am Canadian. [heard in a local bar]
|
|
|
|
|
Tks 4 reply
Tried the statement you suggest, but get the following Error: Could not add the table '('.
|
|
|
|
|
Hi
I also tried pulling the SQL tables into Access and running the same query, but had to change the line:
max(poh.date_entered) last_date
to
max(poh.date_entered) AS last_date ????
But then it returns many records for each product, wheras I only want to return ONE record for EVERY product, and that record must be the once which has the latest date
Maybe I explained what I'm trying to do badly, so if you get a chance, perhaps look at my reply to Kschuler's post as I try to explain what I want in more detail...
|
|
|
|
|
My previous SQL would return too many rows. Try this instead.
select t.product
,t.last_date
,pod.local_expect_cost
from ( select pod.product
,max(poh.date_entered) last_date
from poheadm poh
,podetm pod
where poh.order_no = pod.order_no
group by pod.product ) t
,poheadm poh
,podetm pod
where poh.date_entered = t.last_date
and poh.order_no = pod.order_no
and pod.product = t.product
As long as your order header records have unique date time stamps, you should only get one record for each product. But this assumes that on the podetm table that the order_no, product fields are a unique key. If they are not a unique key, it possible that a product would appear twice.
Good Luck.
Chris Meech
I am Canadian. [heard in a local bar]
|
|
|
|
|
Hi Chris
That did not work either, I got multiple results for each product.
One entry in the PoHeadm table can have many line Items in Podetm table
The same product can appear many times in the podetm table, but each with a different order_no, and hence a different date_entered in the header table.
I tried to write a statement in English stating what I want, hoping that I could develop that into an SQL statement, but even that proved difficult.
Select each item from podetm, and return the product and cost of the order number that has the latest date_entered in the poheadm table????
Maybe if you have time to spend, you could try to write a statement based on the data below?
Header Table: Poheadm
Fields: |date_entered |order_no|
Data: |2007-05-28 |00001 |
Data: |2007-05-29 |00002 |
Detail Table: podetm
Fields: |order_no |local_expec_cost |product|
Data: |00001 | USD5.06 |AA01 |
Data: |00001 | USD1.00 |AA02 |
Data: |00001 | USD9.00 |AA03 |
Data: |00002 | USD7.00 |AA01 |
Data: |00002 | USD2.00 |AA04 |
So above we have TWO Purchase orders. Order 00001, has 3 line items. Order two has 2 line items.
As you can see, product AA01 is on TWO purchase orders, I want to create a list of EACH of the products in podetm, and I want the LAST price paid based on the date_entered field in the poheadm table. So for the above, the data I want returned would look as follows (each product is ONLY ONCE, and has the latest price and date):
|product |local_expect_cost |date_enetered|
|AA01 |USD7.00 |2007-05-29 | (Data from Latest Date Order2)
|AA02 |USD1.00 |2007-05-28 | (Only Ordered once)
|AA03 |USD9.00 |2007-05-29 |
|AA04 |USD2.00 |2007-05-29 |
|
|
|
|
|
These are the settings I've set for a one-to-many relationship between 'order' and 'orderItem' tables:
Enforce Foreign Key Constraint: No
Delete Rule: Set Default
Update Rule: No Action
I've set 'Enforce Foreign Key Constraint' to 'No' so that I can delete an order that has some orderItems.
The orderItem table contains orderId field as the foreign key. Now I want this foreign key to be set to its default value (0) whenever I delete an order record. The problem is whenever I delete an order, the orderId value in the corresponding records in orderItem table remain intact and sustain their orderId value which has been deleted. Do you know what's wrong with that? I'm confused!
|
|
|
|
|
I think I've figured out what's going on. The problem is that I should set Enforce Foreign Key Constraint to Yes for this to work. And, Default Value of orderId should not be (0), it should be something that already exists in order table.
|
|
|
|
|
I have 2 databases with over 7 million records where about half are probably duplicated. I would like to merge these tables and take out the duplicates. The problem comes in where the the only fields are name address and email address(sometimes). So i have to do a dup check on the name and address. The way im doing it is taking 5 min for every 1000 records.
I tried writing a stored procedure that checked each one with a if exists statement then insert. I know there has to be a better way than that. ANy help or am i stuck waiting a couple weeks to be done.
By the way the cpu i'm using is also using 85% cpu to do the checking.
|
|
|
|
|
The answer to your question would depend on where your duplicates can exist. Are there duplicates inside of the individual databases or only between the two databases? If it is the latter you can use a strategy like this:
1) Copy over 1 table to a new table.
2) The new table should have indexes on Name & Address (to avoid mulitple table scans). For speed you can create the indexes after the copy.
3) Create an insert statement qualified by the non-duplicates.
INSERT INTO [NewTable]
(Name, Address, Email, etc...)
SELECT
s.Name,
s.Address,
s.Email,
s.etc...
FROM
[2ndTable] s
LEFT JOIN
[NewTable] n
ON ((s.Name = n.Name)&&(s.Address = n.Address))
WHERE
n.Name IS NULL
|
|
|
|
|
I would like to setup a website where people can purchase products online. I need help figuring out how the money transaction part done.
|
|
|
|
|
I had to know that in Ms SQL database we cannot able grant permission of one particular table in a database to another user but we can have it in Oracle.
Is it true?
Ahamed Azeem
|
|
|
|
|
Try grant select on MyTable to AnotherUser. Its exactly the same as with Oracle.
If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".
|
|
|
|
|
Ok, once again I need some help with replication.
Due to a limit of 5-Transactional Subscription on SQL Server 2005 Workgroup edition I want to make use of a "uni-directional" Merge Publication instead, simply because those subscriptions are only limited to 25, which will suffice for now.
Question: Can I create a merge-replication from SQL 2005 >to> SQL 2000?
What I've tried:
When creating the publication I get an option to set the publication's compatibility to include SQL 2000 servers, but as soon as I try to add an article SQL fails with an error reporting that my publication's compatibility level should be at leat 90RTM and that I should run sp_changemergepublication.
After running that SP, I can successfully add articles but I cannot subscribe any of our SQL 2000 servers.
I'm guessing the article itself should somehow be set to be compatible... but that's only a guess.
_______________________________________________________________________
http://www.readytogiveup.com/[ ^]
"you can't forget something you never knew..." M. Du Toit
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
