I am having problems with my store procedure displaying my last column name which is "DISPLAY_CATEGORY_DESC". I am trying to display the description from another table. But in order to get to the description I have to go through 2 tables. The tables look like something like this.
FROM di_webclickstats cwebs
left join di_websessions webs on cwebs.session_id = webs.session_id
where cwebs.DIR_CATEGORY_ID <> 0 and webs.ENTERED_DATE between @startDate and @endDate+1
I want to be able to see when records have been added to a table. The issue is we have a DTS job scheduled to run every night. The developer who wrote it password protected it and doesn't work here anymore. I want to add a step to this series of DTS jobs and want to run it just prior to his job. Is there a way to see when the records are being added or when this job is being run? Thanks again, you guys are the best.
I have one table that has unique id's associated with each row of information. I want to delete rows of information in one table that have a unique ID that references information in another table.
Here is a basic breakdown of what I am trying to do:
Table1 (the table where the rows need to be deleted from) Column_x (Holds the id that is unique to the various rows of data - User ID)
Table2 (Holds the user information & has the associated ID) Column_z (holds the User ID)
I tried this on a test set of tables and could not get it to work. What I am trying to do is skip all rows of Table1 that have ID's present in Table2, and delete the rows of ID's that are not present in Table2.
Code:
SELECT Column_z FROM dbo.Table2 DELETE FROM dbo.Table1 WHERE Column_z <> Column_x
This did not seem to do what I needed, it did not delete any rows at all.
I wanted it to delete all rows in Table1 that did not have a reference to a user ID that matched any ID's in Column_z of Table2
Then I tried another scenerio that I also needed to do:
Code:
SELECT Column_z, Column_a FROM dbo.Table2 DELETE FROM dbo.Table1 WHERE Column_z = Column_x AND Column_a='0'
'0' being the user id is inactive so I wanted to delete rows in Table1 and remove all references to users that were in an inactive status in Table2.
Neither one of the Queries wanted to work for me in the Query Analyzer when I ran them. It just said (0) rows affected.
Can someone write a query for me that would return names of tables that would slelect all tables of particular database and show if table has set primary key and if it has then if key has Identity Specification set to true?
something like:
SELECT TABLE_NAME ,HAS_PRIMARY_KEY ,KEY_HAS_IDENTITY_SPECIFICATION FROM ...this is where you come in ORDER BY TABLE_NAME
I need to be able to sum items grouped by op_wkctr and op_part and op_date. Here's my latest version which of course does not work.
SELECT op_wkctr, pcs, hrs.b FROM dbo.op_hist INNER JOIN ( SELECT op_part, SUM(op_qty_comp) as pcs FROM dbo.op_hist where op_type='BACKFLSH' group by op_hist.op_part
UNION ALL
SELECT op_part, SUM(op_act_setup + op_act_run) as b FROM dbo.op_hist where op_type='LABOR' group by op_hist.op_part ) AS hrs ON op_hist.op_part = hrs.op_part where (op_wkctr = 'P-P36' or op_wkctr = 'P-P38' or op_wkctr='P39') and op_date ='10/22/07'
I just made some huge changes to a database on the development computer. However, I want to transfer all of the information from the Employees table in the old DB to the new DB (no changes made on that table).
I have run into a challenge that I'm not sure how to best solve. I'dappreciate any opinions or input.I am working with a third party database. They are storing some datathat I need to use in a binary field. I've got the code to parse thebinary and reconstruct what I need. Unfortunately, there might bemultiple "entries" stored in a single binary field. a certain byte,let's just say the first, will always be the count of "entries" inthis particular SQL entry.An example:then entry might be:0x01000012341234where 12341234 is the data entry that I will parse.Another possible entry is:0x03000012341234567856789ABC9ABCThe first byte indicates that there are three data values I want toparse out:12341234567856789ABC9ABCThe portions of the binary I need are always the same length and theremay be from 1 to ~100 of them. (usually 1 if it matters.)The big question:How could a SQL query return an entry for each of the "entries" in thebinary field? For the second example I would want three entries in myresults, each row returning a different section of the binary data.For the first, only one row. I'll be querying the set and expecting toget back more results than the number of entries in the set.I haven't really tried much with this yet, (other than some mentalcalisthenics were I fell down onto the mat pretty hard), so just somedirection on where to begin would be helpful.(Yes, I understand that the difficulty is that this data shouldn't bestored this way, but there is not much I can do about that.)Thanks...James FraserJoin Bytes!
Now from various links i have established that you have to use DBCC PAGE() with relevant info to get more information, i get back a object and index id, m_objid.
However i dont know how to get the tablename form here, msdn simply says that it is an allocation id not and actual table id, so how can i from here get to table information?
All i am trying to figure out is which table is affected fromt he RID dealock, but that does not seem to be a simple process.
Does TSQL (2005) offer a way to retrieve a given table's total size (data size vs index size)? Would like to be able to get at this info using TSQL (not management studio).
Can someone write a query for me that would return names of tables that would slelect all tables of particular database and show if table has set primary key and if it has then if key has Identity Specification set to true?
something like:
SELECT
TABLE_NAME ,HAS_PRIMARY_KEY ,KEY_HAS_IDENTITY_SPECIFICATION FROM
After I used "copy database" to upgrade a SQL 7 database to SQL 2000, all are fine except the TaskPad view. The table info section is blank. Though we know all tables are in the table object.
I am importing a csv file into a temp table (works fine), now I want to take the data in the temp table based off of the temp id compared to the id of the permanant file.
basically if the record id exists skip to the next line and so forth, I am able to import everything just fine but I cannot get the WHERE condition to acually work.
INSERT INTO TABLE2 (COL1, COL2, COL3) SELECT COL1, COL4, COL7 FROM TABLE1 WHERE table2.col1 <> table1.col1
this is what my actual code is
INSERT INTO USER_INFO (fpu_id,FIRSTNAME, LASTNAME, BOXNUMBER) SELECT id,first,last,box FROM stmailbox WHERE stmailbox.id <> dbo.USER_INFO.USERID
this is the actual error that I get.
Server: Msg 107, Level 16, State 3, Line 2 The column prefix 'USER_INFO' does not match with a table name or alias name used in the query.
I used to use this code to extract coupon usage from two tables orders and coupons
select distinct o.OrderDate, p.SKU, c.CustomerID, c.CustomerLevelID, c.Email, c.FirstName, c.LastName from customer c join dbo.Orders_ShoppingCart os with (NOLOCK) on os.CustomerID = c.CustomerID join dbo.Product p with (NOLOCK) on p.ProductID = os.ProductID join dbo.Orders o with (NOLOCK) on o.OrderNumber = os.OrderNumber
[code]....
Following a database upgrade the Coupon Code is no longer used. Instead there are two new SQL tables called Promotions and PromotionsUsage.. The link between the two tables are
ID in Promotions
and
PromotionID in PromotionUsage
Both are the same value. So in order to see new orders for coupon usage how would I change this line in the original SQL to retireve this?
SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tab_db1'The above query will give the information of the table 'tab_db1' if it is available in the current database (say db1) connected.How can i access the information of a table (say 'tab_db2') which is created under a different database say db2 from the current db connected say db1.I tried the above query with changing the table_name to 'db2..tab_db2'but went invain.sysobjects also dint work..Any help on this will be appreciated..regards,Sathya V
I'm kinda new to SQL. I'm trying to get a print out of a specific table and its columns and column types. Is there a specific command or code that I can use to accomplish this?.
i want to have a user defined function or stored procedure(i don't know which one should be used, anyway!) which returns false if the table has no records, with the table name comes with a parameter.
I've just written a query that successfully brings back the data from one table based on the information from another. Basically we have been given a table of information and need to update certain fields in our user_group table with the new info.
Here is the SELECT statement SELECT user_group.id, user_group.name, user_group.description, Consultants.description AS Expr10, user_group.btype, user_group.rootmenu, user_group.intra_user, user_group.primary_g_id, user_group.fname, user_group.lname, user_group.ntlogon, user_group.lang_id, user_group.[external], user_group.title, user_group.work_tel, user_group.work_fax, user_group.work_ext, user_group.mobile, user_group.sex, user_group.add2, user_group.add3, user_group.town, user_group.county, user_group.pcode, user_group.private_flag,
[code]....
We want to update the 'description' on the user_group table with the 'description' from the 'consultants' table. To test this, we only want to write the UPDATE so that it changes the description where the name is 'Adam Froth. The UPDATE statement that we've written is
UPDATE user_group SET user_group.description = Consultants.description FROM user_group INNER JOIN Consultants ON user_group.description = consultants.description WHERE name like 'Adam Froth%'
but it keeps erroring and saying that it could 'Not be bound'.
MSSQL noob here. Sorry guys, but I really need some help.
I have a MSSQL (2000) database and a Products Table. Now, there was three columns used for pricing that I was not able to populate on the initial usage. So I exported the table as an Excel document, and got rid of everything except the UID (unique identifier column) the Name, and the Three columns, so that the customer could take their time in filling out the information.
Ok, so These are what are common to both the MSSQL database and the Excel sheet
column uid = unique id integer column Name = text column eBay = number column PriceGrabber = number column Amazon = number
So, any idea how I can update my Products Table with this?
I have one database named StudInfo. It has two tables named StudentInfo, and GradeInfo. StudentInfo conntains 4 columns. The 1st one is StudentID (PK) int, LastName varchar(10), FirstName varchar(10), and PhoneNumber int.
GradeInfo contains 4 columns also StudentID (FK) int, GradeID varchar(10), Grade int, Date Datetime.
What I would like to know is how using a T-sql query I could make a temp table with studentID, LastName, FirstName, and then the average of all the different types under GradeID. As of right now I have been limiting the names that are put into GradeID to Homework, Daily, Test, Quiz, and Bonus. When I say average I mean the average of all Homeworks under one studentID, and all Daily under one studentID... etc. I would like the info returned for each student in studentID. Allow Nulls has been turned off.
Never assume someone knows what you are talking about.
Hi, I have tables parent - PurchaseInvoices and child - PurchaseInvoiceDetails. I have trigger in PurchaseInvoiceDetails that would update stock qty based on Location code stored in parent table - PurchaseInvoices upon INSERT/UPDATE/DELETE.
I also set table RI to have cascade delete. I faced problem that, when I delete record in parent table, it would cascade delete child table. Trigger in child table fired and try to update stock qty. However, in this case, trigger unable to locate parent table record anymore.
How to overcome this? I can't move my stock update code to other place since I got to update stock if any changes happen to child table.
Can u pls tell me How to Get the SQL Affinity Mask value in Sql 2005.
and what is the table/views name that store this info.
for SQL 2000 , we were using following query:
SQLStatement.printf("select c.value from master..sysconfigures c, master..spt_values v," "master..syscurconfigs r where v.type = 'C' and v.number = c.config and v.number >= 0 " "and v.number = r.config and v.name ='affinity mask'");
I need ssrs report which get job info from job table in list control and other one is jobid and workdate which also need to be group by and storeproc I am using for this report is as follow:
ALTER proc [dbo].[VRAJ_JobScheduleReport] @JobID int = null, @StartDate varchar(25) = null, @EndDate varchar(25) = nullASSET NOCOUNT ON SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON DECLARE @sql varchar(max);DECLARE @param varchar(max);DECLARE @where1 varchar(max);DECLARE @where2 varchar(max);SET @param='';SET @where1='';SET @where2='';IF @JobID IS NOT NULL BEGIN SET @param = @param + 'DECLARE @JobID int; SET @JobID=' + CAST(@JobID AS varchar(50)) + ';'; IF @where1 = '' [Code] ...
I need ssrs report which get job info from job table in list control and other one is jobid and workdate which also need to be group by and storeproc I am using for this report is as follow:
ALTER proc [dbo].[VRAJ_JobScheduleReport] @JobID int = null, @StartDate varchar(25) = null, @EndDate varchar(25) = null AS SET NOCOUNT ON
I am still new to SQL and I am having trouble obtaining the results I need from a query. I have worked on this command for some time and searched the internet but cannot seem to still get it correct.
I have a table called Patient. It's primary key is pat_id.
I have a second table called Coverage. It has no primary key. The foreign keys are pat_id, coverage_plan_id, and hosp_status.
I have a third table called Coverage_History. It has a primary key consisting of pat_id, hosp_status, copay_priority, and effective_from.
I want to get the pat_id and all the coverage information that is current. The coverage table contains specific insurance policy information. The coverage_history table will indicate the effective dates for the coverage. So the tables could contain something like this:
Patient (pat_id and lname) P123 Monto P124 Minto P125 Dento P126 Donto
Coverage (pat_id, coverage_plan_id, hosp_status, policy_num) P123 MED1 OP A1499 P123 ACT4 OP H39B P124 MED1 OP C90009 P124 RAC OP 99KKKK P124 RAC OP 99KKKK P124 MED1 OP C90009 P125 ARP OP G190 P126 BCB OP H88
Coverage_History (pat_id, hosp_status, copay_priority, effective_from, coverage_plan_id, effective_to) P123 OP 1 20150102 MED1 NULL P123 OP 2 20150102 ACT4 NULL P124 OP 1 20150203 RAC 20150430 P124 OP 2 20150203 MED1 20150430 P124 OP 1 20150501 MED1 NULL P124 OP 2 20150501 RAC NULL P125 OP 1 20150801 ARP NULL P126 OP 1 20150801 BCB 20160101
select p.pat_id, p.lname, ch.coverage_plan_id, ch.hosp_status, ch.effective_from, ch.effective_to, ch.copay_priority, from patient p left join ( coverage_history ch left join coverage c on ch.coverage_plan_id = c.coverage_plan_id and ch.patient_id = c.patient_id and (ch.effective_to is NULL or ch.effective_to >= getdate() ) ) on ch.patient_id = p.patient_id
where ( ch.effective_to is NULL or ch.effective_to >= getdate() )
So I want to see:
P123 Monto MED1 OP 20150102 NULL 1 P123 Monto ACT4 OP 20150102 NULL 2 P124 Minto MED1 OP 20150501 NULL 1 P124 Minto RAC OP 20150501 NULL 2 P125 Dento ARP OP 20150801 NULL 1 P126 Donto BCB OP 20150801 20160101 1
Hi all,I have a problem and need some ideas.What I have done: I created a page to upload an excel file into a SQL Server table along with some customer info (from the login, day, etc.). This excel file contains several rows (some of them may be blank) and columns (also some may be blank). The file is stored in an image object.The file will be checked (they want to do it manually, because contents is a problem). If they say it is OK, I want to run a program to add a record into an existing table with the request no. (from the first table, where the object is stored) and all the information available from the filled rows (first row is header). I have a column, which can be checked, if the row contains data or not.Any ideas?I know how to read from and write the contents of the object to a field in the SQL table. Can I use this?Thanks for any idea / code / link.
Let's say I have a table of data as per the below..
I'm trying to extract only the green highlighted items..
The rules applied are: Only the latest data concerning all cases, and only 1 line (the latest) per case.
As you can see in the image, I don't want the 2nd,3rd, and 4th record extracted cause they are all superseded by more recent records (identified as they are further in the table).
I've considered using either Distinct or Having? but can't get that to work.. If I could use Distinct but then ensure it's the latest record in the table that would be perfect.
I have a table with score info for each group, and the table also contains historical data, I need to get the ranking for the current week and previous week, here is what I did and the result is apparently wrong:
select CurRank = row_number() OVER (ORDER BY cr.CurScore desc) , cr.group_name,cr.CurScore , lastWeek.PreRank, lastWeek.group_name,lastWeek.PreScore from (select group_name, Avg(case when datediff(day, asAtDate, getdate()) <= 7 then sumscore else 0 end) as CurScore
[Code] ....
The query consists two parts: from current week and previous week respectively. Each part returns correct result, the final merged result is wrong.
I've only been doing sql 2005 for a couple of months with minimal training so there's a lot I don't know. What I'm trying to do is load a nested table (industry & customer totals) based on a value from the table it's nested in. This is the relationship of the data. I have at the highest group, an industry code, then a customer, then the part and then the fiscal year. What I'm trying to accomplish is to get a group total (footer) for the (1) industry code and (2) the customer code. This footer would contain the fiscal years (ascending) and their monthly totals. I would like to take the industry code from table one and pass it to the select statement in the dataset that feeds the nested table. I've read this is not possible to load a dataset field into a parm but I've seen where some people know how to work around this. If you reply, please explain in simple terms. Thanks!
industry Customer Year OCT NOV DEC 001 - Signposts M12345 Part 1 2006 5 6 2 2007 0 3 1
I've inherited the administration of two SQL Servers one 7.0 and the other 2000. We are looking into upgrading to 2005 so I'm gathering all the info I can on the DBs and applications using them.
Now, how can I check if a DB is currently in use? Is there a way to find out when a DB was last accessed?
By the way, I've never done DB administration so you can figure out how desperate I'm getting.
i need small information regarding how the picture image data can be stored in the database and how it can be made possible. is there for the need for any conversion.
