Jeff Moden

Forum Replies Created

Viewing 15 posts - 2,431 through 2,445 (of 59,099 total)

Reply To: First wednesday of February & August Every year
Ravi wrote:
SELECT TOP 1 [Date]
FROM DimDate
WHERE CalendarYear= DATEPART(YEAR, getdate())
AND WeekdayLongName= 'Wednesday'
AND CalendarMonthNumber = 2
ORDER BY [Date] ASC

same as above for august month
That means that you have to...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
November 25, 2022 at 10:43 pm

#4118147
Reply To: CASE TRY_CONVERT causes error message

But, you DO have a UNIQUE set of column keys based on Entity, Attribute, and StartDate.
This looks suspiciously like a column based audit table or Slowly Changing Dimension format. Is...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

November 25, 2022 at 3:34 am

#4117799
Reply To: mdf space balance

I don't know your database but I'd take advantage of this as an opportunity to improve. I'd move my two to four largest fragmenting clustered indexes each to their own...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

November 25, 2022 at 3:29 am

#4117794
Reply To: Get all StudentIds for each consecutive date

SwePeso wrote:
You can substitute this simple GREATEST with a CASE WHEN THEN END statement.
Since GREATEST isn't available in 2019, you should post that change to the code for the OP....
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

November 25, 2022 at 2:56 am

#4117783
Reply To: indian authors for sql server book

wrote:
Thanks a lot!
For what?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

November 25, 2022 at 1:15 am

#4117764
Reply To: CASE TRY_CONVERT causes error message

Shifting gears back to the problem, is there a UNIQUE column in the table to identify individual rows with?

--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

November 23, 2022 at 7:49 pm

#4117233
Reply To: CASE TRY_CONVERT causes error message

Chrissy321 wrote:
Thank you both for replying. I apologize for my delayed response, I though I would get an email when follow-ups were posted.
You thought correctly except that functionality was broken...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

November 23, 2022 at 7:40 pm

#4117231
Reply To: Get all StudentIds for each consecutive date
SwePeso wrote:
```
SELECT          StudentId
FROM            @student
GROUP BY  ...
```
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
November 23, 2022 at 7:28 pm

#4117216
Reply To: how to minimize prevent sql injection

@Raja mssql ,
This is meant as a friendly but serious suggestion... IF you even think that you're experiencing "SQL Injection" and YOU are asking these types of questions on a...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

November 23, 2022 at 3:28 pm

#4117106
Reply To: Get all StudentIds for each consecutive date

p.s. This would make a great DA whiteboard interview question... if the candidate only comes up with the solution to the exact problem, you might want to consider someone else....
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

November 23, 2022 at 2:53 pm

#4117089
Reply To: Get all StudentIds for each consecutive date

Just in case anyone wonders what I'm going on about, the original question is a "data analysis" question.
Yep. It can be solved without using a series of dates (and Peter's...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

November 23, 2022 at 2:51 pm

#4117088
Reply To: Get all StudentIds for each consecutive date
SwePeso wrote:
```
-- swePeso solution
WITH cteData(StudentId, delta, rnk)
AS (
        SELECT  StudentId,
         ...
```
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
November 23, 2022 at 2:19 pm

#4117076
Reply To: Get all StudentIds for each consecutive date
jignesh209 ,
Jeffrey must've gotten busy. Apologies for the delay. Here's the code that will answer the question that you asked.
```
 SELECT StudentId
   FROM #Student
 ...
```
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
November 23, 2022 at 2:20 am

#4116850
Reply To: Get all StudentIds for each consecutive date

Ok, Jeffrey... the Op posted some good test data. I'll give you the honors for providing the solution.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

November 23, 2022 at 1:42 am

#4116840
Reply To: Get all StudentIds for each consecutive date

Jeffrey Williams wrote:
Yeah, but in the actual question the OP asks for those students that turned in the assignment for all dates.
Sorry... I was in the process of changing my reply...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

November 23, 2022 at 1:35 am

#4116835

Viewing 15 posts - 2,431 through 2,445 (of 59,099 total)