November 20, 2007 at 12:55 pm
close but what it needs it to use the date from .01 since that is the first date it hist going up.
November 21, 2007 at 7:11 am
One other quick question... Does anyone know how (if I can) once the results from this query (below) are done, I want to rerun a query that will check and see what dates are still null, if there are any then email the results to me?
Also, on my previous issue, for some reason it is not getting dates for proj_id bigger than 11334.001.02, say 11334.001.02.01?
November 22, 2007 at 12:38 am
Bob Fazio (11/20/2007)
Also, if the current PK is on the PROJ_ID which I expect it is, and assuming that it is a clustered index which is most likely the case, and it is sorted in the right order, you don't need the temp table anymore. However, this is a safe test.
Be careful. Just because the clustered index defines the physical sort ofrder of the data doesn't mean that the data will always be returned in that order. If there's no order by speciifed, SQL makes no guarentees about the order of the result set.
It will often be ordered by the clustering key, but there's no guarentee that it always will be.
Bottom line, if you need a specific order in a result set, use an order by.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 11, 2007 at 10:36 am
Hi Bob, its your Bengal friend again 🙂
I have another SQL Server question, wondered if you could help me with?
I need to "join" the output of several queries so the columns appears as though it was run as a single select statment...
SELECT col1,col2,col3
FROM table1
SELECT col4,col5,col6
form table2
personnel_id would say be the key...
Desired Output :
col1,col2,col3,col4,col5,col6
December 11, 2007 at 10:57 am
SELECT col1,col2,col3,col4,col5,col6
FROM table1
inner join table2 on table1.personnel_id = table2.personnel_id
Is this what your talking about?
December 11, 2007 at 11:41 am
I just sent you an email with the details....
If this is too much dont worry....
December 11, 2007 at 12:33 pm
Based upon what you sent me, I think this is now what you are looking for.
IF(OBJECT_ID('tempdb..#t1') IS NOT NULL) DROP TABLE #t1
IF(OBJECT_ID('tempdb..#t2') IS NOT NULL) DROP TABLE #t2
CREATE TABLE #t1 (t1_id INT PRIMARY KEY CLUSTERED, t1_name VARCHAR(50))
INSERT INTO #t1(t1_id,t1_name)
SELECT 1,'Emp1'
UNION
SELECT 2,'Emp2'
-- FK just for show.
CREATE TABLE #t2 (t2_id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, t1_id INT FOREIGN KEY REFERENCES #t1,deduction MONEY)
INSERT INTO [#t2] (t1_id,deduction)
SELECT 1,20.0
UNION SELECT 1,20.0
UNION SELECT 1,40.0
UNION SELECT 2,20.0
UNION SELECT 2,20.0
UNION SELECT 2,10.0
UNION SELECT 2,10.0
UNION SELECT 2,40.0
SELECT #t1.t1_id,#t1.t1_name,
-- Need one for each POSSIBLE deduction [#] AS [Deduction#]
[1] AS [Deduction1],[2] AS [Deduction2],[3] AS [Deduction3],[4] AS [Deduction4] FROM (SELECT
ROW_NUMBER() OVER(PARTITION BY t1_id ORDER BY t2_id ) AS cntr,
t1_id,
deduction FROM #t2) AS P
PIVOT
(MAX(deduction) FOR cntr IN
-- Needs to match above [Deduction#]
([1],[2],[3],[4])) AS p2
-- Everything above gives you it on one line
-- Now you can join it to main employee table
INNER JOIN #t1
ON [#t1].t1_id = p2.t1_id
1Emp120.0040.00NULLNULL
2Emp210.0020.0040.00NULL
December 11, 2007 at 12:37 pm
This can also be done with a series of case/group by statements. The pivot however is designed for this situation.
It's not that I don't want to help you with this, but I am by far the expert at T-SQL on this site. Let's keep it open for others to contribute too.
December 11, 2007 at 12:44 pm
Trying to figure out why I wasn't seeing the dups. Not the pivot, the test table
IF(OBJECT_ID('tempdb..#t1') IS NOT NULL) DROP TABLE #t1
IF(OBJECT_ID('tempdb..#t2') IS NOT NULL) DROP TABLE #t2
CREATE TABLE #t1 (t1_id INT PRIMARY KEY CLUSTERED, t1_name VARCHAR(50))
INSERT INTO #t1(t1_id,t1_name)
SELECT 1,'Emp1'
UNION
SELECT 2,'Emp2'
-- FK just for show.
CREATE TABLE #t2 (t2_id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, t1_id INT FOREIGN KEY REFERENCES #t1,deduction MONEY)
INSERT INTO [#t2] (t1_id,deduction)
SELECT 1,20.0
UNION ALL SELECT 1,20.0
UNION ALL SELECT 1,40.0
UNION ALL SELECT 2,20.0
UNION ALL SELECT 2,20.0
UNION ALL SELECT 2,10.0
UNION ALL SELECT 2,10.0
UNION ALL SELECT 2,40.0
SELECT * FROM #t2
SELECT #t1.t1_id,#t1.t1_name,
-- Need one for each POSSIBLE deduction [#] AS [Deduction#]
[1] AS [Deduction1],[2] AS [Deduction2],[3] AS [Deduction3],[4] AS [Deduction4],[5] AS [Deduction5] FROM (SELECT
ROW_NUMBER() OVER(PARTITION BY t1_id ORDER BY t2_id ) AS cntr,
t1_id,
deduction FROM #t2) AS P
PIVOT
(MAX(deduction) FOR cntr IN
-- Needs to match above [Deduction#]
([1],[2],[3],[4],[5])) AS p2
-- Everything above gives you it on one line
-- Now you can join it to main employee table
INNER JOIN #t1
ON [#t1].t1_id = p2.t1_id
This shows it better.
December 11, 2007 at 2:16 pm
thanks Bob... I am at home now but will give that a shot first thing in the morning...
I appreciate your help... I am new at SQL Server and I am getting really tough requirements (at least for me), but just looking at your code has taught me alot!
Thanks!
December 12, 2007 at 4:38 am
ok, got in this morning and looked at the code... I am a little confused as to what is happening? The query that I sent you, how does this fit in to what you wrote below? Thanks!
December 12, 2007 at 7:21 am
You sent me 4 queries which all could be represented by a view/table where the PK is Empl_ID (Except Deductions)
From your comments You didn't appear to have issue with the first 3
--earnings
--city
--paytype
select e.*,c.*,p.*
from (..Earnings query) AS e
inner join (...City Query) AS c
on e.empl_id = c.empl_id
inner join (...paytype query) AS p
on p.empl_id = e.empl_id
but issue came into the deductions where there are more than 1 deduction per employee.
Adding that query/view to the mix gave you a row for each deduction rather than 1 row per employee which is what the others do.
The example I gave above with the pivot table is the logic needed denormalize the deductions so that you only have 1 row per empl_id.
which then allows you to just add it in
--deductions
select e.*,c.*,p.*,d.*
from (..Earnings query) AS e
inner join (...City Query) AS c
on e.empl_id = c.empl_id
inner join (...paytype query) AS p
on p.empl_id = e.empl_id
inner join (...Deductions PIVOT query) as d
on d.empl_id = e.empl_id
December 12, 2007 at 7:31 am
Here is the same example using a CASE/Group by rather than the pivot.
SELECT t1_id,
MAX(CASE WHEN RN = 1 THEN deduction END) AS Deduction1,
MAX(CASE WHEN RN = 2 THEN deduction END) AS Deduction2,
MAX(CASE WHEN RN = 3 THEN deduction END) AS Deduction3,
MAX(CASE WHEN RN = 4 THEN deduction END) AS Deduction4,
MAX(CASE WHEN RN = 5 THEN deduction END) AS Deduction5
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY t1_id ORDER BY t2_id) AS RN,T1_ID,Deduction
FROM #t2) AS p
GROUP BY t1_id
December 12, 2007 at 12:10 pm
Got the message about there being 39 possible deductions. FYI, I don't mind you sending me stuff that you don't want to post, but I do want to keep this thread going for informational purposes if nothing else.
Here is what I suggest. You could do the pivot or case with 39 possible uggh! However, in this case, you are probably better off doing it dynamically.
Are you doing this through some .NET (asp) or any variation of an outside language?
Are you building the SP that will be used to return the results?
I ask, because this is a foolish request. Sounds to me like your developers don't know how to handle multiple rows, so are asking you to denormalize the data for them.
One possible solution (this is a BAD WAY TO DO THIS) is to create a temp table.
Fill it with all of the rows that you can get that are always unique based upon empl_id.
Go back and determine what columns you need (max deductions/ empl_id) and alter the temp table to include those columns.
then just return the data via "select * from #my_temp_Table" or something. Of course how will they know what they are going to get?
December 14, 2007 at 4:01 am
Hi Bob, here is what I was able to come up with, almost, but not quite.
The original entries look like:
empl_id CodeCat
------------ ------------------
5 401CU,227.2700
5 401,10.0000
5 DENT,15.5000
5 MED,133.0200
5 OPLFEE,70.4500
8 401L,283.5700
8 LTDSUP,11.5900
8 OPLFEE,16.3200
8 DENT,12.0000
8 MED,109.4600
8 VISION,6.7800
8 401,8.0000
After the script they look like:
empl_id deds
--------------- ----------
5 401CU,227.27
8 OPLFEE,16.32
What I am trying to get is:
empl_id deds
--------------- ----------
5 401CU,227.27,401,10.0000,DENT,15.5000 ...
8 401L,283.5700,LTDSUP,11.5900,OPLFEE,16.3200 ...
Below is the code I used:
-- create table #t2 (empl_id varchar(15), deds varchar(800))
set nocount on
declare
@emp varchar(15),
@ded varchar(20),
@ded_amt money,
@cnt int,
@stat int,
@d_entry varchar(50)
set @cnt = 0
declare ded_zone cursor for
select empl_id, ded_cd, ded_rt_amt
from deeds order by empl_id
open ded_zone
Fetch next from ded_zone
into @emp, @ded, @ded_amt
while @@fetch_status = 0
begin
set @d_entry = @ded + ',' + cast(@ded_amt as varchar)
update #t2 set deds = @d_entry where empl_id = @emp
set @cnt = @@Rowcount
if @cnt = 0
begin
insert #t2 (empl_id, deds) values (@emp, @ded)
end
Fetch next from ded_zone
into @emp, @ded, @ded_amt
end
close ded_zone
deallocate ded_zone
Viewing 15 posts - 31 through 45 (of 52 total)
You must be logged in to reply to this topic. Login to reply