[T-SQL] Moving the decimal |
- Moving the decimal
- Help with Retrieving 1 Fax number
- How do you analyze a query?
- Help on triggers
- TSQL For Xml Path query to get nested data
- CTE
| Posted: 14 Aug 2013 08:13 AM PDT Hi,I have a decimal field (10,4) that has values in it like:0.01230.00630.0595I need a way in my select statement to make them look like:1.230.635.95I've tried this but it's not correct:SELECTCAST([Overtime Actual Percentage].[Actual Overtime Percentage]*100 AS DECIMAL(20)) AS 'Actual Overtime Percentage', |
| Help with Retrieving 1 Fax number Posted: 14 Aug 2013 06:17 AM PDT Hello all, I was wondering if someone can help me with an issue that I'm having. I'm trying to retrieve 1 fax number on a join. Here's the scenerio.1. I have a Vendors table that has the Vendor ID in it.2. I have a Address_Phone table that contains phone numbers with phone types such as FAX, BUS, etc. In this table it also has a POS (position) column that tells what position that phone number is in. A FAX number can be in any position 1,2,3.. So, in my scenerio I can have 3 record where I have a BUS phone number in POS 1 (First Record) then a FAX number in POS 2 (Second Record) and another FAX number in POS 3 (Third Record). I'm doing a left outer join because a Vendor may not have a fax number.SELECT V.VENDOR_ID, ADDRESS_PHONE_NUM FROM VENDOR V LEFT OUTER JOIN ADDRESS_PHONE AP ONV.VENDOR_ID = AP.VENDOR_ID WHERE ADDRESS_PHONE_TYPE = 'FAX'The problem is that if the VENDOR ID is in ADDRESS_PHONE table more than once (which it is) with more than 1 fax number it brings me back both fax numbers. I only want one; but I'm don't know what position this fax number will be in because it can be in any position if one exists.Any thoughts? |
| Posted: 14 Aug 2013 01:11 PM PDT This is more of a question to people who read code and understand exactly what it does. Like someone posts a bunch of code.. and there are people answering the questions related to it.I would like to know if you have a process or method to analyze the code in your head.. or do you type up a query to see what it does. I have seen people look at a stored procedure that runs into a 2 page printout and know what it does.Most of it is probably experience,knowledge, etc.. but there must an underlying technique to break it down into "human"... |
| Posted: 30 Jun 2013 02:52 AM PDT Dear Friend,Am I able to capture the query inside the trigger that I was executed..?For example, I have instead of delete trigger for a table say SampleTable.[code="sql"] create table sampletable (Id int ,name varchar(800)) create trigger sampletriger on sampletable instead of delete as Print 'Desired Query' insert into sampletable values(1, 'heavenguy') insert into sampletable values(2, 'tristan') insert into sampletable values(3, 'Jack') [/code]Now i'm trying to delete the sampletable...[code="sql"]delete from sampletable where id = 1[/code]I wanted the trigger to return the query that I executed...output:-delete from sampletable where id = 1 |
| TSQL For Xml Path query to get nested data Posted: 14 Aug 2013 02:53 AM PDT I have one xml file with nested data when the xml is loaded with SSIS; it produces 3 tables. I created tables in SQL Server. The 3 tables are:1. Customer (95 columns) 2. Booking - (46 columns) 3. Customer_Booking_ID - (2 columns) is empty in the xml before loading the data but it populated after loading the data which provide the ID's to link the table (not sure how it done to recreate in my own xml)I now have to recreate the same xml format but with more data but I'm currently having problems producing a nested XML format like below:[code="other"]<?xml version="1.0"?><Customers xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="Customers.xsd"> <Customer> <AddressID>12132434454349</AddressID> <AddressOptin>1</AddressOptin> <CustomerID>12132434454349</CustomerID> <Customer_Booking_ID> <Booking> <BookingTypeCode>LastBooked</BookingTypeCode> <Complained>0</Complained> </Booking> <Booking> <BookingTypeCode>LastReturned</BookingTypeCode> <BalanceOutstanding>2333.12</BalanceOutstanding> <BookingReference>ref1232343454</BookingReference> <EngagementChannelCode>CC</EngagementChannelCode> <EnquiryReference>ref1232343454</EnquiryReference> <Complained>0</Complained> </Booking> </Customer_Booking_ID> <LastBrochureRequestDate>2013-07-24</LastBrochureRequestDate> <LastComplaintIssuedDate>2013-07-26</LastComplaintIssuedDate> </CustomerContact> </Customers>[/code][code="sql"]Create Table dbo.Customers ( AddressID Varchar(50) ,AddressOptin int ,CustomerID int ,LastBrochureRequestDate INT ,LastComplaintIssuedDate INT ,Complained Varchar(50))Create Table dbo.Customer_Booking_ID(customerID int /** not in the xml file create during loading data **/ ,bookingID int) /** not in the xml file create during loading data **/ Create Table Booking ( BookingTypeCode Varchar(50) ,BalanceOutstanding int ,BookingReference Varchar(50) ,EngagementChannelCode Varchar(50) ,EnquiryReference Varchar(50) ,BookingID int) **** *** bookingID is also in the Booking Table but is not in the xml file but it created during ssis loading into the tables. INSERT into Customer_Booking_ID ([customerID],[bookingID])Values(1, 1), (2, 2)INSERT INTO Customers (AddressID, AddressOptin,CustomerID,LastBrochureRequestDate,LastComplaintIssuedDate,Complained) VALUES ('1111', 1,1111,'2013-12-12','2013-12-12',0), ('2222', 1,2222,'2013-12-12','2013-12-12',0) INSERT INTO dbo.Booking (BookingTypeCode,BalanceOutstanding,BookingReference,EngagementChannelCode,EnquiryReference,BookingID) VALUES ('LastReturned', 222.13,'BookingREF11','CC','EEREF11',1), ('LastReturned', 222.13,'BookingREF12','CC','EEREF12',2)[/code] |
| Posted: 01 Aug 2013 02:49 AM PDT How do I nested common table expression? |
| You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) To stop receiving these emails, you may unsubscribe now. | Email delivery powered by Google |
| Google Inc., 20 West Kinzie, Chicago IL USA 60610 | |
No comments:
Post a Comment