Query help

Question

Post reply

Query help

sqlenthu 89358

Hall of Fame

Points: 3069
More actions
May 24, 2022 at 5:18 pm

#4038382

Hello friends,
I am looking at a query where the designs are as follows:
Table A
Name, DateOfBirth
Sam, 05/17/1988
Michael, 02/26/1982
Samantha, 01/01/1993
Frank, 02/11/1984

I have another table showing some numbers datewise (except the table does not contain weekends and holidays)
Date, Count
01/01/2001, 23
02/01/2001, 35
03/01/2001, 42
04/01/2001, 5
05/01/2001, 16
08/01/2001, 8

Now I simply looking to create a query with Name, DateOfBirth, Date, Count
(Simple BUT for the birthdates which fall on Saturday/Sunday or holidays, the next available date should pick up with other details)
Example: 02/11/1984 is Saturday so the details should be like:
Name, DateOfBirth, Date, Count
Frank, 02/11/1984, 02/13/1984, 12
Please help me. Seems I am overthinking maybe 😞
May 26, 2022 at 7:38 pm

This was removed by the editor as SPAM

June 6, 2022 at 1:37 pm

This was removed by the editor as SPAM
June 15, 2022 at 7:55 am

This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101099 More actions · Answer 1

Didn't have usable data to test with, so the code is untested:

SELECT A.Name, A.DateOfBirth, ot.Count
FROM TableA A
CROSS APPLY (
    SELECT TOP (1) *
    FROM otherTable ot
    WHERE ot.Date >= A.DateOfBirth
    ORDER BY ot.Date
) AS ot

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

sqlenthu 89358 Hall of Fame Points: 3069 More actions · Answer 2

Thanks a lot Scott. That helped. There's one more query which is not showing any issues but i doubt in future when the table size increases, it will start giving issue. Query is something like this:

select a.Col1, a.Col35, b.Col32, c.Col18,
case when a.col5 = 'abc' and a.ColDate6 is not null then datediff(day,a.ColDate5,a.ColDate6) when a.Col5 = 'abc' and a.ColDate6 is null then 0 when a.Col5 <> 'def' then datediff(day,(select top 1 x.ColDateStart from TableTimeTracker x where a.Col10 = x.Col10), getdate()) when a.Col5 = 'def' then datediff(day,(select top 1 x.ColDateStart from TableTimeTracker x where a.Col11 = x.Col11), getdate()) else datediff(day,(select top 1 x.ColDateStart from TableTimeTracker x where a.Col10 = x.Col10), getdate()) end as TimeTrackerDays
from TableA a inner join TableB b on a.col1 = b.col1 inner join TableC c on a.Col1 = c.Col1

In this query, there is a case statement in the "select" section which i think can create issue when table starts bulking up. I am trying to see how this query can be simplified. This is a sample query. Actual one contains many joins and many columns in select section including the case statement.

davelittle Grasshopper Points: 13 More actions · Answer 3

Thanks for the information that you share with us. I will try to figure it out for more.

My PepsiCo

This reply was modified 2 years, 5 months ago by davelittle.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

I think that the easiest way would be to add a column to table A with an "OffsetDate" column built in. That will also me nicely indexable and easy to code for.

--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)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

davelittle wrote:

Thanks for the information that you share with us. I will try to figure it out for more.

Hi and welcome aboard.

Be advised that your post looks and smells like a precursor to spam. If you're a spammer, please go away. We're watching.

If your not a spammer then, like I said, welcome aboard. You'll find some interesting stuff here.

--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)