July 27, 2015 at 3:22 am
I have a table with dates and values and other columns. In a proc i need to get the result as Month and the values for all the months whether or not the data exists for the month.
The Similar table would be-
create table testing(
DepDate datetime,
val int)
insert into testing values ('2014-01-10 00:00:00.000', 1)
insert into testing values ('2014-05-19 00:00:00.000', 10)
insert into testing values ('2014-08-15 00:00:00.000', 20)
insert into testing values ('2014-11-20 00:00:00.000', 30)
But in result i want the table as -
Month Value
Jan1
Febnull
Marnull
Aprnull
May10
Junnull
Julnull
Aug20
Sepnull
Octnull
Nov30
Decnull
Can anyone please suggest something.
July 27, 2015 at 4:06 am
any suggestions, please?
July 27, 2015 at 4:29 am
try something
SELECT DATENAME(month, GETDATE()) AS 'Month Name', Value From MyTable
SQL 2000/2005/2008/2012 DBA - MCTS/MCITP
July 27, 2015 at 4:31 am
This will give only the four rows for which there is data... I need result for all the months
July 27, 2015 at 5:24 am
Naina_11 (7/27/2015)
This will give only the four rows for which there is data... I need result for all the months
select Mth = datename(month, dateadd(month, Mths.Mth, -1))
,t.val
from ( values ( 1), ( 2), ( 3), ( 4), ( 5), ( 6), ( 7), ( 8), ( 9), ( 10), ( 11), ( 12) ) as Mths (Mth)
left join dbo.testing t on Mths.Mth = month(t.DepDate)
order by Mths.Mth;
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 27, 2015 at 5:59 am
ty so much. I tried your suggestion and got the result for all the months.
The final query after a CTE is -
select Month,Resource,DepDate,Increase,Proj_Bkd,Vol_Target,Difference_B_T,Difference_Proj_T,Perc_Bkd_Trgt,Final_Prev_Year,Booked_SDL,Perc_Booked_To_Final from temp
union all
select 'Total' ,null,null,sum(Increase),sum(Proj_Bkd),sum(Vol_Target),sum(Difference_B_T),sum(Difference_Proj_T),sum(Perc_Bkd_Trgt),sum(Final_Prev_Year),sum(Booked_SDL),sum(Perc_Booked_To_Final) from temp
order by DepDate
This gives me the desired result except that i want the Total row in the alst but it is giving it as the first row and then the months data.
July 27, 2015 at 6:13 am
SELECT ISNULL(Month,'TOTAL'),Resource,DepDate,SUM(Increase),SUM(Proj_Bkd),SUM(Vol_Target),SUM(Difference_B_T),SUM(Difference_Proj_T),SUM(Perc_Bkd_Trgt),SUM(Final_Prev_Year),SUM(Booked_SDL),SUM(Perc_Booked_To_Final)
FROM temp
GROUP BY Month,Resource,DepDate
WITH ROLLUP
HAVING (GROUPING(Resource) = 0 AND GROUPING(DepDate) = 0) OR GROUPING(Month) = 1
ORDER BY GROUPING(Month) ASC,DepDate ASC
Far away is close at hand in the images of elsewhere.
Anon.
July 27, 2015 at 6:25 am
it's done. I added a Level column with 0 and 1 and ordered accordingly.
Thanks
July 30, 2015 at 10:39 pm
WITH months(MonthNumber) AS
(
SELECT 0
UNION ALL
SELECT MonthNumber+1 FROM months WHERE MonthNumber < 11
)
select datename(month,dateadd(mm,m.MonthNumber,0)),t.val from testing as t
right outer join
months as m on datename(month,dateadd(month,month(DepDate),-1))=datename(month,dateadd(mm,MonthNumber,0))
July 31, 2015 at 9:07 am
Naina_11 (7/27/2015)
it's done. I added a Level column with 0 and 1 and ordered accordingly.Thanks
Another alternative:
DECLARE @testing AS TABLE (
DepDate datetime,
val int
);
INSERT INTO @testing VALUES ('2014-01-10 00:00:00.000', 1);
INSERT INTO @testing VALUES ('2014-05-19 00:00:00.000', 10);
INSERT INTO @testing VALUES ('2014-08-15 00:00:00.000', 20);
INSERT INTO @testing VALUES ('2014-11-20 00:00:00.000', 30);
WITH MONTHS AS (
SELECT 1 AS MTH, DATEADD(month, 1, '2013-12-01') AS MONTH_START, DATEPART(quarter, DATEADD(month, 1, '2013-12-01')) AS [QUARTER]
UNION ALL
SELECT M.MTH + 1, DATEADD(month, 1, M.MONTH_START), DATEPART(quarter, DATEADD(month, 1, M.MONTH_START))
FROM MONTHS AS M
WHERE M.MTH + 1 < 13
),
QUARTERS AS (
SELECT 1 AS QTR, 'st' AS EXT
UNION ALL
SELECT 2, 'nd'
UNION ALL
SELECT 3, 'rd'
UNION ALL
SELECT 4, 'th'
)
SELECT ISNULL(CAST(M.[QUARTER] AS varchar(1)) + Q.EXT + ' QTR', 'GRAND') AS [QUARTER],
ISNULL(CAST(LEFT(DATENAME(month, M.MONTH_START), 3) AS varchar(5)), 'TOTAL') AS [MONTH],
SUM(T.val) AS VAL_TOTAL
FROM MONTHS AS M
INNER JOIN QUARTERS AS Q
ON M.[QUARTER] = Q.QTR
LEFT OUTER JOIN @testing AS T
ON M.MONTH_START = DATEADD(day, 1 - DATEPART(day, T.DepDate), T.DepDate)
GROUP BY CAST(M.[QUARTER] AS varchar(1)) + Q.EXT + ' QTR', M.[MONTH_START]
WITH ROLLUP
ORDER BY [QUARTER], [MONTH]
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 31, 2015 at 9:12 am
sgmunson (7/31/2015)
Naina_11 (7/27/2015)
it's done. I added a Level column with 0 and 1 and ordered accordingly.Thanks
Another alternative:
DECLARE @testing AS TABLE (
DepDate datetime,
val int
);
INSERT INTO @testing VALUES ('2014-01-10 00:00:00.000', 1);
INSERT INTO @testing VALUES ('2014-05-19 00:00:00.000', 10);
INSERT INTO @testing VALUES ('2014-08-15 00:00:00.000', 20);
INSERT INTO @testing VALUES ('2014-11-20 00:00:00.000', 30);
WITH MONTHS AS (
SELECT 1 AS MTH, DATEADD(month, 1, '2013-12-01') AS MONTH_START, DATEPART(quarter, DATEADD(month, 1, '2013-12-01')) AS [QUARTER]
UNION ALL
SELECT M.MTH + 1, DATEADD(month, 1, M.MONTH_START), DATEPART(quarter, DATEADD(month, 1, M.MONTH_START))
FROM MONTHS AS M
WHERE M.MTH + 1 < 13
),
QUARTERS AS (
SELECT 1 AS QTR, 'st' AS EXT
UNION ALL
SELECT 2, 'nd'
UNION ALL
SELECT 3, 'rd'
UNION ALL
SELECT 4, 'th'
)
SELECT ISNULL(CAST(M.[QUARTER] AS varchar(1)) + Q.EXT + ' QTR', 'GRAND') AS [QUARTER],
ISNULL(CAST(LEFT(DATENAME(month, M.MONTH_START), 3) AS varchar(5)), 'TOTAL') AS [MONTH],
SUM(T.val) AS VAL_TOTAL
FROM MONTHS AS M
INNER JOIN QUARTERS AS Q
ON M.[QUARTER] = Q.QTR
LEFT OUTER JOIN @testing AS T
ON M.MONTH_START = DATEADD(day, 1 - DATEPART(day, T.DepDate), T.DepDate)
GROUP BY CAST(M.[QUARTER] AS varchar(1)) + Q.EXT + ' QTR', M.[MONTH_START]
WITH ROLLUP
ORDER BY [QUARTER], [MONTH]
Phases of the moon missing?? :w00t:
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 31, 2015 at 9:40 am
Phil Parkin (7/31/2015)
sgmunson (7/31/2015)
Naina_11 (7/27/2015)
it's done. I added a Level column with 0 and 1 and ordered accordingly.Thanks
Another alternative:
DECLARE @testing AS TABLE (
DepDate datetime,
val int
);
INSERT INTO @testing VALUES ('2014-01-10 00:00:00.000', 1);
INSERT INTO @testing VALUES ('2014-05-19 00:00:00.000', 10);
INSERT INTO @testing VALUES ('2014-08-15 00:00:00.000', 20);
INSERT INTO @testing VALUES ('2014-11-20 00:00:00.000', 30);
WITH MONTHS AS (
SELECT 1 AS MTH, DATEADD(month, 1, '2013-12-01') AS MONTH_START, DATEPART(quarter, DATEADD(month, 1, '2013-12-01')) AS [QUARTER]
UNION ALL
SELECT M.MTH + 1, DATEADD(month, 1, M.MONTH_START), DATEPART(quarter, DATEADD(month, 1, M.MONTH_START))
FROM MONTHS AS M
WHERE M.MTH + 1 < 13
),
QUARTERS AS (
SELECT 1 AS QTR, 'st' AS EXT
UNION ALL
SELECT 2, 'nd'
UNION ALL
SELECT 3, 'rd'
UNION ALL
SELECT 4, 'th'
)
SELECT ISNULL(CAST(M.[QUARTER] AS varchar(1)) + Q.EXT + ' QTR', 'GRAND') AS [QUARTER],
ISNULL(CAST(LEFT(DATENAME(month, M.MONTH_START), 3) AS varchar(5)), 'TOTAL') AS [MONTH],
SUM(T.val) AS VAL_TOTAL
FROM MONTHS AS M
INNER JOIN QUARTERS AS Q
ON M.[QUARTER] = Q.QTR
LEFT OUTER JOIN @testing AS T
ON M.MONTH_START = DATEADD(day, 1 - DATEPART(day, T.DepDate), T.DepDate)
GROUP BY CAST(M.[QUARTER] AS varchar(1)) + Q.EXT + ' QTR', M.[MONTH_START]
WITH ROLLUP
ORDER BY [QUARTER], [MONTH]
Phases of the moon missing?? :w00t:
Phil, you mean:
DATEPART(MoonPhase, T.DepDate) :w00t:
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
July 31, 2015 at 10:08 am
Phil Parkin (7/31/2015)
Phases of the moon missing?? :w00t:
Yeah, they went missing along with that blue moon we just had... :w00t:
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply