Wednesday, July 31, 2013

[T-SQL] how to get members of date dimension from current month

[T-SQL] how to get members of date dimension from current month

how to get members of date dimension from current month

Posted: 30 Jul 2013 07:25 PM PDT

Hi ,my query is:select distinct calendarmonthkey, calendarmonth from dim.dateorder by calendarmonthkey[b]Result:[/b] 205801 jan 2058205802 feb 2058........198001 jan 1980[b]expected result is :[/b]201307 jul 2013201306 jun 2013201305 may 2013...........198001 jan 1980or else it can be starts with previous month(jun 2013).please suggest Regards,Niharika

SSIS ( cannot able to change datatype column using datatype conversion......

Posted: 30 Jul 2013 09:42 PM PDT

Hi, Source: multiple text fileRequirement: 1. Define age columns as small integer.2. Convert gross column from $ 667 00 to 667.00 and change datatype from string to money.3. Convert date format from 12122012 to 12/12/2012 and change datatype from string to date data type.4. Finally load all flat file to a db table in sql server using a single destination.For which I converted gross column using dervied column transformation and for date column using substring.Now i can't able to change gross and date columns datatype from string to money and date data type.Please help me to solve this issue....Thanks in advance

T-SQL subquery

Posted: 30 Jul 2013 05:37 PM PDT

Hello guys, I have performance question or what is better for serverI have select[quote]SELECT TOP 5 CONVERT(DATETIME,WOT.ORDER_DATE) ORDER_DATE, WOT.COMMIT_NO, WOT.BODY_NO, WOT.CAR_CODE, WOT.PROD_FLAG FROM ( SELECT TOP 2 WO.ORDER_DATE, WO.COMMIT_NO, WO.BODY_NO, WO.CAR_CODE + ' ' + WO.DRIVE_TYPE CAR_CODE , 'T' PROD_FLAG FROM TB_WORK_ORDER_TRSB1 WO LEFT JOIN TB_TRACKING_TRSB1 TR ON WO.ORDER_DATE = TR.ORDER_DATE AND WO.COMMIT_NO = TR.COMMIT_NO WHERE WO.ORDER_DATE <= @vLAST_ORDER_DATE AND WO.ORDER_DATE + WO.COMMIT_NO <= @vLAST_ORDERCOMMIT AND DATA_TYPE <> 'SD' ORDER BY WO.ORDER_DATE DESC, WO.COMMIT_NO DESC UNION ALL SELECT WO.ORDER_DATE, WO.COMMIT_NO, WO.BODY_NO, WO.CAR_CODE + ' ' + WO.DRIVE_TYPE CAR_CODE , CASE ISNULL(TR.WORK_COMPLETE,'') WHEN '' THEN 'F' WHEN 'F' THEN 'Y' WHEN 'T' THEN 'G' END PROD_FLAG FROM TB_WORK_ORDER_TRSB1 WO LEFT JOIN TB_TRACKING_TRSB1 TR ON WO.ORDER_DATE = TR.ORDER_DATE AND WO.COMMIT_NO = TR.COMMIT_NO WHERE WO.ORDER_DATE >= @vLAST_ORDER_DATE AND WO.ORDER_DATE + WO.COMMIT_NO > @vLAST_ORDERCOMMIT AND DATA_TYPE <> 'SD' ) WOT [/quote][b]And what do you think what is it better .. to each clausule WHERE add query below or OR add behind *TEMP* table WOT on end of query to WHERE ? [/b][quote]AND WO.ORDER_DATE+WO.COMMIT_NO IN ( SELECT distinct ORDER_DATE+COMMIT_NO FROM TB_MASTER_TRSB1 WHERE ORDER_DATE >= @vLAST_ORDER_DATE AND STATION_ID = @rSTATION_ID )[/quote]Here are result from statistics [quote]--- NEW with MASTER in each union CPU time = 0 ms, elapsed time = 0 ms.Table 'TB_TRACKING_TRSB1'. Scan count 2, logical reads 66, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'Worktable'. Scan count 1, logical reads 1153, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'TB_WORK_ORDER_TRSB1'. Scan count 2, logical reads 34, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'TB_MASTER_TRSB1'. Scan count 2, logical reads 364, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 31 ms, elapsed time = 26 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 31 ms, elapsed time = 26 ms. --- NEW with MASTER in TEMP WOT Table 'Worktable'. Scan count 1, logical reads 1678, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'TB_MASTER_TRSB1'. Scan count 2, logical reads 364, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'TB_WORK_ORDER_TRSB1'. Scan count 2, logical reads 31, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'TB_TRACKING_TRSB1'. Scan count 2, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 31 ms, elapsed time = 25 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 31 ms, elapsed time = 26 ms.[/quote]What do you think about it ?

Need Help TSQL Code.

Posted: 30 Jul 2013 07:54 AM PDT

I have source file with columnFull NameJim k SmithRobert K LinChris Timand My destination hasFirstName and LastNameI want to parse FullNameI know how I can use SSIS Expression to parse the address, Here it isFirstName = LTRIM(RTRIM(TRIM(REVERSE(SUBSTRING(REVERSE([Full Name]),1,FINDSTRING(REVERSE([Full Name])," ",1))))))LastName = LTRIM(RTRIM(TRIM(SUBSTRING([Full Name],1,FINDSTRING([Full Name]," ",1)))))Is any one can help me to transfer in T-SQL?Thank You in advance.

Universal CSV-Importer

Posted: 30 Jul 2013 07:17 AM PDT

Is there a way, to import csv Files with different count of fields (all nvarchar (max)) with one Stored-Procedure in one fix table (with Import-ID) , without having to make much adjustment? The Files are flat Staging csv-Files. Maybe with pivot/unpivot Function etc...Field and Rowdelimiter are allways the same.Thank youKind RegardsNicole ;-)
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog