Comments by sk. maqdoom ali (Top 86 by date)

using u r query , i am getting
Package_ID Package_Name [Level] Description Main_Package_Id
5 Project 1A 2 Project 1B NULL
5 Project 1A 2 Project 1B NULL

i modified that query as

select p1.* from Package_Details p left join Package_Details p1
on p.Package_ID=p1.Package_ID where p.Main_Package_Id in (5,13)
and getting result as
Package_ID Package_Name [Level] Description Main_Package_Id
6 PKG-1 3 Package 1 5
7 PKG-2 3 Package 2 5
8 PKG-3 3 Package 3 13
9 PKG-4 3 Package 4 13

but here
5 Project 1A 2 Project 1B NULL
13 Project 1B 2 Project 2B NULL
are missing

select * from Package_Details where Main_Package_Id is null or Main_Package_Id in (5,13)
with this query i am getting the same result as
Package_ID Package_Name [Level] Description Main_Package_Id
5 Project 1A 2 Project 1B NULL
6 PKG-1 3 Package 1 5
7 PKG-2 3 Package 2 5
8 PKG-3 3 Package 3 13
9 PKG-4 3 Package 4 13
13 Project 1B 2 Project 2B NULL
but i want as
5 Project 1A 2 Project 1B NULL
6 PKG-1 3 Package 1 5
7 PKG-2 3 Package 2 5
similarly
13 Project 1B 2 Project 2B NULL
8 PKG-3 3 Package 3 13
9 PKG-4 3 Package 4 13

sorry ,,i posted this question here for u ,
there is no date column here,
i mentioned the all requirement that i want

hi, i am having a table PackageDetails
having data as below
Package_ID Package_Name [Level] Description Main_Package_Id
5 Project 1A 2 Project 1B NULL
6 PKG-1 3 Package 1 5
7 PKG-2 3 Package 2 5
8 PKG-3 3 Package 3 13
9 PKG-4 3 Package 4 13
13 Project 1B 2 Project 2B NULL
14 Project 2 2 Project 2 NULL
12 PKG-7 3 Package 7 14
---- and so on
from the above table data 5,13 are Main_Package_Id's so we are having NULL value in that Main_Package_Id column. Package_ID's 6,7 are sub_packages of 5, similarly Package_ID's 8,9 are are sub_packages of 13.

now i want the output in below format:
Package_ID Package_Name [Level] Description Main_Package_Id
5 Project 1A 2 Project 1B NULL
6 PKG-1 3 Package 1 5
7 PKG-2 3 Package 2 5
13 Project 1B 2 Project 2B NULL
8 PKG-3 3 Package 3 13
9 PKG-4 3 Package 4 13
?????
??

1.we can write SELECT DATEADD(ADate,INTERVAL -1 DAY)
ex:2013-01-29 15:34:16
2.TIME_FORMAT(ADate,'%H:%i:%s') gives result as 15:34:16
and i want to check 15:34:16 > 6:00:00
the above two will be check in the single query

hi,

for my application i will pass the input Date and

i want the result as the day before input Date and also check
TIME_FORMAT(ADate,'%H:%i:%s') >'6:00:00' from the table data.

happy weekend

thank u

hi how r u??

busy??

hi,
shall we declare
output return value in the returns clause in functions??

hi , gd mrng

hmm....

thank u,,

happy weekend

i have 8 months experience

i joined as a DBA
i will work on database only

i am sql developer , Hyderabad

madam

in which organization u r working??

in which organization u r working??

its working fine just now i explained this to my LEAD

thanks a lot madam
u helped me a lot:
thank u

select dbo.Check_MemberIDCombination('41,51') as Msg

the above '41,51' is already existed in the table

but here i am getting the Msg as 'Available' instead of 'Already Exists'

id strings
18 39,39,39,39,51
102 41,51
113 41,116
155 39,217
793 150,150,150,150

i am writing the whole in Add your solution here
please check it,

hello

i think i am clear??

what you send queries before are very useful to me from those two as i am mentioning below

Alter Function [dbo].[fn_Splitter] (@IDs Varchar(100))
Returns @Tbl_IDs Table (ID Int) As
Begin
-- Append comma
Set @IDs = @IDs + ','
-- Indexes to keep the position of searching
Declare @Pos1 Int
Declare @pos2 Int
-- Start from first character
Set @Pos1=1
Set @Pos2=1
While @Pos1

I am sorry for that actually the problem is with my LEAD.like this for every time he is asking me to do in different way without giving me proper requirement.

how much experience do u have in db??

madam shall i ask one qsn??

my requirement is
i have to convert string data values which passed as input into ascending order
and
the data which is return from the following query
select ID, substring(Applicant_Member_Ids,1,len(Applicant_Member_Ids)-1) from
(
select ID ,
(select convert(varchar, Applicant_Member_Id) + ',' as [text()] from Savings_Account_Applicant where ID =saa.ID order by Applicant_Member_Id for xml path('')) as Applicant_Member_Ids
from Savings_Account_Applicant saa
group by ID
) as Savings_Account ORDER BY ID

and compare both of this if any combination is existed then 'Already Exists' else 'Available'

using this [dbo].[fn_Splitter] function shall we split the data into ascending order as input??
ex:24,35,78,45
into
24,35,45,78
to give input to [dbo].[Check_MemberIDCombination]

its just

While @Pos1

For the execution of dbo.[fn_Splitter]

it shows an error for the below line

While @Pos1<len(@ids)>

Msg 102, Level 15, State 1, Procedure fn_Splitter, Line 12
Incorrect syntax near '>'.

ALTER FUNCTION [dbo].[Check_MemberIDCombination]
(
@Ids VARCHAR(1000)
)
RETURNS VARCHAR(20)
AS
BEGIN

DECLARE @Msg VARCHAR(20);
declare @count int

DECLARE @Computed table(id nvarchar(1000),Member_Id nvarchar(1000))
insert into @computed
select ID, substring(Applicant_Member_Ids,1,len(Applicant_Member_Ids)-1) from
(
select ID ,
(select convert(varchar, Applicant_Member_Id) + ',' as [text()] from Savings_Account_Applicant where ID =saa.ID order by Applicant_Member_Id for xml path('')) as Applicant_Member_Ids
from Savings_Account_Applicant saa
group by ID
) as Savings_Account ORDER BY ID

set @count = ( select count(*) from @Computed where Member_Id=@Ids)

if @count>0
begin

SET @Msg = 'Already Exists'
end
ELSE
begin
SET @Msg = 'Available'

END
RETURN @Msg
end

hi, using u r query i written the following function that checks where the input given is existed in that computed table or not. if it exists it returns 'Already Exists' else 'Available'.
But here i will pass input @Ids in any order ex:@Ids = '41,56,87,34'
from the above ex: input @Ids = '41,56,87,34' the string must be taken input in ascending order ie;'34,41,56,87'.
so how we can convert the @Ids value in ascending order in that function.

Thank u Madam

but here one problem is i already inserted ID Applicant_Member_id 18 39,39,39,51,39 102 41,51 155 39,217 now again i am giving 51,41 for any ID then this must not be display as 'Notexists' becoz the 51,41 is already existed. here we changed the order only not numbers. how can we come across this problem

i used this function for checking the combination of existed values but its is not checking if the combination is reversed etc CREATE FUNCTION [dbo].[Check_MemberIDCombination] ( @Ids VARCHAR(1000) ) RETURNS VARCHAR(20) AS BEGIN DECLARE @Msg VARCHAR(20); IF EXISTS ( SELECT 1 FROM ( SELECT ID,STUFF(( SELECT ',' + CAST(Applicant_Member_Id AS VARCHAR) FROM Savings_Account_Applicant WHERE ID = A.Id FOR XML PATH('') ),1,1,'')Ids FROM Savings_Account_Applicant A GROUP BY ID ) A WHERE Ids = @Ids ) SET @Msg = 'Already Exists' ELSE SET @Msg = 'Available' RETURN @Msg END

i used this function for checking the combination of existed values but its is not checking if the combination is reversed etc

CREATE FUNCTION [dbo].[Check_MemberIDCombination]
(
@Ids VARCHAR(1000)
)
RETURNS VARCHAR(20)
AS
BEGIN

DECLARE @Msg VARCHAR(20);

IF EXISTS ( SELECT 1 FROM (
SELECT ID,STUFF(( SELECT ',' + CAST(Applicant_Member_Id AS VARCHAR) FROM Savings_Account_Applicant
WHERE ID = A.Id FOR XML PATH('') ),1,1,'')Ids
FROM Savings_Account_Applicant A GROUP BY ID ) A WHERE Ids = @Ids )


SET @Msg = 'Already Exists'

ELSE

SET @Msg = 'Available'

RETURN @Msg
END

sir , how can we check if there is any existed value in combination for this

thank u , but here one problem is
i already inserted
ID Applicant_Member_id
18 39,39,39,51,39
102 41,51
155 39,217

now again i am giving 51,41 for any ID then this must not be display as 'Notexists'
becoz the 51,41 is already existed.
here we changed the order only not numbers.
how can we come across this problem

initially they are inserted the data like that but now we are inserting at a time more than two values , in that values we have to check any combination is existed for a any ID of a table:
i written the below query that return data
select ID, substring(Applicant_Member_Ids,1,len(Applicant_Member_Ids)-1) from
(
select ID ,
(select convert(varchar, Applicant_Member_Id) + ',' as [text()] from Savings_Account_Applicant where ID =saa.ID for xml path('')) as Applicant_Member_Ids
from Savings_Account_Applicant saa
group by ID
) as Savings_Account

from this above query i separated
ID Applicant_Member_id
18 39,39,39,51,39
102 41,51
155 39,217

for my application i have give 2 or more values to check for existence in ID column

in my table Applicant_member_Id is a column having only a single value for particular ID
ex:101 41
102 42
103 41
- -
- -

yes sir , Applicant_member_Id is a string containing comma separated value
ex:input=41,42,43,---
i have to check any ID in my table having this combination 41,42,43
if this type of combination is existed then dislay msg as 'Already Existed'
else we can insert

tell me sir, how can i check this combination of values based on ID in a table??

yes, u r correct
i have a table with ID , Applicant_member_Id columns
when we are inserting one or more Applicant_member_Id's for ex:39,41,116 as input then i have to check entire this combination 39,41,116 is already existed for any particular ID in the table. if the combination is existed then we display "Already Existed' else 'available'

if when we are inserting one or more Applicant_member_Id's
for ex:39,41,116 as input then ??
i have to check entire this combination 39,41,116 is already existed for any particular ID in the table.
if the combination is existed then we display "Already Existed' else 'available'

yes ,
it return 0 then it can be added else Applicant_member_Id have already included in a join account no. is 101 you can not add it in another join account 102

ex: i have a joint account id =101 having
Applicant_member_Id=10,11,12
now i am giving Applicant_member_Id = 10 as input i have to check that 10 is existed or not , like this i will give 11 as input i have to check that 11 is existed or not ,
if it existed i will return 1 else return 2
here the condition is no two or more same combination of Applicant_member_Id's
is not valid for another JointAccount. i have to check this condition before insertion

i written this query but i am getting the Row_Number which shows invidually not by combination ,
SELECT
ROW_NUMBER() over(order by id) AS Row, Applicant_Member_Id ,ID
FROM Savings_Account_Applicant where Applicant_Member_Id in(39,51)

thank u,
For my banking application i will pass many number of Applicant_Member_Id 's and my requirement is if i selected 41,42 for one JointAccount then for another JointAccount i will restrict the same combination which i have given before ie,41,42
i can use 41,43 or 42,43 like this.
i want to restrict the combination which is already repeated

using this SELECT SUBSTRING(
(SELECT ',' + CONVERT(varchar, Applicant_Member_Id) FROM Savings_Account_Applicant SAA,Savings_Account SA WHERE SA.ID=SAA.ID AND SA.Account_Operation_Type = 2
FOR XML PATH('')),2,100000) AS CSV
i am getting array of values 41,46,46,41,41,41,41,39,38,202,202,205,205,150,150,150,150,41,41,187

but in my procedure i will give only one value as input ie,id=41 for this i want to check the id value is existed in that array or not

yes, i want to fetch the combination

yes, i want output like this
id grp_id grp_nm
948 3 fixed
948 2 liabilities
948 1 assets

it will continue from 0002 to 9999 when every insertion is made

i have to insert a field which increments by 1 in a table,
the value starts from 0001
in a procedure i am getting only 0001 by writing
SET @Number = @Number + 1

sir ,
give me any idea

here Autogenerate_Id is one of my column name thats why its giving output when executing separately
(SELECT TOP 1 Autogenerate_ID FROM Document_Management DM WHERE ID = (SELECT MAX(ID) FROM Document_Management WHERE Document_Type = @Document_Type))
output:A0001

A0001

yes, u r correct , but when i am executing this seperately i am getting the value.
for ex:A0001

(SELECT TOP 1 Autogenerate_ID FROM Document_Management DM WHERE ID = (SELECT MAX(ID) FROM Document_Management WHERE Document_Type = @Document_Type))

is that A0001 will not generate automatically??? i just pass MR as my input
i want the result enter into table as MRA0001

there is system defined Tempdb is there we can store it there??

i compared like this and got the required solution

GETDATE() < = Valid_From_Date AND GETDATE() !> Valid_To_Date

sir, i updated it like this

ALTER PROCEDURE [dbo].[sp_CSP_SaveAccount_Closure_Temp_Table]

@Account_Id INT=NULL,
@Application_Ref_No NVARCHAR(20),
@Balance_Repayable FLOAT=NULL,
@Closing_Type TINYINT=NULL,
@Closing_Interest_Rate FLOAT=NULL,
@Source_Funds TINYINT=NULL,
@Reason_Balance_Transfer TINYINT=NULL,
@ROI_OfferedBy_Bank FLOAT=NULL,
@Reason_Balance_Transfer_Others NVarchar(150)=NULL,
@CheckBook_Status TINYINT=NULL,
@ATMCard_Status TINYINT=NULL,
@Reason_Closure NVARCHAR(150),
@User_ID INT=NULL,
@IP_Address NVARCHAR(15)

AS
BEGIN

IF OBJECT_ID('tempdb..##Closure_Copy') IS NULL
BEGIN
CREATE TABLE ##Closure_Copy(

[Account_Id] [int] NULL,
[Application_Ref_No] [nvarchar](20) NULL,
[Balance_Repayable] [float] NULL,
[Closing_Type] [tinyint] NULL,
[Closing_Interest_Rate] [float] NULL,
[Source_Funds] [tinyint] NULL,
[Reason_Balance_Transfer] [tinyint] NULL,
[ROI_OfferedBy_Bank] [float] NULL,
[Reason_Balance_Transfer_Others] [nvarchar](150) NULL,
[CheckBook_Status] [tinyint] NULL,
[ATMCard_Status] [tinyint] NULL,


[Reason_Closure] [text] NULL,
[User_ID] [int] NULL,
[IP_Address] [nvarchar](15) NULL,
[Date_Time] [datetime] NULL,
)

INSERT INTO ##Closure_Copy(
Account_Id,
Application_Ref_No,
Balance_Repayable ,
Closing_Type ,
Closing_Interest_Rate,
Source_Funds ,
Reason_Balance_Transfer ,
ROI_OfferedBy_Bank ,
Reason_Balance_Transfer_Others ,
CheckBook_Status,
ATMCard_Status,
Reason_Closure,
[User_ID],
IP_Address,
Date_Time

)
values(@Account_Id,
@Application_Ref_No,
@Balance_Repayable,
@Closing_Type ,
@Closing_Interest_Rate,
@Source_Funds ,
@Reason_Balance_Transfer ,
@ROI_OfferedBy_Bank ,
@Reason_Balance_Transfer_Others ,
@CheckBook_Status,
@ATMCard_Status,
@Reason_Closure,
@User_ID,
@IP_Address,
GETDATE()
)

END

ELSE
BEGIN
INSERT INTO ##Closure_Copy(
Account_Id,
Application_Ref_No,
Balance_Repayable,
Closing_Type ,
Closing_Interest_Rate,
Source_Funds ,
Reason_Balance_Transfer ,
ROI_OfferedBy_Bank ,
Reason_Balance_Transfer_Others ,
CheckBook_Status,
ATMCard_Status,
Reason_Closure,

[User_ID],
IP_Address,
Date_Time)
values(@Account_Id,
@Application_Ref_No,
@Balance_Repayable,
@Closing_Type ,
@Closing_Interest_Rate,
@Source_Funds ,
@Reason_Balance_Transfer ,
@ROI_OfferedBy_Bank ,
@Reason_Balance_Transfer_Others ,
@CheckBook_Status,
@ATMCard_Status,
@Reason_Closure,
@User_ID,
@IP_Address,
GETDATE()
)
END
END

ok sir, till now i don't know about this ,i will rectify it

no sir, i am not at all shout you , i just explained my requirement clearly thats it..... if i hurted u i am extreamly very very sorry.

I WANT TO GET RETRIVE THE DATA BEFORE THE DAY OF Valid_From_Date ex;Valid_From_Date = 12-08-2012 then for 11-08-2012 also i want to retrive the data
and also
not to retrive data Valid_To_Date which is expired before . for ex:Valid_To_Date = 12-07-2012 if we check it today this Valid_To_Date data must not be retrived

yes, and also not to retrive data Valid_To_Date which is expired before . for
ex:Valid_To_Date = 12-07-2012 if we check it today this Valid_To_Date data must not be retrived

I WANT TO GET RETRIVE THE DATA BEFORE THE DAY OF Valid_From_Date ex;Valid_From_Date = 12-08-2012 then for 11-08-2012 also i want to retrive the data

sorry sir i am not downvoted you i am trying do using this method

yes u r correct but my requirement is before closing the connection the data must be there ..means for one connection i will create one temptable only after closing the connection its not required , multiple members will enter the data but the inserted data must be entered into that existed table for that connection

from the above procedure i have a problem that for every new insertion new temporary table is created . i don't want this for every new insertion that newly inserted data must be inserted after the previous data which is inserted before by the member,,,,,,,,,,,, the temporary table must be there for every insertion

please tell me solution

alter PROCEDURE sp_CSP_SaveTemp_Table
@ID INT,
@Account_Id INT=NULL,
@Application_Ref_No NVARCHAR(20),
@Balance_Repayable FLOAT=NULL,
@CheckBook_Status TINYINT=NULL,
@ATMCard_Status TINYINT=NULL,
@Closing_Type TINYINT=NULL,
@Closing_Interest_Rate TINYINT=NULL,
@Source_Funds TINYINT=NULL,
@Reason_Balance_Transfer TINYINT=NULL,
@ROI_OfferedBy_Bank FLOAT=NULL,
@Reason_Balance_Transfer_Others NVARCHAR(150),
@Mode_Payment TINYINT=NULL,
@Reason_Closure NVARCHAR(150),
@Is_Manager_Approved tinyint =NULL,
@Reason_Rejeted TEXT=NULL,
@User_ID INT=NULL,
@IP_Address NVARCHAR(15),
@ApprovedBy INT=NULL,

@Insertion_Approved BIT=NULL


AS
BEGIN

IF @Insertion_Approved IS NULL

BEGIN

CREATE TABLE #Closure_Copy(
[ID] [int] ,
[Account_Id] [int] NULL,
[Application_Ref_No] [nvarchar](20) NULL,
[Balance_Repayable] [float] NULL,
[CheckBook_Status] [tinyint] NULL,
[ATMCard_Status] [tinyint] NULL,
[Closing_Type] [tinyint] NULL,
[Closing_Interest_Rate] [float] NULL,
[Source_Funds] [tinyint] NULL,
[Reason_Balance_Transfer] [tinyint] NULL,
[ROI_OfferedBy_Bank] [float] NULL,
[Reason_Balance_Transfer_Others] [nvarchar](150) NULL,
[Mode_Payment] [tinyint] NULL,
[Reason_Closure] [text] NULL,
[Is_Manager_Approved] [tinyint] NULL,
[Reason_Rejeted] [text] NULL,
[User_ID] [int] NULL,
[IP_Address] [nvarchar](15) NULL,
[Date_Time] [datetime] NULL,
[ApprovedBy] [int] NULL)


INSERT INTO #Closure_Copy(ID ,
Account_Id,
Application_Ref_No,
Balance_Repayable,
CheckBook_Status,
ATMCard_Status,
Closing_Type,
Closing_Interest_Rate,
Source_Funds,
Reason_Balance_Transfer,
ROI_OfferedBy_Bank,
Reason_Balance_Transfer_Others,
Mode_Payment,
Reason_Closure,
Is_Manager_Approved,
Reason_Rejeted,
[User_ID],
IP_Address,
Date_Time,
ApprovedBy)
values(@id,@Account_Id,
@Application_Ref_No,
@Balance_Repayable,
@CheckBook_Status,
@ATMCard_Status,
@Closing_Type,
@Closing_Interest_Rate,
@Source_Funds,
@Reason_Balance_Transfer,
@ROI_OfferedBy_Bank,
@Reason_Balance_Transfer_Others,
@Mode_Payment,
@Reason_Closure,
@Is_Manager_Approved,
@Reason_Rejeted,
@User_ID,
@IP_Address,
GETDATE(),
@ApprovedBy)

END


ELSE

BEGIN

INSERT INTO Account_Closure(
Account_Id,
Application_Ref_No,
Balance_Repayable,
CheckBook_Status,
ATMCard_Status,
Closing_Type,
Closing_Interest_Rate,
Source_Funds,
Reason_Balance_Transfer,
ROI_OfferedBy_Bank,
Reason_Balance_Transfer_Others,
Mode_Payment,
Reason_Closure,
Is_Manager_Approved,
Reason_Rejeted,
[User_ID],
IP_Address,
Date_Time,
ApprovedBy)

VALUES(@Account_Id,
@Application_Ref_No,
@Balance_Repayable,
@CheckBook_Status,
@ATMCard_Status,
@Closing_Type,
@Closing_Interest_Rate,
@Source_Funds,
@Reason_Balance_Transfer,
@ROI_OfferedBy_Bank,
@Reason_Balance_Transfer_Others,
@Mode_Payment,
@Reason_Closure,
@Is_Manager_Approved,
@Reason_Rejeted,
@User_ID,
@IP_Address,
GETDATE(),
@ApprovedBy)

END
END


i am using the above procedure but after execution i am getting the result as Invalid object name '#Closure_Copy'.

yes, from admin level approval is there

ALTER TRIGGER Qno_Generation
ON Queue_Number
AFTER INSERT
AS
BEGIN

DECLARE @ID INT
DECLARE @QID_No NVarchar(25)
DECLARE @QId NVarchar(6)
DECLARE @QNo Int = 0
DECLARE @Month Int = 0


SELECT @ID=I.ID FROM INSERTED I
SELECT @QId=I.QID FROM INSERTED I
SELECT @QNo=I.QNo FROM INSERTED I
SELECT @QID_No=I.QID_No FROM INSERTED I


SET @Month = (SELECT DATEPART(MONTH,GETDATE()))

IF @Month > 3
BEGIN
SET @QId = (SELECT (CONVERT(NVARCHAR(4),DATEPART(Year,GETDATE())) + CONVERT(NVarchar(2),RIGHT((DATEPART(Year,DATEADD(Year,1,GETDATE()))),2))))
END

SET @QNo = (SELECT count(QNo) FROM Queue_Number WHERE QID = @QID)

SET @QNo = @QNo + 1

SET @QID_No = @QId + (SELECT (CONVERT(NVarchar(10),@QNo)))

update Queue_Number set QID=@QID,
QNo=@QNo,
QID_No=@QID_No

WHERE ID=@ID

END

yes , i updated it

i am externally inserting these two values Queue_Type, Reference_ID

i want after insertion of Queue_Type, Reference_ID fields the trigger must be fire and insert the computed date into QID,QNo,QID_No fields

i want to insert 5 fields into the table , in that the three fields QID,QNo,QID_No are automatically inserted when i insert another another two fields ie, Queue_Type, Reference_ID .
when i am inserting these two field values externally ie, insert into table(Queue_Type, Reference_ID)values(1,12)
after doing this the insertion the inserted values in the table as
for Queue_Type, Reference_ID the values are inserted in one row but the remaining fields QID,QNo,QID_No are inserted in another row.

i want to insert all the fields in a single row only

i want to insert 5 fields into the table , in that the three fields QID,QNo,QID_No are automatically inserted when i insert another another two fields ie, Queue_Type, Reference_ID .
when i am inserting these two field values externally ie, insert into table(Queue_Type, Reference_ID)values(1,12)
after doing this the insertion the inserted values in the table as
for Queue_Type, Reference_ID the values are inserted in one row but the remaining fields QID,QNo,QID_No are inserted in another row.

i want to insert all the fields in a single row only