I was wondering if you guys have any nth script to reads from table
and outputs into a temp table subset of records. There was a nth tool
I used to use it was GROUP1 which was written in C and it used to be
very fast on nth -in a flat file. In this program we used to pass few
paramaeters. For example if I want 30,000 records from the file of
500,000. The function seams to work something like this. you divide
the 30,000 records of 500,000 which will result with .090909090909.
Now we would pass only the first 7 digit (0909090) as parameter that
would nth the file down to 30,000 records. This function allways
worked whichever number you use as long as the read file is larger
than output file
I like to use the similar concept in Sql Server and I was wondering if
anyone has any script to do this or how to go about this?
I have multiple tables with information about a user. The tables are Roles, Users, Groups and Profiles.
For a user session I need information from all those tables. Would it be better to make a table called UserSession and collect the necessary data from the above mentioned tables and stick them in one the UserSession table or should I just write a query that goes out and gets the data from the different tables.
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'
How would I write a select statement that would return multiple fields in a records based on a a distinct of one of those fiels.
Example
Table Name : Sales Table Field Name : Name Address Phone Zip Sale Rec1: Peter Smith 12 Market St 999-999-9999 12345 99.99 Rec2: John Jones 73 Broadway 999-999-8888 12345 12.34 Rec3: Charle Brown 42 Peanuts Ave 999-999-7777 12345 34.56 Rec4: Peter Smith 12 Market St 999-999-6666 12345 67.89 Rec5: John Jone 73 Broadway 999-999-5555 12345 36.52
How would I be able to return the columns Name Address and Phone based on the distinct of Name.
I have a table thats updated daily with monthly data totals:
Month, Total orders1, Total orders2, etc 12/01/2012, 5, 8, etc 11/01/2012, 6, 5, etc
How do I pull data from this table in SQL Server for ONLY the current month? I was thinking using the getdate() function to get the current month, but it doesn't match exactly so I get no results
I have a need to dump a subset of a database from the server (SQL Server) to a notebook via the network, for data entry to be done on the notebook when it is in the field & not connected to the network & then the changes made to this data on the notebook to be applied to the database on the server.
The application for the front end to this is in Access. Would MSDE be the way to go for the database on the notebook ?
It's a small application with not many users, likelihood of conflicting edits is small.
Would the data transfer best be done with replication or with DTS ? Presumably replication would allow options for control over conflicts, such as the same bit of data being changed on the server & on the notebook’s copy of the data ?
I need guidance re direction to head in with this.
Hi all, I’ve a table with production objects, and another with possible items composition of the object. I need to count how many occurrences of each standard composition appears: Table PROD: PROD_ID COMPONENT TYPE --------- ----------- ----- 1 AAA X1 BBB Y2 AAA X3 BBB Y4 AAA Y5 AAA X5 BBB YTable ITEM_COMPITEM_ID COMPONENT TYPE-------- --------- -----7 AAA X7 BBB Y8 AAA X9 BBB Y The result should be: ITEM_ID OCCURRENCES-------- ----7 28 19 1 Table PROD have millions of rows, my way is too slow (I’ve a loop where each PROD object are separately queried against the ITEM_COMP), some have an idea for a most efficient way?
There is a production database which has ever increasing data. For testing purposes though, I would like to build a test database with exactly the same schema but only a subset of data copied from the production database . I'll specify the criteria (something like a where clause in select query) for copying the data from the production database.
Is there a tool that anyone has come across to do this job ?
I am using SQL Server 2005 and trying to create a linked server on Oracle 10. I used the commands below: EXEC sp_addlinkedserver @server = 'test1', @srvproduct = 'Oracle', @provider = 'MSDAORA', @datasrc = 'testsource' exec sp_addlinkedsrvlogin @rmtsrvname = 'test1', @useself = 'false', @rmtuser='sp', @rmtpassword='sp'
When I execute select * from test1...COUNTRY I get the error. "The OLE DB provider "MSDAORA" for linked server "...." does not contain the table "COUNTRY". The table either does not exist or the current user does not have permissions on that table." The 'sp' user I am connecting is the owner of the table. What could be the problem ? Thanks a lot.
I'm using a legacy application built using VB5 and SQL Server 7. After recompiling it, and putting the database in SQL Server 2012. I want to access the current login user using the SQL function SUSER_SNAME().
This is the code.
Set rdoRes = goDatabase.Connection.OpenResultset("select suser_sname()")
But I'm unable to get the current user login in the application. If I write any other SQL statement instead of this, then it runs. But only this statement is not running. Is there any security reasons for this?
I have data for online catalogue in SQL 7.0. The web grogrammer asked me to add a unique key for reference. I used int datatype with identity seed of 1 and increment of 1. This works fine BUT when I try to import new data I get an error because the csv file has no column and therefore no value for the unique field which will not allow null by definition.
How can I maintain a unique field to act as primary key in my data when I want to add (and delete) data that doesn't have this field.
I tried adding the uniqueidentifier field but this gives error message.
The only work round is to delete the unigue field altogether and then add the new data and afterwards create a new unique field. At 600000 + lines of data, this is time and memory consuming
hello all.. i want to update my table information with the value which is currently inserted/updated in another table dynamically..how to get the value of a currently inserted single cell in that table..? table1 contains Refno,.....Refno is primarykey,identity table 2 contains uploadID,....uploadID is primary key,identity table3 contains RefNo,UploadID(both r foriegn keys corresponding to table1 & table2) how to fill table3 with values(not manually) when i am inserting records into table1 n table2 ..( refno and uploadId are identity columns )..Any Idea..?
I have a table and that I need to update one column by add to the front of each row.As an example I currently has "Mydata" I want to add 2008 to all rows to show "2008Mydata". How do I add these 4 characters to the front of each row.
I have 2 identical tables one contains current settings, the other contains all historical settings.I could create a union view to display the current values from table A and all historical values from table B, butthat would also require a Variable to hold the tblid for both select statements.
Q. Can this be done with one joined or conditional select statement?
DECLARE @tblid int = 501 SELECT 1,2,3,4,'CurrentSetting' FROM TableA ta WHERE tblid = @tblid UNION SELECT 1,2,3,4,'PreviosSetting' FROM Tableb tb WHERE tblid = @tblid
moe writes "Could anybody can tell me about having a current date automatically on my table I created by using enterprise manager. The field name I put it as date and the data type is datetime and the length is 8. So what I have to do to get the current date on date field automatically without showing time. I appreciate your help. Thanks!"
Hi,I have a stored procedure that needs to know the name of the tablefrom which it is called. If you are familiar with a "this" pointer inJava or C++, that's very similar to what I need. I know I can usedb_name() to retrieve the database name, but how do retrieve the tablename?Thanks,--Michael
Hi all... Iam a newbie and i have one question want to ask experts ... . I am current working on one App which have some user connect to database at the same time. And i want to know: how many user connect or use (read , update) to one row in a table? Could it be possible to know that??? If you have one solution to solve this problem , please let me know :) ... Could it be done by Software , T-SQL or anything ...., iam happy to know. Thanks all.
In my reports I have data of all the weeks from 1 to 52. Now i want a report where there is data of only the current week in which we are there. So, I am trying out all the possibilities but nothing is working. Can any one help me with the logic of current week????/
Just a quick easy question. If I alter a table (add a column to the table), will it take the table offline during the ALTER process? I am adding the column to the end of the table not in the middle. I know if I add it in the middle it will offline the table.
The following is the sql being executed in my Crystal report. There seems to be an issue with the same date request. Sometimes it shows data, other times not. We have data every day, we are a mass market company. Is there anything i can do for the sql to do select when from and to dates are the same?
SELECT "OEHIS1"."ODORD#", "OEHIS1"."ODORDT", "OEHIS1"."ODNTU$", "OEHIS1"."ODSHP#", "ICPRT1"."IARC11", "OEHIS1"."ODORDD", "ICPRT1"."IARCC4", "OEHIS1"."ODQTY#", "OEHIS1"."ODRQSD", "MFHHMH"."MHAWGT", "OEHIS1"."ODPRT#", "OEHIS1"."ODPRLC" FROM ("S10M10"."ASTCCDTA"."EODDETAILS" "OEHIS1" INNER JOIN "S10M10"."ASTDTA"."ICPRT1" "ICPRT1" ON "OEHIS1"."ODPRT#"="ICPRT1"."IAPRT#") INNER JOIN "S10M10"."DLIB"."MFHHMH" "MFHHMH" ON "OEHIS1"."ODORD#"="MFHHMH"."MHORDP"
I have an employee table (empl), and a labor table (lab). The labor table is populated whenever an employee clocks in/out. If an employee does not clock in, they do not appear in the labor table.
I need to capture employees whose time is less than 8 hours, or whoever did not log in at all that date. If I run the following query without a WHERE clause, I get all of my employees, with NULL data in the labor table if they did not log in that day. This is good.
However, if I add
WHERE (DATEDIFF(s, tt.StartTime, tt.EndTime) < 28800) OR (tt.StartTime = NULL)
I get all employees whose time is less than 8 hours, but do not get any of the employees who did not work at all that date, and that's expected... there should NEVER be a NULL in the labor table - it is populated whenever somebody clocks in/out.
So, how would I query the resulting table to get those that worked less than 8 hours, and those that did not?
FROM (select empl.EmpNum as EmpNo, empl.FFName as FirstName, empl.FLName as LastName from empl where empl.ftermdate IN ('1900-01-01 00:00:00.000') AS et
LEFT JOIN (select MIN(lab.StartDt) as StartTime, MAX(lab.EndDt) as EndTime, lab.WorkDt as WorkDate, lab.EmpNum as EmpNo from lab where lab.WorkDt in ('2008-03-03 00:00:00.000') group by lab.WorkDT, lab.EmpNum) AS TT
I've this query SELECT t1.ID, t1.Date, t1.Time, t1.VALUE FROM RESULT WHERE t1.Date>=CONVERT(VARCHAR(10),DATEADD(d,-7,GETDATE()),101) AND t1.Date<CONVERT(VARCHAR(10), GETDATE(), 101)
Let's say, current date is 8 AUG 2005 and current time is 2045 So, i will get ID | Date (this is datetime) | Time (this is integer) | Value -------------------------------------------------- 204 | 8/1/2005| 2359 | 90 205 | 8/1/2005| 2250 | 99 206 | 8/1/2005| 1950 | 88 ... ... 207 | 8/7/2005| 1845 | 77 208 | 8/7/2005| 2255 | 77 209 | 8/7/2005| 2140 | 77
Can someone can show me to filter data between t1.Date>=CONVERT(VARCHAR(10),DATEADD(d,-7,GETDATE()),101) AND TIME>=CurrentTime t1.Date<CONVERT(VARCHAR(10), GETDATE(), 101) AND TIME<=CurrentTime
If current date is 8 AUG 2005 and current time is 2045, so the result shown as follow
ID | Date (this is datetime) | Time (this is integer) | Value -------------------------------------------------- 204 | 8/1/2005| 2359 | 90 205 | 8/1/2005| 2250 | 99 ... ... 207 | 8/7/2005| 1845 | 77
I only have this query, SELECT t1.ID, t1.Date, t1.Time, t1.VALUE FROM RESULT WHERE t1.Date>=CONVERT(VARCHAR(10),DATEADD(d,-7,GETDATE()),101) AND t1.Date<CONVERT(VARCHAR(10), GETDATE(), 101)
I am trying to exclude patients from a dataset. There are multiple records per patid in this dataset. I have the following code:
SELECT meds.PATID, meds.MEDICATION, meds.MEDTYPE FROM meds INNER JOIN patient ON meds.PATID = patient.PATID WHERE (((meds.MEDTYPE) Not In ("FI (Fusion Inhibitor)","NNUC (","Non-nucleoside","NRTI & NNUC","NRTI (Nucleoside/tide Rev","PI (Protease Inhibitor)")));
I want to exclude all patient records if the patient had any of the above exclusions ever. If they have the exclusion if one record get rid of the rest of the records for that patient. Right now the code only excludes the particular record.
