June 7, 2011 at 7:04 pm
I have the query below which will be used in ssrs.
the problem i have is that i need to query data using the month in the where clause only. not using "transaction_date" between in the query. I need some help on how to do this.
SELECT --dbo.SalesRep.Number,
dbo.SalesReportRMS.SalesRep ,
dbo.SalesReportRMS.LogicalStoreName AS [Home Store] ,
dbo.SalesRep.[Target] ,
SUM(dbo.SalesReportRMS.FullPrice) AS [Home Store Sales] ,
SUM(dbo.SalesReportRMS.Quantity) AS [Quantity]
FROM dbo.SalesReportRMS
LEFT OUTER JOIN dbo.SalesRep ON dbo.SalesReportRMS.SalesRepID = dbo.SalesRep.Number
WHERE ( NOT ( dbo.SalesRep.[Target] = '0' )
)
AND ( NOT ClassID LIKE 'MISS-%'
)
AND ( dbo.SalesRep.ShTarget = 'y' )
AND dbo.SalesReportRMS.StoreID = dbo.SalesRep.HomeStore
AND dbo.SalesReportRMS.Transaction_Date BETWEEN '5/01/11 00:00:01'
AND '5/31/11 23:59:00'
GROUP BY dbo.SalesRep.Number ,
dbo.SalesReportRMS.SalesRep ,
dbo.SalesReportRMS.LogicalStoreName ,
dbo.SalesRep.[Target]
June 7, 2011 at 8:35 pm
npeters 86796 (6/7/2011)
I have the query below which will be used in ssrs.the problem i have is that i need to query data using the month in the where clause only. not using "transaction_date" between in the query. I need some help on how to do this.
SELECT --dbo.SalesRep.Number,dbo.SalesReportRMS.SalesRep ,
dbo.SalesReportRMS.LogicalStoreName AS [Home Store] ,
dbo.SalesRep.[Target] ,
SUM(dbo.SalesReportRMS.FullPrice) AS [Home Store Sales] ,
SUM(dbo.SalesReportRMS.Quantity) AS [Quantity]
FROM dbo.SalesReportRMS
LEFT OUTER JOIN dbo.SalesRep ON dbo.SalesReportRMS.SalesRepID = dbo.SalesRep.Number
WHERE ( NOT ( dbo.SalesRep.[Target] = '0' )
)
AND ( NOT ClassID LIKE 'MISS-%'
)
AND ( dbo.SalesRep.ShTarget = 'y' )
AND dbo.SalesReportRMS.StoreID = dbo.SalesRep.HomeStore
AND dbo.SalesReportRMS.Transaction_Date BETWEEN '5/01/11 00:00:01'
AND '5/31/11 23:59:00'
GROUP BY dbo.SalesRep.Number ,
dbo.SalesReportRMS.SalesRep ,
dbo.SalesReportRMS.LogicalStoreName ,
dbo.SalesRep.[Target]
For last month - change the where clause to:
AND dbo.SalesReportRMS.Transaction_Date >= DATEADD(month, DATEDIFF(month, 0, getdate)) - 1, 0) -- '5/01/11 00:00:01'
AND dbo.SalesReportRMS.Transaction_Date < DATEADD(month, DATEDIFF(month, 0, getdate()), 0) -- '5/31/11 23:59:00'
Better yet, change the where clause to:
AND dbo.SalesReportRMS.Transaction_Date >= @startDate
AND dbo.SalesReportRMS.Transaction_Date < dateadd(day, 1, @endDate)
Add report parameters for both - should be added automatically once you refresh. Configure the report parameters as datetime and let the users put in the actual dates they want to run. Or, you can set the default value of the parameters. I don't have the exact code to calculate the beginning/end of month - but you should be able to find it.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 7, 2011 at 9:13 pm
npeters 86796 (6/7/2011)
I have the query below which will be used in ssrs.the problem i have is that i need to query data using the month in the where clause only. not using "transaction_date" between in the query. I need some help on how to do this.
Unless you have a calendar table that includes the month as one of the fields, you really DON'T want to do this, because you won't be able to use indexes on that field.
I think what you really want to do is pass in a single parameter (month) and use that parameter to filter the data rather than having to pass in two parameters (one for the begin date and one for the end date). Is that accurate?
WHERE ( NOT ( dbo.SalesRep.[Target] = '0' )
)
Why use two operators "NOT" and "=" when you can get the same the results with one operator?
WHERE dbo.SalesRep.[Target] <> '0'
This also has the added bonus that it better matches the English for this criteria and is therefore just a little clearer.
AND ( NOT ClassID LIKE 'MISS-%'
)
There is a similar issue here.
AND ClassID NOT LIKE 'MISS-%'
While neither of the above will produce wrong results, I like to keep my code as clean and simple as possible.
On the other hand, the below will produce wrong results:
AND dbo.SalesReportRMS.Transaction_Date BETWEEN '5/01/11 00:00:01'
AND '5/31/11 23:59:00'
Using BETWEEN with datetime fields usually doesn't work, because it's so easy to miss time periods or double count time periods. For instance, you are missing '2011-05-01 00:00:00.0000' to '2011-05-01 00:00:00.9997' and also '2011-05-31 23:59:00.0003' to '2011-05-31 23:59:59.9997'
Because it's so easy to calculate midnight on the first of the month--both current and next--the most common way to write this is equivalent to
AND dbo.SalesReportRMS.Transaction_Date >= '2011-05-01' -- midnight since no time is specified
AND dbo.SalesReportRMS.Transaction_Date < '2011-06-01' -- again midnight
Note that the ">=" includes midnight for the current month, but the "<" excludes midnight for the next month.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 8, 2011 at 8:48 am
Thank you both.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply