tag:blogger.com,1999:blog-4282222233168200124.comments2023-11-02T04:19:29.341-04:00Zen of SQLPlamen Ratchevhttp://www.blogger.com/profile/02027705815827955614noreply@blogger.comBlogger383125tag:blogger.com,1999:blog-4282222233168200124.post-82655835301423025322018-03-02T03:06:44.814-05:002018-03-02T03:06:44.814-05:00Hello everyone,
I am struggling with the following...Hello everyone,<br />I am struggling with the following problem. I need to calculate hours between 2 dates. For example let say that the start day is 01.03.2018 -Thursday and the end day is 06.03.2018 - Tuesday. I want to be able to calculate only the working hours between these 2 days. Let say that working hours are between 9 and 18. I tried a few things but no luck. Any help will be much appreciated. Thanks<br />Anonymoushttps://www.blogger.com/profile/15436509952474702662noreply@blogger.comtag:blogger.com,1999:blog-4282222233168200124.post-46035916115016777632018-03-01T14:04:22.303-05:002018-03-01T14:04:22.303-05:00Hello everyone,
I am struggling with the following...Hello everyone,<br />I am struggling with the following problem. I need to calculate hours between 2 dates. For example let say that the start day is 01.03.2018 -Thursday and the end day is 06.03.2018 - Tuesday. I want to be able to calculate only the working hours between these 2 days. Let say that working hours are between 9 and 18. I tried a few things but no luck. Any help will be much appreciated. ThanksAnonymoushttps://www.blogger.com/profile/15436509952474702662noreply@blogger.comtag:blogger.com,1999:blog-4282222233168200124.post-79644316944285545622018-03-01T14:03:28.195-05:002018-03-01T14:03:28.195-05:00This comment has been removed by the author.Anonymoushttps://www.blogger.com/profile/15436509952474702662noreply@blogger.comtag:blogger.com,1999:blog-4282222233168200124.post-86069728427246635882016-11-29T07:37:21.264-05:002016-11-29T07:37:21.264-05:00In case of only one element like the folowing exam...In case of only one element like the folowing example :<br /><br />"< root><br /> < subroot> info1 <br />< subroot> info1 <br />< subroot> info1 <br />< subroot> info1 <br />"<br /><br /><br />it returns empty fields, I don't know how to fix it, any ideas please?Anonymoushttps://www.blogger.com/profile/14043310496581190015noreply@blogger.comtag:blogger.com,1999:blog-4282222233168200124.post-80198559532358287562016-11-29T07:35:11.809-05:002016-11-29T07:35:11.809-05:00This comment has been removed by the author.Anonymoushttps://www.blogger.com/profile/14043310496581190015noreply@blogger.comtag:blogger.com,1999:blog-4282222233168200124.post-73015294588431387742016-09-01T15:31:40.238-04:002016-09-01T15:31:40.238-04:00Is there a way to achieve this dynamically when th...Is there a way to achieve this dynamically when the columns are not known or subject to change?Anonymoushttps://www.blogger.com/profile/12677133768269092931noreply@blogger.comtag:blogger.com,1999:blog-4282222233168200124.post-32431187996476384912016-06-27T19:28:19.172-04:002016-06-27T19:28:19.172-04:00This is fantastic . Thanks for sharing.This is <b> fantastic </b>. Thanks for sharing.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-4282222233168200124.post-23784450426830966512016-06-09T21:46:54.150-04:002016-06-09T21:46:54.150-04:00Life is full of many challenges. Challenges that w...Life is full of many challenges. Challenges that will make you or break you depending on how you handle it. Visit my site for more updates. God Bless to your site.<br /><br />n8fan.net<br /><br />www.n8fan.netAnonymoushttps://www.blogger.com/profile/00391915680800427200noreply@blogger.comtag:blogger.com,1999:blog-4282222233168200124.post-76006383485927962872016-06-09T21:39:33.779-04:002016-06-09T21:39:33.779-04:00Life is full of many challenges. Challenges that w...Life is full of many challenges. Challenges that will make you or break you depending on how you handle it. Visit my site for more updates. God Bless to your site.<br /><br />n8fan.net<br /><br />www.n8fan.netAnonymoushttps://www.blogger.com/profile/00391915680800427200noreply@blogger.comtag:blogger.com,1999:blog-4282222233168200124.post-6325533046331812112016-04-14T21:51:06.938-04:002016-04-14T21:51:06.938-04:00Better three hours too soon than a minute too late...Better three <a href="http://www.ufgop.org/pdf/hours" rel="nofollow">hours</a> too soon than a minute too late. See the link below for more info. <br /><br /><br />#hours<br />www.ufgop.orgAnonymoushttps://www.blogger.com/profile/07720547920308398294noreply@blogger.comtag:blogger.com,1999:blog-4282222233168200124.post-5831773292117623702016-03-02T21:45:50.327-05:002016-03-02T21:45:50.327-05:00Thanks Nick, I have used 4 columns and worked grea...Thanks Nick, I have used 4 columns and worked great.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-4282222233168200124.post-18496773203625482912016-02-03T01:50:21.271-05:002016-02-03T01:50:21.271-05:00I really liked your entry about manually sql injec...I really liked your entry about manually <a href="http://infoseczone.net/" rel="nofollow">sql injection attack</a>, unfortunelly i got stucked at the LAST step :( always something caused by ModSecurity :(martyn jhonhttp://infoseczone.net/noreply@blogger.comtag:blogger.com,1999:blog-4282222233168200124.post-48545284788676846022015-11-11T09:12:36.909-05:002015-11-11T09:12:36.909-05:00thank you. very good explanationthank you. very good explanationAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-4282222233168200124.post-70735910839648129812015-11-09T19:11:34.012-05:002015-11-09T19:11:34.012-05:00I don't think this guy needs an extra buck a m...I don't think this guy needs an extra buck a month.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-4282222233168200124.post-76780543363629387382015-09-28T08:26:11.872-04:002015-09-28T08:26:11.872-04:00Plamen, this is truly a great example of your work...Plamen, this is truly a great example of your work. I was wondering how could it be taken one step more and add a division to the results of MAX([2003]) AS v2003 / MAX([3]) AS q2003,Anonymoushttps://www.blogger.com/profile/03592325297345411143noreply@blogger.comtag:blogger.com,1999:blog-4282222233168200124.post-30784258450226529282015-09-17T11:53:52.906-04:002015-09-17T11:53:52.906-04:00Thank you Plamen, for your posts, and also to all ...Thank you Plamen, for your posts, and also to all contributing with questions and answers here. <br />Here are my two cents, the dynamic versions of your CASE based pivot:<br /><br />DECLARE @cols NVARCHAR(MAX),<br /> @sql NVARCHAR(MAX);<br /><br />SELECT @Cols = STUFF((select ', <br />SUM(CASE WHEN [market_year] = ' + CAST([market_year] as char(4)) + ' THEN [value] ELSE 0 END) AS [' + CAST([market_year] as char(4)) + N'-V],<br />SUM(CASE WHEN [market_year] = ' + CAST([market_year] as char(4)) + ' THEN [quantity] ELSE 0 END) AS [' + CAST([market_year] as char(4)) + N'-Q]'<br />FROM Products<br />ORDER BY [market_year] FOR XML PATH(''),type).value('.','varchar(max)'),1,2,'')<br />;<br />--PRINT @cols;<br /><br />SET @sql = 'SELECT product, ' + @cols + ' FROM Products GROUP BY Product';<br /> <br />--PRINT @sql;<br />EXEC(@sql);Davidhttps://www.blogger.com/profile/07766815790410779802noreply@blogger.comtag:blogger.com,1999:blog-4282222233168200124.post-45682617731043526852015-07-29T13:15:55.500-04:002015-07-29T13:15:55.500-04:00I used the first approach but i keep getting the e...I used the first approach but i keep getting the error invalid column name. I already have the table created hence I am using the query window to try and do the SQL query. I am a beginner at this. Followed the first query to the letter. <br />What can you suggest?Anonymoushttps://www.blogger.com/profile/16073226678539622472noreply@blogger.comtag:blogger.com,1999:blog-4282222233168200124.post-59340585153329332262015-07-01T04:58:27.558-04:002015-07-01T04:58:27.558-04:00amazing and nice looking site please love it and m...amazing and nice looking site please love it and make more effective… keep it up.<br /><br /><a href="http://www.gofastek.com" rel="nofollow">Bubble</a><br />www.gofastek.comhttp://www.drama.net/m1/autumn-tale-episode-14/part3http://www.drama.net/m1/autumn-tale-episode-14/part3Anonymoushttps://www.blogger.com/profile/11708398102654526740noreply@blogger.comtag:blogger.com,1999:blog-4282222233168200124.post-54394017469653648012015-03-30T17:30:39.676-04:002015-03-30T17:30:39.676-04:00select dbo.CalcBusinessHours('2014-05-14 15:46...select dbo.CalcBusinessHours('2014-05-14 15:46:16.000','2014-05-14 19:21:41.000')<br /><br />is returning 8 and not 4 why?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-4282222233168200124.post-59286782176826892672015-03-08T09:49:53.967-04:002015-03-08T09:49:53.967-04:00Hi I have a stored procedure and i want to use dyn...Hi I have a stored procedure and i want to use dynamic aliases it would be the current year. I searched and the suggested solution is dynamic sql only . please suggest as it is a long proc and making sql dynamic requires alot of declared variables that i have to like specify as '++' in this . please let me know if i can use dynamic aliases widout using dynamic sqlTechPersonhttps://www.blogger.com/profile/04279343379384537674noreply@blogger.comtag:blogger.com,1999:blog-4282222233168200124.post-2031026886965163002015-02-22T08:39:46.129-05:002015-02-22T08:39:46.129-05:00Thank you very much. It is easy to Understood.Thank you very much. It is easy to Understood.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-4282222233168200124.post-43829962583888874882015-02-16T06:51:52.936-05:002015-02-16T06:51:52.936-05:00Thank you so much. It helped me solve my problemThank you so much. It helped me solve my problemAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-4282222233168200124.post-58213905834046406082015-01-14T22:59:54.038-05:002015-01-14T22:59:54.038-05:00This comment has been removed by the author.Anonymoushttps://www.blogger.com/profile/10707282472191452646noreply@blogger.comtag:blogger.com,1999:blog-4282222233168200124.post-2924799647759603782014-12-14T12:54:07.540-05:002014-12-14T12:54:07.540-05:00Hi, I followed the suggestions in your article. H...Hi, I followed the suggestions in your article. However I am not able to get the values into the table. My XML is as follows:<br />--- Quote<br /><br /> <br /> <br /> <br /> <br /> All Masters<br /> <br /> Duroflex Private Limited-NF-Server<br /> <br /> <br /> <br /> <br /> 20140419<br /> Mattress Gallery (Trichy)<br /> SL-EX/NF/4030011/KM/14-15<br /> <br /> <br /> <br /> <br /><br /><br />--- Unquote<br /><br />The Script as per your example is as follows :<br /><br />--- Quote<br />Use NF22224<br /><br />IF OBJECT_ID('InvFromXML') IS NOT NULL DROP TABLE InvFromXML<br /><br /> <br /> CREATE TABLE dbo.InvFromXML<br /> (<br /> <br /><br /> [VOUCHERNUMBER] [VARCHAR] (64) DEFAULT NULL,<br /> [DATE] [VARCHAR](08) DEFAULT NULL,<br /> [PARTYNAME] [VARCHAR](64) DEFAULT NULL<br /> )<br /> <br />INSERT INTO InvFromXML <br /><br />---VOUCHERNUMBER, DATE, PARTYNAME<br /> <br />SELECT X.query('VOUCHERNUMBER').value('.', 'VARCHAR(64)'),<br /> X.query('DATE').value('.', 'VARCHAR(08)'),<br /> X.query('PARTYNAME').value('.','VARCHAR(64)')<br />FROM ( <br />SELECT CAST(x AS XML)<br />FROM OPENROWSET(<br /> BULK 'E:\Client Data\NF\NF One Invoice.xml',<br /> SINGLE_BLOB) AS T(x)<br /> ) AS T(x)<br />CROSS APPLY x.nodes('ENVELOPE/BODY/IMPORTDATA/REQUESTDATA/TALLYMESSAGE/VOUCHER') AS X(Voucher)<br /><br />SELECT * FROM InvFromXML<br />----- Unquote<br /><br />Please help<br /><br />Varadarajan RAnonymoushttps://www.blogger.com/profile/12886263517986717883noreply@blogger.comtag:blogger.com,1999:blog-4282222233168200124.post-46786202369598154532014-10-04T12:36:46.815-04:002014-10-04T12:36:46.815-04:00Really you are Geniussssssssssssssss...Really you are Geniussssssssssssssss...Anonymoushttps://www.blogger.com/profile/10120706403159524404noreply@blogger.com