Using Date From Prior Record

Apr 8, 2004

Hello gurus, I have a table of data containing stop and start times of equipment, such as this:

StartTime....................EndTime
12/01/01 15:44...........12/01/01 18:44
12/02/01 3:44............12/02/01 14:44
12/02/01 15:22...........12/02/01 15:33

etc.

With this, I can build a query that subtracts the start and end dates to give me the total differences between each record... But, how do I write a query that can count the elapsed time between the current record's "start time" and the prior record's "end time" to give me the elapsed time between each record?

View 14 Replies


ADVERTISEMENT

Transact SQL :: Return Date Which Is 15 Working Days Prior To Given Future Date

Oct 28, 2015

i have written a sql function which returns only number of working days (excludes holidays and Weekends)  between given StartDate  and EndDate.
                 
USE [XXX]
GO
/****** Object:  UserDefinedFunction [dbo].[CalculateNumberOFWorkDays]    Script Date: 10/28/2015 10:20:25 AM ******/
SET ANSI_NULLS ON
GO

[code]...

I need  a function or stored procedure which will return the date which is 15 working days (should exclude holidays and Weekends) prior to the given future Date? the future date should be passed as a parameter to this function or stored procedure to return the date.  Example scenario:  If i give date as  12/01/2015, my function or stored procedure should return the date which is 15 working days (should exclude holidays and Weekends) prior to the given date i.e 12/01/2015...In my application i have a table  tblMasHolidayList where all the 2015 year holidays dates and info are stored.

View 18 Replies View Related

Date Returned Is Prior To Date In Database

Jan 31, 2007

would someone be able to shed some light as to why the date returned to my windows application does not match the date that is actually stored in the database. the products_soldDate field is assigned 1/1/1900 at the product records creation time and is updated when it's sold.  when the windows application received that data and I check for it I get the following value 12/31/1899 11:00:00 PManyone has a clue as to what is happening here. It worked fine when I hosted it on a MS SQL 2000 database I just recently moved the data over to a SQL 2005 database   

View 2 Replies View Related

Power Pivot :: Prior Month To Date Calculation With DAX

Feb 9, 2013

I’m using DAX to calculate the prior MTD count of a specific column. My data ends on 2/8/2013 and that day's PriorMTD is incorrectly corresponding to 1/31/2013. Whereas, the previous 7 days in February correctly match their corresponding January dates..Below is an image of my pivot table and I have outlined the values in red that are in question.Below are my DAX formulas used each column visible in my image:

Distinct Count of Events:=DISTINCTCOUNT([EventID])CurrentMTD:=CALCULATE([Distinct Count of Events], DATESMTD(Events[EventDate]), ALL (dimDate) )PriorMTD:=CALCULATE([Distinct Count of Events], DATEADD(DATESMTD(Events[EventDate]), -1, MONTH), all(dimDate)) ParallelMonth:= CALCULATE ([Distinct Count of Events], ParallelPeriod(Events[EventDate], -1, MONTH), ALL(dimDate))

View 5 Replies View Related

Transact SQL :: Date Calculation - 7 Days Prior To Specified Day Of Last Week

Sep 8, 2015

I need to run a select on Mondays to pull data for 7 days prior to the Thursday of last week; i.e. Friday - Thursday inclusive.  I'm sure this is simple, but I work with dates so infrequently that I need a refressher. 

View 7 Replies View Related

Power Pivot :: Calculating Count Prior To Header Date

Nov 13, 2015

I have two tables - one with dates at the end of each month (10/31, 11/30, 12/31, et al) that's linked to a data table containing client signups. While I can get the count per month in a pivot table, I'm trying to calculate the beginning count of each month.

How do I use calculate or something similar to sum the count of records created minus count of records closed prior to the beginning of the period?

The pivot looks like this right now and works by month:

beginning active records ?????
count of records created 100
count of records deleted 50
net records created 50
running balance in net records created 100 (theoretically the next month's beginning balance, but can't figure out how to reference or calculate)

View 3 Replies View Related

Add Date To Record In SQL Server Each Time Record Is Added

Mar 1, 2006

Hi
 
Can anyone advise me as to how I can add the date and time to 2 columns in the sql server database for each record that is added. I'd prefer not to use the webform. Can sql server add the date automatically to the row?
thanks

View 6 Replies View Related

Get End Date From Next Record

Mar 6, 2008

I have the following table and have included some sample data. Basically, the records are grouped by opid and it shows ownership changes for the opids. It only shows the owner change date which is the date that the owner was assigned to the opid. What I want to figure out is the date that the owner ceased to be assigned to the opid as well. If it is still the current owner then the end date should just be the current date.




Code Snippet
CREATE TABLE #op_owner_history(
[opshid] [int] IDENTITY(1,1) NOT NULL,
[opid] [int] NOT NULL,
[role] [varchar](30) NOT NULL,
[rep] [char](20) NOT NULL,
[percentage] [int] NOT NULL,
[ownerchg_date] [datetime] NOT NULL,
[ownerchg_rep] [char](20) NOT NULL,
CONSTRAINT [PK_#op_oppshare_history] PRIMARY KEY CLUSTERED
(
[opshid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
SET IDENTITY_INSERT #op_owner_history ON
GO
INSERT into #op_owner_history (opshid,opid, role,rep,percentage,ownerchg_date,ownerchg_rep) VALUES (4653, 23795, 'Sales','KOHLENBERG', 100, '9/26/2006 3:57:22 PM', 'Kohlenberg')
INSERT into #op_owner_history (opshid,opid, role,rep,percentage,ownerchg_date,ownerchg_rep) VALUES (8576, 23795, 'Sales', 'Haber', 100, '1/3/2007 10:40:00 AM', 'ADMIN')
INSERT into #op_owner_history (opshid,opid, role,rep,percentage,ownerchg_date,ownerchg_rep) VALUES (9363, 23795, 'Sales', 'KOHLENBERG', 100, '1/4/2007 12:40:14 PM', 'Kohlenberg')
INSERT into #op_owner_history (opshid,opid, role,rep,percentage,ownerchg_date,ownerchg_rep) VALUES (2919, 24623, 'Sales', 'COLEMAN', 100, '9/1/2006 12:02:03 PM', 'Dunkel')
INSERT into #op_owner_history (opshid,opid, role,rep,percentage,ownerchg_date,ownerchg_rep) VALUES (15475, 24623, 'Sales', 'COLEMAN', 100, '5/8/2007 8:10:51 AM', 'Woodruff')
INSERT into #op_owner_history (opshid,opid, role,rep,percentage,ownerchg_date,ownerchg_rep) VALUES (8580, 24623, 'Sales', 'Woodruff', 100, '1/3/2007 10:40:00 AM', 'ADMIN')
INSERT into #op_owner_history (opshid,opid, role,rep,percentage,ownerchg_date,ownerchg_rep) VALUES (4310, 5548, 'Sales', 'KOHLENBERG', 100, '9/19/2006 1:53:47 PM', 'KOHLENBERG')
INSERT into #op_owner_history (opshid,opid, role,rep,percentage,ownerchg_date,ownerchg_rep) VALUES (10649, 5548, 'Sales', 'HABER', 100, '2/7/2007 3:50:40 PM', 'Kohlenberg')




Based on this sample data, here is what I want to see in the results: (My apologies, I couldn't get it formated very well to make it more readable.)

opshid opid role rep percentage ownerchg_date End Date ownerchg_rep
4310 5548 Sales KOHLENBERG 100 9/19/2006 1:53:47 PM 2/7/2007 3:50:40 PM KOHLENBERG
10649 5548 Sales HABER 100 2/7/2007 3:50:40 PM GETDATE() Kohlenberg
4653 23795 Sales KOHLENBERG 100 9/26/2006 3:57:22 PM 1/3/2007 10:40:00 AM Kohlenberg
8576 23795 Sales Haber 100 1/3/2007 10:40:00 AM 1/4/2007 12:40:14 PM ADMIN
9363 23795 Sales KOHLENBERG 100 1/4/2007 12:40:14 PM GETDATE() Kohlenberg
2919 24623 Sales COLEMAN 100 9/1/2006 12:02:03 PM 1/3/2007 10:40:00 AM Dunkel
8580 24623 Sales Woodruff 100 1/3/2007 10:40:00 AM 5/8/2007 8:10:51 AM ADMIN
15475 24623 Sales COLEMAN 100 5/8/2007 8:10:51 AM GETDATE() Woodruff

View 4 Replies View Related

Get Record With Latest Date

Jun 4, 2007

 if there are 2 records with different date  how to write query for ----> get record with latest date 

View 4 Replies View Related

Select Date Record

Feb 18, 2006

    how to select record from the table where the data between a  range. example  between 2/16/2005 and 12/16/2005. the data record in the table formated like this ( 2/16/2005 11:44:38 PM). help me with some sql code, thanks

View 2 Replies View Related

How To Get Latest Date Record

Apr 10, 2014

I want to get latest updated date on each transid and only for status =approved .

-------------------
out put would be
------------
idtransid date status
31013/1/2014 approved
61031/2/2014 approved

table
-----------
idtransid date status
11011/1/2014 approved
21012/1/2014 close
31013/1/2014 approved
41021/2/2014 approved
51022/2/2014 close
61031/2/2014 approved

View 1 Replies View Related

Record Update Date

Nov 2, 2005

How to update the Last Update Date of a modified record? I want to put this in table trigger .

Or any setting can be used?

Please help~~~~

View 20 Replies View Related

Only Need One Record Per Newest Date.

Feb 7, 2008

Hey all,

I just discovered this cool forum. I am fairly new to T-SQL, so please bear with me.

Here is my problem. I am trying to return 1 record for each distinct MachineName, based on the most current CreateTime.

I have tried a bunch of different things, but the follwing query seems to get me close, except for all of the records, per MachineName, that aren't the newest CreateTime. Can anyone offer a suggestion I may be missing?

Here is my query:

SELECT
rcv.Name AS MachineName, r.CreateTime, rpv.Path + rpv.Name AS ResourcePool

FROM
dbo.Result_View AS r WITH (NOLOCK) INNER JOIN
dbo.ResourceConfiguration_View AS rcv WITH (NOLOCK) ON r.ResourceConfigurationId = rcv.Id INNER JOIN
dbo.Resource_View AS rv WITH (NOLOCK) ON rcv.ResourceId = rv.Id INNER JOIN
dbo.ResourcePool_View AS rpv WITH (NOLOCK) ON rv.ResourcePoolId = rpv.Id

WHERE
(rpv.Name NOT LIKE 'Archive')
AND
(r.CreateTime > '2/1/2008')
AND
(r.CreateTime = (SELECT TOP (1) r.CreateTime FROM dbo.Result_View))

ORDER BY MachineName


I have added the '2/1/2008' filter, so as not to hammer the DB too hard. It goes back for some time.

Thanks in advance,

Allen

View 3 Replies View Related

Transact SQL :: Get One Record By ID Which Has Max Date

May 4, 2015

I have records like this

id      address1          date
1      xyz                 01/01/2013
1       abd                01/01/2014
2       dfg                 01/03/2015

Now what I want is just 1 record by id which has max date,if there is 2 records for same id, if there is 1 record then it should come.

So here result should be

1       abd                01/01/2014
2       dfg                 01/03/2015

View 12 Replies View Related

Search Record Base On Date

Oct 27, 2000

Hi,

The following is a VB code which I use it to check the existence of the record before I insert a new record
into my SQL 7.0 database. When I run the vb code, I got an error which is
"-2147217913 [Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value." I couldn't figure out what's wrong with my code especially the WHERE clause.
Please help me look into this problem. TQ.

Private Sub AddDate()
Dim rstDate As ADODB.Recordset
Dim myDate As Date
Dim Criteria As String

myFileDate=#27-10-2000 16:00#
myDate = Format(myFileDate, "mm-dd-yyyy")

Criteria = "SELECT Date " _
& "FROM DateGen " _
& "WHERE Date = '" & myDate & "'"

Set rstDate = New ADODB.Recordset

With rstDate
.ActiveConnection = cn
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Open Criteria
End With

If rstDate.RecordCount > 0 Then
RecordExist = True
Else
RecordExist = False
rstDate.AddNew
rstDate("Date") = Format(myFileDate, "dd-mm-yyyy")
rstDate.Update
End If
rstDate.Close
End Sub

Regards,
May

View 1 Replies View Related

Automatically Change Record By Date

Jul 1, 2005

How can I automatically change records by a specific date specified inside the record. An example would be the way ebay sales work. How does ebay have the status of an item change to closed, at the time inside the item record of the database.

I know i could use triggers or something to check the current date against the enddate everytime the record is accessed, but is there a more efficient method?

View 2 Replies View Related

Get Inbeetween Record In Date Range

Apr 7, 2006

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 ...

View 2 Replies View Related

Select Record Based On Max Date

Sep 12, 2012

I have the following table:

Occ_Num Feature_Num Trans_Date Peril_Desc
123 1 1-2-2012 Water
123 1 1-11-2012 Ice
123 2 1-2-2012 Other
123 2 1-13-2012 Other
123 2 1-19-2012 Wind

I want to select each Occ_Num, Feature_NUM, Trans_Date, and PERIL_Desc but with only the Peril that was part of the max trans_date.

So i would want the following from above:

Occ_Num Feature_Num Trans_Date Peril_Desc
123 1 1-11-2012 Ice
123 2 1-19-2012 Wind

I'm having trouble with the syntax need to accomplish this.

View 2 Replies View Related

Unable To Get Latest Date Record?

Apr 10, 2014

i want to get latest updated date on each transid and only for status =approved .

-------------------
out put would be
------------
idtransid date status
31013/1/2014 approved
61031/2/2014 approved
table
-----------

idtransid date status
11011/1/2014 approved
21012/1/2014 close
31013/1/2014 approved
41021/2/2014 approved
51022/2/2014 close
61031/2/2014 approved

View 2 Replies View Related

Max Record Count And Date Range

Aug 31, 2006

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

View 1 Replies View Related

Select Record With Oldest Date

Mar 4, 2008

Hi,

I have a base query that will return the ID, StartDate and Code for all IDs. I SELECT only for Codes 5 and 9. For most of the IDs I return a record for both a Code 5 and Code 9. They will have different dates however. How could I select from this base query one record for each ID with the oldest date? The items in yellow are the ones that I would want to return to a report in SSRS. Is there a way to put this data in a temp table and read through it to compare IDs and grab the one with the older date?










ID
StartDate
Code

100
1/2/2000
5

100
4/6/2004
9

205
3/13/2002
5

205
9/10/2002
9

300
10/10/1999
9

407
2/12/2005
5

407
7/17/2007
9


Thanks,

rb

View 10 Replies View Related

Transact SQL :: Previous Log Date For Each Record

Sep 22, 2015

I have one table :

file _target

which has below records..

file_target_ID is identity column which will repeat per files_ID

Now, i just shown Target log  for file_ID 77796 see the last Target Date i want another column which returns a previous log Target date for each files beside Target date  column

Like this ..

Target Date                     New Column
2015-09-09 00:00:00.000   2015-09-16 00:00:00.000
2015-09-16 00:00:00.000   2015-09-25 00:00:00.000
2015-09-25 00:00:00.000   New Target date after 25-9-2015

how to do this in SQL Server ??

using Cross Apply ?? Row_Number() ??

View 3 Replies View Related

Only Max Date Record Should Be Shown For Every Student

May 25, 2015

I have a table which shows data in below format

Now I need that only the max date record should be shown for every student as shown below...

View 8 Replies View Related

Select Null Value Date Fields Record

Sep 28, 1999

Hi:

I used this statement, select * from table1 where date1 = null, in SQL Query window and got a few records back. Now, I used the same statement in my VB 5 code and no record is found. How do I select all the records in table1 which do not have values in field date1? Thanks for the help.

-Nicole-

View 2 Replies View Related

SQL Server 2008 :: Add Todays Date To One New Record Only

Apr 9, 2015

I'm wanting a SQL Formula to insert into my Accounting Systems which uses a SQL Database which, when I add a new product code I want a custom field to update with today's date.

I've tried to use GetDate () however, what this does is to update all records with this date (each record has this custom field assign to it). I only want the new record to show todays date so I know when the product was created in the database. Should I be looking at creating this with a Function?

View 3 Replies View Related

Query To Pull Lastest Date Record

Apr 16, 2008

How do I pull only the latest shipdate record from a multiple record key?

I tried the following, but it doesn't pull only the lastest date record:

Select catnum, Max(shipdate) as shipdate from table_a group by catnum, shipdate

Thanks in advance.

View 7 Replies View Related

Trigger To Set Creation Date Of A New Record In Database?

Feb 28, 2014

I need a trigger to set the creation date of a new record in the database... I tried the following, but it changed all records, not just the new one...

CREATE TRIGGER trgCreationDate
ON [dbo].tabCustomerLookup
FOR INSERT
AS
BEGIN
UPDATE tabCustomerLookup
SET CreationDate = getdate()
END

View 3 Replies View Related

Find The Last Record By Date In A Sub Group Of Records.

Oct 1, 2007

Looking to see if thier is a better way to find the last record entered in a group of records.

What I'm doing now is finding the max for the secound column and then doing a sub query to find the max of the third column having the second columns equal.

Table example using simplied data.






PolId

CoveragId

EffDate

Status

Limit1


2

1

9/7/2007

a

10000


2

2

9/7/2007

a

150000


2

2

10/1/2007

a

200000


3

1

9/7/2007

a

10000

The parent program addes a row every time the data is changed. To make things worst; the records arn't always in sqenal order like the above table and some time edits the row instead.

The current query returns a single value. from a single table.

Current code used in the select protion on a larger query. bpi = basicpolicyInformation.

( Select c1.limit1
From AFW_Coverage as c1
Where c1.PolId=bpi.PolId
and c1.CoverageId = (select max(CoverageId) as CoverageId
From AFW_Coverage as c
where c.PolId = c1.PolId
and c.CoverageCode = 'Dwelling'
and status <> 'D'
)
and c1.effDate = (select max(Effdate) as Effdate
From AFW_Coverage as c
where c.PolId = c1.PolId
and c.CoverageID = c1.CoverageId
)


Explain the current code. It uses the two sub queries to find the correct record ID that has the data needed.

View 16 Replies View Related

Transact SQL :: Select First And Last Record For Certain Date Range

Jun 16, 2015

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.

View 7 Replies View Related

Create A Record Based On Date Range

Mar 25, 2008

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

View 8 Replies View Related

How Do I Select The Maximum Date For Each Record Having Duplicate ID

Dec 19, 2007



Hi All,
Here is my story, how to change a column called Flag_Status based on the maximum Updated date. i.e. i want to make Flag_Status be 1 for the records which have maximum Updated_date (current record) and the rest to make it 0. for example accountID 1 has three records updated, but only one is current the rest are historical, thus i want the history record to be Falg_status 0 and the current record be 1. Note that Inserted_Date and Updated_Date are created using SSIS Derived column During loading the source table, it helps me when each record is inserted into the Data warehouse.

Here is My source table Name: Source_table,


CREATE TABLE dbo.Source_table

(

AccountID INT PRIMARY KEY,

Price int,

Address varchar(30),

added DATETIME DEFAULT GETDATE(),

edited DATETIME DEFAULT GETDATE(),

Flag_Status Int Default 1

)

GO

Here is the Fact_table


CREATE TABLE dbo.Fact_table

(

Fact_table_Key Int Identity (1, 1) NOT NULL,

AccountID INT,

Price INT,

Address varchar(30),

added DATETIME DEFAULT GETDATE(),

edited DATETIME DEFAULT GETDATE(),

editor VARCHAR(64),

Flag_Status Int Default 1,

Inserted_Date DATETIME DEFAULT GETDATE(),

Updated_Date DATETIME DEFAULT GETDATE()

)

GO

Source_table:

AccountID Price Address added edited Flag_Status
---------------- --------- ------------- ----------- ------------- ------------------
1 10 xyz 01-2006 01-2006 1
2 14 abc 02-2006 02-2006 1
3 13 mno 03-2006 03-2006 1

Here is the fact table, table name= Fact_table



Fact_table_Key AccountID Price Address added edited Flag_Status Inserted_Date Updated_Date
-------------------- ------------- --------- ------------ ------- --------- ---------------- ------------------ -------------------
1 1 10 xyz 01-2006 01-2006 1 05-2006 NULL
2 2 14 abc 02-2006 02-2006 1 05-2006 NULL
3 3 13 mno 03-2006 03-2006 1 05-2006 NULL
4 1 17 ght 01-2006 06-2006 1 NULL 08-2006
5 2 18 dmc 02-2006 07-2006 1 NULL 08-2006
6 3 20 kmc 03-2006 09-2006 1 NULL 10-2006
7 1 19 xyz 01-2006 11-2006 1 NULL 12-2006
8 2 19 klm 02-2006 01-2007 1 NULL 02-2007
9 3 21 pqr 03-2006 03-2007 1 NULL 04-2007

Here is what i am thinking: But it gives me Wrong Flag_Status.


UPDATE Fact_table

SET Flag_Status =


CASE


WHEN (SELECT Max(Updated_Date) From Fact_table

WHERE AccountID IN (SELECT AccountID FROM Fact_table

Group By AccountID Having Count(AccountID)>1)) >

(SELECT Max(edited) From Source_table

WHERE Flag_Status = 1)

THEN 1


WHEN (SELECT AccountID From Source_table

Group By AccountID

Having Count(AccountID) =1) =

(SELECT AccountID From Fact_table

WHERE Updated_Date IS NULL)

THEN 1

ELSE 0

END

View 12 Replies View Related

Find Nearest Date Record Without Cross Apply

Oct 13, 2012

Table :

ChangeID ChangeDate EquipmentID ModuleID EquipStatus
1 12/9/08 230 1789 Normal
2 13/9/08 450 1245 Normal
3 17/9/08 230 1789 Open
4 21/9/08 230 1899 Open
5 21/9/08 450 1674 Normal
6 22/9/08 450 2364 Normal

Given a date, what module was each equipment item in on that date?How do I get the date of the nearest previous event from a list like this? I got a query from one of the post in this Forum only using Cross Apply to find the nearest record from the above table based on Date i.e.

SELECT outerT.*
FROM your_table AS outerT
CROSS APPLY
(
SELECT TOP 1
equipment_id
, change_date
FROM your_table AS innerT
WHERE innerT.change_date <= @point_in_time
AND innerT.equipment_id = outerT.equipment_id
ORDER BY change_date DESC
) AS applicable_records
WHERE applicable_records.change_date = outerT.change_date

The problem is I need to get this query without using Cross Apply as i need to use the same for the LINQ which doesn't support Cross Apply.

View 1 Replies View Related

Find Most Recent Record In Table According To Date Field

Sep 19, 2015

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] .....

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved