Dates matrix

Hi,
I have a query that i run which shows me the amount of people that have turned active on the day in question, for the month to date, and for the previous 3 months.
I would like to be able to put this into a report so it would look like this:

Yesterday    Month to date           Oct        Sept       August
12                  200                          400        450        500

So my problem lies in not knowing how to get the layout of the report as above.
I have been lookin up the use of functions with in the script but i am not doing this corretly.
i have created example tables
So the results should look like:
Yesterday    Month to date       Oct        Spet       Aug
3                    6                          3            3             3

Please Advise.
create table account
(account_id int, 
 account_status_date datetime)
 
insert into account values (123,'2008-11-17')
insert into account values (1234,'2008-11-17')
insert into account values (1235,'2008-11-17')
 
insert into account values (12355,'2008-11-16')
insert into account values (123554,'2008-11-14')
insert into account values (123145,'2008-11-13')
 
 
insert into account values (112355,'2008-10-16')
insert into account values (1323554,'2008-10-14')
insert into account values (1323145,'2008-10-13')
 
insert into account values (012355,'2008-09-16')
insert into account values (0123554,'2008-09-14')
insert into account values (0123145,'2008-09-13')
 
insert into account values (111111,'2008-08-16')
insert into account values (55555,'2008-08-14')
insert into account values (77777,'2008-08-13')

Open in new window

PutochAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin CrossChief Technology OfficerCommented:
Hello Putoch,

Do you already know how to get the data in vertical format?  Depending on your SQL platform and version (please confirm what that is), you can get the query with data in rows first and then use the PIVOT keyword or manual pivot of data to horizontal format.

Best regards,

mwvisa1
0
PutochAuthor Commented:
No, well usually i would use CASE to get anything i need like that, but in this case i know i can't unless i start using a table called month and year, and then saying case when month betwwen this and this then... if you get me.

i am using SQL SERVER 2005

thanks,
putoch
0
Kevin CrossChief Technology OfficerCommented:
This gets it for you without the hassle of using dynamic SQL to PIVOT, but usually the pivot would be of this format if you want to investigate further:

SELECT *
FROM table
PIVOT (COUNT(account_id) FOR account_status_date IN ([datevalue], [anotherdatevalue])) p

Anyway, here is what I have for syntax without PIVOT.
SELECT        SUM(CASE WHEN account_status_date >= DATEADD(dd, - 1, DATEDIFF(dd, 0, GETDATE())) AND account_status_date < DATEADD(dd, 0, DATEDIFF(dd, 0, 
                         GETDATE())) THEN 1 ELSE 0 END) AS Yesterday, SUM(CASE WHEN account_status_date >= DATEADD(mm, 0, DATEDIFF(dd, 0, GETDATE() - DAY(GETDATE()) 
                         + 1)) THEN 1 ELSE 0 END) AS MonthToDate, SUM(CASE WHEN account_status_date >= DATEADD(mm, - 1, DATEDIFF(dd, 0, GETDATE() - DAY(GETDATE()) + 1)) 
                         AND account_status_date < DATEADD(mm, 0, DATEDIFF(dd, 0, GETDATE() - DAY(GETDATE()) + 1)) THEN 1 ELSE 0 END) AS LastMonth, 
                         SUM(CASE WHEN account_status_date >= DATEADD(mm, - 2, DATEDIFF(dd, 0, GETDATE() - DAY(GETDATE()) + 1)) AND account_status_date < DATEADD(mm, 
                         - 1, DATEDIFF(dd, 0, GETDATE() - DAY(GETDATE()) + 1)) THEN 1 ELSE 0 END) AS TwoMonthsAgo, SUM(CASE WHEN account_status_date >= DATEADD(mm, - 3,
                          DATEDIFF(dd, 0, GETDATE() - DAY(GETDATE()) + 1)) AND account_status_date < DATEADD(mm, - 2, DATEDIFF(dd, 0, GETDATE() - DAY(GETDATE()) + 1)) 
                         THEN 1 ELSE 0 END) AS ThreeMonthsAgo
FROM            account
WHERE        (account_status_date >= DATEADD(mm, - 3, DATEDIFF(dd, 0, GETDATE() - DAY(GETDATE()) + 1)))

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Kevin CrossChief Technology OfficerCommented:
I started out using CASE statement to build this going vertically as you indicated and that is usually easy.  As a matter of fact, just for the months you can use the WHERE clause I have above and group by:

LEFT(DATENAME(mm, account_status_date), 3)

...and you will get count for AUG, SEP, OCT, NOV and then you could write that to PIVOT.  You could then just UNION in Yesterday values.  The problem with it in a straight case is that yesterday's data also needs to be in month to date and so order of case matters would probably have to start at 3 months out and work you way back to present.  Again, will ultimately be faced with the challenge of using PIVOT which requires hardcoded column names which is fine if you use dynamic verbiage like I have of LastMonth, TwoMonthsAgo, etc. versus the actual month names.

Anyway, hopefully that helps.

Regards,
Kevin
0
PutochAuthor Commented:
Thanks a million, i will have a good look at this, and try to understand this, it gives me back teh results i was looking for.
Thank you Kevin
0
Kevin CrossChief Technology OfficerCommented:
I am glad.

DATEADD(mm, - 3, DATEDIFF(dd, 0, GETDATE() - DAY(GETDATE()) + 1))

Is the basis of this as understanding this piece, you will understand the other date manipulation I am doing as they follow the same logic.

DATEDIFF(dd, 0, GETDATE()) ==> gets todays date in numerical representation, stripping out the time, so DATEADD of 0 to this will get you todays date at midnight in date format.

DAY(GETDATE()) ==> gets the number of days this is from start of month, so subtracting this from today gets you the last day of last month so adding back one to formula above gets you first day of the month at midnight.

DATEADD(mm, - 3, {date}) ==> putting all the together, this gets you the first day of the month three months ago -- august 1st, 2008.

Happy coding!

/Kev
0
PutochAuthor Commented:
thanks a million for that Kevin, makes much more sense now!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.