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, 10 months ago by pietlinden.
• This reply was modified 5 years, 10 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?

Read that maybe 20 times before I understood. LOL Let me see if I have this right... If this were a "normal" table of component parts...

CREATE TABLE Component(ComponentID INT IDENTITY...

If you have a Calendar table that's similar to one you would use for a Date dimension in DAX/SSAS Tabular, you could just specify the start and end dates of...

https://www.sqlshack.com/get-details-of-sql-server-database-growth-and-shrink-events/

AutoShrink = True?

No. Just No.

progress! I created a Table-valued function to do the BOM stuff, then used CROSS APPLY to join it to OrderDetails, and it worked. (It's an approximation of a Work Order...

Same as mine, except I put the column names at the beginning.

Glad you sorted it out. I didn't want to just give you the answer, because then you wouldn't learn...

Oops, yes, I did miss it.  This is what I used. (Funny I would get the answer right and then miss copying it... the easy part!)

SELECT AllergyName
,LowStart...

This is how I did it...

SELECT AllergyName
,LowStart = MAX(CASE WHEN ScaleValue = 'Low' THEN ScaleStart END)
,LowEnd = MAX(CASE WHEN ScaleValue = 'Low' THEN ScaleEnd END)
,ModerateStart = MAX(CASE...

I could do it like this, I suppose...

use AdventureWorks2017;
go

-- what parts are in stock
SELECT currInv.ProductId
, currInv.TotalQOH
, sod.OrderQty
, rt_Sold = SUM(sod.OrderQty) OVER (PARTITION BY currInv.ProductID 
ORDER BY soh.OrderDate
ROWS...

I'll give you a hint... MAX( CASE WHEN...)

It's a LOT of that.

Jeff Moden posted an article on Crosstabs... that might help too. (Don't want to give the game away... I...