I am trying to pull only those records with a maximum upload date for a file upload log.
This table keeps a list of files uploaded by supplierID and will have multiple records from the same supplier but with all different upload dates. Overall there are over 1,000 records for 48 uniqur suppliers.
The field names are:
SupplierID, UploadDate, FeedFileName, RecordCount, FeedFileDate, RecordCount, and FeedLoaded.
So for each distinct SupplierID I'd like to grab the line item based on the max feedfiledate for each one - in other words I am trying to get the latest uploaded file information, how many records were in the file, and when it was uploaded... I've tried multiple group by and max clauses but there is something I am missing...
I am currently working in the sql server 2000 environment and I want towrite a function to pull all dates within a given date range. I havecreated several diferent ways to do this but I am unsatisfied withthem. Here is what I have so far:declare @Sdate as datetimedeclare @Edate as datetimeset @SDate = '07/01/2006'set @EDate = '12/31/2006'select dateadd(dd, count(*) - 1, @SDate)from [atable] vinner join [same table] v2 on v.id < v2.idgroup by v.idhaving count(*) < datediff(dd, @SDate, @EDate)+ 2order by count(*)this works just fine but it is dependent on the size of the table youpull from, and is really more or less a hack job. Can anyone help mewith this?thanks in advance
Please help I am trying to do an access query using an inner join...I am using an or to seperate the statements after the inner join...Can this not be done? I need a way to pull the records just once and right now it is duplicating them...Any help would be great!
<cfquery name="formWait" datasource="#datasource#"> SELECT distinct demographics.colors, demographics.colors2, formApprasialStateP.rec_ID, formApprasialStateP.keyName, formApprasialStateP.form_id, formApprasialStateP.teacherID as sid, formApprasialStateP.teacherStatus, formApprasialStateP.teacherStatus, formApprasialStateP.appraiserStatus, formApprasialStateP.appraiserStatus, formApprasialStateP.cycleYear as ty, formApprasialStateP.cycle as tdc, formApprasialStateP.saved, formApprasialStateP.status FROM formApprasialStateP INNER JOIN demographics ON formApprasialStateP.status = demographics.colors or formapprasialstatep.status=demographics.colors2 where teacherStatus = 2 and appraiserStatus = 1 and teacherID=#id# </cfquery>
My query is pulling to many records becuase of the last join. This table can have multiple registration files for a computer. I just want the latest one or last one insert which is based on dttRegistration. I thought a top 1 select and order by would do it, but now returns no computer names.
I'm trying to use the NEWID function in dynamic SQL and get an errormessage Incorrect syntax near the keyword 'ORDER'. Looks like I can'tdo an insert with an Order by clause.Here's the code:SELECT @SQLString = N'INSERT INTO TMP_UR_Randoms(Admit_DOCID,Client_ID, SelectDate, SelectType,RecordChosen)'SELECT @SQLString = @SQLString + N'(SELECT TOP ' + @RequFilesST + 'Admit_DOCID, Client_ID, SelectDate, SelectType, RecordChosen FROMFD__UR_Randoms 'SELECT @SQLString = @SQLString + N'WHERE SelectType = ' +@CodeRevTypeSt + ' AND SelectDate = ''' + @TodaySt + '''' + ' ORDERBY NEWID())'execute sp_executesql @SQLStringMy goal is to get a random percentage of records.The full SP follows. In a nutshell - I pull a set of records fromFD__Restart_Prog_Admit into a temporary table called FD__UR_Randoms.I need to retain the set of all records that COULD be eligible forselection. Based on the count of those records, I calculate how manyneed to be pulled - and then need to mark those records as "chosen".I'd just as soon not use the TMP_UR_Randoms table - I went that routebecause I ran into trouble with a #Tmp table in the above SQL.Can anyone help with this? Thanks in advance.Full SQL:CREATE PROCEDURE TP_rURRandomReview @ReviewType varchar(30)--Review type will fill using Crystal Parameter (setting defaults)AS/* 6.06.2007UR Requirements:(1) Initial 4-6 month review: 15% of eligible admissions(eligible via days in program and not yet discharged) must be reviewed4-6 months after admission. This review will be done monthly -meaning we'll have a moving target of names (with overlaps) whichcould be pulled from each month. (Minimum 5 records)(2) Subsequent 6-12 month review: Out of those already reviewed(in #1), we must review 25% of them (minimum of 5 records)(3) Initial 6-12 month review: Exclude any included in 1 or 2 -review 25% of admissions in program from 6-12 months (minimum 5)*/DECLARE @CodeRevType intDECLARE @PriorRec int -- number of records already markedeligible (in case user hits button more than once on same day for sametype of review)DECLARE @CurrRec int --number of eligible admitsDECLARE @RequFiles intDECLARE @SQLString nvarchar(1000)DECLARE @RequFilesSt varchar(100)DECLARE @CodeRevTypeSt char(1)DECLARE @TodayNotime datetimeDECLARE @TodaySt varchar(10)--strip the time off todaySELECT @TodayNotime = DateAdd(day,datediff(day,0,GetDate()),0)--convert the review type to a codeSelect @CodeRevType = Case @ReviewType when 'Initial 4 - 6 Month' then1 when 'Initial 6 - 12 Month' then 2 when 'Subsequent 6 - 12 month'then 3 END--FD__UR_Randoms always gets filled when this is run (unless it waspreviously run)--Check to see if the review was already pulled for this recordSELECT @PriorRec = (Select Count(*) FROM FD__UR_Randoms whereSelectType = @CodeRevType and SelectDate = @TodayNotime)If @PriorRec 0 GOTO ENDThis--************************************STEP A: Populate FD__UR_Randomstable with records that are candidates for review************************If @CodeRevType = 1BEGININSERT INTO FD__UR_Randoms (Admit_DOCID, Client_ID, SelectDate,SelectType,RecordChosen)(SELECT pa.OP__DOCID, pa.Client_ID,Convert(varchar(10),GetDate(),101) as SelectDate, @CodeRevType, 'F'FROM dbo.FD__RESTART_PROG_ADMIT paInner join FD__Client cOn pa.Client_ID = c.Client_IDWHERE Left(c.Fullname,2) <'TT' AND (Date_Discharge IS NULL)AND(DATEDIFF(d, Date_Admission, GETDATE()) 119)AND (DATEDIFF(d, Date_Admission, GETDATE()) <= 211)AND pa.OP__DOCID not in (Select Admit_DOCID from FD__UR_Randomswhere RecordChosen = 'T'))ENDIf @CodeRevType = 2--only want those that were selected in a batch 1 - in program 6-12months; selected for first reviewBEGININSERT INTO FD__UR_Randoms (Admit_DOCID, Client_ID, SelectDate,SelectType,RecordChosen)(SELECT pa.OP__DOCID, pa.Client_ID,Convert(varchar(10),GetDate(),101) as SelectDate, @CodeRevType, 'F'FROM dbo.FD__RESTART_PROG_ADMIT paInner join FD__Client cOn pa.Client_ID = c.Client_IDWHERE Left(c.Fullname,2) <'TT' AND (Date_Discharge IS NULL)AND(DATEDIFF(d, Date_Admission, GETDATE()) 211)AND (DATEDIFF(d, Date_Admission, GETDATE()) < 364)AND pa.OP__DOCID in (Select Admit_DOCID from FD__UR_Randomswhere SelectType = 1 AND RecordChosen= 'T'))ENDIf @CodeRevType = 3--only want those that were not in batch 1 or 2 - in program 6 to 12monthsBEGININSERT INTO FD__UR_Randoms (Admit_DOCID, Client_ID, SelectDate,SelectType,RecordChosen)(SELECT pa.OP__DOCID, pa.Client_ID,Convert(varchar(10),GetDate(),101) as SelectDate, @CodeRevType, 'F'FROM dbo.FD__RESTART_PROG_ADMIT paInner join FD__Client cOn pa.Client_ID = c.Client_IDWHERE Left(c.Fullname,2) <'TT' AND (Date_Discharge IS NULL)AND(DATEDIFF(d, Date_Admission, GETDATE()) 211)AND (DATEDIFF(d, Date_Admission, GETDATE()) < 364)AND pa.OP__DOCID NOT in (Select Admit_DOCID from FD__UR_Randomswhere SelectType < 3 AND RecordChosen= 'T'))ENDSELECT @CurrRec = (Select Count(*) FROM FD__UR_Randoms whereSelectType = @CodeRevType and SelectDate = @TodayNoTime)--*************************************STEP B Pick the necessarypercentage **************************************--if code type = 1, 15% otherwise 25%If @CodeRevType = 1BEGINSELECT @RequFiles = (@CurrRec * .15)ENDELSEBEGINSELECT @RequFiles = (@CurrRec * .25)END--make sure we have at least 5If @RequFiles < 5BEGINSELECT @RequFiles = 5End--*************************************STEP C Randomly select thatmany files**************************************--convert all variables to stringsSELECT @RequFilesSt = Convert(Varchar(100),@RequFiles)SELECT @CodeRevTypeSt = Convert(Char(1),@CodeRevType)SELECT @TodaySt = Convert(VarChar(10),@TodayNoTime,101)SELECT @SQLString = N'INSERT INTO TMP_UR_Randoms(Admit_DOCID,Client_ID, SelectDate, SelectType,RecordChosen)'SELECT @SQLString = @SQLString + N'(SELECT TOP ' + @RequFilesST + 'Admit_DOCID, Client_ID, SelectDate, SelectType, RecordChosen FROMFD__UR_Randoms 'SELECT @SQLString = @SQLString + N'WHERE SelectType = ' +@CodeRevTypeSt + ' AND SelectDate = ''' + @TodaySt + '''' + ' ORDERBY NEWID())'print @SQLStringexecute sp_executesql @SQLStringSELECT * FROM TMP_UR_Randoms/*--This select statement gives me what i want but I need to somehowmark these records and/or move this subset into the temp tableSelect Top @RequFilesFROM FD__UR_RandomsWHERE SelectType = @CodeRevType and SelectDate =Convert(varchar(10),GetDate(),101))ORDER BY NewID()*/ENDTHIS:GO
I've been experiencing difficulty with pulling records using a where clause date range. I'm using this:
select * from dbo.ACCTING_TRANSACTION_hISTORY where ath_postype = 'NTC' or ath_postype='NTD' and
ath_postdate >= '2013-01-01 00:00:00' and ath_postdate <= '2013-01-05 23:59:59'
I've also tried variations of this without the time portion of the ath_postdate field (of type datetime) , but it still seems to be pulling records from 2009, etc.
Is there a way to get a Stored Procedure to return only unique records from a table? I am using a Stored PRocedure to query a table and it returns all the records in the table and there are many duplicates in the information. I am using SQL Server 2K.
SELECT COUNT(patient.patientID) AS total_patients FROM patient LEFT JOIN patient_record ON patient_record.patientID = patient.patientID WHERE sub_categoryID = 4 OR patient_record.allocated = 4
from the database this gives me a COUNT of 22, it should only be 10. I am doing a join and it gives the total records in the two tables where I only want the total in the left table(patient). How can I GROUP BY patient.patientID in a COUNT query
Hello Everyone and thanks for your help in advance. I have a SQL Server Table wtih approximately 100,000 records. I need to determine if there are duplicate records in this table. My probelm is that there is a unique ID column that was added for each row, so I'm not exactly sure how to filter the rows. Any help on this would be greatly appreciated. Thanks.
I'm trying to read from a table and return only the unique records from a table. The table has 3 columns, ID - Autoinc Amount - decimal 6,2 Name - varchar(10)
I am wanting to retreive only records 3 & 6 (values 0.05 0.06) as they are the only ones where amount is unique i.e. only 1 entry in the table.
I put the following sql statement together which achieves this but it it only returns the amount value select Count(amount),amount from bids group by amount having count(amount)<2 order by amount
So I then expanded it to read select Count(amount),amount,name from bids group by amount,name having count(amount)<2 order by amount not sure where i'm going wrong but the results returned were 0.03 0.03 0.05 0.06 0.07 0.07
I have a table with sixty columns in it, five of which define uniqueness for the records. Currently there are 190,775 records in the table. One of the records is a duplicate. I need to insert only the unique records from this table (all columns) into another table. I cannot use a unique nonclistered index with IGNORE_DUP_KEY in the destination table because of a problem I am having with the 'duplicate key was ignored' message. The destination table has a primary key with a clustered index on the same five columns.
How can I put together a SELECT statement that will give me all of the columns in the source table based on uniqueness of the five key columns?
Does my request make sense? Please let me know if you have questions.
Let's say I have a data entry from a pool of employees: table is as follow: EmpNo Branch Date Amount 1 A101 11/30/2007 $0.90 1 A101 11/30/2007 $1.20 2 A101 11/30/2007 $0.90 3 A101 11/30/2007 $0.80
How can I select the whole table and only take in 1 unique latest entry if there are multiple entries for the same day, same branch under same employee number?
I am trying to create a select query similar to the following but the problem I am having is that I want to only select one record where there may be several with the same dw_order_no. I have tried various ways using SQL developer but without success
SELECT VE_EZP_ORDER_TRANS.EZP_BILL_STATUS AS EZP_BILL_STATUS1, VE_EZP_AGED_CUSTOMER_DEBT.SURNAME, VE_EZP_AGED_CUSTOMER_DEBT.DEBT_AGE_CATEGORY, VE_EZP_AGED_CUSTOMER_DEBT.DEBT_AGE, VE_ORDERLINE.DW_ORDER_NO,
Steve writes "I need a query to show all the medicare patients only. I want only one kind of insurance for each patient. I am getting records where each patient has more than one insurance like medicare and something else. Each patient has one,two, three or more insurances like medicare,medical,blue cross,tricare etc.
The result should look like this: Sam pick Medicare John white Medicare Ann Richmond Medicare
My query retrieves patients with more than one insurance.
select patient_first_name f_name,patient_last_name l_name,patient_zip zip, count(distinct payer_id) payer_id from claims where payer_id ='abcdefhj' group by patient_first_name, patient_last_name,patient_zip having count(distinct payer_id)= 1 order by patient_zip asc;
please help me. Greatly appreciate your response."
Could some one please help me with the following query.
I have multiple tables link together base on wo_No and prt_Mark fields and I get the following results. wo_No | prt_Mark | dwg_Seq | 324037 | d400 | 1S2 | 324037 | d400 | A1 | 324037 | d400 | 1 | 219001 | 56 | 2B | 219001 | 56 | 2C | 219001 | 56 | 2C |
What I would like is to combine the unique dwg_Seq where they have the same wo_No and prt_Mark. 324037 | d400 | 1S2,A1,1 | 219001 | 56 | 2B,2C |
I would also be happy with the following results get the Max unique dwg_Seq and count how many more unique dwg_Seq there are. 324037 | d400 | A1 + 2 | 219001 | 56 | 2B + 1 |
I have a stored procedure that appends data from a temp table to a destination table. The procedure is called from an aspx web page. The destination table has an index on certain fields so as to not allow duplicates. The issue I'm having is if the imported data contains some records that are unique and some that would be duplicate, the procedure stops and no records are appended. How can I have this procedure complete it's run, passing over the duplicates and appending the unique records? Since the data is in a temp table (which gets deleted after each append) should I run some sort of 'find duplicates' query, and delete the duplicates from the temp table first, then append to the destination table? Thanks in advance.SMc
I need a bit of help with a join. I have 2 tables :
TradeSummary has fields : SymbolID, CurrentPrice, TotalValue
Trades has fields : SymbolID, TradeID, ExecutionTime, TradeValue
TradeSummary has one entry for each SymbolID, while Trades contains one or more entries per SymbolID
and what I want to retreive is :
For every item in TradeSummary get CurrentPrice, TotalValue from TradeSummary and also get TradeValue from Trades for the record for max(ExecutionTime) tables are joined on TradeSummary.SymbolID = Trades.SymbolID
Every attempt of mine so far returns multiple rows for each SymbolID - I want only one row per SymbolID
I'm hoping someone can point me in the right path with a design issue I'm running into. I'm somewhat new with database design and SQL server, so bear with me.
I'm creating a sort of project management system, and I've got a main PROJECT table, and then a STUDENT_PROJECT_PARTICIPANT table, with foreign keys to STUDENT and PROJECT tables. Each has an 'institute' field. What I want to do is to be able to report on the number of students involved in each project, the number of unique students involved in project with a particular institute, and then the number of unique students involved in all projects. I'd also like to be able to report the number of unique students involved in a custom-picked list of projects (this seems the least feasible). The trick is that I need to report this statics within a specific date range. My original idea was to just save statistics of the numbers of students involved in a project every time it is saved, but from there I'm not sure how I can find out the unique count for all projects, since I don't have the date that each student was saved. That's my basic problem. Let me know if you need any clarification.
If anyone has done something similar or has any advice for me on this, please let me know!
> This is a common problem with some solution[color=blue]>>[/color]/************************************************** *********************************** Problem:* Determine the Duplicated Records in a table using single SELECT.** We shall be using Northwind database, add some duplicate records.** Here we want to know if 2 columns (CompanyName,* PHone) are duplicated in a table.*** ShipperID CompanyName Phone* ----------- ------------------------- ------------------* 1 Speedy Express (503) 555-9831* 2 United Package (503) 555-3199* 3 Federal Shipping (503) 555-9931* 4 Federal Shipping (503) 555-9931* 5 Speedy Express (503) 555-9831* 6 Federal Shipping (503) 555-9931***************************************************** **/================================================== SOLUTION 1: Gives me the IDs that are duplicated.================================================== SELECTShipperID, CompanyName, PhoneFROMSHIPPERSWHEREEXISTS (SELECTNULLFROMSHIPPERS bWHEREb.CompanyName = SHIPPERS.CompanyNameAND b.Phone = SHIPPERS.PhoneGROUP BYb.CompanyName, b.PhoneHAVINGSHIPPERS.ShipperID < MAX( b.ShipperID ))/* ********************* Output results********************/ShipperID CompanyName Phone----------- ----------------------------------------------------------------1 Speedy Express (503) 555-98313 Federal Shipping (503) 555-99314 Federal Shipping (503) 555-9931(3 row(s) affected)================================================== ===========SOLUTION 2: Gives me the data which are duplicate butnot the IDs================================================== ===========SELECTCompanyName, PhoneFROMSHIPPERSGROUP BYCompanyName, PhoneHAVINGCOUNT(*) > 1/* ********************* Output results********************/CompanyName Phone---------------------------------------- ------------------------Speedy Express (503) 555-9831Federal Shipping (503) 555-9931(2 row(s) affected)
Hi all,We have an app that uses SQL 2000. I am trying to track when a code field(selcode) is changed on an order which then causes a status field (status)to change. I tried a trigger but the app may use 2 different updatestatements to change these fields depending on what the user does. When thetrigger fires (on update to selcode), the status field has already beenchanged. So my trigger to record the changes from inserted and deleted donot get the true 'before' value of the status field.The app does use a log table that tracks these changes. The problem I amhaving is that 2 records are created, one for the change to selcode andanother for the change to status.I am looking for help with a script to combine the existence of these 2 logrecords into 1 unique record or occurance that I can track.example:ordlog: table that logs order changesordernr: order numbervarname: name of field being changedold_value: contents of field before changenew_value: contents of field after changesyscreated: date/time of log entrySELECT ordernr, varname, old_value, new_value, syscreatedFROM ordlogwhere varname = 'selcode' and ordernr = '10580'SELECT ordernr, varname, old_value, new_value, syscreatedFROM ordlogwhere varname = 'status' and ordernr = '10580' and old_value = 'A' andnew_value = 'O'So I need a way to combine these 2 log entries into a unique occurance. Theordernr and syscreated could be used to link records. syscreated alwaysappears to be the same for the 2 log entries down to the second. Selcodecan change from NULL to a number of different values or back to NULL.Statusis either 'A' for approved or 'O' for open. An order can have many logentries during its life. The selcode may be changed several times for thesame order.Ideally, I would like a result that links 2 log entries and shows the statuschanged from 'A' to 'O' when selcode changed.Thanks for your time.
I am trying to select all records added between 2 dates that the user inputs into a form and am having problems. I had this working no problems with asp but can't seem to get it working with .net. BTW I am using SQL Server and Visual Studio.
The asp.net code I am trying to use is:
Me.SqlSelectCommand1.CommandText = "SELECT news_title, news_date, news_type, news_link FROM news WHERE (news_type = 'news') AND (news_date BETWEEN CONVERT(DATETIME, '"" & startdate & ""', 102) AND CONVERT(DATETIME, '"" & enddate & ""', 102))"
....
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim startdate As DateTime startdate = Request.Form("date_from")
Dim enddate As DateTime enddate = Request.Form("date_to")
I have a database table which contains customer orders. I am trying to code my SQL select statement to:
1) Only return records where the record orderdate is within the last 30 days
2) Between two dates, selected from the datepicker control.
With regards to issue 1, I could fill a table with all the records for the account in question and then for each record do a datediff between the records order date and the current date to determine if the number of days is within 30 days. If yes then add this record to a temp table and then set this table as the datasource for the datagridview.
hilet me explain my need..following are the tables im using..tbl_company (company table - parent) id company 1 test 2 test123 tbl_dept (department table - master) id dept 1 dept1 2 dept2tbl_compdept (company departments table - child) cmpid deptid 1 1 2 1 2 2 wats my need is.. while the company is listing..by query using joins, result was like this..company depttest dept1test123 dept1test123 dept2i need company test123 should be listd only once..when i use group by or distinct means, all r listed..is there any way to filter out therepeating company list by just listing the company list only once..
I'm fairly new in SQL. Been trying for months to create the right script for this particular case but still cannot give me 100% result as required.
SCENARIO :
I am required to query from 2 tables for those unique record that meets both conditions below:- 1. Status is 1 @ max (trans_id), paychnl = CC 2. Status is 2 @ max (trans_id), paychnl = A or B
FYR, 2 tables and respective columns to query are as below:- table PTFF --> col ID, TRANS_ID,TRANSDATE,EFFDATE,TRANSCODE table CHFF --> col STATUS,PAYCHNL
FYI, status refers to the paychnl method status:- ==> 1 means the current paychnl method ==> 2 means the previous paychnl method
paychnl method can be multiple because it will be defined as 2 for all the histories' paychnl chosen earlier, but 1 should only be unique as it is the latest paychnl chosen for each unique ID. however, it may appear more than once when it's taking those in earlier TRANSDATE, so here we would need the max trans_id as it will show the latest updated TRANSDATE.
Apart from that, I need only those most recent paychnl to be A or B and the latest paychnl is CC so, this been indicated by the same max trans_id for the same ID.
Aft trying so many times on this MAX command but failed to get any result, I only managed to come up to this part only. please refer below:-
table PTFF --> col ID, TRANS_ID,TRANSDATE,EFFDATE,TRANSCODE table CHFF --> col STATUS,PAYCHNL
FROM DBO.PTFF PTFF JOIN DBO.CHFF CHFF ON CHFF.ID = PTFF.ID WHERE PTFF.TRANSDATE BETWEEN 130501 AND 130831 AND PTFF.TRANSCODE='T522' AND (CHFF.STATUS=1 AND CHFF.PAYCHNL='CC' OR (CHFF.STATUS=2 AND (CHFF.PAYCHNL='A' OR CHFF.PAYCHNL='B')))
However, the script above returns :-
1. All those records with STATUS 1 regardless paychnl is A or B in most recent status 2, 2. Expected results also appear ==> 1 same ID with status 1 while paychnl=CC and status 2 while paychnl=A or B 3. Also duplicates of expected results but for different TRANSDATE and not at MAX TRANS_ID
Our users use Report Builder for writing ad-hoc queries. They have run into a problem where Report Builder is returning only distinct records is the entities unique identifier is not added to the report.
Example:
TransID Type TransQuantity
1 CS 4 2 CS 4 3 CP 2 4 CN 3
Adding the above fields to the report builder canvas will return 4 rows. However, if I were to add only Type and TransQuantity to the canvas as such, Report Builder will only return 3 rows:
Type TransQuantity
CS 4 CP 2 CN 3
Is there any way to have Report Builder return the actual number of records? Our users often will export the results from Report Builder into Excel to slice and dice the data, and with the functionality the Report Builder has right now, incorrect values will be certain.
Hi Respected, I am working in .net, and according my client he want to see all record from table uniquely, and does not want to give order also. for doing this i did use DISTINCT, but my problem is when i used DISTINCT the query return sorted records that is why, i need guide which will help to make DISTINCT and physical stored record from the table. thanks. bay regards Rajat.
