This works... you need a GO statement between your CREATE and INSERT statements if you're going to run a single script, because they have to be in different batches.

            
June 29, 2020 at 6:46 pm
            
#3766265

            
            
                                                
        

use AdventureWorks2017;
go

DECLARE @SalesOrderID INT = 43659;

SELECT SalesOrderID
-- , ProductID
-- , bom.AssemblyName
, bom.Component
, bom.ComponentID
-- , bom.PerAssemblyQty
, TotalPartQty = CAST(SUM(OrderQty * bom.PerAssemblyQty) AS INT)
FROM Sales.SalesOrderDetail sod
CROSS APPLY dbo.udfProductBOM(sod.ProductID) bom
WHERE...

• This reply was modified 5 years, 5 months ago by pietlinden.

So where are you stuck? Post the SQL statement(s), so we can figure out how to help you.

have you looked up GROUP BY yet?

This is close, but it needs to be changed for the first week of year/last week of previous year comparison.

=CALCULATE( [Total Sales], 
    ...

Oh, sorry... left out a bit of critical information/part of a step. If you want to allow the parameter to be null, do this:

With the report in design mode, right-click...

Assuming you cannot modify the stored procedure, and you want to be able to filter out all records WHERE CaseCompleted IS NULL, you can add a filter to your report...

Is there helpful documentation on the PATINDEX function somewhere? the stuff on the MSFT site is useless.

Don't you have to use Contains for that?

https://docs.microsoft.com/en-us/sql/xquery/functions-on-string-values-contains?view=sql-server-ver15

--method of the xml data type. Inside the exit method,  
--the XQuery contains()function is used to  
--determine whether...

This is ugly, but appears to work. (Someone who knows how to do this better than I will undoubtedly chime in and show a much better way of doing it,...

The short answer to your question is to use the LAG function to get the value from the previous record. Something like this...

LAG([LoginTime],1) OVER (PARTITION BY EmpID ORDER BY [LoginTime])

Oh...

• This reply was modified 5 years, 5 months ago by pietlinden.
• This reply was modified 5 years, 5 months ago by pietlinden.

Maybe use SSIS and a For Each file loop, and dump them all into a single table?

Or use Excel... put all the files in a single folder, use a folder...

1 lakh = 100,000

This place is really quiet compared to the normal before COVID.  Makes me wonder if all the SQL folks got laid off.

If EffectiveDate indexed? What does the new execution plan look like for the updated query?