Click here to Skip to main content
14,641,178 members

Comments by Santosh kumar Pithani (Top 200 by date)

Santosh kumar Pithani 30-Jan-20 7:01am View
Hello,your updating all records for "ReportTable" based on updated "SellTable" columns, i hope there is no addition queries its should be complete in single line
Santosh kumar Pithani 4-Sep-19 0:18am View
Try to remove duplicates by using Rownumber function on (ClassID,FeePartID) still your not clear then its better to post Dummy data along with expected output.
Santosh kumar Pithani 3-Sep-19 5:46am View
Hello nyt72, your simply updating all records without maintaining proper relation on derived and updated table, try to use merge for these type of task.
Santosh kumar Pithani 26-Aug-19 9:04am View
Hello, i hope this problem will be overcome by using "on update cascade" foreign key reference and also write one stored procedure for updating old value to other column.
Santosh kumar Pithani 30-May-19 9:39am View
CREATE TABLE #Test ( SubHeading_Name VARCHAR(500),Pointer_Id int,JV VARCHAR(100),JAN INT,FEB INT);
('Preparation & Planning',1,'JV1',4,2)
,('Preparation & Planning',2,'JV1',3,3)
,('Preparation & Planning',3,'JV1',2,2)
,('Preparation & Planning',4,'JV1',4,1)
,('Preparation & Planning',1,'JV2',4,2)
,('Preparation & Planning',2,'JV2',3,3)
,('Preparation & Planning',3,'JV2',2,2)
,('Preparation & Planning',4,'JV2',4,1)

MAX(case WHEN JV='JV1' THEN CAST(JAN AS Varchar(10)) END)+' '+
MAX(case WHEN JV='JV1' THEN CAST(FEB AS Varchar(10)) END)+' '+
FROM #test GROUP BY SubHeading_Name,Pointer_Id
Santosh kumar Pithani 29-May-19 6:24am View
Hello,your IF condition declare is wrong i.e instead "Else" you given "GO" so how it works?show me your Error Message
Santosh kumar Pithani 20-May-19 5:45am View
If case condition true then it returns InsertedDate , its fine but if condition fails what record should return? Default it returns null values for your understand i given null.
Santosh kumar Pithani 20-May-19 3:16am View
Use your case condition should be "
CASE WHEN LV.TypeName LIKE '%NOTES% AND ND.Row_number=1
null END
Santosh kumar Pithani 17-May-19 1:20am View
There is no need to use While loop instead use Row_number function and case condition in select..
Santosh kumar Pithani 24-Apr-19 9:49am View
If you need to filter only fraction('/') values then use charindex('/',columnName)<>0
Santosh kumar Pithani 17-Apr-19 10:29am View
No need to use triggers its a performance issue use "OUTPUT " cla...
Santosh kumar Pithani 29-Mar-19 5:13am View
Hello,please give one sample records and expected output then its very east to understand.
Santosh kumar Pithani 28-Mar-19 8:15am View
If you know difference between Char,NChar and varchar,Nvarchar then its easy to understand.Some datatypes designed to support especial characters like you question.

SELECT N'Hi friends😘', N'Hi friends😘';

Santosh kumar Pithani 18-Mar-19 9:42am View
Yes chill60,your right i will update my solution..Thank u so much
Santosh kumar Pithani 18-Mar-19 9:21am View
There is no need to use triggers instead use output clau..

CREATE TRIGGER trgrBeforeUpdate on tblEmpDetail
For Update
---Newly inserted value
tblEmpHistoryDetail (MasterID,EmpName,Dob)
select ID,EmpName,Dob from inserted;
---Deleted Old value
tblEmpHistoryDetail (MasterID,EmpName,Dob)
select ID,EmpName,Dob from deleted;
Santosh kumar Pithani 19-Feb-19 3:18am View
--Im not Adding filter condition just sample query no need to group by
SELECT a.*, Count(1)over(partition by a.[Customer_LastName],
a.[Customer_HomePhone] ORDER BY ISNULL(CreatedOn,ModifiedOn)) AS rn
FROM MRSVoid.dbo.Customer_Dataset$ a )
select Distinct * from CTE WHERE rn>=2
Santosh kumar Pithani 19-Feb-19 1:33am View
Use row_number() rank function AS ROw_number()over(partition by [Customer_LastName],
[Customer_HomePhone] ORDER BY ISNULL(CreatedOn,ModifiedOn)) AS rn " in your query and later filter with "where rn>=2"
Santosh kumar Pithani 19-Feb-19 0:10am View
Variable returns only single values not table records.Find my solution
select [result],CASE WHEN [result]=@@ROWCOUNT THEN ' Success ' ELSE 'Failed' END AS [message]
from (values(0),(1)) AS tab([result])
Santosh kumar Pithani 18-Feb-19 23:34pm View
Writing query is not problem but keep sample data.Below query will help you ..all the best.

select row_number()OVER(Partition by EmailAddress ORDER BY ISNULL(ModifiedOn,CreatedOn) DESC) AS RN ,* FROM Table_NAME

WHERE ([FullmailingAddress] IS NOT NULL AND [LastName]=@LastName AND [FirstName] LIKE @FirstName+'%') AND
([EmailAddress]=@EmailAddress OR [FullmailingAddress]=@FullmailingAddress OR [PhoneNumber]=@PhoneNumber])


select * FROM CTE WHERE RN=1;
Santosh kumar Pithani 15-Feb-19 23:36pm View
Santosh kumar Pithani 15-Feb-19 7:42am View
DO you have any primary key column ? your query is wrong its returns M*N records
Santosh kumar Pithani 14-Feb-19 8:31am View
Show me error message.
Santosh kumar Pithani 14-Feb-19 8:00am View
Do group by on your query.In your query "ISNULL( case condition,'') " is using its means each and every records will fetch but "fa_ComponentMaster.Cm_ADTag" Column is splitting to 4 columns so unable to get what you expected.
Santosh kumar Pithani 10-Feb-19 23:31pm View
If you afraid to use string split then take table type parameter for that its a perfect solution.

Create Type Temp as Table(Barcode Varchar(100));
Insert parameters(1,ean,9789830093819) into Table Type from application side .
Pass this Table type as Stored Procedure parameter.

Declare @Temp Temp ;

,ISNULL(Bookcode,'Bookcode not found') AS Bookcode

FROM @Temp AS Stv LEFT JOIN #InventoryCustomer cte
ON( Stv.value=Cte.Barcode)

Santosh kumar Pithani 8-Feb-19 8:57am View
What is the use to post same solution in comment?
Santosh kumar Pithani 8-Feb-19 4:05am View
Are you using SQL Server 2016?
Santosh kumar Pithani 8-Feb-19 3:39am View
Any sample data and expected output?
Santosh kumar Pithani 1-Feb-19 8:18am View
If it is correct then mark as solution.
Santosh kumar Pithani 1-Feb-19 3:29am View
I think day(27) is repeating more than one times check and make sure with
query "select @cols"
Santosh kumar Pithani 31-Jan-19 23:43pm View
Post your error msg;have you executed solution query?I clearly mentioned two columns("Status,Status AS New_status") and how to use them for pivot(PIVOT (MAX(Status) FOR DDate IN (' + @cols + ')) AS stat

PIVOT(COUNT(New_status) FOR New_status IN ([P],[CL],[A])) AS Pvt;")
.Still your least to understand its your problem.
Santosh kumar Pithani 31-Jan-19 0:22am View
I have updated solution check and let me know!
Santosh kumar Pithani 30-Jan-19 23:54pm View
Hello @akhter , already you have used 'Status' column for pivot so you may not use for second time same column for pivot.Take extra columns as "Status AS New_Status"
use that for second pivot.This "RNO_ROWTOTAL" column not existed in your derived table.
Santosh kumar Pithani 30-Jan-19 23:32pm View
No one expects apologies from Masters.We don't know which query is right or wrong its a OP fault i.e i have given all inner joins.Otherwise my answer would be different with right joins
Santosh kumar Pithani 30-Jan-19 7:29am View
Your view "ShiftNo" has (3 rows) so its returning records as M*N ( View rows * Each records of table).There is no relation Between view and other tables so give proper relation.
Santosh kumar Pithani 25-Jan-19 1:06am View
your question and description is not matching.Try to put what u have tried so we understand what you need exactly.
Santosh kumar Pithani 22-Jan-19 8:38am View
how could we find where your query struct? provide sample data and excepted output its better to understand.
Santosh kumar Pithani 22-Jan-19 7:47am View
NVL(m1,0) takes only two parameters but coalesce(m1,m2,0) function work for more than two parameters.Give me stars
Santosh kumar Pithani 22-Jan-19 4:45am View
your query looks good;getting any error?If you found null type exception use like this "NVL(M1,0)+NVL(M2,0)"
Santosh kumar Pithani 22-Jan-19 3:43am View
How you decide my solution is not work for oracle,have you tried to execute?
Santosh kumar Pithani 21-Jan-19 5:43am View
Your question is not clear,improve your question with sample data and expected output.
Santosh kumar Pithani 21-Jan-19 5:32am View
Welcome..give me stars:-)
Santosh kumar Pithani 21-Jan-19 4:50am View
Don't insert duplicate imagepath values "C:\123\Image3.jpg" on VIN column.Maintain primary key on table.
Santosh kumar Pithani 11-Jan-19 0:26am View
Hi suresh, can you show sample queries and count of records you fetching?
Santosh kumar Pithani 9-Jan-19 8:26am View
I accepts your suggestion but many cases joins(multiple tables) and group by commands(complicated queries) used for paging and @cnt its a performance issue compared to Temporary tables.
Santosh kumar Pithani 9-Jan-19 4:59am View
I have create table for showing total records as well as count of records in table so
temporary table is used instead of CTE.We know that CTE supports only single scope execution.
Santosh kumar Pithani 5-Dec-18 9:04am View
Don't pass "[effectivedate1], [effectivedate2], [effectivedate3]" like this instead of that
values should be date like "[2018-04-12],[2018-05-28]"..
Santosh kumar Pithani 23-Nov-18 5:47am View
Then which is created the issue in your merge query.My solution is wrong?
Santosh kumar Pithani 16-Nov-18 23:15pm View
You don't know how to write merger condition and update command ?
Santosh kumar Pithani 1-Nov-18 8:22am View
SQLServer may not accept order by inside derived table without(top,xml,offset)
1."select brcd, round(sum(outamt)/1000,0) amt from cis where assetcd !='IR00001'
group by brcd order by amt desc"

2."rownum" column doesn't given inside derived table then how its possible to give as filter condition outside derived table?
Santosh kumar Pithani 1-Nov-18 4:59am View
Hi, where you stuck ;what you expecting ?
Santosh kumar Pithani 27-Sep-18 8:46am View
your expected output is not clear make sure it correct.what is the use of "BMI" column in both tables?
Santosh kumar Pithani 20-Sep-18 0:01am View
i think TVF is causing performance issue so try to put TVF code for review else apply proper index on filter of TVF code.
Santosh kumar Pithani 19-Sep-18 4:32am View
AS you notice "If I add more than one reference to the individual ID it brings up no table data"..I think you used "AND" operator to add another condition so use "OR" instead of that you can find better.
Santosh kumar Pithani 13-Sep-18 7:34am View
use inner join instead of sub query
SET @query=N'select '+(select Col_Names FROM #Temp1 WHERE ID IN (select EMPID from #Temp2 Where EMPID='999' )+' from #Temp2';
Santosh kumar Pithani 13-Sep-18 7:09am View
What its mean "I need a.DATE_TIME field to find right column name in parentheses. "?
you need to generate columns i.e TRP13,TRP14.TRP15.. these columns existed in SHIFT_TABLE table? first MARK this solution as correct
Santosh kumar Pithani 13-Sep-18 6:15am View
You have to pass like below but if both values are same then why your writing lengthy code?

SET @query=N'select '+(select Col_Names FROM #Temp1 WHERE ID IN (select EMPID from #Temp2 Where EMPID='999' )+' from #Temp2';
Santosh kumar Pithani 13-Sep-18 6:07am View
if your getting columns name from other table(#temp1) then give its id in filter condition like 'SET @query=N'select '+(select Col_Names FROM #Temp1 WHERE ID=12)+' from #Temp2' '.Here ID(12) belongs to column(EmpId).

If you want to give direct then use
Declare @column_name varchar(500)='EmpId'
SET @query=N'select '+@column_name+' from #Temp2;'
Santosh kumar Pithani 13-Sep-18 5:16am View
Can i know what is this '#Temp2.EmpId)';what you expecting from me?
Santosh kumar Pithani 12-Sep-18 6:34am View
yes, now your solution is correct so improve your solution with new query!
Santosh kumar Pithani 12-Sep-18 5:19am View
Hello ,is it possible to insert records in existed table "TargetDB.dbo.TableName " by your solution?
Santosh kumar Pithani 12-Sep-18 2:58am View
good job..
Santosh kumar Pithani 11-Sep-18 23:40pm View
your expecting new and old records from this update query only or on table ?USE CDC on table so you can find all DML records in table.
Santosh kumar Pithani 11-Sep-18 4:26am View
your question is not clear..further "I want to use these output values" does
its make sense ?
Santosh kumar Pithani 11-Sep-18 1:16am View
your expecting new and old records from this update query only or on table ?
Santosh kumar Pithani 7-Sep-18 2:53am View
give me points and Mark as a solution its will be use to others.
Santosh kumar Pithani 7-Sep-18 1:33am View
i think you may not understood simple solution, i give right solution to you problem.Execute above solution and let me know if any error!
EX:INSERT INTO Dbname.schemaname.tablename(columnnames except identity columns)
select 'columnnames except identity columns ' from Dbname.schemaname.tablename
Santosh kumar Pithani 6-Sep-18 23:56pm View
datas means records;Have you implemented solution query?put one example that what you expecting.
Santosh kumar Pithani 6-Sep-18 8:32am View
you know how to alter existed table?
Santosh kumar Pithani 6-Sep-18 2:37am View
Hi,you doesn't given second derived table reference ')' instead of ') as ff'
Santosh kumar Pithani 4-Sep-18 4:15am View
you have using DDL Trigger on auditlog table so complete information in table try to get what you need if you need any kind of query help ?
Santosh kumar Pithani 4-Sep-18 2:53am View
Before posting any sample data you must be sure about on it.
Santosh kumar Pithani 3-Sep-18 23:59pm View
Hi,there is no problem with creation_date column because of its millisecond time. sorry for late response..
Santosh kumar Pithani 31-Aug-18 7:54am View
i just asked,check my solution and let me know soon.
Santosh kumar Pithani 31-Aug-18 6:25am View
Hi, why your inserting and retrieving from same table ;using any triggers?
Santosh kumar Pithani 31-Aug-18 0:03am View
Try to learn and put what you have tried to achieve.we always help you;without you effort you don't know about code its vain to put as a solution!
Santosh kumar Pithani 29-Aug-18 8:29am View
Its your home work don't assign us..:-). Try to put what your have tried ..
Santosh kumar Pithani 29-Aug-18 0:14am View
How can we find problem in this query?Its a conversion problem make sure which datatypes are using for join condition either provide some testing data.
Santosh kumar Pithani 18-Aug-18 8:26am View
Hello , string datatypes only give under single quotation('value') not integers
Santosh kumar Pithani 17-Aug-18 5:26am View
Santosh kumar Pithani 17-Aug-18 5:22am View
what conditions?
Santosh kumar Pithani 17-Aug-18 4:55am View
AS i know single value (2726) is repeating i.e 1..n rows further your doing sum on it like (2726 * N rows in table).i suggest you to use "sum(distinct columnname)"
Santosh kumar Pithani 10-Aug-18 8:55am View
Have you executed query ?Use @ColumnNames instead of @list.
set @SQL= N'
select Time,' + @ColumnNames + ' INTO ##temp
from ...etc.After this,use if condition
Santosh kumar Pithani 10-Aug-18 5:33am View
Hello, i have updates new solution so please check and let me know.
Santosh kumar Pithani 10-Aug-18 0:28am View
Why your using "a.address" and "" for pivot column list ?
Santosh kumar Pithani 9-Aug-18 6:12am View
Welcome binu!
Santosh kumar Pithani 9-Aug-18 6:11am View
Thank you!
Santosh kumar Pithani 9-Aug-18 1:43am View
Hello, please mark this as solution so its can use for others.
Santosh kumar Pithani 9-Aug-18 0:36am View
Hello,i suggest you to use dynamic SQL queries its very easy to append SQL statements.
Santosh kumar Pithani 8-Aug-18 6:29am View
Hello,use pivot
Santosh kumar Pithani 6-Aug-18 0:14am View
Hello, i already mentioned you that integer datatype doesn't allows string values.if your using varchar or char datatypes then make sure its length i.e varchar(500),char(50).
Santosh kumar Pithani 4-Aug-18 0:57am View
why your using same columns "B.col1 = A.col1 and B.col2 = A.col2" for condition as well as update set?
Santosh kumar Pithani 3-Aug-18 3:28am View
Santosh kumar Pithani 2-Aug-18 8:25am View
Always Welcome!
Santosh kumar Pithani 2-Aug-18 1:43am View
i want to add one more thing i.e If you add columns on table but its not reflected on views so in this case you have use :exec sp_refreshview command.
Santosh kumar Pithani 2-Aug-18 1:08am View
AS i know "Go" statement is creating the issue due to "Go" in query line.
Santosh kumar Pithani 2-Aug-18 0:33am View
Hello, your done small mistake i.e..given "<\education>"
instead of "</education>" .Please check once solution query.
Santosh kumar Pithani 1-Aug-18 6:26am View
Have your executed solution query?
Santosh kumar Pithani 31-Jul-18 3:05am View
your update query is fine but check your datatypes and use OUTPUT INSERTED.qty,INSERTED.totale in your update query to checking purpose.
Santosh kumar Pithani 24-Jul-18 6:48am View
we are not ready to do your home work;try to put query in 'what i have tried' :-)
Santosh kumar Pithani 21-Jul-18 7:31am View
Hello GJSS, use inner join instead of left join in your query.please read about left join you will understood better why you got nulls.
Santosh kumar Pithani 21-Jul-18 7:30am View
Hello GJSS, use inner join instead of left join in your query.please read about left join you will understood better.
Santosh kumar Pithani 21-Jul-18 7:30am View
Hello GJSS, use inner join instead of left join in your query.please read about left join you will understood better why you got nulls.
Santosh kumar Pithani 19-Jun-18 5:08am View
How alpha bits are converted to integer? you have to think how much you have knowledge on it!
Santosh kumar Pithani 6-Jun-18 1:32am View
Hello, first you have to check column(invd.Value) datatype and then procedure what you want but no need to do convertion every time at each step.
Santosh kumar Pithani 24-May-18 2:51am View
first create table type like below EX:

CREATE TYPE [dbo].[TableType_WebTrafficSocialChannel] AS TABLE( id int identity(1,1),
[Month] [date] NULL,
[name] [varchar](max) NULL,
[Data] [int] NULL

Create proc proname( @temptable TableType_WebTrafficSocialChannel Readonly)
-- here all records are inserted into TableType_WebTrafficSocialChannel from your application

--you know how to fetch records from while loop
Santosh kumar Pithani 23-May-18 4:38am View
Hello Nishant, i think your try to insert bulk records and fetching one by one so my suggestion is use table type variable
Santosh kumar Pithani 23-May-18 4:28am View
ok, how to insert all records and fetch records from SP , i mean what you expecting from SP?
Santosh kumar Pithani 23-May-18 4:19am View
why you are inserting records in #temp table and using while?
Santosh kumar Pithani 21-May-18 2:15am View
Your question is not clear please show expected output
Santosh kumar Pithani 26-Apr-18 6:48am View
update your question with xml data as well as what you have tried i mean code of one row you fetched.
Santosh kumar Pithani 26-Apr-18 0:29am View
i think your have to add addition root tag ex:"<root>all rows xml data "
Santosh kumar Pithani 25-Apr-18 23:37pm View
Your case statement is not clear i mean more than one case condition is true.You need to write ex:"CASE
(UserPicks.Game_1 = WonLost.GameResults_1) OR
(UserPicks.Game_2 = WonLost.GameResults_2)
Santosh kumar Pithani 25-Apr-18 7:32am View
plz replace "LDD.lamId = LAM.Id" instead of "LDD.Loan_adv_no=LAM.loan_adv_no AND LDD.Emp_Id=LAM.Emp_Id"
Santosh kumar Pithani 25-Apr-18 7:02am View
you have used table names multiple times without giving proper aliases,ambiguity issues will occur.
Santosh kumar Pithani 25-Apr-18 6:24am View
If you created linked server then use "select t1.col,t2.col2 from [linkedserver1].[db].dbo.table1 AS t1 INNER JOIN [linkedserver2].[db].dbo.table2 AS t2 ON(t1.col1=t2.col1)"
Santosh kumar Pithani 24-Apr-18 8:34am View
Hello Yogi, solution is updated check it once...if okay mark it as solution.
Santosh kumar Pithani 24-Apr-18 8:11am View
Santosh kumar Pithani 24-Apr-18 7:06am View
Ok, Use XML Stuff function to make Grandtotal and then append to dynamic query
Santosh kumar Pithani 24-Apr-18 6:47am View
How you get all formulas,have any table for storing all formulas ?
Santosh kumar Pithani 24-Apr-18 6:18am View
If you knew all mathematical formulas then use them directly inside function by if condition.
Santosh kumar Pithani 24-Apr-18 6:17am View
If you knew all mathematical formulas then use them directly inside function by if condition.
Santosh kumar Pithani 24-Apr-18 5:44am View
Hello Yogi, you can use scalar function BY extra parameters like ex:-((@a * @b) + @c) inside your function.
Santosh kumar Pithani 25-Mar-18 23:39pm View
Author, can i know why this answer is down voted?
Santosh kumar Pithani 20-Mar-18 2:11am View
let me know why your are using XML Query instead of sub query in SP?
Santosh kumar Pithani 12-Mar-18 1:22am View
Welcome John:)
Santosh kumar Pithani 12-Mar-18 1:05am View
You have to do group by ..wait i will improve solution for you.
Santosh kumar Pithani 28-Feb-18 7:22am View
If you insert all records at a time in target table then how you compare with source table?
Santosh kumar Pithani 27-Feb-18 7:35am View
create one #temp table and use while loop for insert records in #temp
Santosh kumar Pithani 27-Feb-18 7:21am View
Execute above solution you will understood.
Santosh kumar Pithani 27-Feb-18 7:21am View
Execute above solution you will understood.
Santosh kumar Pithani 27-Feb-18 6:28am View
your not mensioned any relation between two queries but i advice you try to use scalar sub queries.
Santosh kumar Pithani 27-Feb-18 4:23am View
please share Data records and expected output so its easy to give answer
Santosh kumar Pithani 27-Feb-18 1:36am View
Your query is correct but you have to know how filters work with('AND','OR')
Santosh kumar Pithani 19-Feb-18 7:29am View
you have give allies MAX([1]) AS '2018-02-01' instead of [1]..
Santosh kumar Pithani 5-Jan-18 0:07am View
Hi,as i know your have to use "string_split" function to get records as unpivot format.
Santosh kumar Pithani 5-Jan-18 0:03am View
This solution is wrong according to OP's requirement.
Santosh kumar Pithani 27-Dec-17 22:56pm View
If you need only date then use cast or convert function
Santosh kumar Pithani 16-Dec-17 4:27am View
can you give sample example on your question with expected output?
Santosh kumar Pithani 16-Dec-17 4:05am View
How many columns returning "EXEC ('{CALL SCHEMA.SP_NAME (''PARAM1'', ''PARAM2'', ''PARAM3'')}') AT SERVERNAME" this query after execution?
Santosh kumar Pithani 16-Dec-17 3:59am View
what is your expected output?i think you need to do pivot.
Santosh kumar Pithani 16-Dec-17 3:40am View
Hello Rajan, let me know how you solve my issue?
Santosh kumar Pithani 15-Dec-17 1:48am View
Two table returning from CTE(derived tables also possible) and joining data using row_num(id) as relation to avoid M*N rows(duplicate rows) ,i trust this query make sense
Santosh kumar Pithani 13-Dec-17 4:02am View
You have to improve your question, don't post multiple questions separately in solution text.
Santosh kumar Pithani 13-Dec-17 3:18am View
Let me know what is this ->"AccountNo 11 Month 10 Month Remarks 9 Month Remarks"
Santosh kumar Pithani 12-Dec-17 22:57pm View
Remove html tags and improve question with example,What is you input parameter and expected values by function.
Santosh kumar Pithani 11-Dec-17 0:01am View
update union query in "What I have tried:"
Santosh kumar Pithani 10-Dec-17 22:58pm View
Variable tables(@table) is not scheme objects(tables,stored procedures..etc) to store permanently in Database so we cannot drop it.Its a very simple question to find answer easily. Example :-Declare @a int=12 --Here we can't drop a variable but we can read it very time in sql script.
Santosh kumar Pithani 6-Dec-17 22:58pm View
Improve your question according to updated solution, your must be clear before posting your question.This is second time you did like this don't repeat once more further your just expecting perfect solutions without doing any effort.
Santosh kumar Pithani 6-Dec-17 7:43am View
query is updated check and let me know!:)
Santosh kumar Pithani 6-Dec-17 7:23am View
-- you can do group by direction by table column
SELECT DISTINCT Con.CompanyName AS Contact,
'' AS IsClient,
'' AS IsCustomer,
'Y' AS IsSupplier

FROM contacts con
INNER JOIN Countries Cont ON Con.CountryID=Cont.CountryID
INNER JOIN ContactRelations CRes ON Con.ContactID=CRes.ContactID
INNER JOIN ContactRelationTypes CRel ON CRes.ContactRelationTypeID=CRel.ContactRelationTypeID
WHERE CRel.ContactRelationTypeID=8 GROUP BY Con.CompanyName
Santosh kumar Pithani 6-Dec-17 6:39am View
Query is updates check!
Santosh kumar Pithani 5-Dec-17 2:24am View
Yes, your right i will improve my solution.Thank u!
Santosh kumar Pithani 5-Dec-17 2:00am View
Improve your question with expected output or example.
Santosh kumar Pithani 5-Dec-17 1:08am View
Santosh kumar Pithani 5-Dec-17 0:48am View
Welcome !
Santosh kumar Pithani 5-Dec-17 0:47am View
Santosh kumar Pithani 5-Dec-17 0:28am View
why 'String' is used in concat? Use "set string=(select concat('(',number,',',qty,')') );"
Santosh kumar Pithani 2-Dec-17 1:49am View
This link will help you
Santosh kumar Pithani 30-Nov-17 23:08pm View
AS your using inner join on condition of "tempWIPAeroV1.RKEY = NewValues.RKEY " so how u get null records on Rkey column? use filter condition to avoid null records.
Santosh kumar Pithani 28-Nov-17 1:45am View
check this query and let me know what you need!
;WITH CompStructureCTE AS (

SELECT A.CompanyStructureID, A.Name, A.ParentID
FROM CompStructure AS A
INNER JOIN CompStructure B ON B.CompanyStructureID=A.ParentID


CSCTE.ParentID as ParentID,
CSCTE.Name AS Name,
R.Type AS EmployeeType,
YEAR(RI.JoinDate) as JoinYear,
DATEPART( wk, RI.JoinDate) as JoinedWeek,
DATENAME(month, RI.JoinDate) as [Month],
RI.RelievedDate AS RelievedDate,
COUNT(1)OVER(PARTITION BY YEAR(RI.JoinDate),DATENAME(month, RI.JoinDate),CSCTE.CompanyStructureID ,CSCTE.Name,R.Type
ORDER BY YEAR(RI.JoinDate),DATENAME(month, RI.JoinDate),CSCTE.CompanyStructureID) AS CountofEmployees
ON (R.DepartmentID=CSCTE.CompanyStructureID) OR (R.GroupID=CSCTE.CompanyStructureID)
INNER JOIN ResourceInfo RI ON (RI.ResourceID=R.ResourceID)
WHERE RI.RelievedDate IS NULL -- AND R.DepartmentID=10
Santosh kumar Pithani 28-Nov-17 1:03am View
why you have used "ON R2.JoinDate=RI.JoinDate" same table twice? It returns duplicate rows. Atleast Put your expected output!
Santosh kumar Pithani 27-Nov-17 0:22am View
I know by using dynamic query filtering is possible with two different variable but OP is clearly "Below query is not returning any row but DB has the value. Can anyone help on this without using dynamic query"
Santosh kumar Pithani 27-Nov-17 0:09am View
I clearly mentioned "its is possible but @Search2,@Search1 both values should be same".that solution is given because OP is looking for true condition.
Santosh kumar Pithani 26-Nov-17 22:45pm View
My query is correct according OP "WHERE @Search2 = @Search1" this condition is works as true or false ,it return all rows if it is true(1=1) else false(1=3)zero rows.I don't know why he has posted like his question without dynamic query:)
Santosh kumar Pithani 23-Nov-17 3:32am View
its is possible but @Search2,@Search1 both values should be same.
Santosh kumar Pithani 23-Nov-17 2:30am View
In your query filter(where 1=2) condition fails so its returns only headers.
Santosh kumar Pithani 22-Nov-17 23:10pm View
There is no rollback after removing comments but your can read all messages in MessageBox, my last comment is "Always Welcome! :)"
Santosh kumar Pithani 21-Nov-17 23:15pm View
Remove your output which is posted as Solution!
Santosh kumar Pithani 21-Nov-17 7:20am View
Always Welcome :)
Santosh kumar Pithani 21-Nov-17 7:19am View
Always Welcome :)
Santosh kumar Pithani 21-Nov-17 5:39am View
your required Query is updated with output,check and let me know!
Santosh kumar Pithani 21-Nov-17 3:35am View
CREATE TABLE #Demo(Client VARCHAR(50), Country VARCHAR(50),
InvoiceAmount DECIMAL(5,2))

select Country,isnull([Raja],0.00) AS [Raja],isnull([Ajit],0.00) AS [Ajit],isNull([Sukanya],0.00) AS [Sukanya],isNULL([Neha],0.00) As [Neha] from
#demo pivot(sum(InvoiceAmount) for Client in([Raja],[Ajit],[Sukanya],[Neha])) as fg

Country Raja Ajit Sukanya Neha
Begium 0.00 1000.00 0.00 0.00
Belgium 50.00 0.00 0.00 0.00
China 0.00 0.00 1000.00 0.00
France 0.00 0.00 0.00 130.00
India 400.00 0.00 0.00 1200.00
Santosh kumar Pithani 21-Nov-17 0:19am View
Yesterday i'm so busy sorry for late!
Santosh kumar Pithani 21-Nov-17 0:18am View
CREATE TABLE #Demo(Client VARCHAR(50), Country VARCHAR(50),
InvoiceAmount DECIMAL(5,2))

Declare @Pivotcols Nvarchar(max);
Declare @pivoting nvarchar(max);
Declare @col2 nvarchar(50)='country';
Declare @InputTable nvarchar(500)='#demo';
SET @Pivotcols = N'SELECT STUFF((SELECT Distinct '',''+''cast([''+ISNULL('+@col2+',0)+''] AS Varchar(20)) AS [''+isnull('+@col2+',0)+'']'' FROM '+@InputTable+' AS C
FOR XML PATH(''''),Type).value(''.'',''NVARCHAR(MAX)''), 1, 1,'''');'
SET @pivoting=N'SELECT STUFF((SELECT Distinct '',''+'' [''+isnull('+@col2+',0)+'']'' FROM '+@InputTable+' AS C
FOR XML PATH(''''),Type).value(''.'',''NVARCHAR(MAX)''), 1, 1,'''');'

Santosh kumar Pithani 20-Nov-17 2:32am View
Let me know what you need exactly.
Santosh kumar Pithani 20-Nov-17 1:59am View
Don't post like this questions ones more...
Santosh kumar Pithani 20-Nov-17 1:58am View
Santosh kumar Pithani 19-Nov-17 23:31pm View
Santosh kumar Pithani 18-Nov-17 5:44am View
Sure :)
Santosh kumar Pithani 18-Nov-17 4:03am View
I think datatype conversion issue,so convert 0.0 as varchar
Santosh kumar Pithani 18-Nov-17 4:00am View
I think datatype conversion issue,so convert 0.0 as varchar
Santosh kumar Pithani 17-Nov-17 23:20pm View
SET @PivotRows =N'DECLARE @Demo TABLE (Client VARCHAR(50), Country VARCHAR(50),
InvoiceAmount DECIMAL(5,2));
FOR XML PATH(''''),Type).value(''.'',''NVARCHAR(MAX)''), 1, 1,'''');'
--You can understood better from above so don't use @table in dynamic queries.
Santosh kumar Pithani 15-Nov-17 22:59pm View
SSIS packages is very fast for import and export Data Records.
Santosh kumar Pithani 13-Nov-17 7:52am View
Don't post like this its not a solution , give replay by using comment box
Santosh kumar Pithani 12-Nov-17 22:54pm View
Welcome Jaydeep!
Santosh kumar Pithani 10-Nov-17 7:52am View
Welcome AntiRix
Santosh kumar Pithani 10-Nov-17 6:28am View
Query is updates with example check it.
Santosh kumar Pithani 10-Nov-17 6:15am View
Try like this "OffsetDaysBefore.value=313" and let me know
Santosh kumar Pithani 10-Nov-17 5:06am View
Check "OffsetDaysBefore.value" Datatype it should be int otherwise convert it.
Santosh kumar Pithani 10-Nov-17 4:14am View
Thank You Chill60!
Your right 'SELECT *' is bad practice and also performance issue but that is OP's problem.
Santosh kumar Pithani 10-Nov-17 4:12am View
Thank You Chill60!
Your right 'SELECT *' is bad practice and also performance issue but that is OP's problem.
Santosh kumar Pithani 10-Nov-17 3:35am View
why your selected this"@BeforeValue" as column?
Santosh kumar Pithani 9-Nov-17 22:36pm View
Santosh kumar Pithani 9-Nov-17 7:29am View
Santosh kumar Pithani 9-Nov-17 4:51am View
Your not clearly mentioned about "leveltbl" table relation so update your question one's more.
Santosh kumar Pithani 9-Nov-17 2:05am View
Santosh kumar Pithani 9-Nov-17 1:14am View
"SET STATISTICS IO ON ;SET STATISTICS TIME ON " which were help you to find out which query is taking less time CPU time and elapsed time.
Santosh kumar Pithani 8-Nov-17 4:01am View
if "dateTimePicker" is invalid column of you then You have to pass valid "DateTIME" column Name or Date Parameter(@DateTime) instead of this "dateTimePicker".
I have updated query with example check it and let me know
Santosh kumar Pithani 7-Nov-17 6:04am View
if "dateTimePicker" is invalid column of you then You have to pass valid "DateTIME" column Name or Date Parameter(@DateTime) instead of this "dateTimePicker".
I have updated query with example check it and let me know
Santosh kumar Pithani 5-Nov-17 22:50pm View
Always welcome!