Click here to Skip to main content
15,389,177 members
Home / Discussions / Database
   

Database

 
QuestionLong running queries. Pin
sathish487-Jan-15 2:38
Membersathish487-Jan-15 2:38 
AnswerRe: Long running queries. Pin
ZurdoDev7-Jan-15 3:33
professionalZurdoDev7-Jan-15 3:33 
AnswerRe: Long running queries. Pin
Mycroft Holmes7-Jan-15 13:34
professionalMycroft Holmes7-Jan-15 13:34 
AnswerRe: Long running queries. Pin
Swinkaran7-Jan-15 16:26
professionalSwinkaran7-Jan-15 16:26 
QuestionYearly sales report Pin
pkfox5-Jan-15 20:26
professionalpkfox5-Jan-15 20:26 
AnswerRe: Yearly sales report Pin
Mycroft Holmes5-Jan-15 20:58
professionalMycroft Holmes5-Jan-15 20:58 
GeneralRe: Yearly sales report Pin
pkfox5-Jan-15 22:11
professionalpkfox5-Jan-15 22:11 
GeneralRe: Yearly sales report Pin
Umer Akram6-Jan-15 0:13
MemberUmer Akram6-Jan-15 0:13 
you can do this with a simple calendar table. in which you can have all the dates which you required. something like this

Note: I have used variable table for the demonstration purpose.

SQL
Declare @CalendarTable table
	(
	[DateKey] [int] NOT NULL,
	[FullDateAlternateKey] [date] NOT NULL,
	[DayNumberOfWeek] [tinyint] NOT NULL,
	[EnglishDayNameOfWeek] [nvarchar](10) NULL,
	[DayNumberOfMonth] [tinyint] NOT NULL,
	[DayNumberOfYear] [smallint] NOT NULL,
	[WeekNumberOfYear] [tinyint] NULL,
	[EnglishMonthName] [nvarchar](10) NULL,
	[MonthNumberOfYear] [tinyint] NOT NULL,
	[CalendarQuarter] [tinyint] NOT NULL,
	[CalendarYear] [smallint] NOT NULL,
	[CalendarSemester] [tinyint] NULL
	)

-- Populate the table with a TALLY table approach.
Declare @StartDate datetime 
Declare @EndDate datetime 

Select @StartDate = '20140101',
		@EndDate = '20141231'

;WITH cTally
AS
(
select top 1100 ROW_NUMBER() Over (order by (select null)) - 1 as num
from syscolumns c
cross join syscolumns cc
) 


INSERT INTO @CalendarTable(DateKey, FullDateAlternateKey, DayNumberOfWeek,
	        EnglishDayNameOfWeek, DayNumberOfMonth, DayNumberOfYear,
	        WeekNumberOfYear, EnglishMonthName, MonthNumberOfYear,
	        CalendarQuarter, CalendarYear)
SELECT 	  
	  CONVERT(INT, CONVERT(nvarchar(12), DATEADD(dd,num,@StartDate), 112)),
	  DATEADD(dd,num,@StartDate),
	  DATEPART(dw, DATEADD(dd,num,@StartDate)),
	  DATENAME(dw, DATEADD(dd,num,@StartDate)),
	  DATEPART(dd, DATEADD(dd,num,@StartDate)),
	  DATEPART(dayofyear, DATEADD(dd,num,@StartDate)),
	  DATEPART(week, DATEADD(dd,num,@StartDate)),
	  DATENAME(MONTH, DATEADD(dd,num,@StartDate)),
	  DATEPART(mm, DATEADD(dd,num,@StartDate)),
	  DATEPART(qq, DATEADD(dd,num,@StartDate)),
	  DATEPART(yyyy,  DATEADD(dd,num,@StartDate))
from cTally
Where  DATEADD(dd,num,@StartDate) <= @EndDate


this is how you will use this. Let get your sample data in a proper format for better
readability.

SQL
Declare @Table1 table
	(
		owner_id		varchar(10),
		close_date		datetime,
		opp_id			int
	)

INSERT INTO @Table1
(
	owner_id,	
	close_date,
	opp_id		
)

select 'Peter'	, '2014-01-02 00:00:00.000',	1	union all
select 'Peter'	, '2014-01-02 00:00:00.000',	2	union all
select 'Peter'	, '2014-02-04 00:00:00.000',	3	union all
select 'Peter'	, '2014-02-04 00:00:00.000',	4	union all
select 'Peter'	, '2014-03-06 00:00:00.000',	5	union all
select 'Peter'	, '2014-03-06 00:00:00.000',	6	union all
select 'Peter'	, '2014-04-08 00:00:00.000',	7	union all
select 'Paul'	, '2014-01-02 00:00:00.000',	8	union all
select 'Paul'	, '2014-01-02 00:00:00.000',	9	union all
select 'Paul'	, '2014-02-02 00:00:00.000',	10	union all
select 'Paul'	, '2014-02-02 00:00:00.000',	11	union all
select 'Paul'	, '2014-03-02 00:00:00.000',	12	union all
select 'Paul'	, '2014-04-02 00:00:00.000',	13	union all
select 'Paul'	, '2014-04-02 00:00:00.000',	14	union all
select 'Mary'	, '2014-06-01 00:00:00.000',	15

Declare @Table2 table 
	(
	opp_id int,
	amtfinanced float
	)

INSERT INTO @Table2
	(
	opp_id,
	amtfinanced 
	)


select 1	, 2000.00			union all
select 2	, 12000.00			union all
select 3	, 12000.00			union all
select 4	, 13000.00			union all
select 5	, 14000.00			union all
select 6	, 15000.00			union all
select 7	, 16000.00			union all
select 8	, 12000.00			union all
select 9	, 13000.00			union all
select 10	, 14000.00			union all
select 11	, 15000.00			union all
select 12	, 16000.00			union all
select 13	, 17000.00			union all
select 14	, 18000.00			union all
select 15	, 1000000.00		union all
select 17	, 2000000.00


Here we can put things together.

SQL
;WITH CTE
AS
(
SELECT DISTINCT T1.owner_id, c.EnglishMonthName, c.CalendarYear, c.MonthNumberOfYear
FROM @CalendarTable C
CROSS JOIN @Table1 T1
)

SELECT C.owner_id, c.EnglishMonthName, c.CalendarYear, isnull(cpg.TOTA_amtfinanced,0) AS amtfinanced
FROM CTE C
LEFT JOIN (
			SELECT t1.owner_id, datepart(mm,T1.close_date) as mon,  datepart(yy,T1.close_date) as yr,  SUM(T2.amtfinanced) AS TOTA_amtfinanced
			FROM @Table1 t1
			INNER JOIN @Table2 t2 on t1.opp_id = t2.opp_id
			GROUP BY t1.owner_id,T1.close_date
		  ) cpg on c.CalendarYear = cpg.yr
				and c.MonthNumberOfYear = cpg.mon
				and C.owner_id = cpg.owner_id

ORDER BY C.owner_id, c.CalendarYear, c.MonthNumberOfYear


hope it helps.
GeneralRe: Yearly sales report Pin
pkfox6-Jan-15 6:39
professionalpkfox6-Jan-15 6:39 
GeneralRe: Yearly sales report Pin
pkfox10-Jan-15 22:20
professionalpkfox10-Jan-15 22:20 
GeneralRe: Yearly sales report Pin
Umer Akram11-Jan-15 19:18
MemberUmer Akram11-Jan-15 19:18 
QuestionWhat is Schema of a Database Pin
King Fisher25-Dec-14 20:27
professionalKing Fisher25-Dec-14 20:27 
AnswerRe: What is Schema of a Database Pin
Kornfeld Eliyahu Peter25-Dec-14 21:10
professionalKornfeld Eliyahu Peter25-Dec-14 21:10 
Questionmysql 1 to many ralationship Pin
Raymart Calinao (AsthreA)20-Dec-14 17:34
MemberRaymart Calinao (AsthreA)20-Dec-14 17:34 
GeneralRe: mysql 1 to many ralationship Pin
PIEBALDconsult20-Dec-14 18:17
professionalPIEBALDconsult20-Dec-14 18:17 
GeneralRe: mysql 1 to many ralationship Pin
Raymart Calinao (AsthreA)20-Dec-14 18:23
MemberRaymart Calinao (AsthreA)20-Dec-14 18:23 
GeneralRe: mysql 1 to many ralationship Pin
PIEBALDconsult20-Dec-14 18:26
professionalPIEBALDconsult20-Dec-14 18:26 
QuestionMySQL Query Example/Solution of Oracle Query Pin
M Riaz Bashir19-Dec-14 5:08
MemberM Riaz Bashir19-Dec-14 5:08 
QuestionRe: MySQL Query Example/Solution of Oracle Query Pin
Eddy Vluggen19-Dec-14 5:20
professionalEddy Vluggen19-Dec-14 5:20 
AnswerRe: MySQL Query Example/Solution of Oracle Query Pin
Jörgen Andersson19-Dec-14 7:17
professionalJörgen Andersson19-Dec-14 7:17 
GeneralRe: MySQL Query Example/Solution of Oracle Query Pin
Eddy Vluggen19-Dec-14 7:31
professionalEddy Vluggen19-Dec-14 7:31 
GeneralRe: MySQL Query Example/Solution of Oracle Query Pin
Jörgen Andersson19-Dec-14 8:06
professionalJörgen Andersson19-Dec-14 8:06 
GeneralRe: MySQL Query Example/Solution of Oracle Query Pin
M Riaz Bashir20-Dec-14 7:26
MemberM Riaz Bashir20-Dec-14 7:26 
GeneralRe: MySQL Query Example/Solution of Oracle Query Pin
Jörgen Andersson4-Jan-15 2:45
professionalJörgen Andersson4-Jan-15 2:45 
QuestionThe SQL problem about student majoring courses Pin
tzungshian18-Dec-14 0:40
Membertzungshian18-Dec-14 0:40 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.