Re: Yearly sales report - Database Discussion Boards

Could any one help me.what are the necessary steps to be followed to face the long running queries.please help me.
Thanks in Advance.

sathish48 wrote:
what are the necessary steps to be followed to face the long running queries

That depends. Is this a web app? Then you'll want to call it asynchronously so that you can provide feedback to client. Most important, do what you can to increase performance.

There are only 10 types of people in the world, those who understand binary and those who don't.

Assuming it is SQL server AND you are using a stored procedure!
Use the execution plan to identify indexes that may be required and identify the high cost parts of the query.

You then need to look into the structure of your query.

Never underestimate the power of human stupidity
RAH

Well it's not a step by step process. It all depnds on what are you trying to execute and how the layer is structured.

Check for SQL server / query optimization and understand the ways to increase the performance.
Top 10 steps to optimize data access in SQL Server: Part I (use indexing)[^]

Hi all, is there a way to select records from two tables where the right hand side table may or may not have a matching record ? What I'm trying to do is produce a yearly report of sales per month per salesperson and if there are no entries for a given month I still want a record for the salesperson but showing a zero amount. I've looked at various *solutions* on the web but they all produce a null set which is not what I want. Null values for the sales is acceptable but I always want the salesperson name returned
// Table 1 ( Salesperson )
Salesperson
Pete
Paul
Mary

// Table2 ( Sales )
Salesperson. Sale date. SaleAmount
Pete. 01/01/2015. 1000
Pete. 01/01/2015. 2000

I would like the report to show

Salesperson. Month. Sumofmonthsales
Pete Jan. 3000
Pete. Feb. 0
etc... all through to December, and ditto for the rest of the salespersons. Hope this makes sense ( sorry about the formatting )

We can’t stop here, this is bat country - Hunter S Thompson RIP

Yes it is possible - almost anything is possible. This is one solution

SQL

SELECT Person,
		S.Mth,
		s.Yr,
		s.Amount
	FROM SalesPersonTable P
		LEFT JOIN (SELECT SalesPersonID,
									DATEPART(MONTH, SalseDate) Mth,
									DATEPART(YEAR, salesDate) Yr,
									SUM(amount) Amount
								FROM Sales
								WHERE DATEPART(YEAR, salesDate) = 2014
								GROUP BY SalesPersonID,
									DATEPART(MONTH, SalseDate),
									DATEPART(YEAR, salesDate)) S ON S.SalesPersonID = P.SalesPersonID

Never underestimate the power of human stupidity
RAH

Hi there and thank you for your time, please accept my apologies for the misinformation I was typing from memory on the train, the actual data structures are as follows , as you can see the first table can have many records for owner_id and many records for the same month

Table 1 
owner_id	close_date	opp_id
Peter	2014-01-02 00:00:00.000	1
Peter	2014-01-02 00:00:00.000	2
Peter	2014-02-04 00:00:00.000	3
Peter	2014-02-04 00:00:00.000	4
Peter	2014-03-06 00:00:00.000	5
Peter	2014-03-06 00:00:00.000	6
Peter	2014-04-08 00:00:00.000	7
Paul	2014-01-02 00:00:00.000	8
Paul	2014-01-02 00:00:00.000	9
Paul	2014-02-02 00:00:00.000	10
Paul	2014-02-02 00:00:00.000	11
Paul	2014-03-02 00:00:00.000	12
Paul	2014-04-02 00:00:00.000	13
Paul	2014-04-02 00:00:00.000	14
Mary	2014-06-01 00:00:00.000	15


Table 2
opp_id	amtfinanced
1	2000.00
2	12000.00
3	12000.00
4	13000.00
5	14000.00
6	15000.00
7	16000.00
8	12000.00
9	13000.00
10	14000.00
11	15000.00
12	16000.00
13	17000.00
14	18000.00
15	1000000.00
17	2000000.00

is this still doable ?

We can’t stop here, this is bat country - Hunter S Thompson RIP

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.

Impressive thanks I'll have a try when I get back to work

We can’t stop here, this is bat country - Hunter S Thompson RIP

Hi there I tried your solution and it worked perfectly - thanks very much.

We can’t stop here, this is bat country - Hunter S Thompson RIP

Great

I have been asked what is schema of a database during the Interview.
I googled it but I'm not clear what i understand from google.can anyone explain that ? Confused | :confused:

Born To Learn

Schema is the actual structure of the database described in such a form that understandable to humans...
A schema will contain definitions for tables, views, indexes, relations, procedures, functions and every other aspects can be accesses in the db...There is a possibility that for the same database there are several schemas, divided by access rights...So a schema is not equal the database but more a way one can see the database with the proper access rights...

Skipper: We'll fix it.
Alex: Fix it? How you gonna fix this?
Skipper: Grit, spit and a whole lotta duct tape.

I'm new to mysql .. I need a One to many ralation tutorial.. like for example I made a table called MovieList with 1 column name TitleMovie and now I want to create another table called acc1_MyWatchlist and inside of it was also a TitleMovie..my question is I want insert a value in acc1_mywatchlist but only the available values in Movielist's TitleMovie and If I tried to create another table acc2_mywaychlist it should be the same as the first table and if I tried to edit some values in the MovieList table it should be affect all the table that has the same column TitleMovie....

in Access database I just use the lookup wizard but ofcourse mysql is differnt so can you give me some examples of this query..

I don't think you understand normalisation and references.

It sounds like your first table should have and ID of some type as well as the title. Other tables would then refer to that ID, not to the title. If you need to alter the title, the IDs stay the same and no other tables need to be updated.

ok thanks for the reply and tips so can you give me an example if you don't mind?

No. Just try it. And read up on normalization.

Hi,

Please let me know the solution of mentioned Oracle Query, that how can I use the same in MySQL.

SQL

SELECT A.CNAME, B.ANAME FROM CLIENT A, DEALER B
WHERE A.CCODE=B.ACODE(+)

Thanks
(Riaz)

Which part did not work? What error did it throw?

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

MySql doesn't do implicit outer joins, you'll have to do the full ANSI syntax instead.

SQL

SELECT. A.CNAME, B.ANAME
FROM    CLIENT A,
LEFT OUTER JOIN DEALER B
    ON  A.CCODE = B.ACODE

Wrong is evil and must be defeated. - Jeff Ello
(√-sh*t)²

..that's what the plus does?

Nice Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Yeah, but note that the plus isn't on the outer side. It marks the side with the nulls.

Wrong is evil and must be defeated. - Jeff Ello
(√-sh*t)²

thank you sir

You're welcome.

Wrong is evil and must be defeated. - Jeff Ello

COURSE (CourseID, Cname, Ccredit)
TEACHER (TeacherID, Tname, Toffice)
STUDENT (StudentID, Sname, Sclass)
TAKE_COURSES (CourseID, TeacherID, StudentID, Score, ClassRoom)

Question:
List all the name of students whose subjects are all past.

The answer is:
SELECT Sname
FROM STUDENT
WHERE NOT EXISTS(
SELECT *
FROM TAKE_COURSE
WHERE TAKE_COURSE.StudentID= STUDENT.StudentID AND Score<60)

I can't understand this sentence
WHERE TAKE_COURSE.StudentID= STUDENT.StudentID AND Score<60)
Can anyone please tell me how to resolve it?
Thank you very much.