Transact SQL :: Get Subquery To Eliminate Malformed Date?

Jun 30, 2015

I have the following query.

SELECT a.Line_Number as Line_Number,
Cast(a.election_effective_date as DATE) as election_effective_date,
Cast(a.Plan_Year_Effective as DATE) as Plan_year_Effective
FROM (SELECT aa.* FROM TEMP_Validation aa WHERE IsDate(aa.Plan_Year_Effective) = 1 AND IsDate(aa.election_effective_Date) = 1) a
Where Cast(a.election_effective_date as Date) Not Between Cast(a.plan_year_effective as Date)
and DATEADD(DD,-1,DATEADD(YY,1,Cast(a.plan_year_effective as Date)))

There is a malformed date in the Temp data, at line 4932.  This is why I created a subquery that checks if the plan year and election effective dates are dates using the "IsDate" function.  However, when I execute this I get the error "Conversion failed when converting date and/or time from character string".  If I eliminate line 4932 the error no longer occurs.  Somehow line 4932 is not being eliminated in the subquery, except if I run JUST the subquery it will not include line 4932.  What am I doing wrong on these queries and how can I correctly get the subquery to eliminate the malformed date?

View 5 Replies


ADVERTISEMENT

Transact SQL :: Retrieve Another Column (exchange Rate) From Particular Date For Subquery?

May 25, 2015

Below is the query in which i want to retrieve another column (exchange rate) from a particular date for the sub query. 

Actually PurchaseOrderDet table get records related to purchaseorder but for each row in purchaseorderdet need info from the same table for all rows on a particular date.

how i can achieve this query without using RANK() and other functions.

"SELECT  Supplier.Uniid AS SupplierID, Supplier.Name, PurchaseOrder.Uniid AS PurchaseID, PurchaseOrder.OrderNo, PurchaseOrder.FormDate, StockItem.Uniid AS StockID, StockItem.StockCode + N' - ' + StockItem.Description1 AS StockItem,
PurchaseOrderDet.ExchangeRate, PurchaseOrderDet.UnitPrice, PurchaseOrderDet.Discount, SUM(PurchaseOrderDet.OrderQty) AS SumOfOrderQty,

[Code] ....

View 7 Replies View Related

How To Eliminate Timestamp - Only Show Date In Result

Feb 21, 2014

With this qry i need to only show in the result for ordate the date and not the time stamp. below is my qry and the first line of the results:

SELECT DISTINCT
adHock.dbo.PPVVINCE$.Corps, adHock.dbo.PPVVINCE$.House, adHock.dbo.PPVVINCE$.Cust,
IDST_EVENT_ORDERS.ORDATE,
IDST_EVENT_ORDERS.ORTIME,
IDST_EVENT_ORDERS.EVWMIN,
IDST_EVENT_ORDERS.EVTDES,

[Code] ....

result for the field trying to change

ORDATE
1/1/2014 12:00:00 AM

I Only want to see the date.

View 6 Replies View Related

Transact SQL :: Get Max Value Using Subquery?

Nov 23, 2015

I have the below data and want to only show records where Response Column has Max date for the commencement date and expiry date under questions column.. For example for tenantcode 52090 the highest Tab value is 2 but this is not correct as there is no associated value in response.

We need to compare all the commencement and lease expiry dates and show only the commencement and lease expiry with the recent dates. Null should be ignored Instead of using Max Tab or Max Modified date would the below logic work? Not sure how to script this.

1. Filter to find Questions containing "Expiry Date"

2. summarise to find the max expiry date per Tenant Code

3. find which records have that maximum expiry

4. join again to find the commencement date

5. union the records from (3) and (4)

6. cross tab

Declare @rent Table
(
TenantCode CHAR(6)
,HHMID CHAR(5)
,Tab CHAR(4)
,FieldNo CHAR(4)
,FieldRowCHAR(10)

[code]....

View 29 Replies View Related

Transact SQL :: Subquery In WHERE

Aug 18, 2015

I have the following stored proc that I need to pull InsuranceID specific rows when @InsuranceID > 0 is sent.  Below is the code and I think the issue is in the 2nd WHERE condition.

ALTER PROCEDURE [dbo].[ms_selJobSetupSelections]
(@ActiveOnlybit = 1,
@InsuranceIDint = 0)
AS
BEGIN
SELECT J.JobSetupID
,J.JobSetupText

[code]....

View 21 Replies View Related

Transact SQL :: Aggregate In Subquery

Oct 22, 2015

(select SUM(sales.Total) from sales where StudentHist.Curdate = max(sales.curdate))  AS 'Balance'Iam trying to write a subquery to  calculate the total amount of sales until the Curdate  in studenthist equals the Curdate in sales table..how to write this query??

View 11 Replies View Related

Transact SQL :: How To Reuse Subquery

Jul 29, 2015

with c1 As (
select 1 As '1' , 2  As '2' , 3 As '3'

, c2 As (
select 4  As '4', 5  As '5',  6 As '6'
union all
select 1 , 2 ,3 from c1  -- >>>>>  select from c1 here 
) select * from c2 
  union all
  select * from c1 -- >>>>>> and select from c1 here 

According to the query above , I try to reuse the subquery by put  the subquery into 'with cte'  name (c1)  then i select this  2 times .

if I do this way , how many time this subquery (c1) execute ?
if 1 time then this is the right way to reuse this subquery .
if 2 times  , it is not then what should i do to reuse this subquery ?

View 3 Replies View Related

Transact SQL :: Subquery Another Table?

Apr 27, 2015

I have a table which is called PneumoniaCareBundleDiagnosisCodes with the fields DiagnosisCode, DiagnosisDesc

I have a another table called dbo_OP_APPOINTMENT_PROCEDURE_PIVOT

With fields Procedure01, Procedure02, Procedure03, Procedure04, Procedure05, Procedure06, Procedure07, Procedure08, Procedure09, Procedure10, Procedure11, Procedure12.

(for info purposes the relationship here is DiagnosisCode = Procedure01, 02, 03 etc)

Is it possible to right a query that shows all records in dbo_OP_APPOINTMENT_PROCEDURE_PIVOT where the DiagnosisCode in table PneumoniaCareBundleDiagnosisCodes appear in any of the Procedure01, 02, 03 fields etc.

If I was using an IF statement the logic i would right it as follows:
IF dbo_OP_APPOINTMENT_PROCEDURE_PIVOT.Procedure01
IS IN PneumoniaCareBundleDiagnosisCodes.DiagnosisCode OR
IF dbo_OP_APPOINTMENT_PROCEDURE_PIVOT.Procedure02

[Code] ......

View 5 Replies View Related

Transact SQL :: Use Like Command With Subquery

Jun 10, 2015

I am writing below query to pull from customer table.

select * from Customer where SrNo in (Select distinct SrNo from Orders)

This is doing an exact match of SrNo in Orders table SrNo is part of string and can occur at any place in that string. I want to use the like command.

Please see below example.

select
*
from Customer
where SrNo
like('%89898989%','%928hhst%')

View 2 Replies View Related

Transact SQL :: Left Join With Subquery

Aug 24, 2015

SELECT A.EmpId,A.IncidentDate
FROM EmployeePoints1 as A
WHERE IncidentDate=
(SELECT MAX(IncidentDate)
FROM EmployeePoints1
WHERE EmpId = A.EmpId) AND (DATEADD(day,28,DATEADD(WEEK, DATEDIFF(WEEK, 0,A.IncidentDate), 0)) < DATEADD(WEEK, DATEDIFF(WEEK, 0,GetDate()), 0)) AND (A.IncidentCode = 'I' OR A.IncidentCode = 'A')
LEFT JOIN EmployeeTotalPoints1 ON EmployeeTotalPoints1.EmpId = A.EmpId

I am trying to left join another table but  I got

Incorrect syntax near the keyword 'LEFT'.

View 9 Replies View Related

Transact SQL :: Using Aggregate Function With Subquery?

Aug 6, 2015

SSMS does not like mine!  THis is the error that I receive:

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

And this is my syntax:

Select
employeeID
,COUNT(case when rehirestatus IN (select rehirestatus from regionalemptable where rtrim(storename) = 'Location1') THEN userID ELSE 0 END) +
COUNT(case when rehirestatus IN (select rehirestatus from globalemptable where rtrim(storename) = 'Location1') Then userID ELSE 0 End)
FROM production
GROUP BY employeeID
ORDER BY employeeID

View 6 Replies View Related

Transact SQL :: Adding Subquery As A Column

May 19, 2015

I have a subquery i wanted to add a as a fourth column to my  Original Query(is the one below the subquery). How to combine the two queries to one statement?? I tried but was getting an error "Subquery returned more than 1 value "

select  
COUNT(*)FreeReduced
 from students s join Buildings b on
 s.Building_ID = b.Building_ID
 where s.Activeness =1 and (Eligibility = 3 or Eligibility =2) 
 group by building_number,Building_Name

[Code] ....

View 3 Replies View Related

Transact SQL :: Query Works Even If Column Not Exists In Subquery

Jul 23, 2015

When I execute the below queries it works perfectly where as my expectation is, it should break.

Select * from ChildDepartment C where C.ParentId IN (Select Id from TestDepartment where DeptId = 1)
In TestDepartment table, I do not have ID column. However the select in sub query works as ID column exists in ChildDepartment.  If I do change the query to something below then definately it will break -
Select * from ChildDepartment C where C.ParentId IN (Select D.Id from TestDepartment D where D.DeptId = 1)

Shouldn't the default behavior be otherwise? It should throw error if column doesnt exists in sub query table and force me to define the correct source table or alias name.

create table TestDepartment
(
DeptId int identity(1,1) primary key,
name varchar(50)
)
create table ChildDepartment
(
Id int identity(1,1) primary key,

[Code] ....

View 3 Replies View Related

Transact SQL :: Update Unique Records Count For Subquery?

Sep 2, 2015

updating the # of Payer from below query to match with the # of rows for each payer record. See the Current and desired results below. The query is currently counting the # of rows for all payers together and updating 3 as # of payers. I need it to count # of rows for each payer like shown inDesired result below. It should be showing 1 for first payer and 2 for 2nd & 3rd based on # of times each payer is repeated..

SELECT b.FILING_IND, b.PYR_CD, b. PAYER_ID, b. PAYER_NAME,a.CLAIM_ICN,
(Select Count(*) From MMITCGTD.MMIT_CLAIM a, MMITCGTD.MMIT_TPL b , MMITCGTD.MMIT_ATTACHMENT_LINK c where a.CLAIM_ICN_NU =
c.CLAIM_ICN and b.TPL_TS = c.TPL_TS and a.CLAIM_TYPE_CD = 'X' 

[Code] ....

Current Result

FILING_IND
PYR_CD
PAYER_ID
PAYER_NAME
CLAIM_ICN
#_OF_PAYER

[code]....

View 4 Replies View Related

Transact SQL :: Pull Records From 3 Tables Using Joins Or Subquery?

Sep 14, 2015

I have 3 tables.

Table 1:
ID  Name  Description
1  ABc      xyz
2  ABC      XYZ

Table 2:
RoleID   Role
1         Admin
2         QA

Table 3:
ID   RoleID  Time
1     1         09:14
2     1         09:15
1     2         09:16

Now I want all the records which belongs to RoleID 1 but if same ID is belongs to RoleID 2 than i don't want that ID.From above tables ID 1 belongs to RoleID 1 and 2 so i don't want ID 1.

View 4 Replies View Related

Subquery Returned More Than 1 Value. This Is Not Permitted When The Subquery Follows =, !=, &<, &<= , &>, &>= Or When The Subquery Is Used As An Expression.

Apr 26, 2008

hello friends.. I am newbie for sql server...I having a problem when executing this procedure .... ALTER PROCEDURE [dbo].[spgetvalues]    @Uid intASBEGIN    SET NOCOUNT ON;        select                                  DATEPART(year, c.fy)as fy,                                                (select contribeamount from wh_contribute where and contribename like 'Retire-Plan B-1%      JRF' ) as survivorship,                (select contribeamount from wh_contribute where and contribename like  'Gross Earnings' and ) as ytdgross,                (select contribeamount from wh_contribute where and contribename like 'Retire-Plan B-1.5%      JRP') as totalcontrib,                                                       from    wh_contribute c                       where    c.uid=@Uid                 Order by fy Asc .....what is the wrong here??  " Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."please reply asap... 

View 1 Replies View Related

Subquery Returned More Than 1 Value. This Is Not Permitted When The Subquery Follows =, !=, &<, &<= , &>, &>= Or When The Subquery Is Used As An Expression.

Jul 20, 2005

I am getting 2 resultsets depending on conditon, In the secondconditon i am getting the above error could anyone help me..........CREATE proc sp_count_AllNewsPapers@CustomerId intasdeclare @NewsId intset @NewsId = (select NewsDelId from NewsDelivery whereCustomerId=@CustomerId )if not exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count( NewsPapersId) from NewsPapersendif exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count(NewsDelId) from NewsDelivery whereCustomerid=@CustomeridendGO

View 3 Replies View Related

Subquery Max Disconnect Date Within Table

Sep 12, 2014

I've written a query calling for the max disconnect date within a table, but I would also like to make sure to only retrieve records with the most recent connect date. I'm unsure how to go about adding the additional max date function to my query. Here's what I have written:

select distinct T.PRIN_MON, T.AGNT_MON, T.SUB_ACCT_NO_MON, T.TRAN_DTE_MON, T.TRAN_CDE_MON,
T.TRAN_AMT_MON, EQ.eqp_stat_eqp,
case when h.acct_stage_ohi = 'v' then 'VOL_DISCO'
when h.ACCT_STAGE_OHI = 'n' then 'NON_PAY_DISCO'
ELSE '' END AS 'CUSTOMER_STATUS',

[Code] ....

View 1 Replies View Related

Failed To Open Malformed Assembly 'mscorlib'

Aug 29, 2006

Hi,

I was using one of features provided in Microsoft samples regarding StringUtilities.dll. It was running fine for couple of month. Suddenly I am getting the following error message when try to execute this command

Select * from dbo.Split('123,25')

Msg 6507, Level 16, State 2, Procedure Split, Line 1

Failed to open malformed assembly 'mscorlib' with HRESULT 0x80070008.

Please Guide

Thanks

View 1 Replies View Related

SSIS Package Configuration File Malformed

Jan 24, 2008

Can someone identify what is wrong with the XML config file that I i'm using with a SSIS package? The XML file was created by Visual Studio tool




Code Snippet
<DTSConfiguration>
<DTSConfigurationHeading>
<DTSConfigurationFileInfo GeneratedBy="BRIDGENETjpacella" GeneratedFromPackageName="SsisEndBatch" GeneratedFromPackageID="{91A1941E-E066-47F7-8DD4-9E260F38741D}" GeneratedDate="1/21/2008 4:35:22 PM" />
</DTSConfigurationHeading>
<Configuration ConfiguredType="Property" Path="Package.Connections[CHI-DEV02.BNETDB2].Properties[InitialCatalog]" ValueType="String">
<ConfiguredValue>BNETDB2IMPORTTEST</ConfiguredValue>
</Configuration>
<Configuration ConfiguredType="Property" Path="Package.Connections[CHI-DEV02.BNETDB2].Properties[ServerName]" ValueType="String">
<ConfiguredValue>CHI-DEV02</ConfiguredValue>
</Configuration>
<Configuration ConfiguredType="Property" Path="Package.Connections[CHI-PROD01.BNETDB2].Properties[InitialCatalog]" ValueType="String">
<ConfiguredValue>BNETDB2</ConfiguredValue>
</Configuration>
<Configuration ConfiguredType="Property" Path="Package.Connections[CHI-PROD01.BNETDB2].Properties[ServerName]" ValueType="String">
<ConfiguredValue>CHI-FILE01</ConfiguredValue>
</Configuration>
<Configuration ConfiguredType="Property" Path="PackageRun Report Executor.Properties[Executable]" ValueType="String">
<ConfiguredValue>C:ReportsReportProgramCHI_FILEBridgeNet.Reporting.ExecuteConsole.exe</ConfiguredValue>
</Configuration>
<Configuration ConfiguredType="Property" Path="PackageRun Report Executor.Properties[WorkingDirectory]" ValueType="String">
<ConfiguredValue>C:ReportsReportProgramCHI_FILE</ConfiguredValue>
</Configuration>
</DTSConfiguration>





When I run the pacakge, this is what happens:


Started: 10:48:56 AM
Info: 2008-01-24 10:49:02.92
Code: 0x40016041
Source: SsisEndBatch
Description: The package is attempting to configure from the XML file "C:Program Files (x86)Microsoft SQL Server90DTSPackagesBridgeNet.BI.SSIS.EndBatc
ENDBATCH TEST ENV.dtsConfig".
End Info
Warning: 2008-01-24 10:49:03.15
Code: 0x80012011
Source: SsisEndBatch
Description: Cannot load the XML configuration file. The XML configuration file may be malformed or not valid.
End Warning


View 6 Replies View Related

Correlated Subquery And Date Filtering Problem

Oct 6, 2006

Note the following sql query. It contains two separate queries, an correlated subquery and outer query to work against the results of the subquery. Its purpose is twofold (1) get the TOP n ranked field entities using a certain value, (2) return all records for those entities.

SELECT MasterLoanID, NoteNumber, LendingOfficer,OriginalAmount, ReviewSampling FROM MasterLoanData WHERE Import_AsOfDate = '2006-05-31' AND BankID = '1' AND clientID = 1 AND LendingOfficer IN(SELECT TOP 3 LendingOfficer FROM MasterLoanData WHERE Import_AsofDate = '2006-05-31' AND ClientID = 1 AND BankID = '1' GROUP BY LendingOfficer ORDER BY SUM(OriginalAmount) DESC) ORDER BY LendingOfficer, Notenumber

Note that both queries need to filter the same fields -- import_AsofDate, BankID, and ClientID -- in order produce accurate results. Separate indexes exist for all three fields. Both queries work against the Sql Express database, however, when I combine them in a sql statement, Sql Express seems totally lost -- the query runs but never finishes -- I have to abort execution!

I've isolated the problem down to referencing of the import_AsofDate field in the outer query WHERE clause. If I remove that field reference from the outer WHERE clause, the query works quickly -- in seconds, however the results aren't accurate because I'm not getting a filter against the correct Import_AsofDate value. Note too that the same Access database executes the same query in seconds! Sql Express just seems totally confused by date reference contained in both WHERE clauses. I'd say that signifies a definite problem in Sql Express.

Has anyone experienced a similar problem with a correlated subquery and the same date field being referenced in both WHERE clauses of each query? I don't currently have a full fledged Sql Server database to test this query against, but seems as though it should work.

Rick

View 7 Replies View Related

Failed To Open Malformed Assembly 'MyAssembly' With HRESULT 0x80040154

Mar 30, 2007

Hello,

I wrote SQL Stored Procedures in C# and I am trying to register the asembly with SQL Server 2005 (Express Edition) using CREATE ASSEMBLY statment. I get this following error.



"Failed to open malformed assembly 'MyAssembly' with HRESULT 0x80040154"



Does anyone know how to solve this problem? It is driving me nuts for the last few days. I did enable CLR for SQL Server and also tried re-install SQL Server, but still I get this error.



Thanks in Advance,

Ravi



View 6 Replies View Related

Correlated Subquery Column Referencing Outer Date Range

Aug 25, 2006







Any ideas how can I pass date range values from the where clause of an outer query to the inner correlated subquery ... without using a stored procedure because I am using Report Builder?

Using the simplified sql below I need the average freight charge between the dates for all of the ShipCountry's orders. (I have hard coded the dates for demo purposes only as it is these that I need referenced from the outer query's where clause.)

select
OrderDate,
ShipCountry,
ShipCity,
Freight,
/* how do I get to the outer query's date range ? */
(SELECT AVG(Freight) FROM Orders WHERE ShipCountry = O.ShipCountry AND OrderDate between '01-jan-1997' and '01-jan-2000') AS CountryAverageFreight
from
Northwind.dbo.Orders O
where
ShipCity = 'Paris' and OrderDate between '01-jan-1997' and '01-jan-2000'


Thanks

View 9 Replies View Related

Transact SQL :: Convert Server Date MM/DD/CCYY To Oracle Date Formatted As NUMBER (8,0)

Apr 30, 2015

So I have to build dynamic T-SQL because of a date parameter that will be provided. The Date Parameter will be provided in SSRS in normal MM/DD/CCYY format. So how do I then convert that date to my Oracle format

NUMERIC(8,0) CCYYMMDD?

I tried this...

SET@SQLQuery=@SQLQuery+'ANDMEMBER_SPAN.YMDEFF<='''''+CAST(@AsOfDateASVARCHAR)+''''''+@NewLineChar;
SET@SQLQuery=@SQLQuery+'ANDMEMBER_SPAN.YMDEFF>='''''+CAST(@AsOfDateASVARCHAR)+''''''+@NewLineChar;

but that put it in the format of...

AND
MEMBER_SPAN.YMDEFF<=''2015-04-01''
AND
MEMBER_SPAN.YMDEFF>=''2015-04-01''

Which is close...I think I just need to lose the "-"

View 5 Replies View Related

Transact SQL :: Due Date - Conversion Failed When Converting Date And / Or Time From Character String

Nov 16, 2015

SELECT * ,[Due]
  FROM [Events]
 Where Due >= getdate() +90

This returns the error: Conversion failed when converting date and/or time from character string

Why would this be? How to cast or convert this so that it will work? 

View 24 Replies View Related

Transact SQL :: Selecting Latest Date But Over Another Date Column Including Nulls?

Nov 26, 2015

CREATE TABLE #Dateissue
(ID int,
Code nvarchar (20),
Datein datetime,
Declined datetime )

[Code] .....

I have a table here.  I want  find a way of getting the latest date, when the code is the same.  If the Declined date is null.  Then I still want the latest date.  E.g. ID 3.  

If the declined date is filled in.  Then I want to get the row, when the Datein column value is greater then the declined date only.

I tried grouping it by max date, but   i got an error message when trying this out.  Against the code  

WHERE MAX(Datein) > Declined

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.  What do I need to do to get both my outputs working? 

View 9 Replies View Related

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

Transact SQL :: How To Select Rows From Table Where DATE Column Is Today's Date

Aug 31, 2015

So my data column [EODPosting].[MatchDate] is defined as a DATE column. I am trying to SELECT from my table where [EODPosting].[MatchDate] is today's date.

SELECT*
FROM[dbo].[EODPosting]
WHERE[EODPosting].[MatchDate]=GETDATE()

Is this not working because GETDATE() is like a timestamp format? How can I get this to work to return those rows where [EODPosting].[MatchDate] is equal to today's date?

View 2 Replies View Related

SQL Server 2014 :: Passing Malformed Email Address To Send Mail Task?

Feb 5, 2015

I am implementing a fax solution (Right Fax) and pulling email information from a table and passing into a variable. In order to send out a fax via email, the syntax is in the following example below:

Example:

To send a fax from Outlook to Jane Doe at 555-1212, enter:

[RFAX:Jane Doe@/FN=555-1212]

When the following syntax gets passed into the Send Mail Task, into the "To" line and the package is executed I'm receiving the following.

[Send Mail Task] Error: An error occurred with the following error message: "The specified string is not in the form required for an e-mail address.".

[Send Mail Task] Warning: The address in the "To" line is malformed. It is either missing the "@" symbol or is not valid.

I realize it’s a malformed email address. Is it possible to create a group and instead of the fax syntax pass the group name?

View 8 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

Transact SQL :: How To Write A Query To Get Current Date Or End Of Month Date

Sep 29, 2015

how to write a query to get current date or end of month date if we pass year and month as input

Eg: if today date is 2015-09-29
if we pass year =2015 and month=09 then we have to get 2015-09-29
if we pass year =2015 and month=08 then we have to get 2015-08-31(for previous months we have to get EOMonth date & for current month we have to get current date).

View 3 Replies View Related

Subquery Returned More Than 1 Value. This Is Not Permitted When The Subquery Follows =, !=, &&<, &&<= , &&>, &&>= Or When The Subquery I

Mar 6, 2008

I am getting an error as

Msg 512, Level 16, State 1, Line 1

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

while running the following query.





SELECT DISTINCT EmployeeDetails.FirstName+' '+EmployeeDetails.LastName AS EmpName,

LUP_FIX_DeptDetails.DeptName AS CurrentDepartment,

LUP_FIX_DesigDetails.DesigName AS CurrentDesignation,

LUP_FIX_ProjectDetails.ProjectName AS CurrentProject,

ManagerName=(SELECT E.FirstName+' '+E.LastName

FROM EmployeeDetails E

INNER JOIN LUP_EmpProject

ON E.Empid=LUP_EmpProject.Empid

INNER JOIN LUP_FIX_ProjectDetails

ON LUP_EmpProject.Projectid = LUP_FIX_ProjectDetails.Projectid

WHERE LUP_FIX_ProjectDetails.Managerid = E.Empid)



FROM EmployeeDetails

INNER JOIN LUP_EmpDepartment

ON EmployeeDetails.Empid=LUP_EmpDepartment.Empid

INNER JOIN LUP_FIX_DeptDetails

ON LUP_EmpDepartment.Deptid=LUP_FIX_DeptDetails.Deptid

AND LUP_EmpDepartment.Date=(SELECT TOP 1 LUP_EmpDepartment.Date

FROM LUP_EmpDepartment

WHERE EmployeeDetails.Empid=LUP_EmpDepartment.Empid

ORDER BY LUP_EmpDepartment.Date DESC)

INNER JOIN LUP_EmpDesignation

ON EmployeeDetails.Empid=LUP_EmpDesignation.Empid

INNER JOIN LUP_FIX_DesigDetails

ON LUP_EmpDesignation.Desigid=LUP_FIX_DesigDetails.Desigid

AND LUP_EmpDesignation.Date=(SELECT TOP 1 LUP_EmpDesignation.Date

FROM LUP_EmpDesignation

WHERE EmployeeDetails.Empid=LUP_EmpDesignation.Empid

ORDER BY LUP_EmpDesignation.Date DESC)

INNER JOIN LUP_EmpProject

ON EmployeeDetails.Empid=LUP_EmpProject.Empid

AND LUP_EmpProject.StartDate=(SELECT TOP 1 LUP_EmpProject.StartDate

FROM LUP_EmpProject

WHERE EmployeeDetails.Empid=LUP_EmpProject.Empid

ORDER BY LUP_EmpProject.StartDate DESC)

INNER JOIN LUP_FIX_ProjectDetails

ON LUP_EmpProject.Projectid=LUP_FIX_ProjectDetails.Projectid



WHERE EmployeeDetails.Empid=1

PLEASE HELP.................

View 1 Replies View Related







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