16,004,686 members
Sign in
Sign in
Email
Password
Forgot your password?
Sign in with
home
articles
Browse Topics
>
Latest Articles
Top Articles
Posting/Update Guidelines
Article Help Forum
Submit an article or tip
Import GitHub Project
Import your Blog
quick answers
Q&A
Ask a Question
View Unanswered Questions
View All Questions
View C# questions
View C++ questions
View Javascript questions
View Visual Basic questions
View .NET questions
discussions
forums
CodeProject.AI Server
All Message Boards...
Application Lifecycle
>
Running a Business
Sales / Marketing
Collaboration / Beta Testing
Work Issues
Design and Architecture
Artificial Intelligence
ASP.NET
JavaScript
Internet of Things
C / C++ / MFC
>
ATL / WTL / STL
Managed C++/CLI
C#
Free Tools
Objective-C and Swift
Database
Hardware & Devices
>
System Admin
Hosting and Servers
Java
Linux Programming
Python
.NET (Core and Framework)
Android
iOS
Mobile
WPF
Visual Basic
Web Development
Site Bugs / Suggestions
Spam and Abuse Watch
features
features
Competitions
News
The Insider Newsletter
The Daily Build Newsletter
Newsletter archive
Surveys
CodeProject Stuff
community
lounge
Who's Who
Most Valuable Professionals
The Lounge
The CodeProject Blog
Where I Am: Member Photos
The Insider News
The Weird & The Wonderful
help
?
What is 'CodeProject'?
General FAQ
Ask a Question
Bugs and Suggestions
Article Help Forum
About Us
Search within:
Articles
Quick Answers
Messages
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);
INSERT INTO #Test
VALUES
('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)
SELECT
SubHeading_Name,Pointer_Id,
MAX(case WHEN JV='JV1' THEN CAST(JAN AS Varchar(10)) END)+' '+
MAX(case WHEN JV='JV2' THEN CAST(JAN AS Varchar(10)) END) AS JAN,
MAX(case WHEN JV='JV1' THEN CAST(FEB AS Varchar(10)) END)+' '+
MAX(case WHEN JV='JV2' THEN CAST(FEB AS Varchar(10)) END) AS FEB
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
THEN
ND.inserteddate
ELSE
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.
ex:DECLARE @Emoji TABLE(A NVARCHAR(500),B VARCHAR(500));
INSERT INTO @Emoji(A,B)
SELECT N'Hi friends😘', N'Hi friends😘';
SELECT * FROM @Emoji;
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
AS
---Newly inserted value
INSERT INTO
tblEmpHistoryDetail (MasterID,EmpName,Dob)
select ID,EmpName,Dob from inserted;
---Deleted Old value
INSERT INTO
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
;WITH CTE AS(
SELECT a.*, Count(1)over(partition by a.[Customer_LastName],
a.[Customer_FirstName],
a.[Customer_AddressLine1],
a.[Customer_InternetEmail],
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_FirstName],
[Customer_AddressLine1],
[Customer_InternetEmail],
[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.
:WITH CTE AS(
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
Welcome
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 ;
select
ISNULL(InvtID,'') AS InvtID
,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
Deleted
your expecting new and old records from this update query only or on table ?
Santosh kumar Pithani
7-Sep-18 2:53am
View
Welcome..
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 "a.city" 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
Deleted
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
Welcome..!
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
Deleted
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
Deleted
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)
AS
-- 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
WHEN (
(UserPicks.Game_1 = WonLost.GameResults_1) OR
(UserPicks.Game_2 = WonLost.GameResults_2)
)
THEN 'WON' ELSE LOST' END"
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
Sure
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
Deleted
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
Deleted
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
Good!
Santosh kumar Pithani
5-Dec-17 0:48am
View
Welcome !
Santosh kumar Pithani
5-Dec-17 0:47am
View
Deleted
Welcome!
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
http://www.trycatchfinally.net/2014/07/exporting-from-sql-server-to-csv-with-column-names/
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
)
SELECT DISTINCT CSCTE.CompanyStructureID as ID,
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
FROM CompStructureCTE AS CSCTE
LEFT JOIN RESOURCE R
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
Deleted
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))
INSERT INTO #Demo
VALUES
('Raja','India',100.00),('Ajit','Begium',200.00),('Sukanya','China',300.00),
('Neha','India',600.00),('Raja','India',300.00),('Ajit','Begium',800.00),
('Sukanya','China',700.00),('Neha','India',600.00),('Raja','Belgium',50.00),
('Neha','France',130.00);
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
Deleted
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))
INSERT INTO #Demo
VALUES
('Raja','India',100.00),('Ajit','Begium',200.00),('Sukanya',null,300.00),
('Neha','India',600.00),('Raja',null,300.00),('Ajit','Begium',800.00);
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,'''');'
exec(@Pivotcols);
SET @pivoting=N'SELECT STUFF((SELECT Distinct '',''+'' [''+isnull('+@col2+',0)+'']'' FROM '+@InputTable+' AS C
FOR XML PATH(''''),Type).value(''.'',''NVARCHAR(MAX)''), 1, 1,'''');'
exec(@pivoting);
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
https://stackoverflow.com/questions/11857789/when-should-i-use-a-table-variable-vs-temporary-table-in-sql-server
Santosh kumar Pithani
19-Nov-17 23:31pm
View
Sure!:)
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
Deleted
I think datatype conversion issue,so convert 0.0 as varchar
Santosh kumar Pithani
17-Nov-17 23:20pm
View
Deleted
DECLARE @PivotRows AS NVARCHAR(MAX);
SET @PivotRows =N'DECLARE @Demo TABLE (Client VARCHAR(50), Country VARCHAR(50),
InvoiceAmount DECIMAL(5,2));
INSERT INTO @Demo VALUES
(''Raja'',''India'',100.00),(''Ajit'',''Begium'',200.00),(''Sukanya'',''China'',300.00),
(''Neha'',''India'',600.00),(''Raja'',''India'',300.00),(''Ajit'',''Begium'',800.00)
;SELECT STUFF((SELECT DISTINCT '',''+QUOTENAME('+@Col2+',''['')
FROM @DEmo AS C
FOR XML PATH(''''),Type).value(''.'',''NVARCHAR(MAX)''), 1, 1,'''');'
EXEC(@PivotRows);
--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
Deleted
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
Deleted
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
Welcome
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
Deleted
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!
Show More