I have a fairly complex SP with 3 tables the idea being to display a Region, Client and Event
I want to show only the last event for each client
I have replaced my event table with a View which returns the TOP 1 but all I get is the most recent of all records
I want eg:
Region 1, Client 1, Most recent event
Region 1, Client 2, Most recent event
Region 1, Client 3, Most recent event
Could anybody help me with the following scenario:
Table 1 Table2
ID,Date1 ID, Date2
I would like to link the two tables and receive all records from table2 joined on ID and the record from table1 that has the most recent date.
Example:
Table1 data Table2 Data
ID Date1 ID Date2
31 1/1/2008 31 1/5/2008
34 1/4/3008 31 4/1/2008
31 3/2/2008
The first record in table2 would only link to the first record in table1
The second record in table2 would only link to the third record in table1
Any help would be greatly appreciated.
Thanks
I am trying to think of the easiest solution to do this.
What it is, is that I am trying to be alerted, if an account has not had its daily reports ran for x days (for example, 3 days).
I have two tables.
One is a table, with a list of AccountIDs that are to have the report done.
Another is a Log Table, which a record is inserted whenever a report has run. So, if a report has not run, it will not have a record in that table.
Here is the table setup with sample data. I am only going into basics, as the tables are used for a couple other things which are not needed for this.
Code:
Table: Reports_AccountID
ReportID | AccountID
--------------------------
5 | 1234
5 | 987
4 | 1234
3 | 1234
5 | 5677
Log: Reports_Log
LogID | ReportID | AccountID | ReportRunDate
----------------------------------------
9876 | 5 | 1234 | 9/4/2007 10:35:43 AM
9875 | 5 | 987 | 9/4/2007 10:35:43 AM
9874 | 4 | 1234 | 9/4/2007 9:05:43 AM
9873 | 3 | 1234 | 9/4/2007 9:30:17 AM
9872 | 5 | 1234 | 9/3/2007 10:35:43 AM
9871 | 5 | 987 | 9/3/2007 10:35:43 AM
9870 | 4 | 1234 | 9/3/2007 9:05:43 AM
9869 | 3 | 1234 | 9/3/2007 9:30:17 AM
9868 | 5 | 1234 | 9/2/2007 10:35:43 AM
9867 | 5 | 987 | 9/2/2007 10:35:43 AM
9866 | 5 | 5677 | 9/2/2007 10:35:43 AM
9865 | 4 | 1234 | 9/2/2007 9:05:43 AM
9864 | 3 | 1234 | 9/2/2007 9:30:17 AM
so if i wanted to know what report for reportID 5 hasn't run in the past 2 days, it would be accountID of 5677 (last ran on 9/2/2007 10:35:43 AM)
I am not sure where to even start. I am thinking I can grab the latest report ran for all Accounts in the Reports_AccountID table (create temp table and insert most recent log record into that table), and do a date difference between the date and current datetime and select based on datedifference > 2 ?
or would the best way is to do an inner join between the 2 tables for all reports ran in the past 2 days, and then whatever account is not listed in that query, is a report that has not run (do a subquery?)
just trying to think of the best way to do this. any tips/advice would be appreciated
Please concider the line of code below. it doesnt quite work how i want it to. i need it to only pull records where there isnt any activity completed (actualend) after a given period. and only show the most recent activity per regardingobjecid(sales person) however it returns all the records before that period and shows me the most recent one up to that time. Please help.
here is the code
SELECT new_ratingname, regardingobjectidname, actualend, owneridname, createdbyname, activitytypecodename, count(*) AS Total
FROM (SELECT filteredcontact.new_ratingname, filteredactivitypointer.regardingobjectidname, filteredactivitypointer.actualend,
filteredactivitypointer.Owneridname, filteredactivitypointer.createdbyname, filteredactivitypointer.activitytypecodename, row_number()
OVER (Partition BY regardingobjectid
ORDER BY actualend DESC) AS recid
FROM FilteredActivityPointer INNER JOIN
FilteredContact ON FilteredContact.contactid = FilteredActivityPointer.regardingobjectid
WHERE new_ratingname NOT IN ('dead', 'archive', 'continous updates') AND filteredactivitypointer.statecodename = 'completed' AND
filteredactivitypointer.owneridname IN (@user) AND filteredactivitypointer.createdbyname NOT IN ('melvin felicien', 'suzette collymore', 'gasper ') AND
filteredactivitypointer.activitytypecodename IN ('phone call', 'e-mail', 'fax') AND filteredactivitypointer.actualend <= dateadd(d,
- CAST(@NeglectedDays AS INT), GetUTCDate())) AS d
WHERE recid = 1
GROUP BY d .actualend, d .regardingobjectidname, d .owneridname, d .createdbyname, d .activitytypecodename, new_ratingname
ORDER BY d .actualend
Compnetsyslc
Every hour we capture some values from our factory (position of pumps, valves, ...) in our sql server 2000 db.
Normally 1 record is added to the db.
00:00:00
01:00:00
02:00:00
...
21:00:00
22:00:00
23:00:00
All of these values are displayed in an Excel sheet. One sheet contains all the data from one month.
I noticed a problem last week when 2 records were added: first one at 19:00:00 and the second one at 19:00:01
We only want to keep the most recent record (19:00:01) in a situation like this but I can't seem to work out an SQL-statement :o
This is what we have know. It used to work fine untill we had 2 record being added instead of one.
SELECT TimeOfCapture, Value1, Value2, Value3
FROM FactoryTable
WHERE (MONTH(TimeOfCapture) = MONTH(GETDATE())) AND (YEAR(TimeOfCapture) = YEAR(GETDATE()))
Hi,
I want to construct a query that would give the most recent record. Table data looks like -
F1F2F3 F4
20016395074/7/2004 15:40:55
23516395074/6/2004 9:31:54
All columns are strings as the data is obtained from text file. Column F3 can have same or different dates. In the above data select 1st record as it is the most recent.
If data is like -
F1F2F3 F4
20016395074/7/2004 15:40:55
23516395074/7/2004 19:31:54
Select the 2nd record.
How do I construct the SQL query?
Let me know.
Here's the scenario. Consider the following sample data.
CREATE TABLE #MyTest
(
CustomerNumber INT,
Division CHAR,
SalesRepType INT,
SalesRepNumber INT,
EnterDate DATE
[code]....
Essentially, what I’m attempting to do is for each Customer, Division, SalesRepType determine who the most recent assigned SalesRepNumber is and when (EnterDate) that person was assigned. So using the sample data, I would expect the following results.
CustomerNumberDivisionSalesRepTypeSalesRepNumberAssignedDate
10000A11002/1/2015
10000A23001/28/2015
10000B14002/1/2015
10000B26002/2/2015
I’ve tried various ways of using a CTE and ROW_NUMBER trying to get at this, but the area that is giving me the problem is in Division A, SalesRepType 1. Here is what gets me close, but I’m picking on SalesRepNumber 200 instead of 100 for Division A and
SalesRepType 1.
WITH
cteCust (RowNum, CustomerNumber, Division, SalesRepType, SalesRepNumber, BeginDate)
AS
(
SELECT
[code]....
by executing this qry i will get below result ,in that TId is duplicates that is from that i want recent record with same result.i want last one if TId comes duplicate record. any one ans pl
select sa.GrandTotal, sa.TId, sa.Id,sa.Date, pr.PName,
pr.PCode from Sales sa
Left Outer Join Product pr on sa.Pid = pr.Id where sa.GrandTotal is not null
GrandTotalTIdIdDate PNamePCode
200 122014-01-30 18:46:55.000RK100
560 252014-01-30 18:47:49.000RK100
420 262014-01-30 19:55:11.000RK100
MSSQL2000I have a table that contains customer transactionsCustomerIDTransactionTransactionDate....I need to select the most recent record that matches a specific CustomerID.I am fairly new to SQL, could someone provide a sample select statement.TIATim Morrison-- Tim Morrison--------------------------------------------------------------------------------Vehicle Web Studio - The easiest way to create and maintain your vehicle related website.http://www.vehiclewebstudio.com
View 3 Replies View RelatedWe have a work order notes table in our ERP system, and I want to see the most recent note record for each work order. Sometimes there will one be one, so I want to see all those, but sometimes there will be several notes for each work order and in this case I want to see only the most recently added note for that work order.
The query below shows some results as an example. In this case I want to see all the records except for work order number DN-000023 where I only want to see the note dated/timed 07-12-2011 16:52 (the most recent one).
select id, worknumber, date, notes from worksordernotes
id worknumber date
----------- ------------ ----------------------- --------------------
1 DN-000056 2011-12-07 13:22:00 13.20 PM JAMES- SPOK
2 DN-000079 2011-12-07 14:24:00 JCB HAVE TOLD ME THE
4 DN-000065 2011-12-07 15:48:00 ANDY FROM SITE RANG
5 DN-000023 2011-12-07 15:54:00 CHASED THIS 4 TIMES
6 DN-000023 2011-12-07 16:52:00 HOLTS ATTENDED THIS
7 DN-000092 2011-12-08 09:50:00 RETURNING WITH PARTS
Hello, i have this problem
tbl1 :{ pcb varchar(30) ,serial varchar(30)},result varchar(30)
tbl2: { pcb varchar(30) ,date_time varchar(30),result varchar(30),data1 varchar(30),data2 varchar(30),}
what i need is for a range of serials get the pcb (from tbl1) and for those pcb's get as resultSet the table
tbl3:{pcb,serial,data1,data2} containing most recent data on date_time column.
example. tbl1 ={ pcb1,sn1,pass}
pcb2,sn2,pass
pcb3,sn3,pass}
tbl2={pcb1,date1,pass,dataX1,dataY1
pcb1,date2,pass,dataX2,dataY2
pcb2,date3,pass,dataX3,dataY3
pcb3,date4,pass,dataX4,dataY4
pcb1,date5,pass,dataX5,dataY5
pcb2,date6,pass,dataX6,dataY6}
where date1 is the most recent date
Request:what i want is for serial>=s1 and serial<=s2 get
pcb1,sn1,date1,dataX1,dataY1
pcb2,sn2,date3,dataX3,dataY3
What i already did is this:
Code Snippet
select max(CONVERT(DATETIME,tbl2.date_time,103)),tbl1.serial,tbl2.pcb
from tbl2
left JOIN tbl1
ON tbl2.Pcb=tbl1.pcb
where tbl1.serial>='1' and tbl1.serial<='53'
and tbl2."Result" like 'pass' and tbl1."result" like 'pass'
group by tbl2.pcb,tbl1.serial
And this works correctly.But unfortunately i also need data1 and data2 columns from tbl2.
If i include them in the Select Clause i have to include them also in the group by ,and this gives me also duplicate records.I mean it would return from tbl2, all records containing pcb1,pcb2.
How can i resolve this issue?
Thank you very much,
ElectricC
Given the Patients and PatientVisits tables as per below, how do I obtain the most recent (latest) Weight and Height for each patient as per http://www.hazzsoftwaresolutions.net/selectStatement.htm
The result of the query should only return 3 rows/records,not 5. Thank you. Greg
Code Snippet
select p.ID, p.FirstName,p.LastName,DATEDIFF(year, p.DOB, getdate()) AS age
,pv.WeightPounds, pv.HeightInches
from Patients as p
inner join PatientVisits as pv
ON p.ID = PV.PatientID
order by pv.VisitDate desc
INSERT INTO Patients (ID, FirstName,LastName,DOB)
select '1234-12', 'Joe','Smith','3/1/1960'
union
select '5432-30','Bob','Jones','3/1/1960'
union
select '3232-22','Paul','White','5/12/1982'
INSERT INTO PatientVisits (PatientID, VisitDate,WeightPounds,HeightInches)
select '1234-12', '10/11/2001','180','68.5'
union
select '1234-12', '2/1/2003','185','68.7'
union
select '5432-30','11/6/2000','155','63.0'
union
select '5432-30','5/12/2001','165','63.0'
union
select '5432-30','4/5/2000','164','63.5'
union
select '3232-22','1/17/2002','220','75.0'
CREATE TABLE [dbo].[Patients](
[PID] [int] IDENTITY(1,1) NOT NULL,
[ID] [varchar](50) NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[DOB] [datetime] NULL,
CONSTRAINT [PK_Patients] PRIMARY KEY CLUSTERED
CREATE TABLE [dbo].[PatientVisits](
[ID] [int] IDENTITY(1,1) NOT NULL,
[PatientID] [nvarchar](50) NULL,
[VisitDate] [datetime] NULL,
[WeightPounds] [numeric](18, 0) NULL,
[HeightInches] [decimal](18, 0) NULL
) ON [PRIMARY]
I am trying to pickup the most recent record by Product and Supplier.
eg.
Rec Prod Supp Date
1 1000 WES01 Jan 1/2005
2 1000 WES01 Apr 8/2005
3 1001 WES01 Apr 8/2005
4 1001 NAN04 Feb 15/2005
The Result I want is as follows:
Rec Prod Supp Date
2 1000 WES01 Apr 8/2005
3 1001 WES01 Apr 8/2005
4 1001 NAN04 Feb 15/2005
I have tried various methods with the TOP 1 and DISTINCT statements, but have not been able to get the results I am looking for. Help would be appreciated.
I got some issues regarding selecting the most recent record from a datetime type column.
The probelm consists in following:
Table: News
Needed columns: Letter [varchar]
DateTimePublication [datetime]
Table News: Letter DateTimePublication
FR1_dd_mm_yy dd.mm.yy hh:mm:ss
FR1_dd_mm_yy dd.mm.yy hh+1h:mm:ss
FR1_dd_mm_yy dd.mm.yy hh-2h:mm:ss
EN1_dd_mm_yy dd.mm.yy hh+3h:mm:ss
EN1_dd_mm_yy dd.mm.yy hh+4h:mm:ss
EN1_dd_mm_yy dd.mm.yy hh-5h:mm:ss
DU1_dd_mm_yy dd.mm.yy hh:mm:ss
DU1_dd_mm_yy dd.mm.yy hh+1h:mm:ss
DU1_dd_mm_yy dd.mm.yy hh-2h:mm:ss
FR2_dd_mm_yy dd.mm.yy hh:mm:ss
FR2_dd_mm_yy dd.mm.yy hh+1h:mm:ss
FR3_dd_mm_yy dd.mm.yy hh-2h:mm:ss
EN2_dd_mm_yy dd.mm.yy hh+3h:mm:ss
EN3_dd_mm_yy dd.mm.yy hh+4h:mm:ss
EN3_dd_mm_yy dd.mm.yy hh-5h:mm:ss
DU1_dd_mm_yy dd.mm.yy hh:mm:ss
DU3_dd_mm_yy dd.mm.yy hh+1h:mm:ss
DU4_dd_mm_yy dd.mm.yy hh-2h:mm:ss
The ideea is that there are letters with the same name [Letter] but different DateTimePublication ... and I need only the letters with the most recent DateTimePublication for every letter type like FR_dd_mm_yy/EN_dd_mm_yy/DU_dd_mm_yy.
Thank you for your time and consideration!
SenneK
If I have a table structure similar to the following, how might I query it to obtain the Transaction ID, Transaction Date, and Customer Name for the most recent transaction per customer only:
TransactionTable:
TransactionID TransactionDate TransactionType CustomerName
1 10/1/07 1 Bob
2 8/30/07 2 Janet
3 9/17/07 1 Mike
4 9/25/07 1 Bob
The following query will return all records in the table other than Janets...not what I want:
SELECT Transaction ID, TransactionDate, CustomerName
FROM TransactionTable
WHERE TransactionType = 1
ORDER BY TransactionDate DESC
The following query will return all records in the table other than Janets again, because DISTINCT will use the combo of TransactionID, TransactionDate, and Customer name for uniqueness...not what I want:
SELECT DISTINCT Transaction ID, TransactionDate, CustomerName
FROM TransactionTable
WHERE TransactionType = 1
ORDER BY TransactionDate DESC
The results set I'm looking for would be the following, where only the most recent entry per customer with TransactionType 1 is returned (i.e. one record for Bob):
TransactionTable:
TransactionID TransactionDate CustomerName
1 10/1/07 Bob
3 9/17/07 Mike
I believe I need an appropriate subquery to yield the results I desire, but can't sort out what it is? I do not want to execute multiple queries but subqueries are fine. Unfortunately there's probably an easy answer that my brain is not currently generating. Any help would be appreciated.
Note, this is not a real table, but a sample to illustrate the concept for the query I need.
Thanks.
The "Last" function in the query below (line 4 & 5) is not exactly what I'm after. The last function finds the last record in that table, but i need to find the most recent record in the table according to a date field.
Code:
SELECT
tblinmate.statusid,
tblinmate.activedate,
Last(tblclassificationhistory.classificationid) AS LastOfclassificationID,
Last(tblsquadhistory.squadid) AS LastOfsquadID,
tblperson.firstname,
tblperson.middlename,
tblperson.lastname,
[Code] ....
The query below finds the most recent record in a table according to a date field, my problem is i dont know how to integrate this Query into the above to replace the "Last" function
Code:
SELECT a.inmateID,
a.classificationID,
b.max_date
FROM (
SELECT tblClassificationHistory.inmateID,
tblClassificationHistory.classificationID,
[Code] .....
I am working on a query that needs to return the record order number with the most recent requested delivery date.
It seems to work most of the time, but I have found some glitches for some of the items.
example is my item 10702, it is showing 2 records (both valid ones)
Record 1 is order # 10450-0, requested delivery date 03/21/2014
Record 2 is order # 10510-0, requested delivery date 04/29/2014
I need to only get the records with the most recent delivery date, in this example that would be 04/29/2014
This query is what I have so far:
SELECT
s.PriorQuoteNumber
,s.PriorItemNumber
,s.PriorQuoteDate
FROM
(
SELECT
s.SalesQuoteNumberAS 'PriorQuoteNumber'
[code]....
WHERE s.rn = 1What am I missing in my query> how can I change it so it only returns the most recent date?
I have a table full of service invoice records. Some of the invoices are continuous, meaning that there may be an invoice from 01-16-2015 through the end of that month, but then another invoice that starts on feb 1 and goes for 6 months.
I want to only pull the most recent. Keep in mind that there may be other invoices in the same table for a different period. An example might be:
FromDate ToDate Customer Number Contract Number
02/01/2015 07/31/2015 2555 456
01/15/2015 01/31/2015 2555 456
04/01/2013 09/30/2015 2555 123
03/13/2013 03/31/2013 2555 123
From this table, I would like a query that would give me this result:
01/15/2015 07/31/2015 2555 456
03/13/2013 09/30/2015 2555 123
There will likely be more than just 2 consecutive records per contract number.
So I have a query that need to find the most recent datetime record each day for a customer. So I have a query that looks like this:
SELECT
dhi.[GUID],
dhi.[timestamp],
la.[bundle_id],
dhi.[value]
FROM
[dbo].[DecisionHistoryItem] as dhi WITH(NOLOCK)
[Code] ....
I HAVE THIS SQL STATEMENT WHICH GET THE MIN(AMOUNT) and MAX(AMOUNT) and DISPLAY IT IN A SINGLE ROW.
HOW CAN I GET THE RECORD BEFORE THE MAX (AMOUNT) INBETWEEN DATES IN CASE THERE ARE 4 RECORDS WITH SAME EMPLOYEENO IN THE DATE RANGE SPECIFICIED ? THIS IS SORTED BY EMPLOYEENO AND STARTDATE DESC
SELECT EmployeeNo, StartDate AS StartDate, MIN(Amount) AS oldsalary, EndDate AS EndDate, BenefitCode,
(SELECT TOP 1 MAX([amount])
FROM EMPBENEFITS T2
WHERE T2.employeeno = T.employeeno AND startdate >= '2001-06-01' AND startdate <= '2007-06-02') AS newsalary
FROM dbo.empBenefits T
WHERE (StartDate IN
(SELECT TOP 1 ([startdate])
FROM EMPBENEFITS T1
WHERE T1.employeeno = T.employeeno AND Benefitcode <> 'HON' AND startdate >= '2001-06-01' AND startdate <= '2007-06-02'))
GROUP BY EmployeeNo, Amount, BenefitCode, StartDate, EndDate, Amount
ORDER BY EmployeeNo, StartDate DESC
THANK YOU SO MUCH ...
I have a date/time value (dd/MM/yyyy HH:mm:ss) stored in a field as part of each record in a dataset.
I would like to achieve the following via a single T-SQL query if possible (efficiency is not an issue as the dataset is small)
On query run;
Check if any records in the dataset are within 10 minutes + or - of the current system time (using the date/time value referenced above)
if none
Load the first record
I am currently using this SQL code to capture some records over the last 2 months and it has been working great. I am now being asked if I can change this code with specifications:
1) Scan the records in the system until the count (*) as Volume reaches 30 because they prefer that as a denominator when figuring an average
2) Only run the scan for a maximum of 6 months.
So, there will most likely be some records that do not reach a volume number of 30 in this date range. In this instance we will just take the maximum volume number reached at 6 months.
So, how can I write this so it will build the file each time a record has reached the maximum of 30 and keep scanning back until we reach 6 months? If someone could lead me in the right direction on the proper order of the methodology in my code to accomplish these results it would be greatly appreciated. Desperate!
declare
@startdate smalldatetime,
@enddate smalldatetime ,
@month int,
@year int
select
@startdate = dateadd (mm, -2, getdate())
SELECT
@month = datepart (month, @startdate),
@year = datepart (year, @startdate)
SELECT
@startdate = convert (smalldatetime, convert(varchar(2), @month) + "/1/" + convert (varchar(4), @year))
select
@enddate = dateadd (mm, 2 , @startdate)
select distinct
pe1.patev_loc_id as LocID,
pp_cproc_id_r as ProcID,
count (*) as Volume,
sum (datediff (mi, pe1.patev_event_time, pe2.patev_event_time)) as Minutes,
sum (datediff (mi, pe1.patev_event_time, pe2.patev_event_time))/count(*) as AvgMin
from
risdb_rch08_stag..performed_procedure (index pp_serv_time_r_ndx),
risdb_rch04_stag..patient_event pe1,
risdb_rch04_stag..patient_event pe2
where
pp_service_time_r between @Startdate and @Enddate
and pp_asn_req_no = pe1.patev_asn_req_no
and pp_asn_seq_no = pe1.patev_asn_seq_no
and pp_status_v = 'CP'
and pp_rep_id > 0
and pe1.patev_event_code = 'PB'
and (pp_asn_req_no = pe2.patev_asn_req_no
and pp_asn_seq_no = pe2.patev_asn_seq_no
and pe2.patev_event_code = 'PL')
and datediff (mi, pe1.patev_event_time, pe2.patev_event_time) > 0
group by
pe1.patev_loc_id , pp_cproc_id_r
if I create an index for a table with some records, do you think I can retrieve records in a giving range? for example, the 5th to 10th records?Possible? How can I do it?When we insert data at the table, would the index in sequential order? How would the index be created for new inserted records?I'm using SQL 2005 Express, not SQL 2000.
View 14 Replies View RelatedUsing MSSQL 2008 R2
Given the following table
Code:
{ID, PropClass, OffSet, Amount}
{1, 1, 1, .30}
{2, 1, 2, .45}
{3, 1, 3, .50}
{4, 2, 1, .26}
{5, 2, 2, .15}
If I know the exact offset I can query easily enough using PropClass and the exact offset. But what if the offset is not included in the range for a given PropClass? How can I get a query to return the last valid record for a given PropClass from within a join?
For example, if my query contained PropClass = 1 and offset = 4, it should return the Amount of .50 from Record with ID 3
This is a query that I am trying to work on:
Code:
SELECT v.District, v.PropClass, YearAquired, SUM(cost * cnt), SUM(v.Cost * v.Cnt * t.Amount), SUM(v.Tax), COUNT(*)
FROM UPValue v
INNER JOIN UPMaster m on m.Year = v.year and m.Account = v.account
INNER JOIN UPTable T on t.PropClass = v.PropClass and t.Offset = v.Year - v.YearAquired
WHERE v.Year = 2012 and LeaseType = 2
group by v.District, v.PropClass, YearAquired
order by v.District, v.PropClass
Using <= will not work because that would return multiple records from UPTable when the offset is < the max offset.
I have a situation where an agent has number of activities for a certain date range. If an agent has multiple activities within certain date range, I would like BALANCE BEFORE from the first activity and BALANCE AFTER from the last activity. Here is my current SQL query that returns the following data:
DECLARE @BeginDate Datetime
DECLARE @EndDate Datetime
Set @BeginDate = '05-1-2015'
Set @EndDate = '05-31-2015'
SELECT
a.AgentName,
R.BALANCEBEFORE,
[Code] ....
AGENTNAME BALANCE BEFORE BALANCE AFTER DATE
DOUGLAS 9738.75 9782.75 2015-05-11
DOUGLAS 9782.75 9804.75 2015-05-12
DOUGLAS 9804.75 9837.75 2015-05-13
In the sample data above, ideally I would like my query to return data as follow:
AGENTNAME BALANCE BEFORE BALANCE AFTER
DOUGLAS 9738.75 (from first activity) 9837.75 (from last activity)
Not sure how I can write sql query to accomplish this.
I need to create records based on date range on monthly basis, please help.
Here is an example:
ID Startdate EndDate
1 20070301 20070522
and I need to create the following data set based on start and end date range
ID Startdate EndDate
1 20070301 20070331
1 20070301 20070430
1 20070301 20070522
I want insert automatically records for Range that user definded
For example user define :
from to city
10000 12000 tehran
15000 19000 babol
I should inserted 2000 record for tehran (10000,10001,....,12000) and 4000 record for babol (15000,15001,...,19000)
When I run this query:
SELECT orderno, *
FROM _order
WHERE order_date >= '5/14/2015 00:00:00'
AND order_date < '5/15/2015 11:59:59'
ORDER BY order_date asc
I get a result of:
A1G7222015-05-14 13:00:11.143
A1G7232015-05-14 13:33:35.407
A1G7242015-05-14 13:39:16.657
A1G7252015-05-14 14:25:43.507
A1G7262015-05-14 14:29:18.050
A1G7272015-05-14 15:38:12.263
But I know there is one more record that falls into 05/15/2015
A1G7282015-05-15 12:26:52.807
Can you see what I am missing in my query in order for me to retrieve the missing record A1G728?
I need to get all customer records with the most recent tDate. A customer should never have duplicate tDate records, as only one record per day is allowed per customer, yet there somehow there are duplicates. Given this, I need to get the record with the Max date and Max ID. the ID column is an auto-incrementing Identity column.Below is the code without the Max ID column logic
SELECT tCustID, MAX(tDate) AS tDate--get MAX tDate records
FROM table1
GROUP BY tCustID
Have a table that list item#, date the standard cost went into effect and the standard cost. How do I find the difference in StdCost on the last EffectiveDate and second to last EffectiveDate. 5.59 (01/05/2015) minus 5.81 (09/29/.014) = -.22.
Item# EffectiveDate StdCost
1152 01/01/2009 5.50
1152 09/29/2014 5.81
1152 04/04/2011 5.56
[code]....
I am attempting to write a SQL query that retrieves info processed between two times (ie. 2:00 pm to 6:00 pm) during a date range (ie. 8/1/06 to 8/14/06)... I am new to SQL and am perplexed... I have referenced several texts, but have not found a solution. Even being pointed in the right direction would be greatly appreciated!!
View 6 Replies View RelatedI'm running a pre-defined script which used to work fine on a file which was resupplied every month. below is the script used and the error message. looking at the error I assume that there is a rouge record within the file but have looked in Textpad and cannot find it.
UPDATE [matching].[dbo].[hot_nov] SET [AOV] = (CAST([Demand] AS DECIMAL)/CAST([Orders] AS INT)) WHERE [Demand] <> '';
UPDATE [matching].[dbo].[hot_nov] SET [POST2] = left([PostCode], PATINDEX('%[0-9]%', [PostCode] + '1') - 1) ;
UPDATE [matching].[dbo].[hot_nov] SET [POST4] = left([PostCode],LEN([PostCode])-4);
UPDATE [matching].[dbo].[hot_nov] SET [POST6] = left([PostCode],LEN([PostCode])-2);
error message
(1000 row(s) affected)
Msg 245, Level 16, State 1, Line 181
Conversion failed when converting the varchar value '2014-09-03 00:00:00' to data type int.
I'm trying to get the last ten unique clients viewed by each user. I have tried using the TOP (N) but it does not show the last ten just any ten.
When a user views a client record, a record is saved to the tblUserRecentViewedClients table and includes logID (key), client_id (int), username (nvarchar), lastviewed (datetime).
Here is what I have so far. Can anyone offer any suggestions?SELECT TOP (10) tblUserRecentViewedClients.UserName, tblUserRecentViewedClients.Client_ID, tblClient.FirstName, tblClient.LastName,
tblClient.CompanyName
FROM tblUserRecentViewedClients INNER JOIN
tblClient ON tblUserRecentViewedClients.client_ID = tblClient.client_ID
WHERE tblUserRecentViewedClients.UserName=@UserName
GROUP BY tblUserRecentViewedClients.UserName, tblUserRecentViewedClients.Client_ID, tblClient.FirstName, tblClient.LastName,
tblClient.CompanyName