week list
-
I need to generate a select statement that contains dates on a weekly basis. I do not want to put these in a table since the user could run this at any time, not necessarily based on a calendar week.
After that I need to bring in data based on if this date matches a date from a particular table.
This is what I have so far. Example:
SELECT
CASE WHEN dateadd(d, +7,'2006-07-01') = dateadd(d, +7,'2006-07-01') Then dateadd(d, +7,'2006-07-01')
WHEN dateadd(d, +13,'2006-07-01') = dateadd(d, +13,'2006-07-01') Then dateadd(d, +13,'2006-07-01')
WHEN dateadd(d, +20,'2006-07-01') = dateadd(d, +20,'2006-07-01') Then dateadd(d, +20,'2006-07-01')
WHEN dateadd(d, +27,'2006-07-01') = dateadd(d, +27,'2006-07-01') Then dateadd(d, +27,'2006-07-01')
WHEN dateadd(d, +34,'2006-07-01') = dateadd(d, +34,'2006-07-01') Then dateadd(d, +34,'2006-07-01')
WHEN dateadd(d, +41,'2006-07-01') = dateadd(d, +41,'2006-07-01') Then dateadd(d, +41,'2006-07-01')
WHEN dateadd(d, +48,'2006-07-01') = dateadd(d, +48,'2006-07-01') Then dateadd(d, +48,'2006-07-01')
WHEN dateadd(d, +55,'2006-07-01') = dateadd(d, +55,'2006-07-01') Then dateadd(d, +55,'2006-07-01')
WHEN dateadd(d, +62,'2006-07-01') = dateadd(d, +62,'2006-07-01') Then dateadd(d, +62,'2006-07-01')
WHEN dateadd(d, +69,'2006-07-01') = dateadd(d, +69,'2006-07-01') Then dateadd(d, +69,'2006-07-01')
WHEN dateadd(d, +76,'2006-07-01') = dateadd(d, +76,'2006-07-01') Then dateadd(d, +76,'2006-07-01')
WHEN dateadd(d, +83,'2006-07-01') = dateadd(d, +83,'2006-07-01') Then dateadd(d, +83,'2006-07-01')
WHEN dateadd(d, +90,'2006-07-01') = dateadd(d, +90,'2006-07-01') Then dateadd(d, +90,'2006-07-01')
else '' end as sortdate
The problem with this is that it only sees the first 'when' and ignores the rest. I want 12 rows of data that show 07/08/06, 07/15/06, 07/22/06, etc(see below)
07/08/06 123 99% 1 2
07/15/06 456 89% 0 1
07/22/06 789 87% 2 0
I have tried to use a union statement but I can't bring in any other columns in another union statement.
Any help is appreciated. Thanks
-
Here is a starter
DECLARE @BaseDate DATETIME
SELECT @BaseDate = '20060814'
SELECT DATEADD(week, b3.i + b2.i + b1.i + b0.i, @BaseDate)
FROM (SELECT 0 i UNION ALL SELECT 1) b0
CROSS JOIN (SELECT 0 i UNION ALL SELECT 2) b1
CROSS JOIN (SELECT 0 i UNION ALL SELECT 4) b2
CROSS JOIN (SELECT 0 i UNION ALL SELECT 8) b3
WHERE b3.i + b2.i + b1.i + b0.i < 12
N 56°04'39.16"
E 12°55'05.25" -
Thanks a lot. That works great for the weeks. I have been trying to incorporate a count (basically) in another column into that statement. How would I do something like this?
if the moveindate falls between week1 and week2 then count 1. Then if
moveindate falls betwen week2 and week3 then count 1 and so on...
Then sum that up in a total.
-
DECLARE @BaseDate DATETIME
SELECT @BaseDate = '20060814'
SELECT DATEADD(week, b3.i + b2.i + b1.i + b0.i, @BaseDate),
DATEADD(day, 6, DATEADD(week, b3.i + b2.i + b1.i + b0.i, @BaseDate)),
count(*) as SomethingCounting
FROM (SELECT 0 i UNION ALL SELECT 1) b0
CROSS JOIN (SELECT 0 i UNION ALL SELECT 2) b1
CROSS JOIN (SELECT 0 i UNION ALL SELECT 4) b2
CROSS JOIN (SELECT 0 i UNION ALL SELECT 8) b3
INNER JOIN SomeOtherTable sot ON sot.SomeDateColumn BETWEEN DATEADD(week, b3.i + b2.i + b1.i + b0.i, @BaseDate) AND DATEADD(day, 6, DATEADD(week, b3.i + b2.i + b1.i + b0.i, @BaseDate))
WHERE b3.i + b2.i + b1.i + b0.i < 12
GROUP BY b3.i + b2.i + b1.i + b0.i
N 56°04'39.16"
E 12°55'05.25" -
Tammy,
Peter's solution, as usual, is spot on. His ingenious crosstabs return the sequence of numbers of 0 through 15 in less than a heartbeat and then he applies 0-11 (<12) as weeks to add to a base date.
There are a great number of queries that benefit greatly from having a sequence of numbers including CSV splits, other date prestidigitation, Title casing, data cleansing, and a whole lot more including the replacement of Cursors and While loops as Peter did with his code. With that in mind, allow me to introduce you to the unassuming yet powerful Tally or Numbers table (different names for the same thing).
A Tally or Numbers table is nothing more than a permanent table with a single column containing a sequence of numbers from 1 to some number. Most will make this table run from 1 to 8000 for VARCHAR splits and the like. I make mine from 1 to 11000 because I also use it for simple 1, 2, 3, or 4 digit random number generation and because there are 10957.5 days (based on 365.25 days in a year) in 30 years.
Here’s how to make a permanent Tally or Numbers table… it also uses the same very high speed crosstab method that Peter used in his good solution…
- --===== Create and populate the Tally table on the fly
- SELECT TOP 11000
- IDENTITY(INT,1,1) AS N
- INTO dbo.Tally
- FROM Master.dbo.SysColumns sc1,
- Master.dbo.SysColumns sc2
- --===== Add a Primary Key to maximize performance
- ALTER TABLE dbo.Tally
- ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N)
- --===== Allow the general public to use it
- GRANT SELECT ON dbo.Tally TO PUBLIC
... As you can see, it only has 1 column called "N" (for "number") and that column has a clustered primary key on it (which fully covers the table) for maximum speed.
Now, once you have a Tally table, you can simplify queries like Peter's first as follows...
--===== Declare and set the "base date"
DECLARE @BaseDate DATETIME
SET @BaseDate = '20060814'
--===== Produce a list of 12 weeks starting from the base date
SELECT DATEADD(wk,N-1,@BaseDate) AS ThisWeekStart,
DATEADD(wk,N ,@BaseDate) AS NextWeekStart
FROM dbo.Tally
WHERE N <= 12
Now, let's just say you wanted to change Peter's first query to give you, say, 52 weeks... here's how it would look...
DECLARE @BaseDate DATETIME
SELECT @BaseDate = '20060813'
SELECT DATEADD(week, b5.i + b4.i + b3.i + b2.i + b1.i + b0.i, @BaseDate)
FROM (SELECT 0 i UNION ALL SELECT 1) b0
CROSS JOIN (SELECT 0 i UNION ALL SELECT 2) b1
CROSS JOIN (SELECT 0 i UNION ALL SELECT 4) b2
CROSS JOIN (SELECT 0 i UNION ALL SELECT 8) b3
CROSS JOIN (SELECT 0 i UNION ALL SELECT 16) b4
CROSS JOIN (SELECT 0 i UNION ALL SELECT 32) b5
WHERE b5.i + b4.i + b3.i + b2.i + b1.i + b0.i < 52
... it's gotten a wee bit more complicated. Check out how much more complicated (not) the Tally table method would get...
--===== Declare and set the "base date"
DECLARE @BaseDate DATETIME
SET @BaseDate = '20060814'
--===== Produce a list of 52 weeks starting from the base date
SELECT DATEADD(wk,N-1,@BaseDate) AS ThisWeekStart,
DATEADD(wk,N ,@BaseDate) AS NextWeekStart
FROM dbo.Tally
WHERE N <= 52
... two characters in the whole query changed and one of those was in a comment!
Needless to say, Peter's second query would get quite a bit more complicated to change to 52 weeks...
DECLARE @BaseDate DATETIME
SELECT @BaseDate = '20060814'
SELECT DATEADD(week, b5.i + b4.i + b3.i + b2.i + b1.i + b0.i, @BaseDate),
DATEADD(day, 6, DATEADD(week, b5.i + b4.i + b3.i + b2.i + b1.i + b0.i, @BaseDate)),
count(*) as SomethingCounting
FROM (SELECT 0 i UNION ALL SELECT 1) b0
CROSS JOIN (SELECT 0 i UNION ALL SELECT 2) b1
CROSS JOIN (SELECT 0 i UNION ALL SELECT 4) b2
CROSS JOIN (SELECT 0 i UNION ALL SELECT 8) b3
CROSS JOIN (SELECT 0 i UNION ALL SELECT 16) b4
CROSS JOIN (SELECT 0 i UNION ALL SELECT 32) b5
INNER JOIN SomeOtherTable sot ON sot.SomeDateColumn BETWEEN DATEADD(week, b5.i + b4.i + b3.i + b2.i + b1.i + b0.i, @BaseDate) AND DATEADD(day, 6, DATEADD(week, b5.i + b4.i + b3.i + b2.i + b1.i + b0.i, @BaseDate))
WHERE b5.i + b4.i + b3.i + b2.i + b1.i + b0.i < 12
GROUP BY b5.i + b4.i + b3.i + b2.i + b1.i + b0.i
Here's what the same query would look like using the Tally table...
DECLARE @BaseDate DATETIME
SET @BaseDate = '20060814'
SELECT DATEADD(wk,t.N-1,@BaseDate) AS ThisWeekStart,
DATEADD(wk,t.N,@BaseDate) AS NextWeekStart,
COUNT(*) AS SomethingCounting
FROM dbo.Tally t,
dbo.SomeOtherTable sot
WHERE sot.SomeDateColumn >= DATEADD(wk,t.N-1,@BaseDate)
AND sot.SomeDateColumn < DATEADD(wk,t.N,@BaseDate)
AND t.N <= 52
...no... nothing's missing... it does the same thing (although it does allow for times on the dates where Peter's will miss anything that has a time on the last day of the week) ...
Let's go one step further (not that you would in this case, but bear with me... trying to make a point here). Let's say you turn in the 52 week solution using Peter's crosstab method. QA tests it and says it works fine for 52 weeks but the requirement has changed to 1040 weeks (20 years instead of just 1). You would have to add 4 more crosstabs for 64, 128, 512, and 1024 and 4 more additions to each formula...
... using the Tally table method, here's all that you would do (I highlighted the change) ...
DECLARE @BaseDate DATETIME
SET @BaseDate = '20060814'
SELECT DATEADD(wk,t.N-1,@BaseDate) AS ThisWeekStart,
DATEADD(wk,t.N,@BaseDate) AS NextWeekStart,
COUNT(*) AS SomethingCounting
FROM dbo.Tally t,
dbo.SomeOtherTable sot
WHERE sot.SomeDateColumn >= DATEADD(wk,t.N-1,@BaseDate)
AND sot.SomeDateColumn < DATEADD(wk,t.N,@BaseDate)
AND t.N <= 1040
... sorry that took so long...
Let's go just a bit further... let's say that the requirement stayed at 20 years BUT, the reporting periods changed from week long spans to DAILY (again, just trying to make a point)! That's 7305 days (if you calc a year as 365.25 days)... Using Peter's method, you would have to add yet 2 more crosstabs for 2048 and 4096 and do yet two more additions to each formula!!!
Here's all that you would need to change using the Tally table method (again, highlighted the changes)...
DECLARE @BaseDate DATETIME
SET @BaseDate = '20060814'
SELECT DATEADD(dd,t.N-1,@BaseDate) AS TodayStart,
DATEADD(dd,t.N,@BaseDate) AS TomorrowStart,
COUNT(*) AS SomethingCounting
FROM dbo.Tally t,
dbo.SomeOtherTable sot
WHERE sot.SomeDateColumn >= DATEADD(dd,t.N-1,@BaseDate)
AND sot.SomeDateColumn < DATEADD(dd,t.N,@BaseDate)
AND t.N <= 7305
Do you understand my point? You NEED to make a permanent Tally table.
Write back if you have any questions...
--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) -
I am happy you filled in the blanks, Jeff
N 56°04'39.16"
E 12°55'05.25" -
Thanks so much for the reply. Both methods work wonderfully!
-
Peter, can I ask another question I just encountered. My dates are as follows:
8/18/06 - 8/24/06,
8/25/06 - 8/31/06,
9/01/06 - 9/07/06, etc.
What if I wanted to start the next week with the last day of the previous week? 8/25 would 8/24
Thanks
-
O.k. Peter,
I am stuck now. I have the following: I am counting moveins and moveouts. For my beginning number I am getting repetitious counts. For example, if someone moves in at the beginning of a week, such as 9/4 it is counted in the 'weekbegin' twice.
SELECT
DATEADD(week, b3.i + b2.i + b1.i + b0.i, '2006-08-21') sortdate,
DATEADD(day,6,DateAdd(week,b3.i + b2.i + b1.i + b0.i,'2006-08-21')) as sortdate2,
count(t.scode) as weekbegin,
count(t2.scode) as movein,
count(t3.scode) as moveout,
count(u.scode) as uscode
FROM (SELECT 0 i UNION ALL SELECT 1) b0
CROSS JOIN (SELECT 0 i UNION ALL SELECT 2) b1
CROSS JOIN (SELECT 0 i UNION ALL SELECT 4) b2
CROSS JOIN (SELECT 0 i UNION ALL SELECT 8) b3
CROSS JOIN property p
INNER JOIN unit u on u.hProperty = p.hMY
LEFT OUTER JOIN tenant t on (u.hmy = t.hUnit and t.istatus <= 6 and DATEADD(week, b3.i + b2.i + b1.i + b0.i, '2006-08-21') between
t.dtmovein and isnull(t.dtMoveOut,DATEADD(week, b3.i + b2.i + b1.i + b0.i, '2006-08-21'))
and t.dtMovein = (Select max(dtMovein) from tenant (nolock)
where hunit = u.hmy and t.istatus <= 6 and DATEADD(week, b3.i + b2.i + b1.i + b0.i, '2006-08-21')
between dtmovein and isnull(dtmoveout,DATEADD(week, b3.i + b2.i + b1.i + b0.i, '2006-08-21'))))
LEFT OUTER JOIN tenant t2 on (t2.hUnit = u.hMY and t2.dtMoveIN Between DATEADD(week,b3.i + b2.i + b1.i + b0.i,'2006-08-21') and
DATEADD(day, 6,DATEADD(week,b3.i + b2.i + b1.i + b0.i,'2006-08-21'))and t2.istatus in (2,6))
LEFT OUTER join tenant t3 on (u.hMy = t3.hUnit and t3.dtMoveOut Between DATEADD(week,b3.i + b2.i + b1.i + b0.i,'2006-08-21') and
DATEADD(day, 6,DATEADD(week,b3.i + b2.i + b1.i + b0.i,'2006-08-21'))and t3.istatus = 4)
WHERE b3.i + b2.i + b1.i + b0.i < 12
GROUP BY b3.i, b2.i, b1.i, b0.i
How can I get around showing this twice?
Thanks
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply