| I can't make heads nor tails of this.... Posted: 10 Sep 2013 06:06 AM PDT I was given this query this morning. It's not a pattern of syntax I can ever recall seeing before. I guess it works, but I can't figure out how to write a query using these conventions. Does this make any sense to anyone here?(Note - I've replaced the table names with 'aaaa', 'bbbb', etc in a lame attempt to disguise where the data comes from, but aside from that, no changes.[code]select rtrim(dbo.aaaa.vst_ext_id), rtrim(convert(char,dbo.aaaa.adm_ts,1)), rtrim(convert(char,dbo.aaaa.dschrg_ts,1)), rtrim(convert(char,bth_ts,1)), datediff(year,bth_ts,dbo.aaaa.dschrg_ts), isnull(pat_calc_age_no,0), isnull(pat_calc_age_unit,''), rtrim(bbbb.cod_dtl_ext_id), rtrim(cccc.cod_dtl_ext_id), rtrim(dddd.cod_dtl_ext_id), rtrim(dbo.eeee.plan_ext_id), rtrim(ffff.cod_dtl_ext_id) from dbo.kkkk right join (dbo.eeee right join (dbo.gggg right join (dbo.hhhh ffff inner join (dbo.hhhh dddd inner join (dbo.jjjj inner join (dbo.hhhh cccc inner join (dbo.hhhh bbbb inner join dbo.aaaa on bbbb.cod_dtl_int_id = dbo.aaaa.pat_ty) on dbo.aaaa.vst_sta_cd = cccc.cod_dtl_int_id) on dbo.jjjj.psn_int_id = dbo.aaaa.psn_int_id) on dddd.cod_dtl_int_id = dbo.aaaa.fin_cls_cd) on ffff.cod_dtl_int_id = dbo.aaaa.dschg_srv_cd) on dbo.gggg.vst_int_id = dbo.aaaa.vst_int_id) on dbo.eeee.plan_int_id = dbo.gggg.plan_int_id) on dbo.kkkk.vst_int_id = dbo.aaaa.vst_int_id where cccc.cod_dtl_ext_id <> 'ADMIT' and dbo.gggg.pyr_seq_no = 4981 and dbo.aaaa.dschrg_ts > '12/31/2003' and dbo.aaaa.dschrg_ts < '1/1/2005' order by VisitID[/code] |
| Compare a date to a previous group of records' max date Posted: 10 Sep 2013 10:15 PM PDT I want to reference a previous group's max date and compare it to the date on the current record.My data looks like this:-Grp - Date 00 - 28-10-2012 10 - 29-10-2012 10 - 28-10-2012 10 - 28-10-2012 20 - 30-10-2012 20 - 05-11-2012 20 - 10-11-2012 20 - 30-10-2012 30 - 01-11-2012So we can that for Grp 10, the max date is 29-10-2012, for Grp 20, the max date is 10-11-2012, etc...I want to reference the previous group's max date and compare it to the date on the current record.For example, for Grp 20, dated 05-11-2012, it needs to be compared to the max date for Grp 10 (29-10-2012) to calculate the difference in dates.Grp - Date - Previous Date - Delay 00 - 28-10-2012 - 10 - 29-10-2012 - 28-10-2012 - 1 10 - 28-10-2012 - 28-10-2012 - 0 10 - 28-10-2012 - 28-10-2012 - 0 20 - 30-10-2012 - 29-10-2012 - 1 20 - 05-11-2012 - 29-10-2012 - 7 20 - 10-11-2012 - 29-10-2012 - 12 20 - 30-10-2012 - 29-10-2012 - 1 30 - 12-11-2012 - 10-11-2012 - 2Is this possible in TSQL or do i need to write Package/Function?Any guidance would be appreciated. |
| Weird Date Issue slowing down Stored Proc Posted: 10 Sep 2013 10:05 PM PDT Hi All, can anyone help shed some light on the following issue...SQL Server 2008 SP.My SP returns data almost instantly if the date range I supply has data on all possible dates within the date range. If however, I put a date range in that includes one or more days without date, it grinds to a halt.For example, if I run it so the date range is a monday to friday and each of these days has data, the date returns instantly. However if I run it from say, thursday to saturday (and the saturday has no data), the SP takes about 15 seconds to run. If I scale up the date range to about 6 months, there may be many days with no data - suddenly the query is taking 4 to 5 minutes to run! I have indexes set up on all the joining columns and the date field. Sp below (apologies that I can't get it to format into a nice layout here)...ALTER PROCEDURE [dbo].[sp07319_UT_Reporting_Surveyors_by_Spans_Surveyed](@StartDate DATETIME, @EndDate DATETIME, @CircuitFilter VARCHAR(MAX), @VoltageFilter VARCHAR(MAX), @SurveyorFilter VARCHAR(MAX))ASSET NOCOUNT ONSELECT A.[intStaffID] AS [StaffID], ISNULL(A.[strForename], '') AS [Forename], ISNULL(A.[strSurname], '') AS [Surname], ISNULL(A.[strSurname], '') + ': ' + ISNULL(A.[strForename], '') AS [SurveyorName], ISNULL(Y.[SurveyedSpanCount], 0) AS [SurveyedSpanCount], ISNULL(Y.[SurveyedSpanClear], 0) AS [SurveyedSpanClear], ISNULL(Y.[SurveyedSpanNotClear], 0) AS [SurveyedSpanNotClear], ISNULL(Z.[ToBeSurveyedSpanCount], 0) AS [ToBeSurveyedCount], ISNULL(X.[AllocatedSpanCount], 0) AS [AllocatedSpanCount]FROM [tblStaff] A LEFT OUTER JOIN (SELECT B.[SurveyorID], ISNULL(COUNT(A.[SurveyedPoleID]), 0) AS [AllocatedSpanCount] FROM [UT_Surveyed_pole] A INNER JOIN [UT_Survey] B ON A.[SurveyID] = B.[SurveyID] INNER JOIN [UT_Pole] C ON A.[PoleID] = C.[PoleID] INNER JOIN [UT_Circuit] D ON C.[CircuitID] = D.[CircuitID] WHERE B.[SurveyDate] >= @StartDate AND B.[SurveyDate] <= @EndDate AND (@CircuitFilter = '' OR C.[CircuitID] IN (SELECT [splitstring] FROM dbo.udf_parse_string_into_integer_table(@CircuitFilter))) AND (@VoltageFilter = '' OR D.[VoltageID] IN (SELECT [splitstring] FROM dbo.udf_parse_string_into_integer_table(@VoltageFilter))) GROUP BY B.[SurveyorID]) X ON A.[intStaffID] = X.[SurveyorID] LEFT OUTER JOIN (SELECT B.[SurveyorID], ISNULL(COUNT(A.[SurveyedPoleID]), 0) AS [SurveyedSpanCount], ISNULL(SUM(CASE ISNULL(A.[IsSpanClear], 0) WHEN 1 THEN 1 ELSE 0 END), 0) AS [SurveyedSpanClear], ISNULL(SUM(CASE ISNULL(A.[IsSpanClear], 0) WHEN 0 THEN 1 ELSE 0 END), 0) AS [SurveyedSpanNotClear] FROM [UT_Surveyed_pole] A INNER JOIN [UT_Survey] B ON A.[SurveyID] = B.[SurveyID] INNER JOIN [UT_Pole] C ON A.[PoleID] = C.[PoleID] INNER JOIN [UT_Circuit] D ON C.[CircuitID] = D.[CircuitID] WHERE B.[SurveyDate] >= @StartDate AND B.[SurveyDate] <= @EndDate AND ISNULL(A.[SurveyStatusID], 0) > 0 AND (@CircuitFilter = '' OR C.[CircuitID] IN (SELECT [splitstring] FROM dbo.udf_parse_string_into_integer_table(@CircuitFilter))) AND (@VoltageFilter = '' OR D.[VoltageID] IN (SELECT [splitstring] FROM dbo.udf_parse_string_into_integer_table(@VoltageFilter))) GROUP BY B.[SurveyorID]) Y ON A.[intStaffID] = Y.[SurveyorID] LEFT OUTER JOIN (SELECT B.[SurveyorID], ISNULL(COUNT(A.[SurveyedPoleID]), 0) AS [ToBeSurveyedSpanCount] FROM [UT_Surveyed_pole] A INNER JOIN [UT_Survey] B ON A.[SurveyID] = B.[SurveyID] INNER JOIN [UT_Pole] C ON A.[PoleID] = C.[PoleID] INNER JOIN [UT_Circuit] D ON C.[CircuitID] = D.[CircuitID] WHERE B.[SurveyDate] >= @StartDate AND B.[SurveyDate] <= @EndDate AND ISNULL(A.[SurveyStatusID], 0) = 0 AND (@CircuitFilter = '' OR C.[CircuitID] IN (SELECT [splitstring] FROM dbo.udf_parse_string_into_integer_table(@CircuitFilter))) AND (@VoltageFilter = '' OR D.[VoltageID] IN (SELECT [splitstring] FROM dbo.udf_parse_string_into_integer_table(@VoltageFilter))) GROUP BY B.[SurveyorID]) Z ON A.[intStaffID] = Z.[SurveyorID]WHERE (@SurveyorFilter = '' OR A.[intStaffID] IN (SELECT [splitstring] FROM dbo.udf_parse_string_into_integer_table(@SurveyorFilter))) AND ISNULL(A.[IsUtilityArbSurveyor], 0) = 1ORDER BY [Surname] ASC, [Forename] ASC |
| variable product costing Posted: 10 Sep 2013 10:41 PM PDT Not sure how to explain this but here goes...I have a forecast of volume for a product for the year. A product costing can change through the year.I need to calculate a forecast value based on a costing table.so something like...[code]SELECT fcast.Year, fcast.Customer, fcast.StockCode, fcast.Type, fcast.Measure, fcast.MonthNo, fcast.Volume, cost.CostFROM #forecast_unpivot fcastLEFT JOIN( SELECT Customer, StockCode, Year, MonthNo, Cost FROM #costing) costON fcast.Year= cost.YearAND fcast.Customer = cost.CustomerAND fcast.StockCode = cost.StockCodeAND fcast.Year = cost.YearAND fcast.MonthNo = cost.MonthNo[/code]but i only get cost matches on the exact month (i've attached the results in a text file (sqcl.txt), so the NULLS need to be the previous costing)i need to get jan cost to appear in feb then when the cost change in mar apply the new cost to all future months.if i use >= on month i get duplication.thanks** edit - i've add the full sql code, creates tables (temp), data etc. (full_sql.txt)*** edit - i've added another text file (monthNm_to_monthNo.txt) which is a function to convert date name to date number |
| IF EXISTS ( SELECT 1 ......vs..... IF EXISTS ( SELECT * Posted: 10 Sep 2013 06:59 PM PDT Hi,is there a difference in terms of performance?. The execution plans are identical. Does an index on or off, an index be used? Is there a rule what syntax should be used when?RegardsNicole ;-)--------------------------------- IF EXISTS ( SELECT * FROM Northwind.dbo.Customers WHERE CustomerID = 'ALFKI' ) PRINT '1' ELSE PRINT '0'--------------------------------- IF EXISTS ( SELECT 1 FROM Northwind.dbo.Customers WHERE CustomerID = 'ALFKI' ) PRINT '1' ELSE PRINT '0'--------------------------------- IF EXISTS ( SELECT COUNT(1) FROM Northwind.dbo.Customers WHERE CustomerID = 'ALFKI' ) PRINT '1' ELSE PRINT '0'--------------------------------- IF EXISTS ( SELECT TOP(1) CustomerID FROM Northwind.dbo.Customers WHERE CustomerID = 'ALFKI' ) PRINT '1' ELSE PRINT '0' |
| Geography, find the common most point from 4 Lat/Long points Posted: 10 Sep 2013 05:48 AM PDT I am trying to create a Function that I can pass 4 data points (Lat/Long) to and have it calculate the center-most point (lat/long). I thought of using a Polygon and using the EnvelopeCenter function, but I cannot control what order the data points are entered. The field techs make four readings and then upload the results. They take the readings from the same point. My sample DB is rough, but hopefully enough to give you an idea of what I am working with. I am new to the Geography data type. --Creating table to hold spatial dataCREATE TABLE dbo.StreetSignSpaital ( ID uniqueidentifier NOT NULL DEFAULT(NEWID()) , SignUnitID varchar(25) NOT NULL , Point1 geography NULL , Point2 geography NULL , Point3 geography NULL , Point4 geography NULL)--Loading a sample record. INSERT INTO dbo.StreetSignSpatial( SignUnitID , Point1 , Point2 , Point3 , Point4 )SELECT 'E004010038DBE180' , geography::STGeomFromText( 'POINT(-122.561536 45.603118166)', 4326) , geography::STGeomFromText( 'POINT(-122.5615208333 45.6031455)', 4326) , geography::STGeomFromText( 'POINT(-122.56151366666 45.603131666)', 4326) , geography::STGeomFromText( 'POINT(-122.56154616666 45.603134333)', 4326)I did come across something on a Google search that I could simply take the aggregate of the Lat values and the Long values and that would be the common point, but that seems WAY too simple. Hope someone can help and thank you for looking.JJ |
| joining tables Posted: 10 Sep 2013 07:24 AM PDT hello all, Here's what I got:Table 1 structure:Id int,controlID varchar(13)data:1,10002,10013,10024,10035,1004And so ontable 2:id int,controlstart varchar(13),controlend varchar(13),box intdata:1,1000,1001,12,1002,1003,23,1004,1005,3and so onHow would I write a query to join the 2 tables that would display info like this:1000,11001,11002,21003,21004,31005,3Thanks for all the help! |
| view points to linked server tables Posted: 10 Sep 2013 06:32 AM PDT Our lead wants me to maintain two different version of VIEWfor DEV and PROD.We're building custom reports for Financial Application systemand we're not allowed to query PROD Db directly, only through views.In DEV it is "SELECT * FROM EU1_Transform.dbo.MapGLAccount INNER JOIN EU1_Transform.dbo.iScalaCompanies ...."In PROD he wants me to have this:"SELECT * FROM LINKEDSERVER.dbo.EU1_Transform.dbo.MapGLAccount INNER JOIN LINKEDSERVER.EU1_Transform.dbo.iScalaCompanies ...."I'm not sure inner joins work the same way with linked server.I mean will the right INDEXES be used and so on?Is it a good idea? |
