Purchase Order Query: Very Funny And Challenge ^_^

Mar 1, 2006

i query a purchase order table, there is one column called PO_No, format: LP-0245111-0004

i make following statement to query: the middle code act as my id, using it search my records, the last 4 digit used to find the last purchase order number

SqlSelectCommand2.CommandText = "SELECT PO_No FROM [PURCHASE ORDER] WHERE PO_No Like '%" & GetYearCode() & "%' ORDER BY Right(PO_No, 4) DESC"

i checked my database, last record is LP-0545381-0300

in my debuging process, surprisingly found that selected record is LP-0545381-301 !

any one hav any suggestion? ^_^

View 2 Replies

I Wanna Query A Purchase Order Table!...but Can Not:(

Feb 28, 2006

i use microaccess create table, there is a filed call"Complete_PO", value"yes/no"
i wrote following statement to select it, but at runtime, there is warning message"...constraint...one or more row violating non-unique and so so..." how to solve it
SqlSelectCommand2.CommandText = "SELECT Complete_PO FROM [PURCHASE ORDER] WHERE [PO_No] Like '%" & GetYearCode() & "%' ORDER BY Right(PO_No,4) desc"
PoNum_SqlDataAdapter.Fill(PO_DataSet1)
TextBox1.Text = PO_DataSet1.Tables("PURCHASE ORDER").Rows(0).Item("Complete_PO").ToString()

View 5 Replies View Related

How To Add Order Item Into A Purchase Order Using A Stored Procedure/Trigger?

Jan 4, 2008

Hey guys, i need to find out how can i add order items under a Purchase Order number.
My table relationship is PurchaseOrder ->PurchaseOrderItem.

below is a Stored Procedure that i have wrote in creating a PO:

CREATE PROC spCreatePO (@SupplierID SmallInt, @date datetime, @POno SmallInt OUTPUT)

AS

BEGIN

INSERT INTO PurchaseOrder (PurchaseOrderDate, SupplierID) VALUES(@date, @SupplierID)

END

SET @POno = @@IDENTITY

RETURN

However, how do i make it that it will automatically adds item under the POno being gernerated? can i use a trigger so that whenever a Insert for PO is success, it automaticallys proceed to adding the items into the table PurcahseOrderItem?

CREATE TRIGGER trgInsertPOItem

ON PurchaseOrderItem

FOR INSERT

AS

BEGIN

'What do i entered???'
END

RETURN

help is needed asap! thanks!

View 14 Replies View Related

Query Return Funny Characters

Apr 18, 2006

I run a query in QA which return funny characters (suppose to be chinese characters). I try saved as CSV and open in excel, still it remain as those funny characters... What can I do to get those output into the chinese character. It doesn't need to be in QA... but I need it in Excel.
Thanks

View 2 Replies View Related

Query Challenge

Jun 14, 2001

I have a query that I am trying to optimize. It works on some 9000 records and runs too slow. What the query does is takes the multiple assignment of a single contact record to multiple attributes (a.k.a many-to-many). For example:

Membership table.
Contact_ID
1
2
3

Relate table
1 1
1 3
1 4

Relate Item
1 item1
2 item2
3 item3
4 item4

The query will take all ocurrences of the related items and place them in a single field while delimiting by comma "item1" , "item3", "item4"

Here is the query as it exists now:

select
CONTACT_ID,
UNION_NAME
into #tmp
from MEM_UN MU
inner join MEM_UN_REL MUR
on MU.UNION_ID = MUR.UNION_ID
order by CONTACT_ID, UNION_NAME

create TABLE #unionlist (
CONTACT_ID int primary key,
UNIONS varchar(2000) null)

insert into #unionlist (CONTACT_ID, UNIONS)
select distinct CONTACT_ID, UNIONS = '' from MEMBERSHIP

while exists(select CONTACT_ID from #tmp)
BEGIN
update #unionlist
set UNIONS = UNIONS + '"' + (
select min(UNION_NAME) from #tmp
where #unionlist.CONTACT_ID = #tmp.CONTACT_ID
) + '",'
where CONTACT_ID in (select CONTACT_ID from #tmp)

update #unionlist
set UNIONS = UNIONS + '"",'
where CONTACT_ID not in (select CONTACT_ID FROM #tmp)

delete FROM #tmp where UNION_NAME in (
select min(UNION_NAME) from #tmp tmp2
where #tmp.CONTACT_ID = tmp2.CONTACT_ID
)
END

I believe that the slow down is in the process of deleting from #tmp every time it loops through the recordset.

Any suggestions appreciated,

Thx,

Dave

View 1 Replies View Related

Query Challenge

Sep 7, 2004

Hello Everybody,

I am attaching a picture of what the table should look like before and after the transformation. Can anybody help? Thank you in advance.

View 2 Replies View Related

A Challenge: Need To Write A Difficult Query

Aug 18, 2007

GO
CREATE TABLE [dbo].[Product]
(
[ProductId] [smallint] IDENTITY(1,1) NOT NULL CONSTRAINT PkProduct_ProductId PRIMARY KEY,
[Name] [varchar](52) NOT NULL,
[Type] [smallint] NOT NULL,
)
For this table
I have to write the querywhich willget the TOP 1 Row of each Type.
I know the alternate way of doing this by union.
But this is not professional.
Can anyone resolve this issue?

View 2 Replies View Related

Return Missing Records Over Multiple Tables. Query Challenge!

Mar 6, 2008

I have received some data out of a relational database that is incomplete and I need to find where the holes are. Essentially, I have three tables. One table has a primary key of PID. The other two tables have PID as a foreign key. Each table should have at least one instance of every available PID.

I need to find out which ones are in the second and third table that do not show up in the first one,
which ones are in the first and third but not in the second,
and which ones are in the first and second but not in the third.

I've come up with quite a few ways of working it but they all involve multiple union statements (or dumping to temp tables) that are joining back to the original tables and then unioning and sorting the results. It just seems like there should be a clean elegant way to do this.

Here is an example:

create table TBL1(PID int, info1 varchar(10) )

Create table TBL2(TID int,PID int)

Create table TBL3(XID int,PID int)

insert into TBL1

select '1','Someone' union all

select '2','Will ' union all

select '4','Have' union all

select '7','An' union all

select '8','Answer' union all

select '9','ForMe'

insert into TBL2

select '1','1' union all

select '2','1' union all

select '3','8' union all

select '4','2' union all

select '5','3' union all

select '6','3' union all

select '7','5' union all

select '8','9'

insert into TBL3

select '1','10' union all

select '2','10' union all

select '3','8' union all

select '4','6' union all

select '5','7' union all

select '6','3' union all

select '7','5' union all

select '8','9'

I need to find the PID and the table it is missing from. So the results should look like:

PID
MISSING FROM

1
TBL3

2
TBL3

3
TBL1

4
TBL2

4
TBL3

5
TBL1

6
TBL1

6
TBL2

7
TBL2

10
TBL1

10
TBL2

Thanks all.

View 5 Replies View Related

Tech's Are So Funny

Aug 16, 2004

Ok a network tech put an executive assistants database on sql with the upsize wizard in access. Only problem is that she cant input anything into the database, my guess is that its due to no Primary key in the new sql table. I have had this problem before am I correct in my assumptions???

View 6 Replies View Related

Funny Search Error

Oct 28, 2004

I have two fields I am searching on .... address and zipCode

When I have both pieces of data, everything is fine, i get back the expected results, but i am unable able to search on the individual pieces. Here is the SQL I am using. It's placed within a store procedure in Sqlserver that I amreferencing from my VB.net code.... any help??

IF ((NOT @address IS NULL) AND( NOT @zipCode IS NULL))
BEGIN
SELECT OrderNumber FROM Orders WHERE ShpAddr_Address1 = @address AND ShpAddr_ZipCode = @zipCode
END

ELSE IF NOT @address IS NULL
BEGIN
SELECT OrderNumber FROM Orders WHERE ShpAddr_Address1 = @address
END

ELSE IF NOT @zipCode IS NULL
BEGIN
SELECT OrderNumber FROM Orders WHERE ShpAddr_ZipCode = @zipCode
END

any help would be great. thanks

View 3 Replies View Related

Funny Network Behaviour

Jul 20, 2005

Hi,In order to establish a security enhanced SQL server setup, I triedto switch off network access by disabling all networking protocols,so that the server can only be reached through a pipe. Neverthelessthe server was visible in the network and could be accessed fromall clients. Does anybody know what is going on here?Georg

View 1 Replies View Related

Weird And Funny Question

Sep 22, 2006

I specified configuration like shown below

"select * from table where id<>***" always doesn't work

however i changed that like

"id != ***" working!

Who can tell me why?

View 3 Replies View Related

Funny Problem (bug?) Using MAX() On SQL Mobile

Feb 8, 2006

This sure looks like a bug, but I'm still new...

First I create a simple database:

SqlCeEngine eng = new SqlCeEngine(@"Data Source=My Documents est.sdf");
eng.CreateDatabase();
eng.Dispose();

Then I put something in it:

SqlCeConnection conn = new SqlCeConnection(@"Data Source=My Documents est.sdf");
SqlCeCommand cmd = new SqlCeCommand("CREATE TABLE myTable (ID int, name nchar(10))");
cmd.Connection=conn;
conn.Open();
cmd.ExecuteNonQuery();
cmd.CommandText="INSERT INTO myTable (ID, name) VALUES (1,'bill')";
cmd.ExecuteNonQuery();

Then I try to get the maximum value from the first column.

cmd.CommandText = "SELECT max(ID) FROM myTable";
int index = (int)cmd.ExecuteScalar();

At this point I get an InvalidCastException raised. Using the datareader to extract the value produces the same exception, however I can go through the Query Analyzer on the device and execute the query and it comes out fine...???

This has really got me confused. Any thoughts?

View 8 Replies View Related

Funny Error During Runs

Jan 26, 2007

Can any one please help explain why this error has happened, it is not a normal error of SSIS,

"SSIS Debug host has encountered a problem and needs to close. We are sorry for the inconvenience"

It has 2 buttons Debug and Close, this error happened in the middle of a script task.

View 1 Replies View Related

Funny Characters And Repeating Digits

Jan 17, 2003

How do l filter out funny characters in a field like (',','@','#','"'.'/')etc And Repeating digits Like 5555555,2222,111111,00,
000000000.

Tried using the patindex but my method is too long.How do l do that ?

View 3 Replies View Related

Funny SQL Server Error Messages - Anyone Has An Explanation?

Apr 3, 2006

I am getting a new server online at a customer, and our system shows very funny erorrs under full load. No explanations could be found. Anyone has some?
Here we go:
Server failed to resume the transaction, desc: 360000054a. The transaction active in this session has been committed or aborted by another session.,
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
and
New request is not allowed to start because it should come with valid transaction descriptor.
System.Data.SqlClient.SqlException
I am a little lost on those. Simple fact is that I do not find any description of those. Anyone an idea what causes these?

View 2 Replies View Related

SQL SERVER Purchase

Nov 9, 2006

Guys,

Would you mind advising me on what sql server license would be suitable?
What I am concerned about having is the ability to access a database from 10 computers.
I like to use the Enterprise Manager, Query Analyzer, write proceduers, Views and Triggers. Not sure if this affects anything.

View 3 Replies View Related

Need Opinion On New Purchase

Dec 18, 2007

I need to purchase a new computer for a small medical clinic which will basically only have one purpose: to answer to read and write queries to a SQL Server 2005 which is resident on that computer. Queries come from the current 8 stations (up to 14 stations in the future). Most of the time, only 3 stations will be active at a time. Queries are mostly to access patient file information, are not complex and are short-lived.

A friend of mine who owns a computer store just quoted me for a dual quad-core Xeon 5405 2GHz system with Windows Server 2003 10 Cals. I'm concerned about the following:
- What's the use in having 8 cores, each of them running at only
2GHz, when there's really only one service running (SQL Server
2005, likely Express Edition) on the computer. Does SQL Server
have the capability to make use of all cores? Otherwise, why
spend more for Xeon and so many cores instead of a single
C2D running at a faster speed of say 3GHz ?
- What would be the advantage of using a Windows Server over
Windows XP in a peer-to-peer configuration? I don't buy into
the 10 connection limit because the TCPIP.sys file can be
altered to move that limit up, so 14 stations does not trigger
the need for Windows Server in and of itself.

Thanks all!

View 5 Replies View Related

Dissapointing Book Purchase

Jan 21, 2004

I went in to waterstones today to try and get a copy of sql pocket reference (o'riely publishers i think) but there were none in stock, as the new edition is out on the 31st.

so i bought sam teach urself sql "in ten minutes"

unfortunately it covers the alter tag but not its use to modify columns >:(

I'm a bit stuck with this one atm
---------------------------------------
1> alter table news modify ( dateadd varchar(80))
2> go
Msg 170, Level 15, State 1, Server NEIL, Line 1
Line 1: Incorrect syntax near '('.
1>

what exactly am I doing wrong?

View 6 Replies View Related

Idea On Hardware Purchase

Oct 29, 2004

I am looking for a good reference on hardware specs for a dedicated SQL server. I don't want to talk to vendors, because I'm not looking to get snowed. Does anyone know of any resources? The server is to be a dedicated dataserver, for about 300 clients.

View 9 Replies View Related

Which Sql Server Product To Purchase?

Oct 14, 2006

Hi,

The SQL server 2005 is needed to be put for user's access as database backend for VB.net application. We need only one system for development. Which is best suited for our application?

Any suggestions?

View 1 Replies View Related

Average Duration To Purchase Again Same Product

Sep 6, 2015

I have data concerning sale of several products, columns are two : Name_product, Purchase_day

I need to calculate with SQL the average duration (number of days) to purchase again the same product among the products

As in example, one product can be bought many times, so this product we will see it repeated several times at different days "Purchase_day" so here we can have Duration between each two successive purchases for the same product, but for the last purchase of this product the duration until today will be the day of yesterday minus the last day of purchase, and finally the average duration of this product will be the sum of all duration of this product / number of all duration of this product.

Link to the table copy and paste : [URL] .....

View 2 Replies View Related

Exclude All ID Which Doesn't Purchase Any Code

Aug 30, 2013

how can i exclude all id which doesn't purchase any code.

codeA,codeB,codeC,codeD,.....
ID 1 purchase codeA, this will not be pulled out.
ID 2 didnt purchase any code, this will be pulled out.

I tried select id,code from tableA where code not in ('codeA','codeB','codeC','codeD',...)

but it still pull out ID 1.

View 1 Replies View Related

Do We Get SQL2000 Media With A SQL2005 Purchase?

Mar 13, 2007

My company is planning to purchase a SQL2005 standard Processor license (unlimited users - about $5000)

Does anyone know if we can obtain SQL2000 media along with the purchase. Because of the app we are running and its stage of development, we have to install and run SQL2000 for about 6 months before we can run Sql2005. We dont want to purchase SQL2000 for such a short term use.

I asked a DELL rep to help me with this over a month ago and he still has no answers for me.

Any help with this would be greatly appreciated.

View 4 Replies View Related

SQL Statement To Retrieve Rows With More Than One Purchase On Any Particular Day

Sep 10, 2007

Hi,
I am new at SQL hopefully this would be a rather easy question for you guys to help me out with.
I have a table called PRODUCT with the following fields:
a. Product Name
b. Product Dept.
c. Purchase Date.

I would like to run a query to obtain all rows tha has more than one purchases on any particular day.

Any replies would be great.

View 1 Replies View Related

Does SQL 2005 Purchase Come With Access To SQL 2000 Media?

Mar 13, 2007

View 3 Replies View Related

Transact SQL :: Finding Last Purchase Price For Each Product?

Sep 29, 2015

I need to find the last purchase price for each product. If I run the following code, I correctly get 1 result for each productID and the last purchase order number.

SELECT pod.article as ProductID,max(pod.order_ID) as LastOrderfrom apodetail podgroup by pod.articleorder by pod.article

Now I need to add in the price for that product on that orderID. I've tried the following self join query, tried it without the join, and tried adding DISTINCT, but they all return more than 1 row per ProductID.

SELECT pod.article as ProductID,max(pod.order_ID) as LastOrder,pod2.rev_price as UnitPricefrom apodetail podjoinapodetail pod2on (pod2.order_ID = pod.order_id)group by pod.article,pod2.rev_priceorder by pod.article

How can I get it to simply add the price to the first query?

View 10 Replies View Related

Table Set Of Records - Show Purchase Time When It Crossed 1000

Feb 10, 2014

I have a table set of records. Its contains some customerID,SportsGoods,Price in different datetime. I want to add customer spent. If crossed 1000 means i have to show purchase time when it is crossed 1000. I need query without while and looping.

Example:

Customer NameGoodsPriceDatePurchased
ABat2501/31/2014
ABall221/31/2014
BCarrom Board4752/2/2014
CTennis Ball502/1/2014
AFootball1502/2/2014
DBat2501/31/2014
BBall221/31/2014
AHockey Bat1252/4/2014
CChess552/4/2014
AVolley Ball552/4/2014

View 9 Replies View Related

Time Slot Which Has Maximum Number Of Purchase Orders Created Or Updated

Jul 15, 2014

I need to write down a sql query wherein in one particular day(user will enter manually), i need to find out a 15 minutes slot wherein purchase order's created or updated are the highest.

i.e. out of 96 slots(15 minute slot each)-I need to find the slot which has maximum number of Purchase orders created or updated.

View 7 Replies View Related

Order By Query

Jun 14, 2007

Hi, I am using below query:
SELECT tbh_Articles.ArticleID, tbh_Articles.AddedDate, tbh_Articles.AddedBy, tbh_Articles.CategoryID, tbh_Articles.Title, tbh_Articles.Abstract, tbh_Articles.Body, tbh_Articles.Country, tbh_Articles.State, tbh_Articles.City, tbh_Articles.ReleaseDate, tbh_Articles.ExpireDate, tbh_Articles.Approved, tbh_Articles.Listed, tbh_Articles.CommentsEnabled, tbh_Articles.OnlyForMembers, tbh_Articles.ViewCount, tbh_Articles.Votes, tbh_Articles.TotalRating, tbh_Articles.ImageURL, tbh_Articles.specialFROM tbh_Lang CROSS JOIN tbh_ArticlesWHERE (tbh_Lang.LangID = @LanguageID) AND (tbh_Articles.ArticleID = tbh_Lang.ArticleMain OR tbh_Articles.ArticleID = tbh_Lang.ArticleSecond1 OR tbh_Articles.ArticleID = tbh_Lang.ArticleSecond2 OR tbh_Articles.ArticleID = tbh_Lang.ArticleSecond3 OR tbh_Articles.ArticleID = tbh_Lang.ArticleSecond4 OR tbh_Articles.ArticleID = tbh_Lang.ArticleSecond5)
Problem is that I want to sort in a manner which the results returns as ArticleMain as the first column, ArticleSecond1 as the second and so on...
Tables structure: tbh_Articles(id, title, body...) ; tbh_Lang(id,ArticleMain,ArticleSecond1 ,ArticleSecond2.... )
Any suggestions?

View 9 Replies View Related

Order By Query

Sep 29, 2007

I have a Comment Table where a comment can have a reply, if the comment is replied to I want the reply to appear under the comment.

Based on the Fields CommentID and Parent ID the parentID is the Comment and the Comment with a ParentID set too that comment is the answer.

How do I build this Query?

View 7 Replies View Related

SQL Query Or SP? Order Value Between Two Dates

Feb 7, 2008

Hi all,

Been having a good root around the forums and the site here and there's some real smart people on here, i'm hoping one or more of them can help me out. I'm expecting this to be a simple question for some of you, however it's way beyond me at this point!

Table Structure (abridged, relevant columns):

Orders:

Code:

[Orders](
[OrderID] [int] IDENTITY(1,1)
[OrderDateTime] [datetime]
[OrderSiteID] [nvarchar](255)
[OrderOffline] [bit]
[OrderSentToWP] [bit]
[OrderReceivedFromWP] [bit]
[OrderAuthorised] [bit]
[OrderCancelled] [bit]
[OrderApproved] [bit]
[OrderFraud] [bit]
[OrderDispatched] [bit]

OrderItems:

Code:

[OrderItems](
[OrderID] [int]
[ProductID] [nvarchar](255)
[Quantity] [int]
[Price] [real]
[Weight] [real]

Products:

Code:

[Products](
[ProductID] [uniqueidentifier]
[ProductCode] [nvarchar](255)
[ProductTitle] [nvarchar](255)

Product price is captured at time of order, so that reports aren't affected by discounts or promotions, and stored with the productid in orderitems.

I want to get a report between a set of dates and with certain flags set (see below example) and then get a list of unique products, quantity sold and sales values for that products. Results table would have 4 columns; ProductCode, ProductTitle, QuantitySold, Sales Value.

So far I have this:

Code:

SELECT Products.ProductCode, Products.ProductTitle, SUM(OrderItems.Quantity) AS QuantitySold
FROM Orders INNER JOIN
OrderItems ON Orders.OrderID = OrderItems.OrderID INNER JOIN
Products ON OrderItems.ProductID = Products.ProductID
WHERE (Orders.OrderDateTime BETWEEN '2007/01/01' AND '2007/12/31') AND
(Orders.OrderSentToWP = 1) AND (Orders.OrderReceivedFromWP = 1) AND (Orders.OrderAuthorised = 1) AND (Orders.OrderCancelled = 0) AND
(Orders.OrderDispatched = 1) AND (Orders.OrderApproved = 1) AND (Orders.OrderFraud = 0) AND Orders.OrderSiteID= 'someguid'
GROUP BY Products.ProductCode, Products.ProductTitle

Which gets my summed quantities, and I guess I could use ASP to multiply that by the current price, but that defeats the point of setting the database up properly in the first place! I know how to design data, i just don't know how to get it back out again

I could most likely just do the whole thing in ASP and get it to output the correct answer, so if it's impossible/very difficult to do it in pure SQL then I'll go that route. Ideal situation would be a stored proc or saved query that I can pass a start date, an end date and a siteid to and that will get me the answers I want!

Thanks in advance to anyone that looks at this for me.

Also, any recommended books/sites to learn this kind of query?

Richard

View 4 Replies View Related

Parsename Query Order

Jun 5, 2008

Greatful for any help....

Have the query below which is taking delimited address information from _Venue column. This works well apart from the order it is returned, for example, the output below has the address tittle displayed in a different column for each row

Queen Elizabeth's Hunting Lodge is in Address1
All Saints' Church is in Address2
Audley End House is in Address3

As I need to reference from the query the correct part of the address from the same location each time, is there anyway to get around this?

Thanks in advance

SELECT coalesce (PARSENAME(REPLACE(_Venue,',', '.'),4), '-') address1
,coalesce(PARSENAME(REPLACE(_Venue, ',', '.'),3), '-') address2
,coalesce(PARSENAME(REPLACE(_Venue, ',', '.'),2), '-') address3
,coalesce(PARSENAME(REPLACE(_Venue, ',', '.'),1), '-') address4
FROM table

Address1 Address2Address3Address4
----------------------------------------------------------------------------------------
Queen Elizabeth's Hunting LodgeRangers RoadChingfordLondon E4 7 QH
- All Saints' ChurchShrub End RoadColchester
---Audley End House

View 10 Replies View Related