Skip to content

Tag: tsql

Determine time duration based on events without using loops

I have a table with timestamps of 5 different types of events (start, stopped, restart, aborted, and completed). The given table looks like this: Time EventID Event 7:38:20 1 start 7:40:20 2 stopped 7:48:20 3 restart 7:50:20 4 aborted 8:00:20 1 start 8:40:20 5 completed 8:58:20 1 start 9:00:15 4 aborted I wou…

tsql – pivot sum of employee

I have here a sample table data which i want to use pivot and make my DedCode data into column. the AMount example is already computed to its total but how can i count the number of employee if it is same with branch,deptcode and emptype. my sample table data. expected output: Answer You can use conditional a…

Variable length substring between two characters

Data looks like this: I want it to look like this: It’s pretty simple to get rid of one or the other. This: Gives me this: Initiative: Credible Sources Initiative: Just in Time Initiative: Database Normalization And this: Gives me this: Having a hard time figuring out how to combine the two. Answer How …

Find items in table with 2 specific sizes

I have items table where the item code repeats as it has different sizes, variants. I want to find items which has 2 specific sizes, ie size in both M/Y and Euro. Items table: Required, I want to query for item id 1 and 3. I was trying with SUM(), CASE but not able to figure it as it involves

Find groups that are missing values of one column

Hopefully someone can send some light on an issue I’m trying to resolve. Types Codes I would like to be able to determine those Code/ID pairs that are missing a particular type. The result of this query should yield. Answer You need conditional aggregation for this: Another option is to cross join the p…