Forum Replies Created
Viewing 15 posts - 1,411 through 1,425 (of 1,439 total)
-
RE: how to show the last date in the month
Here's another version that will give monthly results for each employee
with cte as (select ID,date_e,val_unit,
row_number() over(partition by ID, convert(char(6),date_e,112) order by date_e desc)...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
RE: how to show the last date in the month
Not entirely clear what you want, ID 4807756 seems to turn up twice in your expected results?
Maybe this
with cte as (select ID,date_e,val_unit,
row_number() over(partition...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
RE: SELECTing Distinct
select Email,max(Fname) as Fname
from mytable
group by Email
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
RE: passing csv list into stored procedure
> Note: I'm not so sure your ROW_NUMBER (ordinal position logic) is correct as you're ordering on the > Numbers, not their position in the string.
It does work. Try it.
As...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
RE: passing csv list into stored procedure
You can quite easily get ordinal numbers by doing this.
CREATE FUNCTION Foo(@p_integer_list VARCHAR(MAX))
RETURNS @return_table TABLE (ROW_NUMBER int IDENTITY(1,1), INTEGER_ID int)
AS
BEGIN;
INSERT INTO @return_table (INTEGER_ID)
SELECT CAST(SUBSTRING(@p_integer_list,
...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
RE: passing csv list into stored procedure
> Personally, I don't like the "numbers" table approach due to limitations on
> the values that can be passed in the delimited string along with duplicate handling.
John,
Can you expand on...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
RE: passing csv list into stored procedure
Using a numbers/sequence table
create procedure [dbo].[_test]@list varchar(50)
as
WITH CTE AS (
SELECT CAST(SUBSTRING(@list,
...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
RE: Creating a finite rollup query
with cte as
(select Col0,Col1,Col2,Col3,
row_number() over(partition by Col1 order by Col0) as rn
from #rollupTable)
select Col1,
max(case when rn=1...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
RE: Script all object in database with TSQL
You can script view, functions, stored procedures and triggers using this
select object_definition(object_id)
from sys.objects
where type_desc in ('SQL_SCALAR_FUNCTION',
'SQL_STORED_PROCEDURE',
'SQL_TABLE_VALUED_FUNCTION',
'SQL_TRIGGER',
'VIEW')
There are plenty of scripts around for tables.
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
RE: xact_abort
Worth reading this
http://www.sommarskog.se/error-handling-I.html
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
RE: Aggregate query problem
Maybe this?
WITH CTE AS(
SELECT a.colA,a.colB
FROM MyTable a
WHERE NOT EXISTS(SELECT * FROM MyTable b
WHERE...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
RE: CTE Recursive Query
Try adding a materialised path to your CTE
With downline (ConsultantID,ConsultantName,SponsorID,SponsorName,DownLineLevel,FullPath)
AS
(
SELECT A.ConsultantID
,A.FirstName + ' ' + A.LastName as ConsultantName
,A.SponsorID
,(SELECT [FirstName] + ' ' + [LastName] FROM Consultant WHERE ConsultantID = a.SponsorID)
,0...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
RE: Calculate number of employees in a month (crosstab)
Using a calendar table such as found here
you can query for the number of employees hired, terminated and total per month using the query below.
There are plenty of articles...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
RE: Get data from XML using XQuery
select r.value('First_Name[1]','varchar(10)') as First_Name,
r.value('Last_Name[1]','varchar(10)') as Last_Name,
r.value('Age[1]','int') as Age
from @Xmldocument.nodes('/Data/Customer') as d(r)
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
RE: Set-based Aggregates Problem
select row_number() over(partition by Value order by Value) as ID,
Value
from TableA
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 15 posts - 1,411 through 1,425 (of 1,439 total)