Filter Records Within Effective And Expiration Date
Mar 11, 2014
I would like to filter records with in effective date and expiration date; If there is no record within that range, then check for grace period records ( effective date -30 days and expiration date + 90 days)
Below is the detailed script for sample data...
declare @tab table ( sno int identity, name varchar(100), EFFECTIVE_DATE date, EXPIRATION_DATE date)
insert into @tab (name, EFFECTIVE_DATE , EXPIRATION_DATE )
SELECT 'chandu', GETDATE(), NULL union all
SELECT 'chandu', '2014-02-11 00:00:00' , '2014-03-20 00:00:00' union all
SELECT 'AAA', '2014-01-11 00:00:00' , '2014-05-11 00:00:00' union all
[Code] ...
Output:
/*
snonameEFFECTIVE_DATEEXPIRATION_DATE
1chandu2014-03-11NULL
2chandu2014-02-112014-03-20
3AAA2014-01-112014-05-11
4CCC2014-04-09NULL
8DDD2014-03-102014-04-11
*/
Expected output:
snonameEFFECTIVE_DATEEXPIRATION_DATE
1chandu2014-03-11NULL
3AAA2014-01-112014-05-11
4CCC2014-04-09NULL
8DDD2014-03-102014-04-11
Looking for query WITH OUT using GROUP BY clause
View 10 Replies
ADVERTISEMENT
Sep 10, 2015
I have two tables. Status and Fourhistory tables.Status table contains a status column with effectivestart and end dates as history. This column is having history at month level.
Fourhistory table maintains 4 columns as part of history with the use of effectivestart and end dates. Here history capturing is at day level.
Desired Result: I want to merge the status column into FourHistory table.Below i have given some possible sample scenarios which i face and the third table contains the expected ouput.how to achieve this in T-SQL query.
create table dbo.#Status(
ID varchar(50),
Status varchar(50),
EffectiveStartDate datetime,
EffectiveEndDate datetime,
Is_Current bit
[code]...
View 4 Replies
View Related
Mar 28, 2007
I'm using an objectDataSource connected to a strongly typed dataset to populate a GridView. I want to be able to show all the records, or let the user to select only those records that expire in a certain month. The expire field is of type date I'm used to all records being returned when a parameter is missing. If I have Select * from table where last=@last, only the records where the last name is 'Smith' will be returned if @last = 'Smith', but all records are returned is @last = "". But that's not how it's working with the date. I'm passing an integer from 1 to 12 in a querystring. I have the equivalent of select * from table where (MONTH([AD ENDS]) = @month)MONTH(datefield) always returns an integer from 1 to 12. If @month is empty, I want all the records to be displayed, but nothing is. If @month is an int form 1 to 12, it works fine. How can I get all the records if no month is selected? Can I have two objectdatasources and programmatically select which one populates the gridview depending on if I want to filter the data or not? Diane
View 5 Replies
View Related
Dec 27, 2007
I have two tables totally unrelated but give the same information, the difference is the duration. I need to create a stored procedure that will give the recent issue dates only, accept if they have already expired. I'm not exactly sure how to do that. We only want the employees to see the current issue date as long as the exclusion has not expired. Can anyone help please. What needs to happen is that the employees need to see only the exclusions that have expired which is based on the Expiration Date.
Code Block
ALTER PROCEDURE [dbo].[SrchIssueDateLstNm_RecntIssueDte]
@StartIssueDate datetime = null,
@EndIssueDate datetime = null,
@Enter_LastName nvarchar(10) = null
AS
SELECT [dbo].[30 Day exclusion].[First Name], [dbo].[30 Day exclusion].[Last Name], [dbo].[30 Day exclusion].[Issue Date],
[dbo].[Extended Exclusions].[First Name], [dbo].[Extended Exclusions].[Last Name], [dbo].[Extended Exclusions].[Issue Date]
FROM [dbo].[30 Day exclusion]
INNER
JOIN [dbo].[Extended Exclusions]
ON [dbo].[Extended Exclusions].[ID] = [dbo].[30 Day exclusion].[id]
WHERE (@StartIssueDate is null or [Issue Date] >= @StartIssueDate)
AND (@EndIssueDate is null or [Issue Date] <= @EndIssueDate)
AND (@Enter_LastName is null or [Last Name] = @Enter_LastName)
ORDER BY [Last Name]
View 3 Replies
View Related
Nov 29, 2000
I have a question on how the retain parameter works for Backups. If I have a nightly full backup for a database and have retain = 10, does that mean on the 11th day the very first backup will be purged? Will the 11th backup attempt to take up some of the space that was occupied by that first backup? It seems that the 11th backup would be too large for the space that the first backup was taking up. I am fairly new to backups with SQL Server and want to ensure I am setting this up properly. Our goal is to keep backups for 10 days only.
View 1 Replies
View Related
Jul 26, 2006
I have a table containing several date fields in which certain events happened, and I need to be able to find the expiration date for renewal. The expiration dates are at the end of the 6th, 12th, or 24th month after the event occurred, depending on the event.
Is there a way to return in a query the last day of month x following a date field? For instance, if the date '3/12/2006' is stored in the field for a 12-month expiration, I need to return '3/31/2007'.
Any ideas?
View 1 Replies
View Related
Jul 24, 2006
Hi There
This may seem like a stupid question but i am trying to get the hang of the new security model.
I have not really heard anything mentioned about certficate expiration date when it comes to creating certificates for keys or service broker endpoints etc.
We have created certificates for keys and service broker endpoints, now what exactly happens when the expiration date, by default 1 year i think is reached, will we no longer be able to decrypt encrypted data and will the service broker endpoints stop working etc ?
Or is this expiration date when the certificate can no longer be used to create security objects ? And all security objects already created with this certificate will always work ?
In other words is there ever danger that keys and endpoints or basically any object referrencing this certificate will just suddenly stop working one day, or will all objects work indefinately regardless of an certificate/objects expiration date ?
Thanx
View 8 Replies
View Related
Jul 15, 2006
First question: I have sql server 2005 enterprise eval edition installed. is there a query, command or tool can tell me the exact expiration date? I understand it is 180 day but I still want some way to query the exact date.
Second question: does sql server log any warning message before eval expires?
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
Jan 24, 2008
We are maintaining a database of drivers, and we are looking for a script or procedure that will automatically notify certain users of the list of drivers who are in need of a recheck or driver's license has expired.
I am new to this , so any help you can give would be greatly appreciated
View 1 Replies
View Related
May 20, 2008
I have a table with a single effective date, rather than both a start and stop date. I have to be able to match up this table to another one with service information in it and am not sure how to get the correct record selected.
So in table one I have a personID, effective date, and lots of other fields. There are also multiple records for each personID, so say personID 1 has records with effective dates of 1/1/2007, 6/1/2007, and 1/1/2008.
Table two has personID, Service Date, and lots of other fields.
So if I am looking to match up the effective row from table one to a record in table two with a service date of 8/1/2007, how do I get the db to locate and return the record with an effective date of 6/1/2007, and only this record?
View 6 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
Oct 16, 2005
I have a procedure that requires picking up the Rate based on Effective Date. This is what I have so far:
SELECT
SHPD.ProductID,
SHPD.ReceivedDate,
SHPD.Shipper,
SHIP.UnitRate
FROM tblShipmentDet SHPD
LEFT OUTER JOIN tblShippers ON SHIP.ProductID = SHPD.ProductID AND SHIP.Shipper = SHPD.Shipper AND Max???(SHIP.Effectivedate) <= SHPD.ReceivedDate
Because there can be more than 1 Shipper record, I would somehow need to pickup the Maximum EffectiveDate in each case. I realize I cannot use the Max aggregate in the JOIN. Not sure where to go from here. On the Mainframe I used a LOOKUP function that would return the correct EffectiveDate. Help would be appreciated.
View 8 Replies
View Related
Oct 29, 2015
I have configured a Linked Server with Active Directory (LDAP), working properly.
Now, through this Linked Server, I need information about password expiration date in my AD Domain accounts.
View 2 Replies
View Related
Jul 22, 2015
We need to use SQL Server Authentication for some reason and would like to enforce Password Policy with 90-day expiration period. I found "Change password" dialogue appears when I first logged in with the new user, but don't know
(1)what happens when the user failed to change the password before it's expired or
(2)how a user can change his password in advance of the expiration date with no particular server-level permission.
View 10 Replies
View Related
Sep 30, 2014
What would be the most straight forword to Calculate 90 days and 3 Years ago from an Effective Date in a table?
as in
SELECT EffectiveDate
from FL.CEFHistory
I need to return the effective date - 90 days and 1 year from that.
[URL]
View 6 Replies
View Related
Aug 27, 2015
I have a QA Deployment Date field that is being returned in a custom report I created. I also found a sample date range parameter:
What I want to accomplish:
I want to select a From and To Date and filter the report to only display the rows that have the QA Deployment Date within the selected range.
For example.. I want to select From Date (8/1/2105) and To Date (8/31/2015) and I only want to return only the results that have a QA Deployment date between that selected range.
View 3 Replies
View Related
Dec 14, 2006
I have a date filter, and I default it to first day this month and last day this month under relative date, when I run it it givis me error:
The Value expression for the report parameter €˜FromDate€™ contains an error: [BC30456] 'Date' is not a member of 'Integer'. (rsCompilerErrorInExpression)
Can anyone fix this problem?
But it works for Today or (n)months ago.
Thanks.
View 6 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
Jan 13, 2006
Dear all,
My question about SQL statement:
Table1 - Fields: T1PK, T1Field
Records:
1, ABC
2, DEF
3, GHI
Table2 - Fields: T2PK, FK (join T1PK), T2Field
Records:
101, 1, XXX
102, 1, YYY
103, 1, XXX
104, 2, XXX
I want to write a SQL statement that join Table1 and Table2 together, T2Field equal to XXX, and list all records from Table1.
That's the result columns: T1PK, T1Field, T2PK, T2Field
Result records:
1, ABC, 101, XXX
1, ABC, 103, XXX
2, DEF, 104, XXX
3, GHI, NULL, NULL
Is it possible? Please help!!
Joey
View 2 Replies
View Related
Jan 16, 2006
I have an SQLDataSource that I would like to filter out some records that are stored in an ObjectDataSource. Is this possible? The data that is filling the ObjectDataSource is being populated by a WebService.
SQL in SQLDataSource----------------------------
SELECT id, accountFROM contactWHERE id NOT IN (SELECT id FROM ObjectDataSource.Records...)
Thanks.
View 1 Replies
View Related
Mar 6, 2014
I have a table that has multiple records as illustrated in the simple list below. The real list is much longer.
MachineA 1/1/2008
MachineA 1/3/2008
MachineB 1/7/2008
MachineB 1/8/2009
MachineB 5/5/2010
MachineA 5/7/2011
MachineA 4/2/2013
I need to query to return a result for each unique machine with the latest date. The example result below would be returned because they have the latest date.
MachineA 5/7/2011
MachineB 5/5/2010
Select Distinct would almost do it, but I need each unique machine that has the latest date.
View 9 Replies
View Related
Apr 13, 2007
Hi All,I have a table with a column DeletedDate which stores a logical deleteof a record.I need to set up transactional replication for reporting purposes thatthis deleted records should not be replicated to the subscriber. Thatis, if i see a value on the DeletedDate, I don't want that record tobe picked up for replication.At the same time, when someone marks the record for deletion (byputting a date on the DeleteDate column), I want that record to bedeleted on the subscriber database. (I can also set up a job to do thedeletes on the subscriber but i'd rather let the replication take careof it).Can this scenario be implemented in Microsoft SQL 2000? I wouldappreciate any ideas / thoughts in this matter.Thanks in advance,Aravin Rajendra.
View 3 Replies
View Related
Feb 28, 2007
I have a parametered filter in my sql query: Warehouse_Class_Code like '%' + @Filter + '%'
In the parameter I have several value to set the right filter. When I run the report I always have to select one of the values. I added a label "All records" with a value "&". I tried "%" as a default value at the parameter settings.
In the preview the label "All records" is shown, but when I run the report in the HTML browser, I see <Select a value>. How to change the default value to "All records"?
View 2 Replies
View Related
Jan 25, 2007
Hi,
I have a merge (SQL 2005 Standard -> Express) topolgoy which is having problems
The main problem is that the join filters don't seem to work for one area and I am hoping someone can help me with some troubleshooting advice
There are 140+ tables in the topology but the ones causing particular pain are a parent child relationship where the child is actually a bridge/linking table to another table.
Therefore although it is a parent child in the replication filters it is the reverse. i.e. the child has the paramterised filter on it and the parent is one level down joined by it's id. There are other tables joined to this parent table but it stays at three levels deep. The @join_unique_key therefore is set to 0 as is the partition options for the parent /child relationship.
However, when we synchronise we have a problem. The rows get inserted in to the database in RI order but only the child records are replicated down to the subscriber.
The child table with the parameterised filter has 13 articles joined to it in total and one of the other branches of join filters go down as deep as four levels. Most though do not.
Does anyone have any suggestions as to why this might be happening? Any help would be greatly appreciated.
Cheers, James
P.S. I should add this problem only occurs when the edits are made at the publisher. If new records are added at the subscriber everything is fine.
View 9 Replies
View Related
Apr 3, 2008
Hi, all experts here,
I am desperate to need you help.
Could you please give me any advices on how to filter out the records through out the data flow by any particular condition? E.g. In my case, I want to filter out rows with null id (will get rid of those rows with null id which are not matched in the look up component)? Hope it is clear for your help and I am looking forward to hearing from you for your help and thank you very much.
With kindest regards,
Yours sincerely,
View 5 Replies
View Related
Mar 6, 2008
I am trying to filter the records in an Access2003 project form using data from a control in a different form. I define the variable in form1 as RegKeyWord. The record source for form2 ends with WHERE CustomerName Like RegKeyWord. I receive the error Invalid column 'RegKeyWord'
Can anyone help?
View 1 Replies
View Related
Jun 9, 2004
I got a field called Date#, datatype = datetime, default value = getdate().
So after i insert a record, the Date# will auto record the system date and the time. And my MS SQL Server show: 6/9/2004 12:24:35 PM
However i am not able to filter my record by date. The statement i tried are:
SELECT * from mytable WHERE Date# = '6/9/2004 12:24:35 PM'
SELECT * from mytable WHERE Date# = "6/9/2004 12:24:35 PM"
SELECT * from mytable WHERE Date# = 6/9/2004 12:24:35 PM
SELECT * from mytable WHERE Date# = '6/9/2004'
Pls help me,
Thanks a million
View 13 Replies
View Related
Feb 7, 2008
Hi guys can you give a query where I can get my data filtered by date..
(a.jetTransactionDate between 01 / 01 / 01 / 2007 AND CONVERT(DATETIME, '2007-01-30 00:00:00', 102))
but I can't get the data for January instead all transactions for the past years have been included
thanks!
:)
View 7 Replies
View Related
Jan 25, 2007
I want to set a filter to calculate the date range to be 1 year ago to last month. Is this possible?
ps. Im working with a AS cube.
View 3 Replies
View Related
May 2, 2007
I have a report in Visual Studio that I want to limit to just the activity for today. Normally I would add a WHERE stmt to the sql script that says docdate=getdate(). However this does not return any data to me in VS. I have tried many different syntax. Does anyone have the correct syntax or filter value that works in Visual Studio? Thank you.
View 1 Replies
View Related
Jan 22, 2008
Hi
I am trying to make a chart by using data from a database.
What I am trying to do is this.
The user will have 3 options
Option 1.
View last quiz
Option 2.
View Data by week(up to 4 weeks).
Option 3.
View by month
Later once I get these down I will have view 7 days worth of data.
So I am not sure how to do option 2 and 3. I have not tired to do Option 1 yet so can't say yet.
Like say if I have a months worth of data and they choose Option 2 it should make 4 columns on my graph. However I don't know how to make my sql to filter like that.
A user also can do 0 or more quiz's a day so first all the values(total right, total wrong, total assitance needed) would need to be added up. After that the filter would have to be taken place so that it filters.
So how would i do something like this?
View 2 Replies
View Related
Feb 27, 2008
I have been trying to write a simple database program using VB and SQL Express. In doing so, I have run into a problem. I am trying to filter by a Date value. I have the following SQL in the background.
SELECT TransID, BIN, CarrierName, CheckDate, NumberOfClaims, BeginningDate, EndingDate, CheckAmount, Code, CheckNumber
FROM Payments
WHERE (CheckDate=@CheckDate)
ORDER BY CheckDate DESC
I am using a datetimepicker to select a date value and pass it in. Doesn't work. I get an error about "errors converting string to date" or something like that. I thought datetimepickers passed date values.
Anyway, just executing the SQL query and typing in the parameter value gives the same results.
Please help. What am I doing wrong.
Thanks for the help
If you need additional info, please let me know.
View 1 Replies
View Related
