October 16, 2023 at 6:32 pm
HI,
Sorry for being so obscure on this problem, just not sure where to start.
I have some data, Student, subject and date, subject is always the same that being "Note"
I'd be running this for each day of the month , each student should have one records each 6 days, but sometimes it can vary.
Here is where I get stuck, I'd like to add days between the actual days of the records,
Here is an example:
StudentJim has a record 9/1 , 9/7, 9/12, 9/19
what I'd like my output to look like is
StudentJim, 9/1
StudentJim, 9/2 <- this would be added in
StudentJim, 9/3 <- this would be added in
StudentJim, 9/4 <- this would be added in
StudentJim, 9/5 <- this would be added in
StudentJim, 9/6 <- this would be added in
StudentJim, 9/7 <- this would be ACTUAL
StudentJim, 9/8 <- this would be added in
StudentJim, 9/9 <- this would be added in
StudentJim, 9/10 <- this would be added in
StudentJim, 9/11 <- this would be added in
StudentJim, 9/12<- this would be ACTUAL
StudentJim, 9/13 <- this would be added in
StudentJim, 9/14 <- this would be added in
StudentJim, 9/15 <- this would be added in
StudentJim, 9/16 <- this would be added in
StudentJim, 9/17<- this would be an ACTUAL record
then the remaining days of month are added in...
Hope this makes sense, any ideas in the right direction are much appreciated
Thanks
October 16, 2023 at 6:43 pm
It's helpful if you give a create table and insert statements, so we understand the structure of the query.
A tally/numbers table can help you get the days of the month. For example, this code will get you all days for the next month from today:
WITH myTally(n)
AS
(SELECT n = ROW_NUMBER() OVER (ORDER BY (SELECT null))
FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) a(n)
CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) b(n)
)
SELECT DATEADD( DAY, n, GETDATE())
FROM myTally
If I were querying a table with a few dates, I'd add this as a CTE and then left join this with the other table.
October 16, 2023 at 7:23 pm
If you want every day in every month, then something like this:
;WITH
cte_tally10 AS (
SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cte_tally100 AS (
SELECT 0 AS number UNION ALL
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS number FROM cte_tally10 c1 CROSS JOIN cte_tally10 c2
)
SELECT Student, DATEADD(DAY, t.number, first_of_month) AS date
FROM dbo.your_table yt
CROSS APPLY (
SELECT CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, date), 0) AS date) AS first_of_month
) AS ca1
INNER JOIN cte_tally100 t ON t.number BETWEEN 0 AND DATEDIFF(DAY, first_of_month, DATEADD(MONTH, 1, first_of_month))
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".
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply