June 13, 2008 at 9:08 am
I have a sales table which contains date, sales amount, customer id. I want a report such that if i give two dates, i should get the customer id and sum(salesamount) of those dates. My table structure is like this
CustomerId SalesAmount Date
121 100 01/Jan/2008
122 500 01/Jan/2008
121 200 01/Jan/2008
121 1000 02/Jan/2008
If I specify the dates 01/Jan/2008 and 02/Jan/2008 I should get the result as
CustomerId 01/Jan/2008 02/Jan/2008
121 300 1000
122 500 0
Please help my to get this result
Thanks in advance.
June 13, 2008 at 10:12 am
On the assumption that your date field is of the DateTime data type, AND that the time portion is always midnight, you could do as follows:
DECLARE @START_DATE DateTime, @END_DATE DateTime
SET @START_DATE = '2008/01/01' -- YYYY/MM/DD format
SET @END_DATE = '2008/01/02' -- YYYY/MM/DD format
INSERT INTO #TEMP_TBL
SELECT CustomerId, [Date], SUM(SalesAmount)
FROM SalesTable
WHERE [Date] >= @START_DATE AND
[Date] <= @END_DATE
GROUP BY CustomerId, [Date]
If you have text for your date field, it may be a matter of translating the slashes into dashes to make it a recognizable date that you then convert to DateTime, as merely comparing the text values shown would never gather any in between dates for date ranges beyond consecutive days of the same month.
Also, this will give you individual records for each date in the range, as opposed to a cross-tab style result. I would suggest using the output of this query be exported to Excel to do a Pivot Table, which is much more adept. If you want to do it all in SQL, then you would need to be able to know the entire range of dates in advance, and it would require the use of the PIVOT operator where you then have to specify all the dates. Such becomes unwieldy if you can't know in advance exactly what date range you'll be dealing with, although dynamic SQL might be able to do the job. I don't have quite the level of experience necessary to write the needed dynamic SQL query.
Hope that helps...
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 13, 2008 at 10:24 am
Thankfully - you don't have to. Peter Larsson published an article on his solution for dynamically doing this kind of stuff...Pretty slick if you ask me...
http://www.sqlservercentral.com/articles/Advanced+Querying/pivottableformicrosoftsqlserver/2434/[/url]
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 14, 2008 at 3:52 am
Thanks Miller for the help
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply