Which Is Best? Select From Joins Or Single Table

Jun 3, 2008

Hi,

I am using a SP which one has lot of Joins(More than 10 tables).

For every request from user SP executed, and the DB performance got slow.

For this i planned to write a schedule which runs once in an hour to put all the results of SP in to One single table.

I thought if the user selects from single table means the performance will increase.

I don't know whether this one is a right solution.

I am very new to SQL server. Help me for this Problem.

Thanks in advance.

View 6 Replies

Which Is Best? Select From Joins Or Single Table

Jun 3, 2008

View 1 Replies View Related

Select 1 Row From Each Group In A Single Table

Aug 19, 2006

Hello everyone, I've got a bit of an SQL mystery that I'm not sure how to solve. For some reason I just cant get my head around it. Here's the scenario:
Table A:
_____________
BidID - Int identity
AuctionID - int
BiderName - varchar(50)
bidAmount - money
______________________
Now obviously each Bid will have a Unique ID using BidID but the other rows will contain multiple bids per user, on many different items possibly.
BidID   AuctionID   BiderName   BidAmount
1            4005            joeblow         100.00
2            4005            janedoe         101.00
3            4005            joeblow         107.00
4            4006            joeblow         100.00
5            4006            janedoe         105.00
6            4006            joeblow         106.00

I need to find out which Auctions JoeBlow is bidding on, but I dont need a table with Rows for every single one of his bids, just a distinct auctionID for his top bid so in this case the only thing returned would be
3            4005            joeblow         107.00
6            4006            joeblow         106.00
Any clues? I've been through sub querys, and stored procedures, and I cant get anything to work quite right.
Thanks in advance for your help.

View 4 Replies View Related

Slow SELECT On Single Table

Aug 4, 2000

SELECT * on a 4000 row table is taking more than 12 seconds.
Other larger tables are not nearly as slow.
I've DBCC dbreindex'd, and dbcc showcontig shows density at 100%.

How can I figure out why this is happening?
What are some remedies?

Thanks for your help.

View 1 Replies View Related

How To Perform SELECT Query With Multiple Parameters In A Single Field In A Table

Sep 13, 2006

i just can't find a way to perform this Select Query in my ASP.Net page. I just want to find out the sales for a certain period[startDate - endDate] for each Region that will be selected in the checkbox. Table Sales Fields: SalesID | RegionID | Date | Amount This is how the interface looks like.Thank You.

View 1 Replies View Related

SQL Server 2012 :: Insert Multiple Rows In A Table With A Single Select Statement?

Feb 12, 2014

I have created a trigger that is set off every time a new item has been added to TableA.The trigger then inserts 4 rows into TableB that contains two columns (item, task type).

Each row will have the same item, but with a different task type.ie.

TableA.item, 'Planning'
TableA.item, 'Design'
TableA.item, 'Program'
TableA.item, 'Production'

How can I do this with tSQL using a single select statement?

View 6 Replies View Related

Recursively Select Records In Single Table When Having Main CategoryID And Text Value Of Subcategory

Aug 16, 2015

I want to recursively select all records within a hierarchy, using the main parentid and a textvalue on level 1 OR level 2 of the subcategories.

My data:

CREATE TABLE [dbo].[articlegroups](
[id] [int] NOT NULL,
[parentid] [int] NOT NULL,
[catlevel] [tinyint] NOT NULL,
[slug_en] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_globos_articlegroups] PRIMARY KEY CLUSTERED

[Code] ...

When selecting rows I always have the main parentId (so catlevel 0) and the slug_en value.

In my example case I have id 129 and slug_en='cradles'.

I want my query to then return:

idparentidcatlevel
12900
1301291
1361302If I have id 129 and slug_en='pillows'.

I want my query to then return:

idparentidcatlevel
12900
1391291

How can I do this? I'm new to SQL Server. I was reading here [URL] .... on recursive SQL, but how to implement this as I just have one table and I also have 2 selection criteria (main category id and a text value on either level 1 or 2).

View 9 Replies View Related

Combining 2 SELECT Statements (with JOINs) Into 1

Sep 17, 2013

I'm looking for a way to combine the following 2 sets of code into one select statement. They're similar in that they reference the same tables, but they have different conditionals:

Code:
SELECT TABLE_01.Date
, TABLE_01.State
, TABLE_01.City
, ISNULL((SUM(A.Bandwidth)),0) AS SD_Bandwidth
INTO TABLE_FINAL_01
FROM TABLE_01
LEFT OUTER JOIN TABLE_02 A

[Code] .....

View 4 Replies View Related

Update From A Select With Multiple Joins

Feb 1, 2014

I am trying to do a query like this...

UPDATE g
SET g.GroupID = gp.GroupID, g.Contact1 = members.FirstName, g.BusPhone1 = members.BusPhone, g.HomePhone1 = members.HomePhone, g.Internet1 = members.Email
FROM statelst AS g INNER JOIN
grpcon AS gp ON g.GroupID = gp.GroupID INNER JOIN
members ON gp.MemberID = members.MemberID CROSS JOIN

I have my table statelst that I want to update certain columns from the values returned by a select on the grpcon table joined to the members table.I am getting an error "Incorrect syntex near 'JOIN'.

View 1 Replies View Related

Combine Data In Single Row From Single Table

Apr 4, 2006

How can i combine my data in single row ? All data are in a single table sorted as employeeno, date

Code:

Employee No Date SALARY
1 10/30/2006 500
1 11/30/2006 1000
2 10/25/2006 800
3 10/26/2006 900
4 10/28/2006 1000
4 11/01/2006 8000

Should Appear

Code:

EmployeeNo Date1 OLDSALARY Date2 NEWSALARY
1 10/30/2006 500 11/30/2006 1000
2 10/25/2006 800
3 10/26/2006 900
4 10/28/2006 1000 11/01/2006 800

PLEASE HELP I REALLY NEED THE RIGHT QUERY FOR THIS OUTPUT.

THANKS IN ADVANCE

View 3 Replies View Related

Select Distinct Records With Multiple Joins

Mar 13, 2008

View 3 Replies View Related

Does It Store All The Results To Tempdb Database When I Query Against A Large Table Which Joins Another Table?

Jun 25, 2007

Hi, all experts here,

I am wondering if tempdb stores all results tempararily whenever I query a large fact table with over 4 million records which joins another dimension table? Since each time when I run the query, the tempdb grows to nearly 1GB which nearly runs out all the space on my local system drive, as a result the performance totally down. Is there any way to fix this problem? Thanks a lot in advance and I am looking forward to hearing from you shortly for your kind advices.

With best regards,

Yours sincerely,

View 11 Replies View Related

Table Joins!

Dec 5, 2007

Just curious if anyone has any in depth knowledge of how table join filtering works:

SELECT col1
FROM tbl1 a
INNER JOIN tbl2 b
ON a.col2 = b.col2
AND a.col3 = 1

(versus)

SELECT col1
FROM tbl1 a
INNER JOIN tbl2 b
ON a.col2 = b.col2
WHERE a.col3 = 1

Running some simple tests, the executions plans look identical. Does anyone know if/when either of these options would be preferential over the other? This is a rather difficult topic to Google and find any decent information.

Thanks!

View 5 Replies View Related

Why Two Joins On The Same Table ?

Apr 10, 2008

I was looking at some sample queries on how to do product promotion. Basically im after the top few products that have been ordered along with a given product ID. I think this should do it, but wasnt sure about the 2nd join...and why ?

SELECT
ProductID,
ProductName,
SUBSTRING(Description, 1, 150) + '...'
AS Description
FROM Products
WHERE ProductID IN
(
SELECT TOP 5 details2.ProductID
FROM OrderDetails details1
INNER JOIN OrderDetails details2
ON details1.OrderID = details2.OrderID
WHERE details1.ProductID = @ProductID
AND details2.ProductID != @ProductID
GROUP BY details2.ProductID
)

View 2 Replies View Related

3 Table Joins

May 16, 2008

I have 3 tables, students (studentId, studentName), classes (classId, className) and studentclass (studentId, classId, sessionId)

Want to display all studentnames, classnames and sessionIds, regardless of whether the student is enrolled in a specific class or not.
So the result set should show all students and all classes:
studentname classname sessionId
----------- --------- ---------
john english 2
john math <null>
jane english <null>
jane math 3

Please help in how to write such SQL?
Thanks in advance

View 6 Replies View Related

Joins On Same Table

Jul 20, 2005

I'm having two general problems trying to do a JOIN. I have a table withthree fields {Code, Date, Amount}. Code+Date is a unique key. I'm tryingto get a rowset with 1) one row for each unique Code+Date pair, 2) andwith each row containing, {Code, Amount for Date-A and Amount forDate-B}. Basically, I want to create two temp tables with the Amounts fora specified Date and then Join them.The problems are1) I'm trying to do this in SQL-Server 7 with a single stantment, and2) If a Code+Date pair doesn't have any Amounts, I'd still like a rowreturned with NULLs.Anybody have any wisdom on this??Thanks

View 9 Replies View Related

How Two JOINS From One Table

Jul 20, 2005

I got this problem where I need to look up values in two columns fromanother table.I can get OUTER LEFT JOIN working when looking up one column from a tablebut when looking up at two columns from a table I can't get the SQL syntaxcorrect.The scenario isA table has definitions for abbreviation of initials. From my search querythere are two columns of initials, I am having problems in writing an SQLstatement to look up and replace both columns of initials with theircorrect definitions. I was using an OUTER LEFT JOIN statementI am sorry if this isnt the correct Newsgroup to post this.

View 3 Replies View Related

Multiple Joins To The Same Table

Oct 29, 2006

I'm trying, with little success, to achieve something that should be quite easy (I think!) and any advice would be appreciated.

I have a leagues table structured so:

LeagueID | Name | Player1 | Player 2 ... Player6

and the data in the player columns is a userid from the users table and I'm trying to display the Leagues but with the player names rather than player IDs.

I'm working along the lines of

Code:

select
u1.displayname as Player1,
u2.displayname as Player2
from DCMLeagues as L
inner join Users as u1 on L.player1 = u1.userid
inner join Users as u2 on L.player2 = u2.userid

but with little success so far. Any thoughts would be appreciated! Thanks very much in advance.

-- Chris

View 5 Replies View Related

Table Self-joins With Updates

Sep 2, 2005

table = PEOPLE

Name Money Type
----- ----- ----
Steve 400 R
Steve 100 R
Paul 500 R
Paul 100 R
Matt 500 R
Matt 200 R
Matt 0 T
Steve 0 T
Paul 0 T

I'm trying to add-up all of the Money values for each Name and store them into their names, but under Type 'T'.

after the update command it should look like this

Name Money Type
----- ----- ----
Steve 400 R
Steve 100 R
Paul 500 R
Paul 100 R
Matt 500 R
Matt 200 R
Matt 700 T
Steve 500 T
Paul 600 T

View 1 Replies View Related

Find All Joins To A Table

Apr 25, 2008

Hello team.

I have an issue I'm hoping you can help me with.

I have very large sql server with 15 databases and thousands of tables. We have an "employee" table where we have historically been joining to the "EmployeeName" field in procs, views, etc.(bad practice, I know). I would like to now go back and make things right. I would like to identify ALL procs and views that have a join on the "EmployeeName" field, and modify it to use the "employeeid" field. I would like a script or a suggestion that would help me identify all the places where I would need to make this change.

I thought of querying the syscomments table, but the joins are not always laid out the same way so I know I wouldn't be able to catch all of them. Maybe using profiler to capture all statements executed and have them trigger an email to a DB developer every time? I don't know... Suggestions?

Many Thanks

View 2 Replies View Related

Problem With 2 Joins From Same Table

Aug 6, 2007

Hi I'm new to SQL and I'm having some problems with the following join. I have a table, Ticket, with two int fields, Submitter and Acceptor, among many others. These fields reference the ID of users stored in the Users table and are usually set to different IDs (ie. they are independant fields).

Here is the relevent SQL to get the ticket records (generated by the Query Editor in SQL Management Studio Express):

SELECT Users.Name, Tickets.ID
FROM Tickets INNER JOIN
Users ON Tickets.Submitter = Users.ID AND Tickets.Acceptor = Users.ID

Problem is, this only returns records where the submitter and acceptor IDs are the same which is rarely the case. What am I doing wrong here? I've created a lot of joins for other records without problem - this is the only case where two fields are coming from the same table.

Thanks for any help.

View 2 Replies View Related

Table Joins On More Than One Field

May 4, 2006

Hello all,Can someone help me with this SQL?1) EMPLOYEE table has columns 'employee_id' and 'emp_sid' and othercolumns2) EMPLOYEE_BENEFITS table has a column called employee_entity, thiscolumn can be joined to either 'employee_id' OR 'emp_sid' but not bothin the EMPLOYEE table.3) EMPLOYEE_TRACK table has column called employee_track_entity, thiscolumn can be joined to the employee_benefits_id (PK) of theEMPLOYEE_BENEFITS table.I am listing the sql for the tables (the tables shows only the columnsin question)CREATE TABLE [dbo].[EMPLOYEE] ([employee_id] [int] IDENTITY (1, 1) NOT NULL ,[empsid_id] [int] NOT NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[EMPLOYEE_BENEFITS] ([employee_benefits_id] [int] IDENTITY (1, 1) NOT NULL ,[employee_entity] [int] NOT NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[EMPLOYEE_TRACK ] ([employee_track_id] [int] IDENTITY (1, 1) NOT NULL ,[employee_track_entity] [int] NOT NULL) ON [PRIMARY]GOSELECT * FROM EMPLOYEE eINNER JOINEMPLOYEE_BENEFITS eb ON (e.employee_id = eb.employee_entity ORe.empsid_id = eb.employee_entity)INNER JOINEMPLOYEE_TRACK et ON eb.employee_benefits_id = et.employee_track_entityThe above SQL I wrote is this: the second inner join uses a OR to joineither of the columns in the first table EMPLOYEE. There is performancedegradation with this SQL. With huge data It takes about 30 seconds toexecute. I know this is not the perfect way to do it, can anyone of theSQL Gurus please enlighten me to a faster approach?If I dont use the OR I can try left join on the same tableEMPLOYEE_BENEFITS twice by changing the join types, but If I did thiswhat table alias can I use to join to the 3rd table?SELECT * FROM EMPLOYEE eLEFT JOINEMPLOYEE_BENEFITS eb1 ON e.employee_id = eb.employee_entityLEFT JOINEMPLOYEE_BENEFITS eb2 ON e.empsid_id = eb.employee_entityINNER JOINEMPLOYEE_TRACK et ON [???].employee_benefits_id =et.employee_track_entitythanksadi[Sorry I am posting this twice, on SQL Programming forum too]

View 3 Replies View Related

Multiple Joins On The Same Table

Jan 5, 2006

I'm new to SQL 2005 & C# - I'm a MySQL/PHP crossover.

I'm using s Stored Procedure and I'm trying to do multiple joins onto
one table. I have 6 fields in one table that are foreign keys of
another table:

Table1

---------

id

PrimaryCode

SecondaryCode1

SecondaryCode2

SecondaryCode3

SecondaryCode4

SecondaryCode5

Table 2

---------

id

Title

CommCode

The fields in table 1 (except is obviously) hold the id of a row in
Table 2. When displaying data I want to display "Title" -
"CommCode" for each item in Table 1. I got myself started by
searchig on the net and I have a stored procedure. The obvious
problem is that as it goes through the Query only the last value
remains in place - since each value before it is cleared in the
UNION. How can I do this?? Here's my Stored Procedure:

=====================================

ALTER PROCEDURE GetRegistersSpecific

@SearchTxt int

AS

SELECT
registrations.Company,registrations.Address1,registrations.Address2,registrations.City,registrations.State,registrations.Zip,registrations.ContactName,registrations.Phone,registrations.Fax,registrations.Email,registrations.Website,registrations.Feid,registrations.BusinessType,registrations.BackupWitholding,registrations.SignedName,registrations.SignedDate,
PrimaryCode AS MyID, Title, CommodityCode

FROM registrations

JOIN CommodityCodes ON PrimaryCode = CommodityCodes.id

UNION

SELECT
registrations.Company,registrations.Address1,registrations.Address2,registrations.City,registrations.State,registrations.Zip,registrations.ContactName,registrations.Phone,registrations.Fax,registrations.Email,registrations.Website,registrations.Feid,registrations.BusinessType,registrations.BackupWitholding,registrations.SignedName,registrations.SignedDate,
SecondaryCode1 AS MyID, Title, CommodityCode

FROM registrations

JOIN CommodityCodes ON SecondaryCode1 = CommodityCodes.id

UNION

SELECT
registrations.Company,registrations.Address1,registrations.Address2,registrations.City,registrations.State,registrations.Zip,registrations.ContactName,registrations.Phone,registrations.Fax,registrations.Email,registrations.Website,registrations.Feid,registrations.BusinessType,registrations.BackupWitholding,registrations.SignedName,registrations.SignedDate,
SecondaryCode2 AS MyID, Title, CommodityCode

FROM registrations

JOIN CommodityCodes ON SecondaryCode2 = CommodityCodes.id

UNION

SELECT
registrations.Company,registrations.Address1,registrations.Address2,registrations.City,registrations.State,registrations.Zip,registrations.ContactName,registrations.Phone,registrations.Fax,registrations.Email,registrations.Website,registrations.Feid,registrations.BusinessType,registrations.BackupWitholding,registrations.SignedName,registrations.SignedDate,
SecondaryCode3 AS MyID, Title, CommodityCode

FROM registrations

JOIN CommodityCodes ON SecondaryCode3 = CommodityCodes.id

UNION

SELECT
registrations.Company,registrations.Address1,registrations.Address2,registrations.City,registrations.State,registrations.Zip,registrations.ContactName,registrations.Phone,registrations.Fax,registrations.Email,registrations.Website,registrations.Feid,registrations.BusinessType,registrations.BackupWitholding,registrations.SignedName,registrations.SignedDate,
SecondaryCode4 AS MyID, Title, CommodityCode

FROM registrations

JOIN CommodityCodes ON SecondaryCode4 = CommodityCodes.id

UNION

SELECT
registrations.Company,registrations.Address1,registrations.Address2,registrations.City,registrations.State,registrations.Zip,registrations.ContactName,registrations.Phone,registrations.Fax,registrations.Email,registrations.Website,registrations.Feid,registrations.BusinessType,registrations.BackupWitholding,registrations.SignedName,registrations.SignedDate,
SecondaryCode5 AS MyID, Title, CommodityCode

FROM registrations

JOIN CommodityCodes ON SecondaryCode5 = CommodityCodes.id

WHERE registrations.ID = @SearchTxt

=====================================

Thanks

View 4 Replies View Related

Need Single Select Query

Feb 8, 2007

Hello Everyone,

Consider the below table

DocNbr Cheques
00001 101010
00002 101010
00002 102020
00003 103030
00004 103030
00004 104040
00005 105050

I just need the single select to result the docnbr which is repeating the values. In the above case I want my result like below where the DocNbr 00002 and 00004 repeated their values.

DocNbr Cheques
00002 101010
00002 102020
00004 103030
00004 104040

Thanks in advance,

Senthil .G

Note: I do not consider if the cheque contain repeated values

View 3 Replies View Related

Single Select Statement

Mar 1, 2008

Hi,

i have an input parameter @PageloadYN Bit Null

if @PageloadYN = 1 then Select top 500 Records from the Table
if @PageloadYN = 0 then Select * from the Table

i tried like this

Select top 500.* From Table where @PageloadYN = 1
Select * From Table where @PageloadYN = 0

Is there any way to get details in Single select statement only.

View 2 Replies View Related

Select 1-20 In Single Query.

Oct 2, 2007

Hello,

I have a question which was given to me in a test. Question is :: print value from 1-20 in a single column using single select query without using any table.
If any one have solution, then please help me.

View 18 Replies View Related

DTS: Single SELECT From 2 Databases Possible?

Jul 19, 2006

Just wondering, in DTS, can I run a SELECT script that selects from 2 different databases (both on the same server)?

I can run this in Sql Query Analyzer, but in a DTS, it doesnt accept my database name prefixes:

SELECT
a.something
FROM
DB_ONE.dbo.product a, DB_ONE.dbo.mp_brand b, DB_TWO.dbo.lk_pcat_cutover c
WHERE
a.PCat <> c.Pcat

(where DB_ONE and DB_TWO are the 2 different db names)

I have one connection to DB_ONE... does this mean I cant access DB_TWO when using this connection? I want to try and avoid using a temporay table for storing DB_TWO's data in DB_ONE... Is this possible?

Thanks,

Andre

View 1 Replies View Related

Table Joins (Mistake Corrected)

Aug 29, 2002

I have a correction to my previous thread all values are '83' not '80' My mistake

Message as it should be:

am new to SQL.

MY issue: Table (A) has the customerID field as a primary key. Table(b) has a customerID field but isn't the primary key. I am selecting all records from Table(A) and Table(B) Where Table(A).customerID=83 AND Table(B).customerID=83.

Table(A) has the customerID '83' Table(B) doesn't. The int 83 doesn't exist in table(B)

The various joins I constructed always returns (0 row(s) affected). When I enter a value that is inboth tables I get the desired result.

MY Goal:
I would like one record returned from table(A) if the customerID is not present in table(B) and one record that has a single customerID in both table(A)and Table(B)

Please advise.

Thanks

View 1 Replies View Related

Multiple INEER JOINS Between Two Table.

Sep 16, 2007

Hello Everyone,

I have a quite unusual problem, and I have hard time finding the answer.

I have a table with Locations - lets say that it has just ID, and Name,
and a Transport table containing the ID, ArrivalLocationID and DepartureLocationID.

Now - when I select the Transport table I want to get names of the Arrival and Departure locations from th other table.

If it was a single link I woul do an INNER JOIN like:

SELECT
Transport.*,
Locations.Name AS ArrivalLocation
FROM TransportProductOperationPeriods
INNER JOIN Locations ON
Transport.ArrivalLocation = Locations.ID

But I want to do a double INNER JOIN between two same table. And here I get a problem - how to do it? Something like:

SELECT
TransportProductOperationPeriods.*,
Locations.Name AS LArrivalLocation,
Locations.Name AS LDepartureLocation,
Locations.ID AS LArrivalLocationID,
Locations.ID AS LDepartureLocationID
FROM TransportProductOperationPeriods
INNER JOIN Locations ON
TransportProductOperationPeriods.ArrivalLocation = LArrivalLocationID
INNER JOIN Locations ON
TransportProductOperationPeriods.DepartureLocation = LDepartureLocationID

but obviously that does not work ;)

Thanks in advance,
Joseph

View 2 Replies View Related

Table Joins Causing Duplicates?

May 15, 2015

I have a table with call data (ContactID, Queues Entered, Call Status, Date & Time Stamps etc). Each entry relating to a contact ID goes onto a new row. The first row for a contact is the date and time it is created. It then captures the queue (or queues) it enters before it is answered. Finally, it captures when the call is released (Completed).

I'm trying to link all this data into one single row per contact ID to make it easier to report on.

I started off by using DISTINCT to pull back all of the Contact ID's. I then used a Left Join to pull back the date and time of creation. I created a further Left Join to pull back the first queue that it entered and so on.

When I did this, I started getting duplicates. This is because some calls enter more than one queue.

How can I do this so that it only has one ContactID per row. Also, for the Queue, is there anything I can do to ensure it pulls back the first Queue it enters? (These are time stamped). Subsequently, I would then need to add the second and third queue it enters in other columns. (A call can enter a maximum of 3 queues).

View 8 Replies View Related

Is There A Method To Convert Select * From Table To Select Field1,field2,...fieldn From Table ?

Nov 29, 2007

Is there a method to convert "Select * From Table" to "Select field1,field2,...fieldn From Table" ?

Thanks

View 1 Replies View Related

SQL Server 2012 :: Select Statement That Take Upper Table And Select Lower Table

Jul 31, 2014

I need to write a select statement that take the upper table and select the lower table.

View 3 Replies View Related

Declaring A Table Variable Within A Select Table Joined To Other Select Tables In Query

Oct 15, 2007

Hello,

I hope someone can answer this, I'm not even sure where to start looking for documentation on this. The SQL query I'm referencing is included at the bottom of this post.

I have a query with 3 select statements joined together like tables. It works great, except for the fact that I need to declare a variable and make it a table within two of those 3. The example is below. You'll see that I have three select statements made into tables A, B, and C, and that table A has a variable @years, which is a table.

This works when I just run table A by itself, but when I execute the entire query, I get an error about the "declare" keyword, and then some other errors near the word "as" and the ")" character. These are some of those errors that I find pretty meaningless that just mean I've really thrown something off.

So, am I not allowed to declare a variable within these SELECT tables that I'm creating and joining?

Thanks in advance,
Andy

Select * from

(

declare @years table (years int);

insert into @years

select

CASE

WHEN month(getdate()) in (1) THEN year(getdate())-1

WHEN month(getdate()) in (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) THEN year(getdate())

END

select

u.fullname

, sum(tx.Dm_Time) LastMonthBillhours

, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) lasmosbillingpercentage

from

Dm_TimeEntry tx

join

systemuserbase u

on

(tx.owninguser = u.systemuserid)

where

Month(tx.Dm_Date) = Month(getdate())-1

and

year(dm_date) = (select years from @years)

and tx.dm_billable = 1

group by u.fullname

) as A

left outer join

(select

u.FullName

, sum(tx.Dm_Time) Billhours

, ((sum(tx.Dm_Time))

/

((day(getdate()) * ((5.0)/(7.0))) * 8)) perc

from

Dm_TimeEntry tx

join

systemuserbase u

on

(tx.owninguser = u.systemuserid)

where

tx.Dm_Billable = '1'

and

month(tx.Dm_Date) = month(GetDate())

and

year(tx.Dm_Date) = year(GetDate())

group by u.fullname) as B

on

A.Fullname = B.Fullname

Left Outer Join

(

select

u.fullname

, sum(tx.Dm_Time) TwomosagoBillhours

, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) twomosagobillingpercentage

from

Dm_TimeEntry tx

join

systemuserbase u

on

(tx.owninguser = u.systemuserid)

where

Month(tx.Dm_Date) = Month(getdate())-2

group by u.fullname

) as C

on

A.Fullname = C.Fullname

View 1 Replies View Related