Gtjkyu

WCF - Store and restore UTC DateTime We expose to our customer a WCF webservice allowing them to store DateTime in SQL Server databases. Extract of the WSDL : <xs:complexType name="TimePeriod"> <xs:sequence> <xs:element minOccurs="0" name="endDateTime" nillable="true" type="xs:dateTime"/> <xs:element minOccurs="0" name="startDateTime" nillable="true" type="xs:dateTime"/> </xs:sequence> For exemple, my customer send me UTC Datetime : <af:effectivePeriod> <af:startDateTime>2018-01-16T10:32:28Z</af:startDateTime> </af:effectivePeriod> This is stored in a SQL Server database in a datetime field. datetime But in the output of the read service, I don't have the UTC indicator : <af:effectivePeriod> <af:startDateTime>2018-01-16T10:32:28</af:startDateTime> </af:effectivePeriod> "Z" is kind of a unique...

Querying a XML column in SQL Server [closed] I wish to query a XML column in SQL Server. Following is the XML structure: <MessageEnvelope xmlns="http://schemas.xyz.com/messagebus/messageenvelope/1.0" xmlns:i="http://www.w3.org/2001/XMLSchema-instance"> <CustomField1/> <CustomField2 /> <CustomField3 /> <MessagePayload> <Counterpart xmlns="http://schemas.xyz.com/messagebus/saxotrading/counterpart/1.1"> <CounterpartID>111</CounterpartID> <TaxCountry>65</TaxCountry> <CustomerIdTypeCode>D</CustomerIdTypeCode> </Counterpart> <Counterpart xmlns="http://schemas.xyz.com/messagebus/saxotrading/counterpart/1.1"> <CounterpartID>112</CounterpartID> <TaxCountry>5</TaxCountry> <CustomerIdTypeCode>N</CustomerIdTypeCode> </Counterpart> <Counterpart xmlns=...

Dynamic SQL query to return results in columns I have this basic database table and am SQL beginner. +-------------+--------+----------+ | location | Week | Expenses | +-------------+--------+----------+ | Backoffice | 201851 | 80 | | frontoffice | 201852 | 110 | | Backoffice | 201901 | 120 | | Backoffice | 201902 | 70 | | frontoffice | 201903 | 68 | +-------------+--------+----------+ Is there a way to dynamically retrieve the last 5 weeks result, instead of hard coding it every time in my pivot table? Here is my code: SELECT * from (SELECT[week] ,[Expenses] from [cafe].[dbo].[table]where location = 'Backoffice' ) as Total_Expenses pivot (sum([expenses]) for [week] in ([201902],[201903],[201904],[201905],[201906],[201907],[201908],[201909])) as pivotable; I would like to be able to just enter "5" and it shows me the last 5 week. May be by a "max" formula" what RDBMS system? SQ...

How to remove overlapping rows based on date and keep most recent in sql? I need to find all episode_ids for every patient. However, when an overlapping episode arises within 90 days of the previous, then I just want to keep the most recent episode. For example, patient_num 3242 below has 3 episodes: the second episode overlaps with the first episode within 90 days, and the third episode overlaps with the second episode within 90 days, in this situation I need to just keep the 3rd episode. patient_num 3242 CREATE TABLE table1 (episode_id nvarchar(max), patient_num nvarchar(max), admit_date date, discharge_date date) INSERT INTO table1 (episode_id, patient_num , admit_date , discharge_date ) VALUES ('1','5743','1/1/2016','1/5/2016'), ('2','5743','4/26/2016','4/29/2016'), ('3','5743','5/26/2016','5/28/2016'), ('4','5743','9/21/2016','9/28/2016'), ('5'...

Joining tables and returning the correct aggregation I'm using Sean Lahman's Baseball database to aggregate runs, hits and 'at bats', wins & losses by team between the year 2010 and 2015. I want to join Teams and Batting table and use the group by function on teamID to return total runs, hits, at bats as well as wins and losses by team from the Teams table. For instance, from the teams table I want to return wins and losses year wise team ID Name Wins Losses Year ARI Arizona Diamondbacks 65 97 2010 ARI Arizona Diamondbacks 94 68 2011 And from the Batting Table this is the output I want year teamID Runs Hits At Bats 2012 ARI 734 1416 5462 2015 ARI 720 1494 5649 I tried the following query but it is returning inflated values for wins and losses columns: select b.yearID, b.teamID, SUM(b.R) as Runs, SUM(b.H) as Hits, SUM(b.AB) as At_Bats, t.name as Team_Name, SUM(t.W) as Wins, SUM(t.L) as Losses from Batting b, Teams t where b.teamI...

how can i fetch the people i follow but they don't follow back? so i have a table called follow_sys with three columns id and follower and following containing the usernames of followers and followed ,so i want to fetch all of the followers i follow but they don't follow me back, how can i do that ? follow_sys id follower following i tried a self join but it didn't work, i'm confused $sqlFollowing = "SELECT * from follows_syds t1 join follows_syds t2 on t1.follower != t2.following where t1.follower = ? group by t1.id"; $Following = $conn -> prepare($sqlFollowing); $Following -> bind_param('s',$getUser); $Following -> execute(); $FollowingGET = $Following -> get_result(); $FollowingRows = $FollowingGET -> num_rows; while($b = $FollowingGET -...

3 tables … one datagrid? C# with SQL Server and IIS …? I'm kinda stuck ... I have three tables... I'll simplify ... tbl_MyTopics tbl_MyComments tbl_MyAttachments Obviously, create a topic, add multiple comments (MyTopics.ID = MyComments.ID_MyTopics) Also, add multiple attachments for topics or comments (MyTopics.ID = MyAttachments.ID_MyTopics and/or MyComments.ID=MyAttachments.ID_MyComments) The ABOVE logic (if not clear in my writing above) is working perfectly in the DB and basically if you ever post a COMMENT on a TOPIC ... it connects the comment to the topic by Topic.ID and if you add an attachment via a comment, or directly to the topic, it connects the ATTACHMENT to the TOPIC or the COMMENT ... everything flows back to the TOPIC. I have built the pages for entry, and have a multiple file drop/drag uploader and it populates the tables properly, builds thumbnails, tracks everything for logging, emails based on other logic and all that good stuff. NOW ... I'm onto the p...