Find Next Entries Value (as Sorted By Date)

Jul 29, 2015

In the database I am querying, there is a field called "group". I can find out when "group" changes with a time field that was logged whenever group changes. I am trying to find what the value was changed into.

Code:
SELECT
Assignment_Log.DBID,
Assignment_Log.Assigned_Group,
Assignment_Log.Submit_Date
FROM Assignment_Log

This will tell me:

The unique ID of the database
The group it was assigned FROM
The time the assignment took place

I need to find the assignment TO

so what I need to do for the last column is something along the lines of...

Find the next record after the current record by looking at the next Submit_Date and view the Assigned_Group.

If no "next record" can be found, leave the cell empty.

is this possible?

View 2 Replies


ADVERTISEMENT

Find All Entries That Have Any Of The Modes

Jan 6, 2015

I have a table where each unique entry can have up to 12 'modes', each mode with a start and stop date. A mode is a certain type of characteristic attributed to an entry.

There are 60 different modes and a given mode number can appear in any of the 12 mode fields. I created a very simplistic example table below.

Criteria:
I need to find all entries that have any of the modes = 1

AND for any of the other mode fields = 2 or 3, all modes = 2 or 3 must have a mode_enddate that is NOT NULL.

example:
select * from #temp_work
where (mode1 = 1 or mode2 =1 or mode3 = 1 ...)

And if any of the other mode1-12 fields = 2 or 3 then all mode 2 or 3 mode_enddate must be NOT NULL. I have tried a few different ways to try and get this data, but none of them have been very good.

Create temporary table
create table #temp_work (
id int NULL,
mode1 varchar(20) NULL,
mode_startdate_1 date NULL,
mode_enddate_1 date NULL,

[Code ....

From this limited example, the only valid entry should be ID 1 as the mode = 3 has a NOT NULL mode_enddate.

ID's 2 and 3 both have mode_enddate_X that are NULL, so should not be included.

View 4 Replies View Related

Using Count To Find # Of Distinct Entries

Apr 17, 2008

I need a query to return the number of distinct call_num(s) between two timestamps.

For example search between 2008-04-17 05:00:00.000 and
2008-04-19 05:00:00.000

Calls
----------
id_tag(unique id) | status | call_num | entry_id(timestamp)

1 HOLD 0123456789 2008-04-17 05:07:00.080
2 ONSCENE 012345679 2008-04-17 05:10:00.012
3 ENROUTE 321654987 2008-04-19 04:00:00.000

so the sample answer would be : 2

View 2 Replies View Related

Transact SQL :: Find All Duplicate Entries In Table

Jun 11, 2015

Someone ran an update statement multiple times so their are multiple entries in the table.  What is the quickest way to track down the multiple entries?  I would only want to see where timein and timeoff exist in the table multiple times for the same id.  So this would be a duplicate

EntryID -- ID  -- timein -- timeoff
1487   11     2015-05-05 16:33:23   2015-05-05 18:45:26
1623   11     2015-05-05 16:33:23   2015-05-05 18:45:26

View 7 Replies View Related

SQL Server 2014 :: Find Duplicate Rows Like Same Entries More Than One Time?

Sep 11, 2014

i have a table like below

create table staff_attendance
(
attendance_id int,
attendace_date datetime,
staff_id int,
working_year int,
hours int
)

values like

1 2014-06-30 00:00:00.0ST10121
2 2014-06-30 00:00:00.0ST10122
3 2014-06-30 00:00:00.0ST10122 ----same entry like previous one
4 2014-07-01 00:00:00.0ST10121
5 2014-07-01 00:00:00.0ST10122
6 2014-07-02 00:00:00.0ST10121
7 2014-07-02 00:00:00.0ST10122
8 2014-06-30 00:00:00.0ST10221
9 2014-06-30 00:00:00.0ST10222
10 2014-07-01 00:00:00.0ST1022 1
11 2014-07-01 00:00:00.0ST102 22
12 2014-07-02 00:00:00.0ST102 21
13 2014-07-02 00:00:00.0ST102 22

I Need to find the duplicate rows like same entries which is having more than 1 rows.... how do i find?

View 3 Replies View Related

SQL Server 2014 :: Find Multiple Entries In Two Fields One Table?

Sep 12, 2014

I am trying to find books which have the same title and publisher name as at least two other books and need to also show the book ref (ISBN number). I have the below script so far:

SELECT isbn, title, publishername
FROM book
WHERE title in (SELECT title
FROM book
GROUP BY title
HAVING count(title)>2 or count(publishername)>2)
order by title;

This is a snap shot of the output:

ISBN Title Publishername
0-1311804-3-6 C Prentice Hall
* 0-0788132-1-2 C OSBORNE MCGRAW-HILL
* 0-0788153-8-X C OSBORNE MCGRAW-HILL
* 0-9435183-3-4 C Database Development MIS
* 1-5582806-2-6 C Database Development MIS

[Code] ....

What I should be seeing is only the ones I have put an * next to. What am I missing from the scrip?

View 3 Replies View Related

How To Retrieve Only Entries With Date = NULL From The DB

Jan 9, 2008

I am trying to write a Stored proc to retrieve only records from my table tat do not have a DateTime value ie DateTime is NULL and for some reason simply adding a DateRemoved = NULL does not work. Can anyone please help me with solution to this issue please??? Thanks in Advance 

View 3 Replies View Related

How To Fill Up Lacking Date Entries

Feb 10, 2004

Hi everyone,

the last years I solved tons of "problems" with plain SQL, but
I think this one will require some programming...
a stored procedure, a vb, I don't know ...

Here is the point:
I'm trying to build a simple OLAP-cube out of personal data.
I get table_person with number, name, birthdate, and so on.
I get table_costcenter with costcenternumber and description and
I get a table_person_costcenter with the following structure:

person-number, costcenter, FROM-Date (and some other data, e.g.
workinghours per week(this and headcount are the measures in the cube))

so...
Person 1 is on costcenter CC01 FROM-Date: 01.01.1998
person changed next year
1 CC02 FROM-Date: 01.01.1999
...
2 CC02 01.05.1999
2 CC03 01.01.2000
and so on....

I can think of loops reading the hole table (roundabout 2000 records),
than the one, two or more records with the person-number 1 and so on
and fill in the missing Dates (every first of a month) BETWEEN the
first FROM-Date to the next FROM-Date and so on till the last record with personnumber X which will lead to CURRENT DATE....
but I never programmed in MS SQL. :-(

So every code examples are heartly wellcome...

many thanks in advance

Michael
PS:
The result-table should look like this
person costcenter date
1 CC01 01.01.1998 january
1 CC01 01.02.1998 feb
1 CC01 01.03.1998 march
..
1 CC02 01.01.1999
1 CC02 01.02.1998
.. when no other connections "1" to costcenter ...
1 CC02 01.02.2004 (current month)

View 2 Replies View Related

List All Entries Made After Specified Date

Feb 17, 2014

Is it possible to find out when a value was last changed/updated? I want to list all entries made after a specified date.

View 6 Replies View Related

Procedure To Select Entries From Yesterdays Date

Mar 13, 2012

I currently have a stored procedure that selects sales entries from a table and inserts them into a temp table. To execute this procedure you must enter the store number and the dates required.

What I am looking to do is run this procedure daily for one store daily to load the previous days sales into the temp table.

I have tried using the following

DECLARE @dt DATETIME
SET @dt = CAST(CAST(MONTH(getdate())as varchar) + '-' +
Cast(DAY(GetDate()-1)as varchar)+ '-' +
Cast(YEAR(GetDate())as varchar)as DateTime)

set @sql = @sql + ' and hd.TXHD_START_DATE between ''''' + @dt + ''''' and ''''' + @dt + '23595999+9999'''''

But i am getting an error

Syntax error converting datetime from character string.

View 4 Replies View Related

Transact SQL :: Find Latest Start Date After A Gap In Date Column

Apr 19, 2015

My requirement is to get the earliest start date after a gap in a date column.My date field will be like this.

Table Name-XXX
StartDate(Column Name)
2014/10/01
2014/11/01
2014/12/01

[code]...

 In this scenario i need the latest start date after the gap ie. 2015/09/01 .If there is no gap in the date column i need 2014/10/01

View 10 Replies View Related

Transact SQL :: Find Latest Start Date After A Gap In Date Field For Each ID

Apr 23, 2015

My requirement is to get the latest start date after a gap in a month for each id and if there is no gap for that particular id minimum date for that id should be taken….Given below the scenario

ID          StartDate
1            2014-01-01
1            2014-02-01
1            2014-05-01-------After Gap Restarted
1            2014-06-01
1            2014-09-01---------After last gap restarted
1            2014-10-01
1            2014-11-01
2            2014-01-01
2           2014-02-01
2            2014-03-01
2            2014-04-01
2            2014-05-01
2            2014-06-01
2            2014-07-01

For Id 1 the start date after the latest gap is  2014-10-01 and for id=2 there is no gap so i need the minimum date  2014-01-01

My Expected Output
id             Startdate
1             2014-10-01
2             2014-01-01

View 4 Replies View Related

Searching For Entries In Subsequent Log Entries?

Dec 2, 2013

If I wanted to search for Jobs as a particular status (e.g. 0130) and wanted to keep the jobs at this status until it has reached 0500, 0125, or 0900 in it's subsequent status log entry, how can I write the SQL for it to achieve it?

I have the following SQL which searches for the Jobs at 0130, but don't know how to develop it further to search on the requirement above.

------ SQL -------
SELECT
job.job_number,
(SELECT MAX(jsl.job_log_number)
FROM job_status_log jsl
WHERE
job.job_number = jsl.job_number AND
jsl.status_code = '0130') as Last_Early_Warning_Status_Entry

[code].....

In the job_status_log table above, there is a job_log_number field which increments by 1 when there is a new status log entry.

View 1 Replies View Related

Find The Newest Date Of 2 Date Fields

Aug 20, 2007

I've 2 date fields clidlp,clidlc in my data base table. How do I find the newest dates between the fields? Thanks for your help!

View 1 Replies View Related

Find The Max And Min Date

Feb 1, 2001

I'm doing a query using SQL Server. How can I get the max and min date from a database? I can't find any function related to it. Can you help me please ....

Thanks in advance
Andy

View 1 Replies View Related

How To Find Date Of Month

Apr 20, 2006

Hi
Problem:
one table "tbl_SalesReporting" in this table salesEr every day submit his information.
i need which day salesEr not submit his information. this is check up to current date.
E.G:
salesEr submit information start this date "1/04/2006" to "17/04/2006" and
currentdate: 20/04/2006
i need 18/04/2006,19/04/2006 this date Using SQL queries........
Please help me........
 

View 9 Replies View Related

Find Value Based On Max(date)

Aug 3, 2007

I know I have done this before, but cannot for the life of me remember how.

I am trying to determine return the current (last added) deduction amount for each deduction type for each employee

Sample Table:
employee|Deduction_type|Date_entered|Amount
1|MED|1/1/2007|50
1|DEPC|1/1/2007|100
1|MED|1/8/2007|50
1|DEPC|1/8/2007|100
1|MED|1/15/2007|150
2|MED|1/1/2007|35
2|DEPC|1/1/2007|100
2|MED|1/8/2007|35
2|DEPC|1/8/2007|75
2|MED|1/15/2007|35

Any suggestions?

View 1 Replies View Related

How To Find A Lower Date

Jan 4, 2007

I have following problem:table includes times for startup and end of operation as datetime fieldrelated to daily shift operations:dateid date starttime endtime458 2006-12-29 22:00 23:15458 2006-12-29 00:15 01:30459 2006-12-30 20:00 21:10459 2006-12-30 22:15 23:35459 2006-12-30 23:30 00:40459 2006-12-30 01:50 02:30records are inserted for a date related to begining of the shift, althoughsome operations are performed also past the midnight (actualy next day, ex:2006-12-31), but belongs to same shift (group)Now I need to build a function that corrects (updates) the date of everyoperation recorded after midnight to a date+1 value, so all records relatedto same groups (458, 459, etc) that starts after midnight has correct date.The procedure has to update already exiting table.Any solution?Grey

View 8 Replies View Related

Find First And Last Date Of A Month?

Jun 18, 2007

Afternoon all,



Just a quickie, is there an expression built into Reporting Services from which you can find the first date and last date of any given month? The first date isn't much of a problem as it isn't quite as dynamic as the last date.



I can do it in an IIf statement but wouldn't be able to make it recognise 29th February in a leap year very easily.



Thanks,



Paul

View 4 Replies View Related

SQL Server 2008 :: Loop Through Date Time Records To Find A Match From Multiple Other Date Time Records?

Aug 5, 2015

I'm looking for a way of taking a query which returns a set of date time fields (probable maximum of 20 rows) and looping through each value to see if it exists in a separate table.

E.g.

Query 1

Select ID, Person, ProposedEvent, DayField, TimeField
from MyOptions
where person = 'me'

Table

Select Person, ExistingEvent, DayField, TimeField
from MyTimetable
where person ='me'

Loop through Query 1 and if it finds ANY matching Dayfield AND Timefield in Query/Table 2, return the ProposedEvent (just as a message, the loop could stop there), if no match a message saying all is fine can proceed to process form blah blah.

I'm essentially wanting somebody to select a bunch of events in a form, query 1 then finds all the days and times those events happen and check that none of them exist in the MyTimetable table.

View 5 Replies View Related

Find The Date For Yesterday, And Delete?

Feb 2, 2008

Hello there,Im not quiet sure this is the right forum, but what the...I got a database with eg. dates in..(Day-Month-Year)01-02-200802-02-2008 <-- Today03-02-2008
Then i want to find the date for yesterday, in this example 01-02-2008, and delete the record.. - How is this done?Hope you understand and can help me,Regards Jeppe Richardt

View 2 Replies View Related

Trying To Find Best Way To Filter On Date Range

Mar 16, 2004

I have a database were a client can have many addresses. Clients may be at one address or another at different time of the year. The table allows for entering a startdate and an enddate for each address. I'm trying to figure out the best way to filter on this to return only the current address. I have tried the Where clause below but I'm not sure this is what i should use. The year is not needed but the datatype is datetime. I think i need to use the startdate also, but I cannot seam to get how to filter this. If anyone has ideas I would like to hear them.

Thank you,

A.Selected=1 AND A.EndDate Is Null OR DatePart(m,A.Enddate) >= DatePart(m,GETDATE()) AND DatePart(d,A.Enddate) >= DatePart(d,GETDATE())

View 9 Replies View Related

Find The Date If I Know The Week And Year

Aug 5, 2004

Hi Guys,
Can anyone please let me know How to find the first day of the week, if I know the weekno and the year.
For ex: If I know the week no is 22 and Year is 2003 then I should find a way to output the date. which is the first day (monday) of that week
for the above query the date will be eg:26/06/2003

I really appreciate your Help

Thanks:confused: Find the Date if I know the week and year

View 3 Replies View Related

Find Start And End Date Strings

Oct 28, 2004

Hello,

I am having some probelms getting script to give me the first and last date a customer had an outstanding item. The data is as follows:

Customer StartDate EndDate
A 4/1/04 4/15/04
A 4/15/04 5/1/04
A 5/1/04 5/15/04
A 5/16/04 5/28/04
A 5/28/04 6/5/04
B 5/1/04 5/15/04
B 5/16/04 5/20/04

The results I am looking for would be as follows:

Customer A : Outstanding 4/1/04 - 5/15/04
Customer A : Outstanding 5/16/04 - 5/28/04 (Theres a one day gap between prior sting, so this would be a new string )
Customer B : OUtstanding 5/1/04 - 5/15/04
Customer B : Outstanding 5/16/04 - 5/20/04

I want to include any strings where the start of one item and the end of another are on the same day as one continuis string. Any ideas on how to do this??

Thanks in advance!!

View 4 Replies View Related

I Need To Find The Most Recent Effective Date

Mar 10, 2008

Hi,

Not sure if you could help or not, but I need to pull the most recent effective date for this report I am trying to run, but I am getting know where. If someone can take a look at this, it would be great.
Example….
pcs number 00004344 effective dates 5/1/2006 and 5/1/2007. I need it to be the most recent effective date which would be, 5/1/2007 date.

Can someone help me?
USE [Impact_PROD]
GO
/****** Object: StoredProcedure [dbo].[p_PrepareMalPracticeReportDataBYCPTCODES] Script Date: 03/10/2008 09:18:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[p_PrepareMalPracticeReportDataBYCPTCODES]

AS
BEGIN
SET NOCOUNT ON

DECLARE @STARTTIME DATETIME, @ENDTIME DATETIME
SET @STARTTIME = GetDate()

PRINT 'SP started on: ' + CAST(@StartTIME as varchar)
PRINT ''

DECLARE @PKey varchar(16), @pcsi_id1 varchar(8), @pcsi_id2 varchar(4) /**@pcsi_id3 varchar(4),@Lpcsi_id3 varchar(4)**/
DECLARE @LplID varchar(12), @LTrm Datetime, @Eff Datetime, @Trm Datetime, @Gap int, @Corrected bit
DECLARE @CTrm DATETIME, @i varchar(8), @LastID varchar(8), @LEff Datetime, @FinalEff DATETIME


SET @i = 0


IF OBJECT_ID('tempdb..#pcsiData') IS NOT NULL
DROP TABLE #pcsiData
IF OBJECT_ID('tempdb..#HoldKey') IS NOT NULL
DROP TABLE #HoldKey

IF OBJECT_ID('tempdb..#HoldKey2') IS NOT NULL
DROP TABLE #HoldKey2
SET DATEFORMAT mdy;

SELECT pcsi_id1 + pcsi_id2 AS pcsi_pkey, pcsi_id1, pcsi_id2, pcsi_eff1, pcsi_trm1
INTO #pcsiData FROM pcsi p
WHERE (SELECT COUNT(pcsi_id1 + pcsi_id2) FROM pcsi WHERE pcsi_id1 = p.pcsi_id1) > 1 --AND p.pcsi_prd = 'dgh'
ORDER BY p.pcsi_id1 + p.pcsi_id2 ASC, p.pcsi_eff1 ASC

--SET TRM DATES TO NULL WHERE DATE IS 1977-03-23 00:00:00.000
--(IMPACT XSQL process uses that date in place of null!)
UPDATE #pcsiData
SET pcsi_trm1 = null
WHERE pcsi_trm1 = '1977-03-23 00:00:00.000'

SELECT pcsi_id1 + pcsi_id2 as Pkey, (COUNT(pcsi_id1 + pcsi_id2)) AS DupCount --pcsi_eff1, pcsi_trm1, COUNT(pcsi_id1 + pcsi_id2) AS DupCount
INTO #holdkey
FROM #pcsiData
GROUP BY pcsi_id1 + pcsi_id2
HAVING count(*) = 1

IF EXISTS
(SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[NonDuppcsiDataForMalPracticeReport]') AND OBJECTPROPERTY(id, N'IsTable') = 1)
DROP TABLE NonDuppcsiDataForMalPracticeReport

SELECT pcsi_id1, pcsi_id2, pcsi_eff1, pcsi_trm1 INTO NonDuppcsiDataForMalPracticeReport
FROM #pcsiData
WHERE pcsi_id1 + pcsi_id2 IN(SELECT pkey from #Holdkey)

DELETE FROM #pcsiData
WHERE pcsi_id1 + pcsi_id2 IN (SELECT pkey from #HoldKey)

DROP TABLE #HoldKey

SELECT pcsi_id1, pcsi_id2, pcsi_eff1, pcsi_trm1, count(*) as NoofDup
INTO #HoldKey2
FROM #pcsiData
GROUP BY pcsi_id1, pcsi_id2, pcsi_eff1, pcsi_trm1
HAVING count(*) > 1

SET NOCOUNT OFF
DELETE #pcsiData
FROM #pcsiData, #holdkey2
WHERE #pcsiData.pcsi_id1 = #holdkey2.pcsi_id1
AND #pcsiData.pcsi_id2 = #holdkey2.pcsi_id2


drop table #holdkey2

SET NOCOUNT ON

IF EXISTS
(SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[pcsiDataForMalPracticeReport]') AND OBJECTPROPERTY(id, N'IsTable') = 1)
DROP TABLE pcsiDataForMalPracticeReport

--CREATE TABLE pcsiDataForMalPracticeReport (pcsi_pkey varchar(16) PRIMARY KEY, pcsi_id1 varchar(8), pcsi_id2 varchar(4), pcsi_id3 varchar(4), pcsi_eff1 varchar(8), pcsi_trm1 varchar(8), Corrected bit)

SELECT pcsi_id1 + pcsi_id2 as pkey, pcsi_id1, pcsi_id2, pcsi_eff1, pcsi_trm1
INTO pcsiDataForMalPracticeReport
FROM #pcsidata p
WHERE pcsi_eff1 = (SELECT MIN(pcsi_eff1) FROM #pcsidata WHERE pcsi_id1 = p.pcsi_id1 AND pcsi_id2 = p.pcsi_id2)


DECLARE cur CURSOR FAST_FORWARD FOR
SELECT pcsi_pkey, pcsi_id1, pcsi_id2, pcsi_eff1, pcsi_trm1
FROM #pcsiData
--group by pcsi_pkey
Order By pcsi_id1 + pcsi_id2, pcsi_eff1 ASC

OPEN Cur
FETCH NEXT FROM cur INTO @pkey, @pcsi_id1, @pcsi_id2, @eff, @trm

SET @lplID = @pcsi_id1 + @pcsi_id2
SET @LEff = @Eff
SET @Ltrm = @Trm


FETCH NEXT FROM cur INTO @pkey, @pcsi_id1, @pcsi_id2, @eff, @trm
SET @i = 2


DELETE FROM tmppcsiDatesWithGaps --Clear table used for debugging

WHILE @@FETCH_STATUS = 0
BEGIN --Begin While Loop

IF @pcsi_id1 + @pcsi_id2 = @lplID
BEGIN --If current record is for the same provider location as the last then...

SET @Gap = DATEDIFF(day, @Ltrm, @Eff)
IF @Gap > 2
BEGIN --If there is a gap greater than 1 day...
PRINT ''
PRINT 'GAP between fetch ' + str(@i - 1) + ' and ' + str(@i) + ' (' + @pcsi_id1 + ' ' + @pcsi_id2 + ' ' + '): ' + str(@gap) + ' days! '
PRINT 'Last Trm: ' + CAST(@LTrm AS varchar) + ' Eff: ' + CAST(@eff AS Varchar)
PRINT ''
--IF EXISTS (SELECT * FROM pcsiDataForMalPracticeReport WHERE pcsi_id1 = @pcsi_id1 AND pcsi_id2 = @pcsi_id2)
--IF @pcsi_id1 + @pcsi_id2 NOT IN (SELECT pcsi_id1 + pcsi_id2 FROM tmppcsiDatesWithGaps)
--BEGIN --Begin if effective date was not already updated
--IF @Leff > @Eff
UPDATE pcsiDataForMalPracticeReport
SET pcsi_eff1 = @Eff-- pcsi_Ltrm = @LTrm
WHERE pcsi_id1 = @pcsi_id1 AND pcsi_id2 = @pcsi_id2
--ELSE
--UPDATE pcsiDataForMalPracticeReport
--SET pcsi_id3 = @lpcsi_id3, pcsi_eff1 = @LEff-- pcsi_Ltrm = @LTrm
--WHERE pcsi_id1 = @pcsi_id1 AND pcsi_id2 = @pcsi_id2

--ELSE
--INSERT INTO pcsiDataForMalPracticeReport (pcsi_pkey, pcsi_id1, pcsi_id2, pcsi_id3, pcsi_eff1, pcsi_trm1)
--VALUES (@pcsi_id1 + @pcsi_id2 + @pcsi_id3, @pcsi_id1, @pcsi_id2, @lpcsi_id3, @LEff, @Ltrm)

INSERT INTO tmppcsiDatesWithGaps (pcsi_id1, pcsi_id2, lpcsiid, EffectiveDate) VALUES (@pcsi_id1, @pcsi_id2, @lplid, @Eff)
--END --End if effective date was not already updated
END --End if there is a gap greater than 1 day
END --End if the provider location is different than the last row

--Set current rows data in last rows variables...

SET @lplID = @pcsi_id1 + @pcsi_id2
SET @LEff = @Eff
SET @Ltrm = @Trm

--Get next row of data

FETCH NEXT FROM cur INTO @pkey, @pcsi_id1, @pcsi_id2, @eff, @trm
SET @i = @i + 1 --increment i
PRINT 'Iteration #' + str(@i) + ' -- ' + @pkey + ' Eff: ' + Cast(@Eff as varchar) + ' ' + ' Trm: ' + cast(@trm as varchar)



END --End While Loop

INSERT INTO pcsiDataForMalPracticeReport
SELECT distinct pcsi_id1 + pcsi_id2, pcsi_id1, pcsi_id2, pcsi_eff1, pcsi_trm1
FROM NonDuppcsiDataForMalPracticeReport
/***
UPDATE #pcsidata
SET pcsi_trm1 = '20470101'
WHERE pcsi_trm1 is null OR pcsi_trm1 = ''
/**
SELECT p.pcsi_id1, p.pcsi_id2, MAX(p.pcsi_trm1)
INTO #HoldKey2
FROM #pcsidata p
group by p.pcsi_id1, p.pcsi_id2
ORDER BY p.pcsi_id1

UPDATE R SET pcsi_trm1 = I.pcsi_trm1 FROM pcsiDataForMalPracticeReport R
INNER JOIN #HoldKey I
ON r.pcsi_id1 = I.pcsi_id1
AND r.pcsi_id2 = I.pcsi_id2
**/

Print ''
Print ''
Print ''
Print 'SETTING MAX TERM VALUES NOW....(This may take a while)'
Print ''
Print ''
UPDATE pcsiDataForMalPracticeReport
set pcsi_trm1 = jp.MaxTrm
FROM pcsi p JOIN (SELECT pcsi_id1, pcsi_id2, MAX(pcsi_trm1) as maxtrm FROM pcsi p2
--WHERE p2.pcsi_id1 + p2.pcsi_id2 = p.pcsi_id1 + p.pcsi_id2
GROUP BY p2.pcsi_id1, p2.pcsi_id2) jp ON (jp.pcsi_id1 + jp.pcsi_id2 = p.pcsi_id1 + p.pcsi_id2)

DECLARE @NotTermed int
SET @NotTermed = (SELECT COUNT(*) FROM pcsiDataForMalPracticeReport WHERE pcsi_trm1 = '20470101')
PRINT''
PRINT 'Total non-duplicate records not termed: ' + str(@NotTermed)


--UPDATE pcsiDataForMalPracticeReport
--SET pcsi_trm1 = NULL
--WHERE pcsi_trm1 = '20470101'
**/
UPDATE pcsiDataForMalPracticeReport
SET pcsi_trm1 = NULL


PRINT ''
PRINT 'STEP TWO.......................'
PRINT 'Preparing the table names...tmpMalPracticeEffectiveDates'
PRINT''
--This step updates tmpMalPracticeEffectiveDates with the desired effective date and most recent termination date
--if there are no current records with a termination date = NULL

TRUNCATE TABLE tmpMalPracticeEffectiveDates

SET NOCOUNT OFF

PRINT 'Inserting new data into tmpMalPracticeEffectiveDates'

INSERT INTO tmpMalPracticeEffectiveDates
(pcsi_id1, pcsi_id2, pcsi_eff1)
SELECT DISTINCT pcsi_id1, pcsi_id2, pcsi_eff1
FROM pcsi p
WHERE p.pcsi_eff1 = (SELECT MIN(pcsi_eff1) FROM pcsi p2
WHERE p2.pcsi_id1 = p.pcsi_id1 AND p2.pcsi_id2 = p.pcsi_id2)
ORDER BY pcsi_id1, pcsi_id2
------------------------------
--Set temp bogus date to distinguis which records are current in
--subsequent statement
PRINT 'Setting bogus date to distinguish pcsi records that are not termed'
UPDATE tmpMalPracticeEffectiveDates
SET tmpMalPracticeEffectiveDates.pcsi_trm1 = '12/21/2049'
WHERE '03/23/1977' IN (SELECT pcsi_trm1 FROM pcsi p WHERE p.pcsi_id1 = tmpMalPracticeEffectiveDates.pcsi_id1 AND p.pcsi_id2 = tmpMalPracticeEffectiveDates.pcsi_id2)
-------------------------------
PRINT 'Setting most recent term date for pcsi records that are not currently active'
UPDATE tmpMalPracticeEffectiveDates
SET tmpMalPracticeEffectiveDates.pcsi_trm1 = (SELECT MAX(pcsi_trm1) FROM pcsi p
WHERE p.pcsi_id1 = tmpMalPracticeEffectiveDates.pcsi_id1
AND p.pcsi_id2 = tmpMalPracticeEffectiveDates.pcsi_id2)
WHERE tmpMalPracticeEffectiveDates.pcsi_trm1 is NULL
-------------------------------
PRINT 'Setting bogus dates back to NULL'
UPDATE tmpMalPracticeEffectiveDates
SET tmpMalPracticeEffectiveDates.pcsi_trm1 = NULL
WHERE pcsi_trm1 = '12/21/2049'
-------------------------------
--CORRECT EFFECTIVE DATES WITH GAPS...
PRINT 'Correcting Effective Dates for those records with gaps in credentialing records'
UPDATE tmpMalPracticeEffectiveDates
SET tmpMalPracticeEffectiveDates.pcsi_eff1 = t.EffectiveDate
FROM tmppcsiDatesWithGaps t
WHERE tmpMalPracticeEffectiveDates.pcsi_id1 = t.pcsi_id1
AND tmpMalPracticeEffectiveDates.pcsi_id2 = t.pcsi_id2

----END OF SP---


DECLARE @Diff decimal
SET @ENDTIME = getdate()
PRINT ''
PRINT ''
DECLARE @GapCount int
SET @GapCount = (SELECT COUNT(*) FROM tmppcsiDatesWithGaps)
PRINT 'Total number of non-distinct provider locations: ' + Str(@i) + '.'
PRINT 'Total number of gaps found: ' + Str(@GapCount) + '.'
PRINT 'FINISHED ON: ' + cast(@ENDTIME as varchar)
SET @Diff = CAST(DATEDIFF(second, @StartTime, @EndTIME) AS varchar)
PRINT ''
PRINT 'Time elapsed: ' + str(@Diff) + ' seconds.'
PRINT ' = ' + str(@Diff/60) + ' Minutes!'


END

View 7 Replies View Related

Find The Most Recent Date Of The Past

Apr 11, 2007

Hello,



I've a table which contains the nest following data:



Date Sales Price

01-01-07 5,00

31-03-07 6,00

16-04-07 5.75

26-04-07 6.25



For example, today is 18-04-07. In my report I want to show the most recent Sales Price, but not the price of next week. I want to see the SalesPrice of 5.75.



How can I get it?



Thx!

View 22 Replies View Related

How To Find The Hour Of A Date Time

Oct 22, 2007

i have a query i need to calulate the HH of a datetime field

example


Dateval Need to get







10/01/2007 7:48
10/01/2007 7:00

10/01/2007 7:49
10/02/2007 7:00

10/01/2007 7:50
10/03/2007 7:00

10/01/2007 7:51
10/04/2007 7:00

10/01/2007 7:52
10/05/2007 7:00

10/01/2007 7:53
10/06/2007 7:00

10/01/2007 7:54
10/07/2007 7:00

10/01/2007 7:55
10/08/2007 7:00

View 5 Replies View Related

Cant Get The Result Sorted

Apr 13, 2008

Hi guys,
The result of my query I am not able to get it sorted by months.Here is my query & the output.


declare @tbl as table(date datetime,amt int)
insert into @tbl
select '2-jan-2008',100 union all
select '15-jan -2008',200 union all
select '20-jan -2008',500 union all
select '12-jan-2008',300 union all
select '02-feb-2008',100 union all
select '09-feb-2008',250 union all
select '03-mar-2008',500 union all
select '05-mar-2008',800

select Months,SumAmt,MaxAmt,MinAmt from
(
select datename(mm,date)as Months,sum(amt)as SumAmt,min(amt)as MinAmt,max(amt)as MaxAmt,row_number()
over(partition by datename(mm,date) order by datename(mm,date))as rowid from @tbl
group by datename(mm,date)
)t

The result is

Months SumAmt MaxAmt MinAmt
------------------------------ ----------- ----------- -----------
February 350 250 100
January 1100 500 100
March 1300 800 500


I want to get it sorted month wise i.e Jan,Feb,March.But I cannot find a way

View 2 Replies View Related

How To Get The First Row Of A Sorted List

May 14, 2007

Hi,

I am a newbie so this might be a very basic question. I have a table that not sorted. So I write a query to sort the table like this

Select * from custInfo where custID <> '0' order by custID Desc

Now obviously I have a selection of the original table in desc order. Now I want to get the first custId from thid Desc selection. How do I do this.

So if the unsorted table looked like this

001 Martha
005 Steve
002 Mike
003 David

and the sorted selection looks like this

005 Steve
003 David
002 Mike
001 Martha

And then I want to select Steve's custID which is 005. how do i do this.

Thanks in advance.

View 2 Replies View Related

Sorted Input

Mar 17, 2006

If a component requires a sorted input it would seem reasonable that you can check the IsSorted property of the attached input, but this will always return false. I have tried this when connecting the output of the Sort transform to my component, and then check the IsSorted property for this input. It is always false. How can this be, and also how can I see if the path is indeed sorted?

If using a virtual input column in my UI, I get a SortKeyPosition on the columns, but when overriding SetUSageType in the component class I always get zero for the key. Why is the sort information not quite there for me?

View 8 Replies View Related

Find Continuous Date Range In Sqlserver2005

Jan 17, 2008

 hi all
how to find the continuous date from the given date range in sqlserver 2005
e.g.
2007-01-27 and 2007-02-02 and output should be
2007-01-27 2007-01-282007-01-292007-01-302007-01-312007-02-012007-02-02any suggestion?
 
 
 
 
 

View 1 Replies View Related

Find Expiration Date For Evaluation Edition

Oct 21, 2006

Is there any way I can check the remaining time before I must upgrade
an SQL Server 2000 Evaluation Edition? I thought I had made a note about
which day it was.
Can I rely upon the file date for uninstallation file in Programs/etc ?

View 2 Replies View Related

SQL 2012 :: Find All Index And Creation Date

Mar 27, 2014

I need to find all the index and the creation date. I did cross apply of sys.objects & Sys.indexes on name column. I am getting some but the team is saying they created so many. Any other option to find Indexes and their creation date?

View 2 Replies View Related







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