Grant Fritchey (3/9/2015)

---

Grace09 (3/8/2015)

---

Can anyone help me tune this query? Name column is nonclustered index with include column, ID and ParentID

SELECT DISTINCT .[ID], .[ParentID]

FROM [Items]

WHERE LOWER(.[Name]) = 'Test'

execution...

Example

SELECT h.SHIPTO,LEFT(h.ORDDATE,6) AS [YearMonth],p.YearMonth AS [PastYearMonth]

FROM Past12Months p

LEFT JOIN OEORDH h ON LEFT(h.ORDDATE,6) = p.YearMonth

NOTE: The use of LEFT to match ORDDATE is not efficient as it...

If Past12Months contains ALL 12 months then you need to start with Past12Months and left join OEORDH and OEORDD

faulknerwilliam2 (3/2/2015)

---

Thanks. Basically the report serves as a snapshot of the previous week so is sound for that purpose.

However, when you say ' otherwise data will be aggregated ' -...

Sorry forgot you are using ToolStrip, to close the menu use the HideDropDown method on the owning ToolStripMenuItem of RandomIntToolStripMenuItem

Glad you found a solution however if you are grouping by day name make sure your date range does not span more than a week otherwise data will be aggregated...

My query (with changed column names) against the new sample produced the results shown.

This solution will not scale well as the joining the...

You should use SelectedIndexChanged event to monitor when the user selects a value from a combo box and in your case...

Try this instead

=String.Format("{0:dddd}",Fields!Date.Value)

;WITH cte (BrNum,Emp_Num,Trn_Dt,_Time,Trn_Dt_Time,RowNum)

AS (

SELECTBrNum,Emp_Num,Trn_Dt,_Time,

CAST(CONVERT(char(10),Trn_Dt,120)+' '+STUFF(_Time,3,0,':') as datetime),

ROW_NUMBER() OVER (PARTITION BY BrNum,Emp_Num,Trn_Dt ORDER BY _Time ASC)

FROM Store_

)

SELECT a.BrNum,a.Emp_Num,a.Trn_Dt,a._Time,

ISNULL(CAST(STUFF(CONVERT(char(5),DATEADD(minute,DATEDIFF(minute,b.Trn_Dt_Time,a.Trn_Dt_Time),0),114),3,1,'') as int),0)

FROM cte a

LEFT JOIN cte b ON b.BrNum = a.BrNum AND b.Emp_Num...

Already answered in dupe post

http://www.sqlservercentral.com/Forums/Topic1664045-391-1.aspx

Personally I use the appSettings section of the app.config and have a standard form I use to add to projects to show and edit the settings and restart the app...

What second dataset?

Do you not have one parameter and one dataset (to retrieve all data for all databases) and currently filtering the dataset with the parameter?

Add a filter to the dataset

Set the expression to

=Left(Fields!DatabaseName.Value,Len(Parameters!ReportParameter1.Value))=Parameters!ReportParameter1.Value

Change the expression return to Boolean

Leave operator as =

Set Value to true

SELECT CAST(

CASE SIGN(DATEPART(dy,@Enddate)-DATEPART(dy,@Startdate))

WHEN 0 THEN DATEDIFF(year,@Startdate,@Enddate)

WHEN 1 THEN DATEDIFF(year,@Startdate,@Enddate)+(DATEPART(dy,@Enddate)-DATEPART(dy,@Startdate))/1000.0

ELSE (DATEDIFF(year,@Startdate,@Enddate)-1)+(DATEDIFF(day,DATEADD(year,DATEDIFF(year,@Startdate,@Enddate)-1,@Startdate),@Enddate))/1000.0

END as decimal(9,3))