June 11, 2009 at 5:39 am
Jeff
do we not need the following in the last part of dynamic SQL?
+ 'WHERE (BusinessDate >= ''' + CONVERT(CHAR(10),@FiscalYearStartDate,101) + ''' and BusinessDate <= DateAdd(yy,1,''' + CONVERT(CHAR(10),@FiscalYearStartDate,101) + '''))' + CHAR(10)
Without it your grand total at the end, (most right hand column), is all units sold, not just the ones in the period specified.
Even so, a very nice piece of code (again). 🙂 It's now in my toolbox.
Edit: See caveat post below.
HTH
Dave J
June 12, 2009 at 5:45 am
David Jackson (6/11/2009)
Jeffdo we not need the following in the last part of dynamic SQL?
I have noticed if you do put the date range in the where clause, and you have not sold any products in said period, they do not appear in the resultset. If you omit the where clause, the totals column reverts to all products sold, whenever. So my (self-imposed) challenge is to show all products, whether any have been sold or not, and make the totals column meaningful. Hey, it's Friday 😀
Dave J
Edit: Note to self: Engage brain before typing. Hopefully the first sentence now makes sense.
June 12, 2009 at 10:04 am
You might also try:
;with DateCTE(FullDate, SubSet)
as
(Select FullDate,
SubSet = NTILE(52) OVER(ORDER BY FullDate)
from dw.DimTime
where FullDate between '12/28/2009' and '12/27/2010') -- or whatever your time period is. . .
Select sum(SalesOrders) , Subset
from SalesTable s
inner join DateCTE d
on s.SalesDate = d.FullDate
group by SubSet
. . . Something like that?
June 12, 2009 at 6:05 pm
Greg Edwards (6/10/2009)
Fiscal Calendars can change. And in Jeff's example, what happens next year?Greg E
Jeff -
I like my chops well done, with some Gates BBQ sauce. :w00t:
Heh... nah... I'll save the chops for folks that are wrong. 😀 Hopefully, though, the OP will take the coding example I created and turn it into a stored procedure that would take the start date of the fiscal year as a parameter instead of having a hardcoded date.
--Jeff Moden
June 12, 2009 at 6:48 pm
David Jackson (6/12/2009)
David Jackson (6/11/2009)
Jeffdo we not need the following in the last part of dynamic SQL?
I have noticed if you do put the date range in the where clause, and you have not sold any products in said period, they do not appear in the resultset. If you omit the where clause, the totals column reverts to all products sold, whenever. So my (self-imposed) challenge is to show all products, whether any have been sold or not, and make the totals column meaningful. Hey, it's Friday 😀
Dave J
Edit: Note to self: Engage brain before typing. Hopefully the first sentence now makes sense.
Had to double check but the caveat doesn't exist. I just ran the following test which deletes all data from the test table for week 49 of the expected result set. 0's are returned as expected.
[font="Courier New"]--DROP TABLE JBMTest
GO
--===== Create and populate a 1,000,000 row test table.'
-- THIS IS NOT A PART OF THE SOLUTION. IT'S A TEST TABLE.
-- Jeff Moden
SELECT TOP 1000000
SomeID = IDENTITY(INT,1,1),
ProductName = CHAR(ABS(CHECKSUM(NEWID()))%26+65)
+ CHAR(ABS(CHECKSUM(NEWID()))%26+65),
BusinessDate = CAST(ABS(CHECKSUM(NEWID()))%3653+36524 AS DATETIME),
QuantitySold = ABS(CHECKSUM(NEWID()))%100+1
INTO dbo.JBMTest
FROM Master.dbo.SysColumns t1
CROSS JOIN Master.dbo.SysColumns t2
--===== Add a clustered key
ALTER TABLE dbo.JBMTest
ADD PRIMARY KEY CLUSTERED (SomeID)
--===== Delete all data from WK49 of the result set.
-- This shows that 0's will be returned for missing data.
DELETE dbo.JBMTest
WHERE BusinessDate >= '11/29/2009' and BusinessDate < '12/06/2009'
--==============================================================================
-- Solution starts here
--==============================================================================
--===== Declare a couple of obviously named variables
DECLARE @FiscalYearStartDate DATETIME,
@SQL VARCHAR(MAX)
--===== Define the beginning of the fiscal year
SELECT @FiscalYearStartDate = '12/28/2008'
--===== Define the first static part of the dynamic SQL
SELECT @SQL = 'SELECT ProductName,' + CHAR(10)
--===== Define the dynamic select list of the dynamic SQL
;WITH
cteDates AS
(
SELECT CAST(v.Number + 1 AS VARCHAR(2)) AS Week,
DATEADD(wk, v.Number, @FiscalYearStartDate) AS StartDate,
DATEADD(wk, v.Number+1, @FiscalYearStartDate)-1 AS EndDate,
DATEADD(wk, v.Number+1, @FiscalYearStartDate) AS NextStartDate
FROM Master.dbo.spt_Values v
WHERE Type = 'P'
AND v.Number BETWEEN 0 and 53
AND YEAR(DATEADD(wk, v.Number+1, @FiscalYearStartDate)) = ''' + CONVERT(CHAR(10),StartDate,101)
+ ''' AND BusinessDate = ''' + CONVERT(CHAR(10),@FiscalYearStartDate,101)
+ ''' AND BusinessDate <= DateAdd(yy,1,''' + CONVERT(CHAR(10),@FiscalYearStartDate,101) + '''))' + CHAR(10)
+ 'GROUP BY ProductName' + CHAR(10)
+ 'ORDER BY ProductName' + CHAR(10)
--===== Print out the Dynamic SQL so we can see it, then execute it
PRINT @SQL
EXEC (@SQL)
[/font]
--Jeff Moden
June 12, 2009 at 6:49 pm
David Jackson (6/11/2009)
Jeffdo we not need the following in the last part of dynamic SQL?
+ 'WHERE (BusinessDate >= ''' + CONVERT(CHAR(10),@FiscalYearStartDate,101) + ''' and BusinessDate <= DateAdd(yy,1,''' + CONVERT(CHAR(10),@FiscalYearStartDate,101) + '''))' + CHAR(10)
Without it your grand total at the end, (most right hand column), is all units sold, not just the ones in the period specified.
Even so, a very nice piece of code (again). 🙂 It's now in my toolbox.
HTH
Dave J
Ah, dang it... I knew I was forgetting something. You're absolutely correct about the totals problem, David. I've edited the code to include your correction. Thank you for the catch.
Again, though, the caveat isn't a problem. The code returns the expected 0's when data isn't present.
--Jeff Moden
June 14, 2009 at 7:10 am
Jeff
I think you have not quite got what I am saying. Apologies, I didn't explain it clearly 😉
Running the code above works as you say, producing zeroes in week 49. But, if I run this snippet,
select count(*) from JBMTest
where businessDate >= '12/28/2008' and productName = 'AA'
select count(*) from JBMTest
where businessDate = '12/28/2008'and productName = 'AA'
And then your code from above, although the Product AA exists in the table, because we have not sold any in the period we are looking at, it does not appear in the result set of your latest code. Take out the dates from the where clause and it does, all zeroes but erroneous totals...
My take on it was not satisfactory, as it involved running the query without dates into a temp table, deleteing from the temp table where entries do exist, appending to the temp table by running your code with the where clause and then selecting all from that. Ugh, a four step process. :w00t:
I'll have another look tomorrow.
HTH
Dave J
Edit: took out my code example as I'm using this technique to do something slightly different and my example confuses things.
June 14, 2009 at 9:09 am
David Jackson (6/14/2009)
JeffI think you have not quite got what I am saying. Apologies, I didn't explain it clearly 😉
Ah... got it. And, you're correct. I didn't make it so that it would return a line for a given product if that product had no activity for the entire report period. That would simply require an outerjoin to a product table. I guess I could easily make one for this example...
--Jeff Moden
June 17, 2009 at 4:51 am
Despite the hints, it's beyond my ken. I have got round it by leaving the where clause off and not having a totals column all together. The subtle difference between my requirement and the OP's is that I do not want a total of items sold, but a count of events occuring. I am querying a view that returns a personId, and DateCreated column and a Caption, that I have aliased to Impression. Here's my version, with a heavy debt of thanks to Jeff.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[usp_ImpressionOverPeriod]
@StartDate datetime = NULL,
@EndDate datetime = NULL
As
/*
Example Call
exec usp_ImpressionOverPeriod -- defaults to last 12 months
exec usp_ImpressionOverPeriod '1 Jan 2009'
exec usp_ImpressionOverPeriod '1 Sep 2008', '1 Oct 2008'
exec usp_ImpressionOverPeriod '29 Dec 2008', '1 feb 2009'
exec usp_ImpressionOverPeriod '29/12/2008', '1/2/2009'
*/
set dateformat DMY
DECLARE @SQL VARCHAR(MAX), @noOfWeeks int
--===== Define the beginning of the fiscal period
if isNull(@StartDate, 0) = 0
select @StartDate = dateadd(year,-1, dateadd(dd, datediff(dd,0,Getdate()),0))
if isNull(@EndDate, 0) = 0
select @EndDate = dateadd(year, 1, @StartDate)
select @noOfWeeks = datediff(week,@StartDate, @EndDate)
--===== Define the first static part of the dynamic SQL
SELECT @SQL = 'SELECT isnull(v1.caption,''No Impression'') Impression,' + CHAR(10)
--===== Define the dynamic select list of the dynamic SQL
;WITH
cteDates AS
(
SELECT CAST(v.Number + 1 AS VARCHAR(2)) AS Week,
DATEADD(wk, v.Number, @StartDate) AS StartDate,
DATEADD(wk, v.Number+1, @StartDate)-1 AS EndDate,
DATEADD(wk, v.Number+1, @StartDate) AS NextStartDate
FROM Master.dbo.spt_Values v
WHERE Type = 'P'
and v.Number >= 0 and v.number <= @noOfWeeks
and YEAR(DATEADD(wk, v.Number + 1, @StartDate)) = ''' + CONVERT(CHAR(11),StartDate,113)
+ ''' AND v1.DateCreated = ''' + CONVERT(CHAR(11),@StartDate,113) + '''' + char(10) +
--'and v1.DateCreated < ''' + CONVERT(CHAR(11),@EndDate,113) + '''' + char(10) +
'GROUP BY v1.caption
ORDER BY v1.caption'
--===== execute it
EXEC (@SQL)
Dave J
July 15, 2009 at 11:39 am
I have a similar report that runs on a schedule. Before I even published it I created to new datasets, one for the start time and one for the end time. I do something like "SELECT getdate()-7 as st".
In Visual Studio I type in the WHERE clause of the main dataset something to the likes of startDate >= @st and endDate< @et . Under the report parameters menu I select the appropriate dataset as the default value for the parameters. (eg// map @st default value to the dataset with the query above.)
Once I publish the report, in the front end view I click the Properties tab then the History link. There I schedule the report to run like every Wednesday. The default values will be getdate()-7 and getdate() per the default values I gave the report in VS designer... {
you can add the whole DATEADD/DATEDIFF stuff to drop the time portion }
Just offered in case it might help.
----------------------------------------------------
July 16, 2009 at 7:37 am
If your report is interactive (contains parameters), the statement in the WHERE clause could read something like: where OrderDate between dateadd(dd,-6,@DateParameter) and @DateParameter
Otherwise, you can replace the @DateParameter with your server system date - for example, we are on SQL Server 2005 and our system date code (which returns today's date) is getdate(); our most current data is always one day behind present date. In that case, the code would need to read: where OrderDate between dateadd(dd,-7,getdate()) and dateadd(dd,-1,getdate())
🙂
July 16, 2009 at 7:50 am
Did u try the set datefirst function from T-sql.It actually sets the firstdate of week to Monday. Then on this you can aggregate on the datepart(wk,date) function. To top it if you are looking for first week in the fiscal you can actually set it apart in a function.
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply