January 15, 2013 at 11:37 pm
I've created following code:
select DATEpart(Mm,(Convert(Date,semua_tkh_Daftar_txt,103))) as MonthNo,
DATENAME(Year,(Convert(Date,semua_tkh_Daftar_txt,103))) as Year,
DATENAME(Mm,(Convert(Date,semua_tkh_Daftar_txt,103))) as Month,
COUNT(doclinkid) as Registered,
sum(case when REPWPK.T_BMAL.Semua_Status_Kod like 'S%' then 1 else 0 end) as Finilised,
sum(case when REPWPK.T_BMAL.Semua_Status_Kod not like 'S%' then 1 else 0 end) as Remainig
from REPWPK.T_BMAL
group by
DATEpart(Mm,(Convert(Date,semua_tkh_Daftar_txt,103))),
DATENAME(Year,(Convert(Date,semua_tkh_Daftar_txt,103))),
DATENAME(Mm,(Convert(Date,semua_tkh_Daftar_txt,103)))
order by DATENAME(Year,(Convert(Date,semua_tkh_Daftar_txt,103))),
DATEpart(Mm,(Convert(Date,semua_tkh_Daftar_txt,103)))
Now the result will appear as below:
MonthNo Year MonthRegisteredFinilisedRemainig
1 2012 January762 685 77
2 2012 February719 653 66
3 2012 March 759 655 104
4 2012 April 704 599 105
5 2012 May 821 684 137
The actual requirement for "remaining" is different. E.g. for January 2012 it supposed sum ALL not finilised cases before January 2012.
Any suggestion is appreciated. Feel free to change the code, as it is not optimized ๐
January 16, 2013 at 12:59 am
Please provide table definitions and sample data.
(read the first link in my signature on how to do this)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 16, 2013 at 1:53 am
You may find this modification easier to work with:
SELECT
x.MonthNo,
x.[Year],
x.[Month],
COUNT(doclinkid) as Registered, -- is this correct?
COUNT(DISTINCT doclinkid) as Registered, -- OR is this correct?
SUM(x.Finalised) as Finalised,
COUNT(*) - SUM(x.Finalised) AS [Remaining]
FROM REPWPK.T_BMAL
CROSS APPLY (
SELECT
semua_date = CONVERT(DATE,semua_tkh_Daftar_txt,103),
Finalised = CASE WHEN Semua_Status_Kod LIKE 'S%' THEN 1 ELSE 0 END
) x
CROSS APPLY (
SELECT
[Year] = DATENAME(YEAR,x.semua_date),
[Month] = DATENAME(Mm,x.semua_date),
[MonthNo] = DATEPART(Mm,x.semua_date)
) y
GROUP BY
x.MonthNo,
x.[Year],
x.[Month]
ORDER BY
x.[Year],
x.MonthNo
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 16, 2013 at 9:33 am
Thanks ChrisM@Work, it looks promising. I never thought of minus from total ๐
just having following error, any idea how to fix that?
Msg 207, Level 16, State 1, Line 22
Invalid column name 'MonthNo'.
Msg 207, Level 16, State 1, Line 23
Invalid column name 'Year'.
Msg 207, Level 16, State 1, Line 24
Invalid column name 'Month'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'MonthNo'.
Msg 207, Level 16, State 1, Line 3
Invalid column name 'Year'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'Month'.
By the way we don't need to use distinct, row data itself has no duplicate doclinkid's.
January 16, 2013 at 9:36 am
Sorry, my mistake:
SELECT
y.MonthNo,
y.[Year],
y.[Month],
COUNT(doclinkid) as Registered, -- is this correct?
COUNT(DISTINCT doclinkid) as Registered, -- OR is this correct?
SUM(x.Finalised) as Finalised,
COUNT(*) - SUM(x.Finalised) AS [Remaining]
FROM REPWPK.T_BMAL
CROSS APPLY (
SELECT
semua_date = CONVERT(DATE,semua_tkh_Daftar_txt,103),
Finalised = CASE WHEN Semua_Status_Kod LIKE 'S%' THEN 1 ELSE 0 END
) x
CROSS APPLY (
SELECT
[Year] = DATENAME(YEAR,x.semua_date),
[Month] = DATENAME(Mm,x.semua_date),
[MonthNo] = DATEPART(Mm,x.semua_date)
) y
GROUP BY
y.MonthNo,
y.[Year],
y.[Month]
ORDER BY
y.[Year],
y.MonthNo
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 16, 2013 at 8:31 pm
The result is same with my previous query, I think I couldn't explain it right. By requirement for the column remaining it had to sum up all previous remaining cases. Let's say for January 2012, it has to give sum of all remaining cases <=January 2012. So the number keeps on increasing. For February 2012, all remaining cases from 2011, 2010... and 2012 January.
Any solution is appreciated. By the way thanks ChrisM@Work to give better code.
January 16, 2013 at 11:52 pm
Dehqon D. (1/16/2013)
The result is same with my previous query, I think I couldn't explain it right. By requirement for the column remaining it had to sum up all previous remaining cases. Let's say for January 2012, it has to give sum of all remaining cases <=January 2012. So the number keeps on increasing. For February 2012, all remaining cases from 2011, 2010... and 2012 January.Any solution is appreciated. By the way thanks ChrisM@Work to give better code.
It's concept of Additive facts. Coul you please provide the DDl's ??
Here is how I tried :
CREATE TABLE tblPopulation (
MonthNo VARCHAR(100),
Month_Name VARCHAR(100),
Population_A INT
)
GO
INSERT INTO tblPopulation VALUES('1', 'January',9 )
INSERT INTO tblPopulation VALUES('2', 'February',8 )
INSERT INTO tblPopulation VALUES('3', 'March',5.5)
INSERT INTO tblPopulation VALUES('4', 'April',7.5)
INSERT INTO tblPopulation VALUES('5', 'May',9.5)
-- Query
SELECT a.MONTHNO,
(SELECT SUM(POPULATION_A)
FROM (SELECT POPULATION_A
FROM TBLPOPULATION b
WHERE a.MONTHNO >= b.MONTHNO) test)
FROM TBLPOPULATION a
Result set is :
MONTHNO SUM_COL
19
217
322
429
538
Let me know , if it helps.
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:
January 16, 2013 at 11:55 pm
Result set is all integer , because of Population_A INT, I should have added that as float.
with float , result set would be
MONTHNO SUM_COL
1 9
2 17
3 22.5
4 30
5 39.5
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:
January 18, 2013 at 7:25 am
Thanks for idea @demonfox, you have got it right. But how to change it to fit my ChrisM@Work code.
Here is DDL:
--===== Create the table
CREATE TABLE [REPWPK].[T_BMAL](
[DocLinkID] [nvarchar](50) NULL,
[Semua_Tkh_Daftar_TXT] [varchar](50) NULL,
[Semua_Status_Kod] [nvarchar](50) NULL,
) ON [PRIMARY]
--== Some sample data
INSERT INTO [REPWPK].[T_BMAL]
([DocLinkID]
,[Semua_Tkh_Daftar_TXT]
,[Semua_Status_Kod])
SELECT '73G9VMWPKY2352007MCO','23/05/2007','S07' UNION ALL
SELECT '7L2BYCWPKY3112008MCO','03/11/2008','S02' UNION ALL
SELECT '6XJAUMWPKY1712007MCO','17/01/2007','S05' UNION ALL
SELECT '6KBAFLWPKY22122005MCO','22/12/2005','S07' UNION ALL
SELECT '8WABNCWPKY1772012MCO','17/07/2012','TT03' UNION ALL
SELECT '8FT6RLWPKY1142011MCO','1/04/2011','S05' UNION ALL
SELECT '76K7P7WPKY3082007P','30/08/2007','S01' UNION ALL
SELECT '8US5APWPKY3052012','30/05/2012','S06' UNION ALL
SELECT '93THMMWPKY1012013MCO','10/01/2013','TT02' UNION ALL
SELECT '7QNBNXWPKY3132009MCO','31/03/2009','S07'
Any help is appreciated
January 21, 2013 at 1:51 am
Dehqon D. (1/18/2013)
Thanks for idea @demonfox, you have got it right. But how to change it to fit my ChrisM@Work code.
Like this?
--===== Create the table
CREATE TABLE #T_BMAL(
[DocLinkID] [nvarchar](50) NULL,
[Semua_Tkh_Daftar_TXT] [varchar](50) NULL,
[Semua_Status_Kod] [nvarchar](50) NULL,
) ON [PRIMARY]
--== Some sample data
INSERT INTO #T_BMAL
([DocLinkID]
,[Semua_Tkh_Daftar_TXT]
,[Semua_Status_Kod])
SELECT '73G9VMWPKY2352007MCO','23/05/2007','S07' UNION ALL
SELECT '7L2BYCWPKY3112008MCO','03/11/2008','S02' UNION ALL
SELECT '6XJAUMWPKY1712007MCO','17/01/2007','S05' UNION ALL
SELECT '6KBAFLWPKY22122005MCO','22/12/2005','S07' UNION ALL
SELECT '8WABNCWPKY1772012MCO','17/07/2012','TT03' UNION ALL
SELECT '8FT6RLWPKY1142011MCO','1/04/2011','S05' UNION ALL
SELECT '76K7P7WPKY3082007P','30/08/2007','S01' UNION ALL
SELECT '8US5APWPKY3052012','30/05/2012','S06' UNION ALL
SELECT '93THMMWPKY1012013MCO','10/01/2013','TT02' UNION ALL
SELECT '7QNBNXWPKY3132009MCO','31/03/2009','S07'
-- Group by month, pop the results into a #temp table (or use a CTE)
SELECT
Seq = ROW_NUMBER() OVER(ORDER BY y.[Year],y.MonthNo),
y.MonthNo,
y.[Year],
y.[Month],
Registered= COUNT(*),
Finalised= SUM(x.Finalised),
Remaining= COUNT(*) - SUM(x.Finalised)
INTO #Temp
FROM #T_BMAL
CROSS APPLY (
SELECT
semua_date = CONVERT(DATE,semua_tkh_Daftar_txt,103),
Finalised = CASE WHEN Semua_Status_Kod LIKE 'S%' THEN 1 ELSE 0 END
) x
CROSS APPLY (
SELECT
[Year] = DATENAME(YEAR,x.semua_date),
[Month] = DATENAME(Mm,x.semua_date),
[MonthNo] = DATEPART(Mm,x.semua_date)
) y
GROUP BY
y.MonthNo,
y.[Year],
y.[Month]
ORDER BY
y.[Year],
y.MonthNo
-- Use a recursive cTE to calculate the running total of Remaining
;WITH Calculator AS (
SELECT t.Seq, t.[Year], t.[Month], t.MonthNo, t.Registered, t.Finalised, t.Remaining,
RunningTotal = t.Remaining
FROM #Temp t
WHERE Seq = 1
UNION ALL
SELECT t.Seq, t.[Year], t.[Month], t.MonthNo, t.Registered, t.Finalised, t.Remaining,
RunningTotal = c.RunningTotal + t.Remaining
FROM #Temp t
INNER JOIN Calculator c ON c.Seq + 1 = t.Seq
) SELECT *
FROM Calculator
ORDER BY Seq
-- Clean up
DROP TABLE #Temp
DROP TABLE #T_BMAL
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 21, 2013 at 7:32 am
Thanks ChrisM@Work, it worked with adding (maxrecursion 365);
at the end.
Later on I'll have to find a way how to do the same thing in the Cube, as it was for POC. May be i'll have to go with 'Additive facts' as demonfox mentioned. If you have any better idea, feel free to share.
Thanks for all to solve the mystery ๐
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply