June 22, 2010 at 10:29 am
Hi all,
I have a query to be formed which I am facing difficulty with:-
I have attached the query here, actually the problem is with the number of rows it is returnung when I add month name to the query as I also group it by month name it counts less rows.
My requirement is that to find the same orderkey,same custkey and count of separate Orderdatefor last 3 months and show if the count is greater than 1 in last 3 months, like in this sample: for custkey 123 and orderkey 11 I will get last 3 months count as May = 1, June =2
123112010-06-01 00:00:00.000
123112010-05-19 00:00:00.000
123112010-06-13 00:00:00.000
but in my query I am unable to show the count with monthnames as when I add monthname to my query the count reduces.....
Please let me know how can I get exact count along with month names.
I this sample the count should be like this along with month names:
123113
124143
122152
126122
122112
(the result of 1st query.)
Any help is appreciated...
--drop table [temp2]
--create script
CREATE TABLE [dbo].[temp2](
[CustKey] [int] NULL,
[OrderKey] [int] NULL,
[OrderDate] datetime NULL
)
--Insert script
INSERT INTO temp2 ( CustKey,OrderKey,OrderDate) VALUES ( 122, 15, 'Jun 3 2010 12:00AM')
INSERT INTO temp2 ( CustKey,OrderKey,OrderDate) VALUES ( 124, 14, 'Jun 13 2010 12:00AM')
INSERT INTO temp2 ( CustKey,OrderKey,OrderDate) VALUES ( 124, 14, 'May 23 2010 12:00AM')
INSERT INTO temp2 ( CustKey,OrderKey,OrderDate) VALUES ( 127, 11, 'May 27 2010 12:00AM')
INSERT INTO temp2 ( CustKey,OrderKey,OrderDate) VALUES ( 122, 15, 'Apr 3 2010 12:00AM')
INSERT INTO temp2 ( CustKey,OrderKey,OrderDate) VALUES ( 127, 12, 'Jun 13 2010 12:00AM')
INSERT INTO temp2 ( CustKey,OrderKey,OrderDate) VALUES ( 129, 16, 'May 11 2010 12:00AM')
INSERT INTO temp2 ( CustKey,OrderKey,OrderDate) VALUES ( 123, 11, 'Jun 13 2010 12:00AM')
INSERT INTO temp2 ( CustKey,OrderKey,OrderDate) VALUES ( 126, 13, 'May 28 2010 12:00AM')
INSERT INTO temp2 ( CustKey,OrderKey,OrderDate) VALUES ( 122, 11, 'Jun 13 2010 12:00AM')
INSERT INTO temp2 ( CustKey,OrderKey,OrderDate) VALUES ( 123, 11, 'May 19 2010 12:00AM')
INSERT INTO temp2 ( CustKey,OrderKey,OrderDate) VALUES ( 122, 11, 'Jun 3 2010 12:00AM')
INSERT INTO temp2 ( CustKey,OrderKey,OrderDate) VALUES ( 121, 15, 'Jun 3 2010 12:00AM')
INSERT INTO temp2 ( CustKey,OrderKey,OrderDate) VALUES ( 124, 14, 'Jun 13 2010 12:00AM')
INSERT INTO temp2 ( CustKey,OrderKey,OrderDate) VALUES ( 123, 11, 'Jun 1 2010 12:00AM')
INSERT INTO temp2 ( CustKey,OrderKey,OrderDate) VALUES ( 126, 12, 'May 27 2010 12:00AM')
INSERT INTO temp2 ( CustKey,OrderKey,OrderDate) VALUES ( 122, 16, 'Jun 13 2010 12:00AM')
INSERT INTO temp2 ( CustKey,OrderKey,OrderDate) VALUES ( 126, 12, 'May 18 2010 12:00AM')
INSERT INTO temp2 ( CustKey,OrderKey,OrderDate) VALUES ( 124, 11, 'May 21 2010 12:00AM')
INSERT INTO temp2 ( CustKey,OrderKey,OrderDate) VALUES ( 128, 11, 'May 25 2010 12:00AM')
select * from temp2
---query without month name (shows 5 rows)
select CustKey, OrderKey -- ,month (OrderDate ) as [month]
,count( month (OrderDate ) ) as [month_count]
from temp2
group by CustKey, OrderKey -- , month (OrderDate )
having count( month (OrderDate ) ) >1
order by 3 desc
---query without month name (only 4 rows)
select CustKey, OrderKey , datename(month ,OrderDate ) as [month] ,count( month (OrderDate ) ) as [month_count]
from temp2
group by CustKey, OrderKey ,datename(month ,OrderDate )
having count( month (OrderDate ) ) >1
order by 3 desc
Thanks [/font]
June 22, 2010 at 11:01 am
Please clarify your requirement: Do you need to to show just the total number of counts within the last three months (then it wouldn't make sense to display each month separately) or do you first need to find the orderkey+custkey with counts > 1 and for those subresult display a monthly count?
June 22, 2010 at 11:11 am
Hi,
Thanks for your quick response...
I need to show month also but as i include month in query count goes down,I need to show the keys (order and cust) along with last 3 month names in Matrix format:
Order Key / Cust Key in Rows and Apr MAY and JUNE in Headings the count will be displayed if it is greater than 1 , adding last 3 months count.
ex:like in this sample:
for custkey 123 and orderkey 11 I will get last 3 months total count as apr =0, May = 1, June =2
123 11 2010-06-01 00:00:00.000
123 11 2010-05-19 00:00:00.000
123 11 2010-06-13 00:00:00.000
but this set of data if run using the month query(query #2) it shows only June count and not the May count as May count =1 and I have a condition to having count( month (OrderDate ) ) >1
but I need to see may also in the result, as that comes in greater than 1 for last 3 months total, but as only for may the total is = 1, it is eliminating MAY as well, How can i form such query?
hope you got the problem, Let me know if you need more information.
Thanks [/font]
June 22, 2010 at 11:49 am
Would something like the following get the result you require?
I used a CTE to get the rows with OrderDate within the last three month as well as the total count within that date range using OVER (PARTITION BY).
;WITH
subresult AS
(
SELECT
CustKey,
OrderKey ,
OrderDate,
COUNT(*) OVER (PARTITION BY CustKey,OrderKey) AS [month_count_total]
FROM temp2
WHERE OrderDate > DATEADD(mm,-3,GETDATE())
)
SELECT
CustKey,
OrderKey,
[month_count_total],
COUNT(*) AS [month_count],
DATENAME(MONTH ,OrderDate ) AS [MONTH]
FROM subresult
WHERE[month_count_total]>1
GROUP BY CustKey, OrderKey,DATENAME(MONTH ,OrderDate ), [month_count_total],DATEPART(MONTH ,OrderDate )
ORDER BY CustKey, DATEPART(MONTH ,OrderDate )
June 22, 2010 at 12:06 pm
Hey 🙂
Thanks a lot Imu,
It works like a charm....... 🙂
Thank you again.
Thanks [/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply