CTE query that I don't understand well enough to modify

Transact-SQL
1

Afternoon all,

I'll cut to the chase, I have a CTE query that I just about understand but not well enough to modify it. Would some one be good enough to have a look over it and point me in the right direction... The query is:

with tallyno10(n) 
as (select 0 from (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as tallyno10(n)) 
,tallyno1000(n) 
as (select row_number() over(order by (select null))-1 
from tallyno10 as a 
cross apply tallyno10 as b 
cross apply tallyno10 as c) 
select A_TID 
,A_AvailDate 
,A_Num 
from availability 
where a_availdate>=cast(GETDATE() as date) 
union all
select b.b_tid 
,dateadd(day,a.n,b.b_startdate) 
,3
from tallyno1000 as a 
cross apply bookings as b 
where dateadd(day,a.n,b.b_startdate)>=cast(GETDATE() as date) 
and dateadd(day,a.n,b.b_startdate)<=b.b_enddate
ORDER BY A_TID, A_AvailDate, A_Num

On the test database its result is:

|A_TID|A_AvailDate|A_Num|

|11003|2020-01-24|5|
|11003|2020-01-27|3|
|11003|2020-01-28|3|
|11003|2020-01-28|5|
|11003|2020-01-29|3|
|11003|2020-01-30|3|
|11003|2020-01-31|3|

If you look at lines 3 & 4 the dates (A_AvailDate) are the same but the A_Num is different. A '5' needs to replace a '3' if present for the same date but i'm really not sure how to go about this... Any ideas??

Many thanks

Dave

2

hi Dave

It would help to see some test data .. !!!!
drop create sample data

You will have to see the data how its getting populated
One idea is
put a where clause ... to filter exactly !!!
where A_AvailDate = '2020-01-28'

once you do this see
where the 3 and five is coming !!!

I see 3 hardcoded in the bottom part of union all
... but 5 needs to replace what does that mean !!!
is the 5 coming from the top part of union all !!!

3

Hard to tell without sample data, but this may work:

WITH tallyno10 (n)
AS (
	SELECT 0
	FROM (
		VALUES (0)
			,(0)
			,(0)
			,(0)
			,(0)
			,(0)
			,(0)
			,(0)
			,(0)
			,(0)
		) AS tallyno10(n)
	)
	,tallyno1000 (n)
AS (
	SELECT row_number() OVER (ORDER BY (SELECT NULL)) - 1
	FROM tallyno10 AS a
	CROSS APPLY tallyno10 AS b
	CROSS APPLY tallyno10 AS c
	)
, CTE AS (
SELECT A_TID
	,A_AvailDate
	,A_Num
FROM availability
WHERE a_availdate >= cast(GETDATE() AS DATE)

UNION ALL

SELECT b.b_tid
	,dateadd(day, a.n, b.b_startdate)
	,3
FROM tallyno1000 AS a
CROSS APPLY bookings AS b
WHERE dateadd(day, a.n, b.b_startdate) >= cast(GETDATE() AS DATE)
	AND dateadd(day, a.n, b.b_startdate) <= b.b_enddate
)
, CTE_2 AS(
SELECT A_TID
	,A_AvailDate
	,A_Num
	, ROW_NUMBER() OVER(PARTITION BY A_TID, A_AvailDate ORDER BY A_Num DESC) AS RowNum
FROM CTE
)
SELECT A_TID
	,A_AvailDate
	,A_Num
FROM CTE2
WHERE CTE2.RowNum = 1
ORDER BY A_TID
	,A_AvailDate
	,A_Num
4

Dave,

this is the first part of your query

select A_TID
,A_AvailDate
,A_Num
from availability
where a_availdate>=cast(GETDATE() as date)

The value 5 is coming from the availability table and the second part (the union all) is using you CTE with the value hardcoded to 3. With some sample data, it's hard to help you

5

Morning all,

Apologies, no thats a fair comment, I should have been a bit less lazy & included a lot more detail there, temp table & query are as follows:

BEGIN
IF OBJECT_ID('tempdb..#Availability') IS NOT NULL DROP TABLE #Availability
IF OBJECT_ID('tempdb..#Bookings') IS NOT NULL DROP TABLE #Bookings
END

BEGIN
CREATE TABLE #Availability
  ( 
    A_AID INT,
    A_TID INT,
    A_AvailDate DATE,
    A_Colour VARCHAR(10),
    A_Num INT
  ) 

INSERT INTO #Availability
  (A_AID, A_TID, A_AvailDate, A_Colour, A_Num)
VALUES
  ('1000', '11003', '2020-01-24', 'Blue', 5),
  ('1001', '11003', '2020-01-28', 'Blue', 5)

CREATE TABLE #Bookings
  ( 
    B_BID INT,
    B_TID INT,
    B_StartDate DATE,
    B_EndDate DATE
  ) 

INSERT INTO #Bookings
  (B_BID, B_TID, B_StartDate, B_EndDate)
VALUES
  ('3000', '11003', '2020-01-27', '2020-01-31'),
  ('3001', '11003', '2020-02-04', '2020-02-06')
END

BEGIN
WITH tallyno10(n) 
AS (
		SELECT 0 
		FROM (
				VALUES(0),
					  (0),
					  (0),
					  (0),
					  (0),
					  (0),
					  (0),
					  (0),
					  (0),
					  (0)
			  ) AS tallyno10(n)
	) 
	,tallyno1000(n) 
AS (
		SELECT row_number() OVER(ORDER BY (SELECT null))-1 
		FROM tallyno10 AS a 
		cross apply tallyno10 AS b 
		cross apply tallyno10 AS c
	) 
SELECT A_TID,
	   A_AvailDate,
	   A_Num 
FROM #availability 
WHERE a_availdate>=cast(GETDATE() AS DATE) 
UNION all
SELECT b.b_tid,
	   dateadd(day,a.n,b.b_startdate),
	   3
FROM tallyno1000 AS a 
cross apply #bookings AS b 
WHERE dateadd(day,a.n,b.b_startdate)>=cast(GETDATE() AS DATE) 
AND dateadd(day,a.n,b.b_startdate)<=b.b_enddate
ORDER BY A_TID, A_AvailDate, A_Num
END

The results from that shold be:

|A_TID|A_AvailDate|A_Num|
|11003|2020-01-24|5|
|11003|2020-01-27|3|
|11003|2020-01-28|3|
|11003|2020-01-28|5|
|11003|2020-01-29|3|
|11003|2020-01-30|3|
|11003|2020-01-31|3|
|11003|2020-02-04|3|
|11003|2020-02-05|3|
|11003|2020-02-06|3|

The numbers are essentially a status code, 3 is booked & 5 is sick. The problem i have is that if someone is marked sick then the query returns that they are both booked and sick:

|11003|2020-01-28|3|
|11003|2020-01-28|5|

Which in theory is true however, for display purposes i'd like it to return only the sick day eg:

|A_TID|A_AvailDate|A_Num|
|11003|2020-01-24|5|
|11003|2020-01-27|3|
|11003|2020-01-28|5|
|11003|2020-01-29|3|
|11003|2020-01-30|3|
|11003|2020-01-31|3|
|11003|2020-02-04|3|
|11003|2020-02-05|3|
|11003|2020-02-06|3|

Hope that helps a little...

Thanks

Dave

6

hi

++++++++++++++++++++++++++++++++++++++++++++++++++++++++
The problem i have is that if someone is marked sick then the query returns that they are both booked and sick:

Which in theory is true however, for display purposes i'd like it to return only the sick day eg:
+++++++++++++++++++++++++++++++++++++++++++++++++++

Then tell it ... using case statement
That if marked sick ... give only sick from both "booked and sick"

first you will have you see where ( how )
when you say sick
both booked and sick .. are coming !!!

7

Hi Dave,

Try this. It's basically my previous script with a typo corrected.

WITH tallyno10 (n) AS (
SELECT 0
FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS tallyno10(n)
)
,tallyno1000 (n) AS (
SELECT row_number() OVER (ORDER BY (SELECT NULL)) - 1
FROM tallyno10 AS a, tallyno10 AS b, tallyno10 AS c
)
, CTE AS (
SELECT A_TID
,A_AvailDate
,A_Num
FROM #availability
WHERE a_availdate >= cast(GETDATE() AS DATE)

UNION ALL

SELECT b.b_tid
	,dateadd(day, a.n, b.b_startdate)
	,3
FROM tallyno1000 AS a
CROSS APPLY #Bookings AS b
WHERE dateadd(day, a.n, b.b_startdate) >= cast(GETDATE() AS DATE)
	AND dateadd(day, a.n, b.b_startdate) <= b.b_enddate
)
, CTE_2 AS(
SELECT A_TID
	,A_AvailDate
	,A_Num
	, ROW_NUMBER() OVER(PARTITION BY A_TID, A_AvailDate ORDER BY A_Num DESC) AS RowNum
FROM CTE
)
SELECT A_TID
	,A_AvailDate
	,A_Num
FROM CTE_2
WHERE RowNum = 1
ORDER BY A_TID
	,A_AvailDate
	,A_Num
8

Morning,

Apologies for the late reply. Wow, perfect, thank you very much!! That is working exactly as I hoped.

Longer term plans I'm thinking about a bit of a re-write, looking at how the entire process works its a bit messy and not as slick as I think it can be. It currently makes 2 calls to the database, first to retrieve all the Employees, the second to get the data (query above), the third step happens at application level. It generates a list of dates (today + 3 months) then combines all the data into a single table. You can probably tell where i'm going here, I'm wondering if all the data can't be returned from a SQL query in 1 hit... Is somthing like the example below theoretically possible to achieve??

|E_TID|E_EmpName  |E_Status|2020-01-28 Tuesday|2020-01-29 Wednesday|2020-01-30 Thursday| etc
|11003|John Doe   |Live    |0                 |3                   |3                  |         
|11004|Jane Doe   |Live    |3                 |3                   |3                  |         
|11005|Mike Smith |Live    |5                 |5                   |5                  |         
|11006|Helen Brown|Live    |0                 |0                   |0                  |         

Thanks
Dave
9

That's realistic. Success!
:slightly_smiling_face: