I have a BOM table with all finished item receipes and semi items recipes. create a query where semi item materials are also listed in finished item recipe.
View 5 Replieshi everyone
what the matrix's class name ?
where is the matrix's dll?
Can i create a new class extends the matrix?
I want to override the matrix's onpaint method.
Can i do this ?
why the table not have the column group?
Hi,
Here's my problem. I have 2 tasks defined in my Control Flow tab:
EXECUTE SQL--------->EXECUTE DTS 2000 PACKAGE
When I attempt to run it, by right-clicking the EXECUTE SQL task, and selecting "Execute Task", it only runs the EXECUTE SQL part (successfully), and does not "kick off" the EXECUTE DTS 2000 PACKAGE, after it is done running (even though it completes successfully, as shown by the green box).
Yes, they are connected by a dark green arrow, as indicated in my diagram above.
Why is this?? Am I missing something here? Need help.
THANKS
Hello,Basically, I have a table with 2 fieldsId item#1 33332 33333 22224 22225 22226 33337 33338 3333I would like to only select the last identical Item# which in this case would be the id 6,7 and 8Any idea how could I do that?Thanks
View 1 Replies View Related
I am having a hard time creating a view with some complex math to chow availability for rental items.
My tables are as follows:
OrderHeader
OrderID int,
Site int,
StartDate datetime,
EndDate datetime
OrderDetail
OrderDetailID int,
Site int,
OrderID int,
ItemName varchar(30),
Qty int
ShipHeader
ShippingID int,
Type int,
ActionDate datetime
ShipDetail
ShipDetailID int ,
ShippingID int,
OrderDetailID int,
Qty int
Transaction
TransactionID int,
Type int
PurchaseHeader
PurchaseHeaderID int,
Site int
PickupDate datetime,
ReturnDate datetime,
PurchaseDetails
PurchaseDetailsID int,
PurchaseHeaderID int,
ItemName varchar(30),
Qty int
I need a view that shows how many items will be available on a particular day for a specified item, date range and a specified €śSite€? (Office location. For example, NY, LA, CA, etc.)
The quantity actually owned is calculated from the Transaction table. Type, Qty are the columns. (type 1 is a purchase or addition and type 2 is a sale or subtraction. The quantity owned is the sum of all type 1s minus the sum of all type 2s.
The ShipHeader table also shows types. Type 1 is ship, Type 2 is return and Type 3 is lost.
Initially, all availability is calculated based on the FromDate and ToDate of our order headers. However, once the order has shipped, the availability should change to our ship and return dates in our ShipDetails table. Additionally, there is a PurchaseDetails table that shows items to be €śSub-Rented€? for a time frame. The PurchaseHeader table contains the FromDate and ToDate for these sub-rented items to be added to availability. If an item is kept beyond the due date set in the OrderHeader, then the item should show as unavailable from the FromDate through all future dates (as there is no way of telling when a late item will be returning. Once, the item is either returned or marked as lost, it should become available again as of that date.
The view should list dates in each row with the number of units available on that date.
Also, a second view needs to be created that shows the details for the dates listed in the first view. this is essentially a list of the orders or puchase orders that were used to calculate the number available.
I think this covers it.
As I said, this is a bit complicated. i understand what needs to be done but need assistance assembling the code to make it happen.
I am having trouble finishing the last bit of a report. The report shows orders that customers have placed that contain 0 promo items, All promo items (all items in order are promo items), and a mix of promo and non promo (at least 1 promo item and 1 non-promo item). Ive simplified this a bit for ease of understanding but lets assume we have 2 tables: A Promo table that contains the items on promotion and the dates that promotion is valid, and a Sales table, that contains the order number, order date, and sku ordered.
I've already written code that finds orders that have at least 1 promo item in them, and using that, I can determine what orders have 0 promo items in them. Where I am stuck is taking the orders that have at least 1 promo item in them, and separating them into orders that have only promo items, and those that have both promo and not promo items in them. Also, there are several promos throughout the year (called "Offers") so in my code below, you can see 2 different Offers ("JF" and "MA") with their corresponding dates they are valid. They will never overlap. My results also have to be split out by Offer so management can look at the results of each offer separately. Here is some code:
Code:
create table #Promos (
Offer varchar(2) null,
SKU int null,
StartDt date null,
EndDt date null
[Code] ....
So my results should show OrderNo A1111 in the Promo and No Promo group because of SKU 5 not being promotional during the time that order was placed. OrderNo A2222 should be in the Promo Only group because both SKUs on the order were promotional at the time the order was placed.
Hi! I'm creating a social network, and in one page I need to compare each result of the datalist to a value of a table in my database. For example, I have the datalist showing all the entries in the table users, and when I am showing this information, I want each dataitem to be compared to a select statement of the friends of the logged in user, so that if that datalistitem is present in the results of that other select, I will change the text of a field to say "already a friend". If the user is not present in that select, ie is not a friend of the user who is logged in, the text will say "add friend". I have this comparison working already for a specific name, but not for the database query. Can anyone please help me? The code is below... <%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %><%@ Import Namespace="System.Data" %><%@ Import Namespace="System.Data.SqlClient" %><asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server"><script language="VB" runat="server"> Sub Page_Load(Sender As Object, E As EventArgs) Dim DS As DataSet Dim MyConnection As SqlConnection Dim MyCommand As SqlDataAdapter MyConnection = New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True") MyCommand = New SqlDataAdapter("select * from aspnet_Users where IsAnonymous='False'", MyConnection) 'Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|;Integrated Security=True;User Instance=True DS = New DataSet() MyCommand.Fill(DS, "aspnet_Users") MyDataList.DataSource = DS.Tables("aspnet_Users").DefaultView MyDataList.DataBind() End Sub</script><body> <br /> <ASP:DataList id="MyDataList" RepeatColumns="1" runat="server"> <ItemTemplate> <table cellpadding="10" style="font: 10pt verdana" cellspacing="0"> <tr> <td width="1" bgcolor="BD8672"/> <td valign="top"> </td> <td valign="top"> <div id="hey"><div id="dados"><b>Name: </b><%#DataBinder.Eval(Container.DataItem, "UserName")%><br><b>city: </b><%#DataBinder.Eval(Container.DataItem, "City")%><br></div> <div id="photo"><b>Photo: </b><%#DataBinder.Eval(Container.DataItem, "UserName")%><br>status: </div> <p></div> <a href='<%# DataBinder.Eval(Container.DataItem, "UserName", "purchase.aspx?titleid={0}") %>' > <%#IIf(Container.DataItem("UserName") = "marta", "<a href='mailto:" & Container.DataItem("UserName") & "'>Email</a>", "")%> <img border="0" src="/quickstart/aspplus/images/purchase_book.gif" > </a> </td> </tr> </table> </ItemTemplate> </ASP:DataList> </body></asp:Content> Thanks a lot.
View 5 Replies View RelatedHi,
I am having problem in getting result out of two table, one table is Item Mater which stores global items for all offices and other is stock file which stores office wise stock items as follows:
ITEM MASTER
--------------
NCODE ITEMNAME
1 A
2 B
3 C
4 D
5 E
STOCKDETAILS
-----------------------------------
NCODE ITEMCODE OFFICEID
1 1 1
2 2 1
3 3 1
4 1 2
5 2 2
6 4 2
7 5 3
I want office wise stock details which inludes items found in stock file and remaining itmes from item master. example for office 1
--------------------------------------------
FOR OFFICE - 1
--------------------------------------------
ITEMCODE ITEMNAME OFFICEID
--------------------------------------------
1 A 1
2 B 1
3 C 1
4 D NULL
5 E NULL
i want a single view from which i can select data like i shown above, any kind of help is highly appriciated, what i tried is , i created union of both tables and tried to get data out of union view but result is not up to desire.
Thanks in advance
Use the Northwind database Products table as an example.Purchasing dept gets a report showing when inventory items on hand qty arebelow the reorder level.easy enough:Select ProductID, ProductName, SupplierID, UnitsInStock, ReorderLevelfrom Productswhere (UnitsInStock < ReorderLevel)Results:ProductID ProductName SupplierID UnitsInStock ReorderLevel2 Chang 1 17253 Aniseed Syrup 1 1325It would be nice to know what other products are purchased from this samevendor in case other items are close to their reorder level.All products for Supplier ID 1Select ProductID, ProductName, SupplierID, UnitsInStock, ReorderLevelfrom Productswhere SupplierID = 1Results:ProductID ProductName SupplierID UnitsInStock ReorderLevel1 Chai 1 39102 Chang 1 17253 Aniseed Syrup 1 1325This shows there is 1 more product (Chai) that also comes from Supplier 1.Is there a way to show all items from a vendor when some of the items arebelow the reorder level without needing a separate query for each vendor?Thanks
View 4 Replies View RelatedThere are two tables testmaster and testdetail. If the value of Price for a particular ID in testdetail is more than the threshold value defined in testmaster, the output should have a new column with value as 'High Value', if the value is less than the threshold the new output should be 'Low Value' other wise 'Ignore'
Example: for ID=3, threshold is defined as 40% in testmaster table, but on 11/12/2015 the new price is 100 which 100% more than the previous value, so the status is High Value as shown below.
ID Date
Price Status
1 11/12/2015 100 Low Value
2 11/12/2015 160 Ignore
3 11/12/2015 100 High Value
create table testmaster
(
ID int,
Threshold int
)
create table testdetail
(
ID int,
Date varchar(20),
Price float
)
[Code] ...
I am using MS SQL 2012 and have a pretty simple table dbo. Migration Breakdown with sample data as follows.
DepartDateTime ZoneMovement
2015-06-26 14:00:00.000 6 to 4
2015-06-26 14:00:00.000 11 to 7
2015-06-26 15:30:00.000 9 to 6
2015-06-26 21:00:00.000 7 to 3
2015-06-27 08:01:00.000 7 to 4
[code]....
What I am trying to do is parse the data set to find out when we have more than three like movements ex. 3 to 10 within ANY rolling 72 hour period. I have looked at the SQL Window Functions OVER with a ROW | RANGE subclause, but I can't find out how to tackle this rolling 72 hour business.
I want read
dr[2] = SqlDataReader dr1.item("lastmane");
from database
but I have error expected
I don't while
SqlDataReader dr1 = cmd.ExecuteReader();
dr1.Read();
dr[2] = SqlDataReader dr1.item("lastmane");
select i.a,
w.c,
w.e
from table1 i,table2 w where
i.a=w.a (Here c is the primary key for each a)-iam getting o/p like this
a c e
aaa x 2
aaa y 10
bbb y 5
ccc x 3
ccc y 4
i need o/p in this way (ie sum of e for each a)
a e
aaa 12
bbb 5
ccc 7
Is it possible
Thanx
I´m trying to get a query where I´ll get an answer like this:
+--------------+----------------+----------------+------------------+-----------------+
| champion_id | champion_name | champion_item1 | champion_item... | champion_item16 |
+--------------+----------------+----------------+------------------+-----------------+
| 1 | Legolas | boots | ... | sword |
+--------------+----------------+----------------+------------------+-----------------+
This is the scenario how I set up my tables (let me know if I should change it):
table champions
+--------------+----------------+
|champion_id | champion_name |
+--------------+----------------+
| 1 | Legolas |
+--------------+----------------+
table equipment
+--------------+----------------+------------------+-----------------+
| champion_id | champion_item1 | champion_item... | champion_item16 |
+--------------+----------------+------------------+-----------------+
| 1 | 5 | ... | 22 |
+--------------+----------------+------------------+-----------------+
table items
+--------------+----------------+
| item_id | item_name |
+--------------+----------------+
| 5 | boots |
+--------------+----------------+
| 22 | sword |
+--------------+----------------+
But I still don´t know how to call the equipment table and get champion name and item names for the numbers. What did I do wrong and how can I fix it?
The foreign keys are in place.
Not 100% sure how to do this so I would appreciate some directions. I have 2 tables, Systems and Contacts. A system can have 1 to infinite contacts and contact can have 1 to infinite systems. So I use a 3rd table Sys_Con to add the contact needed for each system.
Now my question is once I add the System and contact how do I know for sure which ContactID to add in my Sys_Con table? Because while I am doing this operation maybe someone can add an other contact. So is there a way for my store proc Add_Contact to return the contactID needed for my store proc Add_Sys_Con ?
TABLE Sys_Con
SystemID int
ContactID int
Table Contact
ContactID int Identity
ContactName
...
Table System
SystemID
...
Hi,
I'm a bit stuck here.
I have a table called STM_COMM and it contains following fields: id, member_id, email_addr, tel_nr, fax_nr
A member can have multiple email addresses, telephone numbers and faxnumbers. so we could have something like this:
id member_id email_addr tel_nr fax_nr
-------------------------------------------------------
1 5 info@test.com
2 5 555687 568774
3 66 test@go.nl 65897854
4 5 another@one.com
What I would like to have is only ONE (1) row PER member with only the FIRST (not max!) entered known data. In the above case, I'd like to have this as result:
member_id email_addr tel_nr fax_nr
----------------------------------------------------
5 info@test.com 555687 568774
66 test@go.nl 65897854
I've tried lots of things (max, inner select ...) but I can't seem to figure it out.
Thank you for the help !
Christophe
PS: I'm working on a sql server 2005
How can I get the value of the last Item in a coums?SELECT COLUMN FROM DATABASE ORDER BY COLUMN DESC?something with Fields(0).value?Thank you in advance
View 4 Replies View RelatedHi gurus
I have sql server 2000 that was working fine until today(figures), today I opened EM and under sql server group where used to be all my information now there is NO ITEM, nothing shows(database,Nothing) The server is working as I had no complaints from my user on data not been saved or getting data from but its strange that I can not see all my info like I used to.- If I go into sql query analizer, it ask me to connect to local which I do and I can see all my databases from there but once I close that window ,on my EM nothing shows.
I checked all services andevery one of them seems to be up and runnig
Thanks forall the help I can get
Hi Every Body,
I have one table it's called -Access.It contains two columns name of that is Door,Emp_Id.
example Door Emp_Id
10 1
10 2
10 3
11 4
12 5
11 1
this is the value in that table.......
I want get Which Door number maximum Access from the table.....
Thanks & Regards,
S.Sajan
Hi Everyone I have an ADP and I need to create a report that will give me the total number of each item. I need the report to show how many [Violation Type], and the total [Loss] per violation. Can anyone help please
Code Snippet
CREATE TABLE [dbo].[Revised_MainTable] (
[I/RDocument] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IR Number] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Date] [datetime] NULL ,
[Inspector] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Area] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Violation] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Violation Type] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Loss] [money] NULL ,
[Loss Type] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Employee] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Guest] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Action] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Action Type] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Notes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Security/GC] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Hi all,
I have some problem about parameter to query data. My parameter is available from dataset but it doen not have blank or null value. So when I want to select this...
SELECT *
FROM mytable
WHERE (@param_person_id IS NULL OR @param_person_id = person_id)
cannot success. This statement means "if @param_person_id has null value, this statement is select every person_id. On the other way, if @param_person_id is not null value, this statement select only specific person_id".
So I want to add blank or null value to my dataset for working with select statement.
How can I do?
Thank you very much
Is there anyway to sum a value that resides in a textbox?
ie; =Sum(ReportItems!MyTextBox.value) ?
I added something by mistake! If I delete it it gives me an `are you sure?` message that makes me think it'll actually delete the file from the hard drive. As I've accidentally added something from SQL Server (I was trying to add a database and got carried away) I don't think that's such a good idea. I've googled and found this:http://msdn2.microsoft.com/en-us/library/1ee8zw5t(VS.80).aspx which helpfully states:----- To remove a solution item In Solution Explorer, select the item you want to remove. On the Edit menu, choose Remove. ----- which would be great, if only the Edit menu had a remove option. Any ideas?
View 2 Replies View RelatedHi,I want to check that Is there row exists in table or not.Please correct me. Dim conn As SqlConnection Dim comm As SqlCommand Dim reader As SqlDataReader Dim connstring As String connstring = ConfigurationManager.ConnectionStrings("iharyana").ConnectionString conn = New SqlConnection(connstring) comm = New SqlCommand("select * from test where username=@username", conn) comm.Parameters.Add("@username", Data.SqlDbType.VarChar, 20) comm.Parameters("@username").Value = uname conn.Open() reader = comm.ExecuteReader While reader.Read If reader.Item("username").ToString = "" Then Response.Redirect("http://www.iharyana.com") End If End While reader.Close() conn.Close()
View 1 Replies View RelatedI using SQL SERVER
just wondering which command I can use to query items status ?
in my TABLEA
Product(D | Name | Type | Status
1 Apple Fruit 00021234
2 Banana Fruit 00031244
3 Tube Clothe 00012131
4 Tee Clothe 00041111
4 pants Clothe 00041112
4 tank Clothe 00051111
4 Jeans Clothe 00061111
4 Dress Clothe 00051111
in TABLEB
ID StatusID Status
1 0001 A
2 0002 B
3 0003 C
4 0004 D
5 0005 E
6 0006 F
use this record as a example :
ProductID | Name | Type | Status
1 Apple Fruit 00021234
use TableA.Status reference to TableB.StatusID ( compare 0001 , need not to care about the last 4 numbers ) so the status is A
AB is Group 1
C is Group 2
DEF is Group 3
in my TABLEA
Product(D | Name | Type | Status
1 Apple Fruit 00021234
2 Banana Fruit 00031244
3 Tube Clothe 00012131
4 Tee Clothe 00041111
4 pants Clothe 00041112
4 tank Clothe 00051111
4 Jeans Clothe 00061111
4 Dress Clothe 00051111
I want the result looks as follow
TYPE Group 1 Group 2 Group 3
Fruit 1 1 0 //because Apple Fruit 00021234 → 0002 = B → Group 1 And Banana Fruit 00031244 →0003 =C→Group 2
Clothe 1 0 5
I need to run a query to get the result like that , and then put the result to my DataGrid then show to user...
can I just use SQL Query to get the result? I just try to use like sum�Substring ... trying to get the result but I still can't wirte a query to work for this question..
which command I should use for this question? or it must be done in code-behind ?
can you please give me help?
thank you very much
Thats it
How do you receive the last item(row) in a table.
Thanks
Hi,
If you browse to Amazon and drill down through categories until a paged
listing appears, you will see in each item of the listing that the
description rendered as Book Description, is a cutdown version of the
full description, with a ... and "Read More" hyperlink. How is
this achieved programatically? I dont know if thre is an efficient way
in SQL Server to do this, or whether it would be easier to have a
ShortHand field and a Description field, both of which are teh full
description when shown in the Details page, but the ShortHand is the
field rendered in listings. This seems like a simplistic solution
and I am thinking there may be programmatic means of doing the same -
has anybody got any experience in this area?
Thanks!
jr.
Hello All.
I need your advise on this .....
I have a table with Bill_Doc, Bill_Item and Bill_Doc_Count fields. I need to update Bill_Doc_Count with 1 only on the first Bill_Item. Is this possible? I tried min and max but they don't work for me.
Please help. Thanks a million.
For example,
Bill_Doc Bill_Item Bill_Doc_Count
123 1 1
123 2 Null
123 3 Null
124 1 1
125 1 1
125 2 Null
Best regards
My data look like this:
ITEMID DESC QTY PRICE
134 ITEM1 4 $200
134 ITEM1 1 $200
134 ITEM1 1 $200
how do I write SQL stmt to add the quantity if the itemid is the same?
The result should be:
ITEMID DESC QTY PRICE
134 ITEM1 6 $200
I have a sql table containing names of departments. Is there a way after a user has typed a department in a textbox on a web page I can search for it in the sql table and if it isn't there then add it. I am using asp.net for the web page.
View 3 Replies View RelatedHiya,I want to populate a list view (in a variety of ways), but I would rathernot execute my function once for every record I want to show in the list.What I want to do is pass an array of "keys" into a stored procedure andhave that procedure return one recordset with all of the records in. Sowhat I want to do is something like:CREATE PROCEDURE MySuperProcedure@MyArrayOfIDs (some way of specifying an array of integers),ASSELECT *FROM MyTableWHERE MyArrayOfIDs.ContainsTheInteger ( MyTable.ID )Obviously this syntax is not available. Is there another way of doing this?
View 3 Replies View RelatedIs there any way to know you're at the last row in a group footer? I'd like to just hide all other rows except that one...
Thanks,
Steve
Hi there,
I am using SQL Server 2005 Reporting Services. Right now, I have a textbox with 0 or 1 values in my report and I want to show a checkbox: checked for value 1 and uncheck for 0. Is there anyway to add a checkbox in the report?
Thanks in advance.
semaj