How To Find A Lower Date
Jan 4, 2007
I have following problem:
table includes times for startup and end of operation as datetime field
related to daily shift operations:
dateid date starttime endtime
458 2006-12-29 22:00 23:15
458 2006-12-29 00:15 01:30
459 2006-12-30 20:00 21:10
459 2006-12-30 22:15 23:35
459 2006-12-30 23:30 00:40
459 2006-12-30 01:50 02:30
records are inserted for a date related to begining of the shift, although
some 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 every
operation recorded after midnight to a date+1 value, so all records related
to 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
ADVERTISEMENT
Jun 2, 2008
Hello,
I have been having a hard time with this issue. I am attempting to join a table onto itself to get the closest date onto a single row.
What i mean is:
I have the following data
id date
1 10/07/08
2 10/06/07
3 10/06/03
4 10/06/03
the new table should have the current id and the one closes to it as so.
1 10/07/08 2 10/06/07
2 10/06/07 3 10/06/03
3 10/06/03 null null
4 10/06/03 null null
but i am getting duplicates do to the 10/06/03.
1 10/07/08 2 10/06/07
2 10/06/07 3 10/06/03
2 10/06/07 4 10/06/03
3 10/06/03 null null
4 10/06/03 null null
i want so that if there is a duplicate i can take the id thats higher. I cant figure it out.
This is my current sql:
SELECT PB.ID,PB.StartDate, PB2.ID, PB2.Startdate
from table PB
left outer join table PB2 on PB.keyID = PB2.keyID
and PB2.StartDate < PB.StartDate
and PB.StartDate = (select top(1) StartDate from table PB3 where PB.keyID = PB3.keyID
and PB2.StartDate < PB3.StartDate order by PB3.StartDate asc)
Thanks for the help.
View 3 Replies
View Related
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
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
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
Sep 28, 2007
Hi I know i can use a LOWER or UPPER function to change the
case of letters, but say i want to change all but the 1st letter
ie i want MARK SMITH to be Mark Smith ... MARK and SMITH
are 2 seperate columns so im assuming something like
select UPPER(first_name,1) ...
View 6 Replies
View Related
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
Apr 17, 2007
hi
I have a column in my database i would like to convert to lowercase
is their a t-sql statement or something i can use so i dont have to do it manually ??
cheers!!!
View 2 Replies
View Related
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
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
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
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
May 19, 2008
Hi all,
I have a big problem with the design and the queries of a couple of tables. I have to calculate the weighted average of the number of days between an invoice and the sum of its payments (rateal payments), weighting this number with the invoice's amount, and only when the sum of the invoice's payments equal invoice amount.
I have designed two FactTables to accomplish this:
Example:
data in the fist table (FactInvoice) looks like this:
CustomerId, InvoiceDate, InvoiceNumber, InvoiceAmount
1234567 2008-03-10 123 1000.00
1234567 2008-04-10 150 2000.00
and data in the second one (FactPayments) looks as below:
CustomerId, PaymentDate, InvoiceNumber, PaymentsAmount
1234567 2008-03-10 123 500
1234567 2008-03-15 123 500
1234567 2008-04-20 150 800
1234567 2008-04-23 150 1200
Let's suppose that I'm querying the cube for the customer 1234567, at the date 2008-05-01. I need to sum the payments for the first invoice multiplicating it for the count of days elapsed, then divide the number by the InvoiceAmount (or PaymentsAmount, is the same) : (0 days * 500‚¬ + 5 days * 500‚¬ + 10 days * 800‚¬ + 13 days * 1200)/3000‚¬ = 8,7 days of weighted average to cash an invoice.
How can I do this with Analisys Services ? Is it too complicated ? Here, in the southern Italy, the problem of customer's debit is heavily felt, and total like this are really important!
Any help or suggestion will be really appreciated.
Marco
View 13 Replies
View Related
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
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
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
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
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
View Related
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
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
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
Aug 14, 2006
Hi there ;
I've a photo viewer which shows the image based on a datetime QueryString. I've a couple of buttons like next, prev, last, .... .
in the "prev" for instance, i want to select the image with a datetime that is lower than the current image datetime, and is the biggest one of course.but i'm stuck in it!
By the way i use .net 2.0 & Sql 05 Express
thanks in advance
View 4 Replies
View Related
Apr 6, 2007
For example,
select fieldA form tableA where fieldA = 'aaa'
I got following output
fieldA
---------
aaa
aAa
AAA
AAa
...
if I want select only the lower case 'aaa', how can I do that?
View 2 Replies
View Related
May 2, 2008
My SQL Server database is not case sensetive.
How can I compare like cluase with search for capital and small letter?
For example
SELECT add1 from xcty_all where add1 like '%AL'%'
I need only
...................
10 ltncewwod way AL
456 Ruio St. AL
NOT
Duci Ral Rd Mexico
Albi Road Hawai CA
I want to ingore this bold letter on search
Sanjeev Shrestha
12/17/1971
View 1 Replies
View Related
Jul 20, 2005
I need to split a string in two if there are lowercase characters atthe end of it.For example:'AAPLpr' becomes 'AAPL' and 'pr''Ta' becomes 'T' and 'a''MSFT' becomes 'MSFT' and '''TAPA' becomes 'TAPA' and ''I am using SQL 2000. I read about "collate Latin1_General_CS_AS" butnot sure if I can use that outside of a select statement.Thank you in advance for any help.
View 2 Replies
View Related
Oct 25, 2006
I am trying to get this code to work: (left(religion,1))+ lower(right(religion,len(religion)-1))As Religion
I am getting this error:
Invalid length parameter passed to the RIGHT function.
View 6 Replies
View Related
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
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
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
May 19, 2014
What would be the most economy solution for this WHERE, I see the code where we have covnert to 101 type on both sides of equation, which I tnink is not right.
So I'm thinking to put it on the left like this, just curiouse is this the best solution, I also heard that TSQL interpret between even better , here I'm really care abour performance.
declare @DATE DATE = '01/14/2014'
--A:
SELECT * FROM TT
WHERE CAST( TT.DATETIME AS DATE) = @DATE
--B:
SELECT * FROM TT
WHERE TT.DATETIME >= @DATE and TT.DATETIME < DATEADD(D,1,@DATE)
View 3 Replies
View Related
Nov 4, 2015
I want to find previous date from selected date. below is the sample data.
DECLARE @StartDate SMALLDATETIME = '1/11/2016'
declare @tempdat table(repdate smalldatetime)
insert into @tempdat values ('10/26/2015')
insert into @tempdat values ('10/29/2015')
insert into @tempdat values ('11/1/2015')
insert into @tempdat values ('11/27/2015')
insert into @tempdat values ('11/25/2015')
insert into @tempdat values ('11/20/2015')
insert into @tempdat values ('11/10/2015')
insert into @tempdat values ('11/10/2015')
insert into @tempdat values ('11/11/2015')
insert into @tempdat values ('11/11/2015')
Now if i pass the date '10/26/2015' then i want select prev date of passed date. in this example no prev date is available, so result set would be nothing.
if i pass the date '11/10/2015' then result should be '11/1/2015' which is prev small date available in table.
View 4 Replies
View Related
Oct 11, 2006
Say I have 3 .bak files named:jamesB.bak, jamesG.bak, jamesW.bakIs there a clever way to find out which is the most recent of thesebackup files?? Using sql query analyzer preferably...
View 4 Replies
View Related
Jul 24, 2015
I've got two measure groups with a dimension (Dimension A) that is related to one measure group but not the other. I want to find the date ranges of the members of an attribute in Dimension A in Measure Group 1 and apply that to Measure Group 2 in a calculation. This way I can find the sum of a measure from Measure Group A that falls within the time periods of the attributes in Measure Group B. Part of my MDX for this new calculated member starts like this, but doesn't work.
WITH MEMBER [Measures].[New Measure] ASÂ
IIF( ISEMPTY ([Measures].[Measure 1]), NULL,
SUM(([Date].[Hour].[Hour], [Dimension A].[Attribute].[Attribute]), [Measures].[Measure 2]))
View 3 Replies
View Related
