I need to run a query on all of the items in the product table where the amount that I've sold over the past 2 days is greater than the amount I have available. Basically, am I likely to sell out of stock?
I have all of the information I need across 3 tables:
- bvc_product
- bvc_order
- bvc_orderitem
The database is available here: [URL] .... It's about 400 KB zipped & 3 MB unzipped.
I've written the following query & I can see all of the data that I need, but (even conceptually) I don't know how to make the comparison.
SELECT
o.[ID]
,o.[TimeOfOrder]
,oi.[Qty]
,p.[ID]
,p.[AggregateQty]
FROM [tmpDB].[dbo].[bvc_Order] o
INNER JOIN tmpDB.dbo.bvc_OrderItem oi
ON o.ID = oi.OrderID
INNER JOIN tmpDB.dbo.bvc_Product p
ON p.ID = oi.ProductID
I want to be able to track my top 100 items sold in the last 60 days. Each night I want to calculate it then compare it with the numbers from the previous night. Then show both ranks. My problem is assigning them ranks. Bascily I have a view that shows the top 200 sorted correctly. I am thinking creating a DTS to make a new table with adding transformations. But I am not sure how to code the Visial Basic. Below is what I thought it would be, but it doesn't work. Get a generic error. Any ideas?dim ranknumranknum = 0Function Main() ranknum += 1 DTSDestination("Rank") = ranknum DTSDestination("ItemNumber") = DTSSource("ItemNumber") DTSDestination("QtySold") = DTSSource("QtySold") Main = DTSTransformStat_OKEnd Function
I have a code I wrote and I'm required to get Product Name and Quantity with the maximum quantity sold per day. My issue is with the "per day" part, here's my code below.
I am looking for a way to show how I can have a result set that shows a record with one item and the number of records where it was purchased or sold. Below is my sample data.
Use tempdb go create table #ItemLedgerEntry ( ENTRYNO INT NOT NULL , ITEMNO VARCHAR (50) NOT NULL , POSTINGDATE DATETIME NOT NULL , ENTRYTYPE INT NOT NULL
[code]....
I know something like this will give me results but I'd like to run one query.
select itemno, count(*) from #ItemLedgerEntry where entrytype = 0 group by itemno order by count(*) desc
I need a job run page to fire a job on a sql server if the job is not already running. How do I check if the job is running on the MSSQL server. Can I use the sp_job_help as it does return 4 data sets with the first having the data I need, but as yet I have not mastered a multi data set return.
set @FullQry='select @Temp=Emp_ID from Employee where.....' Exec(@FullQry) select @@ROWCOUNT
My Employee table has 3 records and this query sholud return me @@ROWCOUNT=1 but it will return 0 why this i am not able to find out.Exec function return ROWCOUNT or not?
Is there any way to tell me how many users connecting to specific database / who are using it in the management studio? we are use the windows authentication mode.
I want to built a table to a form.... I have some check boxes in this form.... what is the script line for this check box ? I know it is suppose to work 1 or 0 ? for false or true ..... How I suppose to do this ?
Hello,I Have a code:<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" InsertCommand="INSERT INTO [shop_clients] ([ID], [IMIE]) VALUES (@ID, @IMIE)"> <InsertParameters> <asp:Parameter Name="ID" Type="Int32" /> <asp:Parameter Name="IMIE" Type="String" /> </InsertParameters> </asp:SqlDataSource> SO this code It will allow me insert to database SQL textbox - name ?If yes How I can:If I click the button My textbox - name, insert to databasePlease me help :)
Aperently I could not insert a text field to another table from INSERTED in a trigger.It seems th follwoing is working, do you see any problem joining INSERTED to the mySrcTable which is the table that has this trigger INSERT INTO myRemoteDatabase.dbo.myDestTable (myTrID,myFirstName,myBigText) SELECT i.myTrID, i.myFirstName, p.myBigText FROM INSERTED i INNER JOIN mySrcTable p ON i.myTrID = p.myTrID WHERE (i.myTrType = 'In') Thanks,
Hi I was wodering how to add an OR statment right in the Check Constraint expression. This is what I am starting with in the database ([zip] like '[0-9][0-9][0-9][0-9][0-9]') and what I want well not exact but this would answer my question ([zip] like '[0-9][0-9][0-9][0-9][0-9] || [A-Z][A-Z][A-Z][A-Z][A-Z]') Thanks for any help
Hi, I have two web pages in one web page i have 5 check boxes. For example if the user checks the Checkbox1, checkbox2 and clicks on button. On the button click I am storing the selected checkboxes value in database lke the following: Year Options xxx 1 xxx 2 in the above format( user selectes checbox1, check box 2). And in the Second Web page I am showing the 5 checkboxes but in this web page I need to check the first and second checkboxes on the page load because user selectes those two check boxes in the first web page. my select query returning the results like this: Options 1 2 based on options I have to check those corresponding check boxes in the second web page. How to achive this. Thanks in advance
I have a pretty standard form that inserts users name, office, and team. It generates a random 10 digit ID for them. How would i got about checking the table to make sure that ID doesn't exist? Here's my insert code. string strConnection = ConfigurationManager.ConnectionStrings["TimeAccountingConnectionString"].ConnectionString; SqlConnection myConnection = new SqlConnection(strConnection); string usercode = GenPassWithCap(9); String insertCmd = "INSERT into users (ID, firstname, lastname, office, team) values (@id, @firstname, @lastname, @office, @team)"; SqlCommand myCommand = new SqlCommand(insertCmd, myConnection); myCommand.Parameters.Add(new SqlParameter("@id", SqlDbType.VarChar, 10)); myCommand.Parameters["@id"].Value = usercode; myCommand.Parameters.Add(new SqlParameter("@firstname", SqlDbType.VarChar, 50)); myCommand.Parameters["@firstname"].Value = txtFirstName.Text; myCommand.Parameters.Add(new SqlParameter("@lastname", SqlDbType.VarChar, 50)); myCommand.Parameters["@lastname"].Value = txtLastName.Text; myCommand.Parameters.Add(new SqlParameter("@office", SqlDbType.VarChar, 75)); myCommand.Parameters["@office"].Value = dwnOffice.SelectedValue; myCommand.Parameters.Add(new SqlParameter("@team", SqlDbType.VarChar, 20)); myCommand.Parameters["@team"].Value = dwnTeam.SelectedValue; myCommand.Connection.Open(); myCommand.ExecuteNonQuery(); Do I run a completey different select command before hand and try to match that field?
Hi, I'm trying to populate a table in one database with details from a identical table in another database. How do I check to see if the row exsits before I insert the data because at the moment I'm getting a violation of a primary key error.my codes something like the below
insert into db2.table1(values) select * from db1.table1, db1.table2 where t1.id = t2.id
Hi I have two tables. one MasterDetail and second is countrydetail In master detail i have MasterDetailId (Primary) and countryId. In CountryDetail table I have Countrid (Primary),CountryName. I don't want to give relationship because i can insert null value in countryid in MasterDetail table. So i have wriiten query like this -> select c.CountryName,m.MasterDetailId from MasterDetail m,CountryDetail c where c.CountryId=m.CountryId and m.MasterDetailId= '2' If In MasterDetail table if CountryId is null then it will not show me any record. So I want that record and its value with this query and checking null values. Help me about solving this query. Thank You
Hi,I have two databases called DB1 and DB2. DB1 has a table called table1 and DB2 has table2.I want to write one SP into the DB1, that SP will check whether table2 into the DB2 is exists or not, how do I do it? Any help?I know if it is into the same database then,IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = object_id(N'[table2]') and OBJECTPROPERTY(ID, N'IsTable') = 1)-- then do some thingI tried replacing "dbo.sysobjects" with "DB2.dbo.sysobjects", but no luck.Any Help???
How do I check when my database was last used ? I have two old databases that I think is not in use, but I want to check to make sure before I delete them or take them "offline".
Hi, I am developing a database for my company in SQL server 2000 and I have some few problems. Firstly.
I have a customers table and orders table in my DB:
Customers Orders --------- ------ CustID (primary key) ----------------< CustID . ^ ProductID . | Quantity . | . . | . etc. | etc. | relationship (one to many)
What I want to do is: 1) to be able to delete a Customer and automaticaly SQL server delete all the orders that this customer done from the Orders table. 2) If for some reason the CustomerID changes, SQL should be able to automaticaly update the necessary fields with the new values in the Orders table. Finally, 3) I want to be able to insert a new customer that has an order and update both the Customers table and Orders table automaticaly. e.g
CustID Name Address ProductID Quantity etc. ------ ---- ------- --------- -------- ---- 10-003 John London 33-25 2 ...
Such a kind of insert should add automaticaly the following entries in the two tables:
Customers Orders --------- ------ CustID (10-003) CustID (10-003) Name (John) ProductID (33-25) Address(London) Quantity (2) . . . . etc. etc.
A friend of mine told me that this can be done using Foreign Check constraints in SQL server. But I do not know what to do.
I have a DB with 4 tables in MSSQL6.5. In those tables write an NT service (4 threads for each table) 24h/day. I want to know somehow when i have no write in one of those tables for more than 1-2 hours (i dont want to querry those tables everytimes). MSSQL6.5 offer this kind of facilities? Or I can do this using stored procedures or something else?
I am Trying to add a check constraint that if the paymenttotal is 0 the column is allowed to have null and if its greater then 0 it is not allow to have null. Here is what I have so far but i get some syntax errors, See if you can see what im doing wrong and how to get this to be valid. Thanks heres what I got so far
Code:
ALTER TABLE Invoices WITH CHECK PaymentDate SMALLDATETIME NULL, CHECK (PaymentTotal = 0) PaymentDate SMALLDATETIME NOT NULL, CHECK (PaymentTotal > 0)
I also had this before i changed it to that and I got syntax erros as well but i dunno which is closer.
