tag:blogger.com,1999:blog-495177919198719500.comments2023-12-27T20:52:29.483-08:00Microsoft Sql Server TutorialsSuprotim Agarwalhttp://www.blogger.com/profile/08349831623922214390noreply@blogger.comBlogger1152125tag:blogger.com,1999:blog-495177919198719500.post-79424676134511930692018-05-15T20:39:08.285-07:002018-05-15T20:39:08.285-07:00Helpful... nicely explainedHelpful... nicely explainedAditya Ghoshhttps://www.blogger.com/profile/02901885479020803340noreply@blogger.comtag:blogger.com,1999:blog-495177919198719500.post-34345132630581873672018-05-03T05:23:20.079-07:002018-05-03T05:23:20.079-07:00quite easy work for me thanks!quite easy work for me thanks!BIdevhttps://www.blogger.com/profile/01068100682689883928noreply@blogger.comtag:blogger.com,1999:blog-495177919198719500.post-86464094376977405972018-04-18T07:50:05.572-07:002018-04-18T07:50:05.572-07:00Thank you so much!!! Simplified so that even I can...Thank you so much!!! Simplified so that even I can pick this up!!!Unknownhttps://www.blogger.com/profile/14659353641525793625noreply@blogger.comtag:blogger.com,1999:blog-495177919198719500.post-75602849210608995552018-04-11T04:04:52.379-07:002018-04-11T04:04:52.379-07:00The conversion completely ignores the timezone off...The conversion completely ignores the timezone offset information: the input is explicitly one hour off UTC, but the output is 19:20:30 in whatever timezone your server happens to be running.<br />Berthttps://www.blogger.com/profile/10881993743458105129noreply@blogger.comtag:blogger.com,1999:blog-495177919198719500.post-34079281134203213242018-02-26T22:57:20.926-08:002018-02-26T22:57:20.926-08:00perfect solution .. 100/100 for the author. This w...perfect solution .. 100/100 for the author. This worked for me like charmamithttps://www.blogger.com/profile/16131792635007757847noreply@blogger.comtag:blogger.com,1999:blog-495177919198719500.post-68686749094480386912018-02-14T21:11:01.428-08:002018-02-14T21:11:01.428-08:00You are welcome Abhishek and Neal :)You are welcome Abhishek and Neal :)Suprotim Agarwalhttps://www.blogger.com/profile/08349831623922214390noreply@blogger.comtag:blogger.com,1999:blog-495177919198719500.post-262129159307800052018-02-14T12:06:53.683-08:002018-02-14T12:06:53.683-08:00Great Explanation! Thank you very muchGreat Explanation! Thank you very muchAnonymoushttps://www.blogger.com/profile/04071062576214167544noreply@blogger.comtag:blogger.com,1999:blog-495177919198719500.post-45631871980925005142018-01-04T21:56:51.841-08:002018-01-04T21:56:51.841-08:00Thanks Joshua for sharing your solution!Thanks Joshua for sharing your solution!Suprotim Agarwalhttps://www.blogger.com/profile/08349831623922214390noreply@blogger.comtag:blogger.com,1999:blog-495177919198719500.post-30433695036480662542018-01-04T08:48:57.643-08:002018-01-04T08:48:57.643-08:00I ran into a very similar situation. In my case t...I ran into a very similar situation. In my case the Columns that I was pivoting ended with two digits (IE: 01, 02, 03, etc). So I placed a where clause on my pivot table <br /><br /><i>where RIGHT(COL_NO, 2) = RIGHT(COL_AMT, 2)</i><br /><br />So that I would only show the records that both came from the same column. <br /><br />For completeness sake, here's my entire SQL statement.<br /><br /><i><br /> SELECT rtrim(ltrim(hr_pe_id)) as Id, <br /> PYH_NO as CdhNo, <br /> cast(PYH_AMT as bigint) as Amt,<br /> pyh_per_cc as PayPeriod, <br /> py_per_ccyy as PeriodYYCC,<br /> pyh_post_dt as PostDate,<br /> pyh_num_cd as numcd,<br /> pyh_rec_type as rectype,<br /> pyh_tc_batch as batchId,<br /> unique_key as unique_key,<br /> pyh_ck_dt as CheckDate<br />from [finance].[dbo].pyh_hst_dtl <br />UNPIVOT (PYH_NO FOR COL_NO IN(<br /> PYH_NO01,PYH_NO02,PYH_NO03,PYH_NO04,PYH_NO05,<br /> PYH_NO06,PYH_NO07,PYH_NO08,PYH_NO09,PYH_NO10,<br /> PYH_NO11,PYH_NO12,PYH_NO13,PYH_NO14,PYH_NO15)) AS CDH_NO<br />UNPIVOT (PYH_AMT FOR COL_AMT IN(<br /> PYH_AMT01,PYH_AMT02,PYH_AMT03,PYH_AMT04,PYH_AMT05,<br /> PYH_AMT06,PYH_AMT07,PYH_AMT08,PYH_AMT09,PYH_AMT10,<br /> PYH_AMT11,PYH_AMT12,PYH_AMT13,PYH_AMT14,PYH_AMT15)) AS CDH_AMT<br />where RIGHT(COL_NO, 2) = RIGHT(COL_AMT, 2) <br /> AND PYH_NO <> 0<br /></i><br /><br />I hope this helps.Delnarhttps://www.blogger.com/profile/05346281946141025588noreply@blogger.comtag:blogger.com,1999:blog-495177919198719500.post-1099340419847594782017-12-04T08:30:39.352-08:002017-12-04T08:30:39.352-08:00Thanks for the informative article. however, I hav...Thanks for the informative article. however, I have a situation where I my view are fairly complex with multiple joins and "Union". How can i extract the column information? I need to know that a column "x" is coming from "tableA"."ColumnB"? Can we get this extracted from the metadata / view definition?Mr Kumarhttps://www.blogger.com/profile/03710272257823586141noreply@blogger.comtag:blogger.com,1999:blog-495177919198719500.post-32953930164269181032017-11-06T14:01:44.664-08:002017-11-06T14:01:44.664-08:00It is pointless to check if the object is a proced...It is pointless to check if the object is a procedure or not.<br /><br />Assume schema.name is a view, not a procedure.<br /><br />"if object_id(N'schema.name','p') is not null drop procedure schema.name" will not try to drop anything, but your CREATE PROCEDURE schema.view will fail because you can't have TWO objects schema.name.<br /><br />If you use just "if object_id(N'schema.view') is not null drop procedure schema.view" will fail when you try to "drop procedure" on a view.<br /><br />They both fail if schema.name is a non-procedure, they both succeed if schema.name IS a procedure. There is zero benefit in checking of schema.name is a procedure or not.<br />Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-495177919198719500.post-57020355083297435302017-11-06T13:58:38.767-08:002017-11-06T13:58:38.767-08:00I always wonder WHY you check if it's a proced...I always wonder WHY you check if it's a procedure.<br /><br />Assume "schema.name" is a non-procedure object in the database. Say, a view.<br /><br />If you run "if object_id(N'schema.name') is not null drop procedure schema.name", it will fail because schema.name is a view, not a procedure.<br /><br />But if you run "if object_is(N'schema.name','p') is not null drop procedure schema.name" it won't try to drop the view, but your CREATE PROCEDURE schema.name will fail.<br /><br />Either way, it fails. You go out of your way to check if schema.name is a procedure, for no gain. If it exists and it is a procedure, "if object_id(N'schema.name') is not null drop procedure" will work.<br />Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-495177919198719500.post-37721533265691620052017-10-25T18:59:18.936-07:002017-10-25T18:59:18.936-07:00Very good explanation. Thank you sir.Very good explanation. Thank you sir.Anonymoushttps://www.blogger.com/profile/14875543742115433041noreply@blogger.comtag:blogger.com,1999:blog-495177919198719500.post-38843747047065626972017-10-12T14:50:13.034-07:002017-10-12T14:50:13.034-07:00Does this code works in case of consanguinity ?Does this code works in case of consanguinity ?Vincent Duvernethttps://www.blogger.com/profile/12562941960905485456noreply@blogger.comtag:blogger.com,1999:blog-495177919198719500.post-67541411615690404092017-08-21T12:52:36.315-07:002017-08-21T12:52:36.315-07:00what if i do not want round values? how can i stop...what if i do not want round values? how can i stop it?KIRTAN KHATSURIYAhttps://www.blogger.com/profile/08742401301042499701noreply@blogger.comtag:blogger.com,1999:blog-495177919198719500.post-16223310931591473652017-07-03T21:12:35.006-07:002017-07-03T21:12:35.006-07:00Thanks Mr Agarwal ...
It was best explaination I c...Thanks Mr Agarwal ...<br />It was best explaination I could search<br />Good work and keep going . <br />Good wishes Anonymoushttps://www.blogger.com/profile/15189612805754291944noreply@blogger.comtag:blogger.com,1999:blog-495177919198719500.post-40546593433517952012017-06-21T03:35:34.297-07:002017-06-21T03:35:34.297-07:00you can also use azure cmdlets to get the databas...you can also use azure cmdlets to get the database size and space use details. <br />I have posted on the same lines. http://www.sqlservercentral.com/articles/Azure/157249/<br />Tushar Kantihttps://www.blogger.com/profile/00373579239372876487noreply@blogger.comtag:blogger.com,1999:blog-495177919198719500.post-85688489181463996382017-06-05T20:12:32.562-07:002017-06-05T20:12:32.562-07:00Can you give an example of a good use case for thi...Can you give an example of a good use case for this approach?Bearhttps://www.blogger.com/profile/06405749718258946705noreply@blogger.comtag:blogger.com,1999:blog-495177919198719500.post-49457754415626135232017-05-12T06:32:01.272-07:002017-05-12T06:32:01.272-07:00Thank you for this helpful piece of code. As writt...Thank you for this helpful piece of code. As written upper, the day marked as first depends on the SET DATEFIRST option.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-495177919198719500.post-4401262820876307482017-05-02T03:41:54.390-07:002017-05-02T03:41:54.390-07:00GETUTCDATE() is good ,In the case when the content...GETUTCDATE() is good ,In the case when the content is for global readers/users just add Offset hours/minutes according to location in it.Anonymoushttps://www.blogger.com/profile/18083956183924987559noreply@blogger.comtag:blogger.com,1999:blog-495177919198719500.post-30507681618129599822017-04-12T08:11:00.626-07:002017-04-12T08:11:00.626-07:00GETUTCDATE()GETUTCDATE()Shonhttps://www.blogger.com/profile/10463672753807530704noreply@blogger.comtag:blogger.com,1999:blog-495177919198719500.post-56741053191467151732017-04-04T05:09:13.625-07:002017-04-04T05:09:13.625-07:00please can you give proper reason... why limit is ...please can you give proper reason... why limit is 32 for recursion?Prajakta Salokhehttps://www.blogger.com/profile/04903886742046412589noreply@blogger.comtag:blogger.com,1999:blog-495177919198719500.post-3137553798239625942017-03-10T02:34:57.687-08:002017-03-10T02:34:57.687-08:00Dear Sir AuthorName having more than 10 spaces how...Dear Sir AuthorName having more than 10 spaces how ican split the data <br /><br />Example :ganesh tankala mca 1989 20 07<br /><br />please help me on this Anonymoushttps://www.blogger.com/profile/00606548834789085267noreply@blogger.comtag:blogger.com,1999:blog-495177919198719500.post-70245597458292711262017-03-03T01:20:09.478-08:002017-03-03T01:20:09.478-08:00any updates in 2017 ? best practices NET and SQL S...any updates in 2017 ? best practices NET and SQL Server ?Kiquenethttps://www.blogger.com/profile/10446364197640116526noreply@blogger.comtag:blogger.com,1999:blog-495177919198719500.post-92088024356766975892017-02-25T20:16:44.610-08:002017-02-25T20:16:44.610-08:00excellent articleexcellent articleAnonymoushttps://www.blogger.com/profile/05217194961288618954noreply@blogger.com