Hi,
I have to create the query in which I have to select complete row records according to the column "time" where the time is most recent entered.
there are columns "username", "bookname", "Time"
a asp 6/4/2008 6:02:00 AM
b sql 6/4/2008 6:04:00 AM
a php 6/4/2008 6:05:00 AM
Navi
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.
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
There are several day_timestamp for each index_id.Anyone can help me to write a sql to generate the most recentday_timestamp of index_ids which has not accessed into the system in90 days from today's date.So, I need to get the most recent date and time for each index_id in90 days from today's date.Sample data:Index_idday_timestamp2 2001-04-11 21-29-312 2002-05-21 21-29-312 2003-06-11 21-29-3122004-11-21 21-29-312 2004-09-21 21-29-315 2000-04-21 21-29-315 2003-05-21 21-29-315 2003-06-21 21-29-3152004-09-11 21-29-318 2000-08-11 21-29-3182004-04-01 21-29-3182004-09-21 21-29-3182004-09-23 21-29-31102001-04-11 21-29-31102002-04-21 21-29-31102003-08-11 21-29-31102004-10-21 21-29-31102004-09-21 21-29-31The output will be as below:2 2004-11-21 21-29-3152004-09-11 21-29-3182004-09-23 21-29-31102004-10-21 21-29-31
View 6 Replies View RelatedThis is more ASP SElect .
I need to return all the rows. Where the ESNnumber only returns the most recent one that is associsted with the Asset.
Basically, I need the info most current ESN number only.
They are 19,00 rows of each ESN number but it returns 40,000. Duplicates.
SELECT TOP (100) PERCENT dbo.AssetType.Description, dbo.AssetCustomAttributeDef.Name AS [Custom Asset], dbo.ESN.EsnNumber AS [ESN #],
dbo.AssetAttribute.AssetDescription AS [Description Detail], dbo.Asset.Barcode, dbo.Asset.SKU,
dbo.InventoryOrigin.WarehouseDescription AS [Inventory (W/H)], dbo.ESN.DateImplemented, dbo.ESNTracking.TraceTime,
dbo.ESNTracking.PreviousTraceTime, dbo.ESNTracking.HasMoved, dbo.ESNTracking.DistanceMiles, dbo.ESNTracking.Direction,
dbo.ESNTracking.Landmark, dbo.ESNTracking.FemaLocation AS Fema, dbo.ESNTracking.ReportTime AS [Report Time],
dbo.ESNTracking.CurrLocStreet AS Address, dbo.ESNTracking.CurrLocCity AS City, dbo.ESNTracking.CurrLocState AS State,
dbo.ESNTracking.CurrLocZip AS Zipcode, dbo.ESNTracking.CurrLocCounty AS County, dbo.ESNTracking.MapUrl AS [Map Link],
dbo.ESNTracking.ReplaceByDate AS [Replace Batt.], dbo.ESNTracking.CurrMileFromStratix AS [From Stratix Now],
dbo.ESNTracking.PrevMileFromStratix AS [From STratix Then]
FROM dbo.AssetType INNER JOIN
dbo.Asset ON dbo.AssetType.AssetTypeId = dbo.Asset.AssetTypeId INNER JOIN
dbo.InventoryOrigin ON dbo.Asset.WarehouseId = dbo.InventoryOrigin.WarehouseId INNER JOIN
dbo.AssetAttribute ON dbo.Asset.AssetAttributeId = dbo.AssetAttribute.AssetAttributeId INNER JOIN
dbo.EsnAsset ON dbo.Asset.AssetId = dbo.EsnAsset.AssetId INNER JOIN
dbo.ESN ON dbo.EsnAsset.EsnId = dbo.ESN.EsnId LEFT OUTER JOIN
dbo.ESNTracking ON dbo.EsnAsset.EsnId = dbo.ESNTracking.EsnId LEFT OUTER JOIN
dbo.AssetVehicle ON dbo.EsnAsset.AssetId = dbo.AssetVehicle.AssetId LEFT OUTER JOIN
dbo.AssetCustomAttribute ON dbo.EsnAsset.AssetId = dbo.AssetCustomAttribute.AssetId LEFT OUTER JOIN
dbo.AssetCustomAttributeDef ON dbo.AssetCustomAttribute.AssetTypeId = dbo.AssetCustomAttributeDef.AssetTypeId
ORDER BY dbo.AssetType.Description
The TSQL below all works except the bolded part at the end. I'm want to grab only the most recently logged piece of equipment not the most recent and all past ones as well which is what I've got doing minus the bolded part below. But I don't know how to say get this Equipment ID etc and only the most recently logged one to find its present location. The bolded part below is just there to show what I want it to do I know you can use an aggregate in a where clause. So in the first table listed tblRdrLog there is a column Time that I want to do this on so a.Time. I don't want to display a.Time just reference.
String dbsql = " SELECT a.EquipmentID " +
" , f.Subcategory " +
" , c.Area " +
" , d.Room " +
" FROM tblRdrLog a " +
" JOIN tblRdrInfo b ON a.ReaderID = b.ReaderID " +
" JOIN tblRdrArea c ON b.AreaID = c.AreaID " +
" JOIN tblRdrRm d ON b.RoomID = d.RoomID " +
" JOIN tblEquipInfo e ON a.EquipmentID = e.EquipmentID " +
" JOIN tblEquipSubcat f ON e.SubcategoryID = f.SubcategoryID " +
" WHERE a.EquipmentID IN (SELECT a.EquipmentID " +
" FROM tblEquipInfo a " +
" JOIN tblEquipCat b ON a.CategoryID = b.CategoryID " +
" JOIN tblEquipSubcat c ON a.SubcategoryID = c.SubcategoryID " +
" LEFT OUTER JOIN tblEquipMake d ON a.MakeID = d.MakeID " +
" LEFT OUTER JOIN tblEquipModel e ON a.ModelID = e.ModelID " +
" JOIN tblStatus f ON a.StatusID = f.StatusID " +
" WHERE b.CategoryID = '" + this.ddlCategory.SelectedValue.ToString() + "' ";
if (!"".Equals(this.ddlSubcategory.SelectedValue.ToString()))
dbsql += " AND c.SubcategoryID = '" + this.ddlSubcategory.SelectedValue.ToString() + "' ";
#region Advanced Search Criteria
// Check whether advanced search submitted
if (adv)
{
if (!"".Equals(this.tbSerialNo.Text.ToString()))
dbsql += " AND a.SerialNo = '" + this.tbSerialNo.Text.ToString() + "' ";
if (!"".Equals(this.ddlMake.SelectedValue.ToString()))
dbsql += " AND d.MakeID = '" + this.ddlMake.SelectedValue.ToString() + "' ";
if (!"".Equals(this.ddlModel.SelectedValue.ToString()))
dbsql += " AND e.ModelID = '" + this.ddlModel.SelectedValue.ToString() + "' ";
if (!"".Equals(this.ddlStatus.SelectedValue.ToString()))
dbsql += " AND f.StatusID = '" + this.ddlStatus.SelectedValue.ToString() + "' ";
}
#endregion
dbsql += " ) " +
" AND a.Time = max(a.Time) " +
"";
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()))
I need this query to pull just the last scan date, i.e. if a scan ran on 3/7/2012 I need just the records (there may be few or many) that pertain to that date and no other dates. When I run this query I am still getting all of the results (i.e. scan dates of 3/7/2012, 3/5/2012, 3/1/2012, etc)
I just need the most recent date, even if there are many records for that date (i.e. 10 IP addresses that were all scanned on 3/5/2012)
Code:
SELECT CPA.ScanName,
CPA.pspplMSSeverity,
CPA.smachIPAddress,
[Code]....
I am creating a database in SQL2K for collating test results taken at regular intervals across several different sites and frequencies. The table will look something like this:
SiteDate Data
A01/01/2013 ...
B02/01/2013 ...
C03/01/2013 ...
A04/01/2013 ...
B05/01/2013 ...
C06/01/2013 ...
And so on...
In total there will be about 300 sites, with up to 12 records per site every year. I want to be able to create a view showing the 4 most recent results for each site. Is there a simple way of doing this? Obviously getting the most recent result for each site is quite straight forward, but I can't work out how to get the last four. Unfortunately this is on a 2k box, although I could (at a push) use a 2k8 box if needed.
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.
I have 2 tables:
1) Item table (No_, Description)
2) Sales Price HAG table (Item No_, Sales Price HAG, Starting Date)
I want to have a report within from every item the most recent sales price. But, it is possible that a unique item has more than 1 sales price in the 'Sales Price table' (if it has more than one Starting Date). I want to report the sales price with the most recent date.
I've made this statement:
SELECT [DatabaseName$Item].No_, [DatabaseName$Item].Description,
[DatabaseName$Sales Price HAG].[Unit Price]
FROM [DatabaseName$Item] INNER JOIN
[DatabaseName$Sales Price HAG] ON
[DatabaseName$Item].No_ = [DatabaseName$Sales Price HAG].[Item No_]
WHERE ([DatabaseName$Sales Price HAG].[Starting Date] IN
(SELECT MAX([Starting Date]) AS EXPR1
FROM [DatabaseName$Sales Price HAG] AS [DatabaseName$Sales Price HAG_1]
GROUP BY [Item No_]))
After running I still get from some items more than 1 price...... What went wrong?
Thx for help!
I have the following query that should return the most recent FormNote entry for a work order where the note begins with "KPI". However if someone decides to a more recent note, it selects that one, even if it doesn't begin with "KPI".
I would like it to return the most recent record that ALSO begins with "KPI". How can I correct this?
Select wh.worknumber, wh.date_created, wh.itemcode, wn.TextEntry as [Notes] from worksorderhdr wh left join
(select ID, WorksOrder,[CreationDate], TextEntry
from
(
select ROW_NUMBER()over(partition by worksorder order by [CreationDate] desc) OID,*
from FormNotes
)orders where orders.OID=1 ) wn on wn.WorksOrder = wh.worknumber where TextEntry like 'KPI%'
Sample results below, see line 5 - this record should not have been selected as there is a record beginning with "KPI" for that work order, but it is dated before this one.
worknumber date_created itemcode Notes
-------------------- ----------------------- -------------------- -----------------------------------------------------------------------------------
HU-DN-004385 2014-07-21 16:15:00 4261 KPI Hyd oil leak repaired
HU-DN-004707 2014-08-06 11:39:00 8005 KPI Valve replaced on day 2.
HU-DN-004889 2014-08-19 15:44:00 9275A KPI Repaired in 2 days - m/c working
HU-DN-004923 2014-08-22 14:23:00 4261 KPI New tracks fitted
HU-DN-005162 2014-09-12 15:04:00 9360A Mechlock key delivered to site - m/c working
HU-DN-005170 2014-09-15 12:07:00 2130A KPI 28.10.14 Metlock fitted
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 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 am using SQL SERVER 2008R2, not Denali, so I cannot use OFFSET FETCH Clause.
In my stored procedure, I am doing a SELECT INTO #tblTemp FROM... Working fine. This resultset is going to be used in an SSIS package which will generate a pipe-delimited .txt file... Working fine.
For recoverability sake, I am trying to throttle back on the commit chunks to 1000 rows per commit until there are no more rows. I am trying to avoid large rollbacks.
Q: Am I supposed to handle the transactions (begin/commit/rollback/end trans) when the records are being inserted into the temp table? Or when they are being selected form the temp table?
Q: Or can I handle this in my SSIS package for a flat file destination? I don't see option for a flat file destination like I do for an OLE DB Destination (like Rows per batch, Maximum insert commit size).
I'm trying to create a stored procedure from a join of two tables. One table holds a list of containers, and the other table holds the history of the contents of those containers. All I want is to retrive the most recent history for each container. For example, the containers table has the container number and name, and the history table has the
volume in the container and the date and time of the measurements. There can be any number of measurements, but I only want the most recent one.
Normally, I would just create a cursor that holds a list of the containers and some blank fields, and then loop through it, retrieving the most recent record one by one, but I don't know how to do that in Transact-SQL. Also, I thought maybe some SQL wizard out there might know of a way to do it with a simple select statement.
Geoffrey Callaghan
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 have a history table with the following values
CREATE TABLE History (SnapShotDate DATETIME, UID VARCHAR(10), DUEDATE DATETIME)
INSERT INTO History VALUES ('03-23-2015','PT-01','2015-04-22')
INSERT INTO History VALUES ('03-30-2015','PT-01','2015-04-20')
INSERT INTO History VALUES ('04-06-2015','PT-01','2015-06-30')
[Code] ....
I need an output in the below format. I need the most recent changed value for any given UID. Need to get the below result
OUTPUT
UID PreviousDueDate CurrentDueDate
----------------------------------------
PT-01 2015-04-20 2015-06-30
PT-02 2015-04-22 2015-04-22
PT-03 2015-04-18 2015-04-22
PT-042015-04-222015-04-18
This is my table:
ID AppName DepCode DepName Group ModifiedDate YearlyAmount
1 Nestle NS Foods Products 01/12/14 451
1 Nestle NS Foods Products 01/17/14 495
2 Oracle OR Software Info 01/24/14 279
2 Oracle OR Soft & IT Info 01/26/14 310
2 Oracle ORL Software Info 01/25/14 219
2 Oracle ORL Soft IT 01/28/14 600
MonthlyAmount Funded AppCategory Research
37.5623 Yes NE NA
41.2365 No N NA
23.2568 Yes OR InProgress
25.8333 Yes ORL NA
18.2189 Yes SOF Approved
50.0000 No IT RejectedExpected Output:
ID AppName DepCode DepName Group ModifiedDate YearlyAmount
1 Nestle NS Foods Products 01/17/14 946
2 Oracle OR Soft & IT Info 01/26/14 589
2 Oracle ORL Soft IT 01/28/14 819
MonthlyAmount Funded AppCategory Research
78.7988 No N NA
49.0901 Yes ORL NA
68.2189 No IT Rejected
I want to pick the recent modified date for DepCode and sum Yearly and Monthly Amount. I have tried this query and not able to get the output. This is the single table.
select B1.[ID], B1.[AppName], B1.[DepCode], B1.[DepName], B1.[Group],
B2.ModifiedDate, B2.YearlyAmount, B2.MonthlyAmount,
B1.[FuBded], B1.[AppCategory], B1.[Research]
FROM Business B1
INNER JOIN
(select [ID], MAX(ModifiedDate) as ModifiedDate, SUM(YearlyAmount) as YearlyAmount,
SUM(MonthlyAmount) as MonthlyAmount
from Business
Group by ID) B2
ON B1.ID = B2.ID AND B1.ModifiedDate = B2.ModifiedDate
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] .....
Empid 1 has 2 entries for the date 09/01/2015 and my left join returns both of those entries. What do I need to alter to make it so that only the most recent entry is returned not both entries?
Create Table #one
(
empid varchar(100)
,empbadgeid varchar(100)
,emplunchtime decimal(18,4)
,empbreaktime decimal(18,4)
,empworktime decimal(18,4)
[code]....
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]....
Code:
create table Channels (ChannelID int(5), other varchar(255));
create table Blogs (BlogID int(5), BlogXMLPath varchar(255));
create table BlogAssociations (BlogID int(5), ShowID int(5), ChannelID int (5));
create table Episodes (EpisodeID int(6), BlogID int(5), ShowID int(5), ChannelID int(5), Other varchar(30));
insert into Channels values (1, 'Channel 1');
insert into Channels values (2, 'Channel 2');
insert into Channels values (3, 'Channel 3');
insert into Channels values (4, 'Channel 4');
insert into Blogs values ('1', 'blah');
insert into Blogs values ('3', 'blah');
insert into Blogs values ('4', 'blah');
insert into Blogs values ('12', 'blah');
insert into Blogs values ('34', 'blah');
insert into Blogs values ('35', 'blah');
insert into Blogs values ('67', 'blah');
insert into Episodes values (1, 3, '', '', 'Episode 1, blog 3');
insert into Episodes values (2, 3, '', '', 'Episode 2, blog 3');
insert into Episodes values (3, 3, '', '', 'Episode 3, blog 3');
insert into Episodes values (4, 3, '', '', 'Episode 4, blog 3');
insert into Episodes values (5, 1, '', '', 'Episode 5, blog 1');
insert into Episodes values (6, 1, '', '', 'Episode 6, blog 1');
insert into Episodes values (7, 1, '', '', 'Episode 7, blog 1');
insert into Episodes values (8, 4, '', '', 'Episode 8, blog 4');
insert into Episodes values (9, 4, '', '', 'Episode 9, blog 4');
insert into Episodes values (10, 4, '', '', 'Episode 10, blog 4');
insert into Episodes values (11, 4, '', '', 'Episode 11, blog 4');
insert into Episodes values (12, 4, '', '', 'Episode 12, blog 4');
insert into Episodes values (13, 12, '', '', 'Episode 13, blog 12');
insert into Episodes values (14, 12, '', '', 'Episode 14, blog 12');
insert into Episodes values (15, 12, '', '', 'Episode 15, blog 12');
insert into Episodes values (16, 12, '', '', 'Episode 16, blog 12');
insert into Episodes values (17, 12, '', '', 'Episode 17, blog 12');
insert into Episodes values (18, 34, '', '', 'Episode 18, blog 34');
insert into Episodes values (19, 34, '', '', 'Episode 19, blog 34');
insert into Episodes values (20, 34, '', '', 'Episode 20, blog 34');
insert into Episodes values (21, 34, '', '', 'Episode 21, blog 34');
insert into Episodes values (22, 35, '', '', 'Episode 22, blog 35');
insert into Episodes values (23, 35, '', '', 'Episode 23, blog 35');
insert into Episodes values (24, 35, '', '', 'Episode 24, blog 35');
insert into Episodes values (25, 35, '', '', 'Episode 25, blog 35');
insert into Episodes values (26, 67, '', '', 'Episode 26, blog 67');
insert into Episodes values (27, 67, '', '', 'Episode 27, blog 67');
insert into Episodes values (28, 67, '', '', 'Episode 28, blog 67');
insert into Episodes values (29, 67, '', '', 'Episode 29, blog 67');
insert into Episodes values (30, 67, '', '', 'Episode 30, blog 67');
insert into BlogAssociations values (3,'',1);
insert into BlogAssociations values (12,'',1);
There is my above sql script, i'm currently using mysql at home to test things but will convert to work in mssql.
I need to get associated BlogID for Certain ChannelID from the BlogAssociations table.
Then using those BlogID's that it finds for let just say ChannelID "1" list off the 3 most recent EpisodeID's from the Episodes Table per each BlogID.
So if ChannelID "1" has four BlogID's associated with it then in the Episodes table list off the recent 3 EpisodeIDs for BlogID "3", then the 3 most recent EpisodeIDs for BlogID "5" and so on and so on.
I have this which kind of does what I want:
Code:
select
e.BlogID, e.EpisodeID, e.other
FROM
episodes e, BlogAssociations ba
WHERE
e.BlogID = ba.BlogID and ba.ChannelID = '1'
ORDER BY
e.EpisodeID DESC;
It returns:
Code:
+--------+-----------+---------------------+
| BlogID | EpisodeID | other |
+--------+-----------+---------------------+
| 12 | 17 | Episode 17, blog 12 |
| 12 | 16 | Episode 16, blog 12 |
| 12 | 15 | Episode 15, blog 12 |
| 12 | 14 | Episode 14, blog 12 |
| 12 | 13 | Episode 13, blog 12 |
| 3 | 4 | Episode 4, blog 3 |
| 3 | 3 | Episode 3, blog 3 |
| 3 | 2 | Episode 2, blog 3 |
| 3 | 1 | Episode 1, blog 3 |
+--------+-----------+---------------------+
But as you can see it lists off 5 of the Episodes for BlogID "12", I only want the most recent 3 as previously stated. It also lists off more than 3 Episodes for BlogID "3". How in the world do I go about doing this?
I'm making this a stored procedure so I can't use php otherwise I wouldn't even be posting
Thanks so much!
I have the following tables in my DB
Employee table - This table has EmployeeID, Name, DOB.
EmployeeDesignation table - 1 Employee can have many designations with each having an effective date. There is no flag to indicate which among multiples is the current entry. You can only figure it out by the newest/oldest EffectiveDate. I want to get the most recent and the oldest for each employee.
EmployeeSalaryHistory table - Structure/Design is similar to EmployeeDesignation table. I want to get the starting salary and current salary for each employee.
I want my query to output me the following fields...
EmployeeID
EmployeeName
EmployeeDOB
EmployeeStartingDesignation
EmployeeCurrentDesignation
EmployeeStartingSalary
EmployeeCurrentSalary
Here is a piece of code to generate sample data sets
DECLARE @Employee TABLE (EmployeeID INT, EmployeeName VARCHAR (100), EmployeeDOB DATE)
INSERT @Employee VALUES (101, 'James Bond', '07/07/1945'), (102, 'Tanned Tarzan', '12/13/1955'), (103, 'Dracula Transylvanian', '10/22/1967')
DECLARE @EmployeeDesignation TABLE (EmployeeID INT, Designation VARCHAR (100), EffectiveDate DATE)
INSERT @EmployeeDesignation VALUES (101, 'Bond Intern', '01/01/1970'), (101, 'Bond Trainee', '01/01/1975'), (101, 'Bond...James Bond', '01/01/1985')
[Code] ....
Currently, I have a query to get this done which looks as below. Since I have more than 8K employees with each having multiple Designation and Salary entries, my query is taking forever.
selecte.EmployeeID, e.EmployeeName, e.EmployeeDOB,
(select top 1 Designation from @EmployeeDesignation ed where ed.EmployeeID = e.EmployeeID Order By EffectiveDate) EmployeeStartingDesignation,
(select top 1 Designation from @EmployeeDesignation ed where ed.EmployeeID = e.EmployeeID Order By EffectiveDate Desc) EmployeeCurrentDesignation,
[Code] ....
I am querying a table log file in an attempt to get the most recent status of an item. The items can have a variety of different statuses:
(A = Active, R = Repeat, L = liquidation......)
Here is a sample of the data I am trying to report off of:
CREATE TABLE [dbo].[item_status](
[item_number] [varchar](20) NULL,
[sku] [varchar](100) NULL,
[Field_Name] [varchar](50) NULL,
[Old_Value] [varchar](150) NULL,
[New_Value] [varchar](150) NULL,
[Change_Date] [smalldatetime] NULL
) ON [PRIMARY]
[code]...
I have tried join to the same table - but I am still unable to get it to work.
Hello-I'm fairly new to writing SQL statements and would greatly appreciatesome help on this one.I'm working on a project for a non-profit that I volunteer for. Partof the database tracks membership using tables like this:PersonInfo-------------------PersonID (primary key)FirstNameLastNameetc..PeopleMemberships-------------------PPLMembershipIP (primary key)PersonIDMembershipTypeIDFeePaidMembershipTypes--------------------MembershipTypeID (primary key)MembershipYearStandardFeeMembershipDescription (varchar)Just because a person is in PersonInfo, doesn't mean they have anythingin PeopleMemberships (they can be in the databse for other reasons andnot have or have ever had a membership).Membership fees vary by year and type of membership and they want toretain a history of a person's memberships.What I'm looking to do here is write a query (a view in SQL Server)that will return the following InfoPersonID, MostRecentMembershipYear, FeePaidForThatMembership,DescriptionOfThatMembershipI'm thinking that I'd use max(MembershipYear), but that requires groupby for the other columns, so I'm getting all of the people'smemberships returned.I'm pretty sure this can be best done with a subquery, but I'm not surehow.Can someone please point me in the right direction or provide a samplethat I can learn from?Kindly,Ken
View 4 Replies View RelatedHi, all experts here,
Thanks for your kind attention.
I want to use time series algorithm to mine data from my case table and nested table. Case table is Date table, while nested table is the fact table. E.g, I want to predict the monthly sales amount for different region (I have region table related to the fact table), how can I achieve this?
Thanks a lot and I hope it is clear for your help and I am looking forward to hearing from you shortly.
With best regards,
Yours sincerely,
Hi, all experts here,
Thank you very much for your kind attention.
I am confused on key column of case table and key time column of nested table by using Time Series algorithm.
In my case, the case table structure is as below:
Territory key text (the ID is actually dimrisk_key, in this case, I use the name column binding to combine the Territory column of case table Dimrisks),
While the nested table structure is as below:
Cal_month key time (in this case, actually the ID is dimdate_key, again, I used name column bining property to bind the Cal_month to the ID)
So my question is, as the key column of case table has been set to be Territory, as a result, does the model training still cover all the cases (rows) based on the ID of the table?
Also, in the nested table, as the key time column has been set to Cal_month rather than Dimdate_key of the nested table, as a result, would the single series based on the cal_month?
Hope it is clear for your advices and help.
And I am looking forward to hearing from you shortly.
With best regards,
Yours sincerely,
I need to take a temporary table that has various times stored in a text field (4:30 pm, 11:00 am, 5:30 pm, etc.), convert it to miltary time then cast it as an integer with an update statement kind of like:
Update myTable set MovieTime = REPLACE(CONVERT(CHAR(5),GETDATE(),108), ':', '')
how this can be done while my temp table is in session?
Is there a method to convert "Select * From Table" to "Select field1,field2,...fieldn From Table" ?
Thanks
I need to write a select statement that take the upper table and select the lower table.
View 3 Replies View RelatedHello,
I hope someone can answer this, I'm not even sure where to start looking for documentation on this. The SQL query I'm referencing is included at the bottom of this post.
I have a query with 3 select statements joined together like tables. It works great, except for the fact that I need to declare a variable and make it a table within two of those 3. The example is below. You'll see that I have three select statements made into tables A, B, and C, and that table A has a variable @years, which is a table.
This works when I just run table A by itself, but when I execute the entire query, I get an error about the "declare" keyword, and then some other errors near the word "as" and the ")" character. These are some of those errors that I find pretty meaningless that just mean I've really thrown something off.
So, am I not allowed to declare a variable within these SELECT tables that I'm creating and joining?
Thanks in advance,
Andy
Select * from
(
declare @years table (years int);
insert into @years
select
CASE
WHEN month(getdate()) in (1) THEN year(getdate())-1
WHEN month(getdate()) in (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) THEN year(getdate())
END
select
u.fullname
, sum(tx.Dm_Time) LastMonthBillhours
, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) lasmosbillingpercentage
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
Month(tx.Dm_Date) = Month(getdate())-1
and
year(dm_date) = (select years from @years)
and tx.dm_billable = 1
group by u.fullname
) as A
left outer join
(select
u.FullName
, sum(tx.Dm_Time) Billhours
, ((sum(tx.Dm_Time))
/
((day(getdate()) * ((5.0)/(7.0))) * 8)) perc
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
tx.Dm_Billable = '1'
and
month(tx.Dm_Date) = month(GetDate())
and
year(tx.Dm_Date) = year(GetDate())
group by u.fullname) as B
on
A.Fullname = B.Fullname
Left Outer Join
(
select
u.fullname
, sum(tx.Dm_Time) TwomosagoBillhours
, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) twomosagobillingpercentage
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
Month(tx.Dm_Date) = Month(getdate())-2
group by u.fullname
) as C
on
A.Fullname = C.Fullname
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