September 5, 2008 at 9:55 am
WITH DirAgent(AccountID, Number_of_Accesses) AS
(
select AccountID,count(*)
from account_log_BAK
where productid = 100870 and
date > 12/31/2006 and
substring(CAST(DATE AS varchar(30)),1,3) = 'jan'
group by AccountID
)
SELECT AccountID, Number_of_Accesses
FROM DirAgent
ORDER BY AccountID;
this works fine for jan, but I want to but this means I need to run 9 queries....
I want to be able to do in one step so it looks like
AccountID, Number_of_Accesses Date
1111 5 july
1111 2 jan
1112 11 jun
1112 10 jul
THANKS
September 5, 2008 at 10:19 am
here is the answer in case it helps anyone
WITH DirAgent(AccountID, date) AS
(
select AccountID, substring(CAST(DATE AS varchar(30)),1,3)as date
from account_log
where productid = 100870 and
date > 12/31/2006
group by AccountID,date
)
select distinct count(AccountID),accountid,date from DirAgent
group by date,accountid
order by date
1515890Jul
724357Jul
424365Aug
324365Jul
124370Aug
4124420Jul
324425Jul:)
September 5, 2008 at 10:54 am
Great that you have a solution. However, I'd like to make a couple of suggestions that might help generalize the solution, making it easier to re-use and perhaps applicable beyond the exsting requirement.
1.) Use non-reserved words for field names. THE_DATE is a choice I use fairly often when there's no specific information about what the date represents, or when I'm the only one getting the data.
2.) Use the MONTH and YEAR functions to extend monthly grouping beyond a given calendar year.
See the following re-write of your code:
SELECT CAST(YEAR([date] AS char(4)) + '_' + RIGHT(LTRIM(RTRIM(CAST(MONTH([date]) AS varchar(2)))),2) AS YR_MTH,
AccountID, COUNT(*) AS Number_of_Accesses
FROM account_log_BAK
WHERE productid = 100870 AND
[date] > '12/31/2006'
GROUP BY YR_MTH, AccountID
ORDER BY YR_MTH, AccountID;
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 5, 2008 at 11:11 am
SWEET lots of help!!!
September 5, 2008 at 11:28 am
Turns out I had a small error in my script. Here's the corrected version:
SELECT CAST(YEAR([date] AS char(4)) + '_' + RIGHT('0' + LTRIM(RTRIM(CAST(MONTH([date]) AS varchar(2)))),2) AS YR_MTH,
AccountID, COUNT(*) AS Number_of_Accesses
FROM account_log_BAK
WHERE productid = 100870 AND
[date] > '12/31/2006'
GROUP BY YR_MTH, AccountID
ORDER BY YR_MTH, AccountID;
Note the addition of '0' to the string to ensure a leading zero for the month number. That was my intent all along, but I ended up having forgotten to put in the zero. My bad...
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply