May 19, 2010 at 5:31 am
Hi,
I am planning to get a report for a table with following table structure:
ID RequestDate
-----------------------------
1 2010/01/01
2 2010/02/14
3 2010/03/20
4 2010/01/07
5 2009/03/31
I want the results as:
ID_Count RequestDate Sum
-----------------------------------------
2 2010/01 2
1 2010/02 3
2 2010/03 5
Pls help.
May 19, 2010 at 6:07 am
DECLARE @Sample
TABLE (
ID INTEGER NOT NULL PRIMARY KEY,
RequestDate DATE NOT NULL
);
INSERT @Sample (ID, RequestDate)
VALUES (1, '2010-01-01'),
(2, '2010-02-14'),
(3, '2010-03-20'),
(4, '2010-01-07'),
(5, '2009-03-31');
WITH Summary
AS (
SELECT iTVF.RequestMonth,
ID_Count = COUNT_BIG(ID)
FROM @Sample S
CROSS
APPLY (
SELECT DATEADD(MONTH, DATEDIFF(MONTH, '2010-01-01', S.RequestDate), '2010-01-01')
) iTVF (RequestMonth)
GROUP BY
iTVF.RequestMonth
)
SELECT RequestMonth = CONVERT(CHAR(7), S1.RequestMonth, 111),
S1.ID_Count,
iTVF.ID_Sum
FROM Summary S1
CROSS
APPLY (
SELECT ID_Sum = SUM(S2.ID_Count)
FROM Summary S2
WHERE S2.RequestMonth <= S1.RequestMonth
) iTVF;
May 19, 2010 at 6:10 am
Hi, This is my first attempt at running totals, hope i am not screwing up anything!
Now setting up the test environtment:
SET DATEFORMAT YMD
IF OBJECT_ID('TEMPDB..#A') IS NOT NULL
DROP TABLE #A
CREATE TABLE #A
(
ID int,
Date SMALLDATETIME
)
insert into #A (ID,Date)
select 1,'2010/01/01' union all
select 2,'2010/02/14' union all
select 3,'2010/03/20' union all
select 4,'2010/01/07' union all
select 5,'2010/03/31'
Now the real code; note each line is important here!
IF OBJECT_ID('TEMPDB..#temp') IS NOT NULL
DROP TABLE #temp
;with cte as (
select Datepart(mm,Date) monthpart , count(*) ct , null [sum] from #A
GROUP BY Datepart(mm,Date)
)
select Row_number() over(order by monthpart) rn, * into #temp from cte
DECLARE @N INT, @Anchor INT
SELECT @N = 0
update a
set @N = [sum] = @N + ct , --Adds 3 to N and updates SomeInt with N
@Anchor = rn
FROM #temp a --WITH (TABLOCKX) --Can't be used on a table variable
OPTION (MAXDOP 1)
select '2010/'+STUFF('00',LEN('00')-LEN(CONVERT(VARCHAR,monthpart))+1,LEN(monthpart),monthpart) date
, ct, [sum]
from #temp
Hope this helps you! There will be lot of other alternatives! lets wait for other to join in!
😎
May 19, 2010 at 6:16 am
Thanks for the quick replies. Apologies for not letting you know, I have SQL Server 2000 as DB. These queries are SQL 2005 specific I guess.
May 19, 2010 at 6:36 am
jain.ashish21 (5/19/2010)
Thanks for the quick replies. Apologies for not letting you know, I have SQL Server 2000 as DB. These queries are SQL 2005 specific I guess.
Awesome :doze:
May 19, 2010 at 11:47 am
As far as I can remember, this will work in SQL Server 2000. I don't have it installed any more, so no guarantees!
DECLARE @Sample
TABLE (
ID INTEGER NOT NULL PRIMARY KEY,
RequestDate DATETIME NOT NULL
);
INSERT @Sample (ID, RequestDate) VALUES (1, '2010-01-01');
INSERT @Sample (ID, RequestDate) VALUES (2, '2010-02-14');
INSERT @Sample (ID, RequestDate) VALUES (3, '2010-03-20');
INSERT @Sample (ID, RequestDate) VALUES (4, '2010-01-07');
INSERT @Sample (ID, RequestDate) VALUES (5, '2009-03-31');
DECLARE @Summary
TABLE (
RequestMonth DATETIME PRIMARY KEY,
ID_Count INTEGER NOT NULL
);
INSERT @Summary (RequestMonth, ID_Count)
SELECT DATEADD(MONTH, DATEDIFF(MONTH, '2010-01-01', S.RequestDate), '2010-01-01'),
COUNT(*)
FROM @Sample S
GROUP BY
DATEADD(MONTH, DATEDIFF(MONTH, '2010-01-01', S.RequestDate), '2010-01-01');
SELECT RequestMonth = CONVERT(CHAR(7), S1.RequestMonth, 111),
S1.ID_Count,
ID_Sum =
(
SELECT SUM(S2.ID_Count)
FROM @Summary S2
WHERE S2.RequestMonth <= S1.RequestMonth
)
FROM @Summary S1;
May 20, 2010 at 12:09 am
The above query did resolve what I was looking for. But now I have a bit different scenario. Here's my table structure
ID LoginDate RemovalDate
----------------------------------------
1 2009/08/01 NULL
2 2009/09/12 2010/01/02
3 2009/08/31 2009/10/29
4 2010/02/17 NULL
5 2009/10/18 2009/11/22
I want a consolidated results of how many ID's were not removed in a particular month. So the result set should be
Date NotRemoved_ID
--------------------------
2009/08 2
2009/09 3
2009/10 3 [One ID got removed in 2009/10]
2010/02 2 [Two got removed in 2009/11 and 2010/01]
Thanks for the help.
May 20, 2010 at 1:56 am
jain.ashish21 (5/20/2010)
The above query did resolve what I was looking for. But now I have a bit different scenario. Here's my table structure
So you think because I converted your printed structures to code once that I'm going to do it every time?
Put a bit of effort in.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply