Just wondering if anyone could help with returning dates not in a table eg
Orders table has a number of Orders with OrderDate stored. I would like to
write a query that displays all dates between say 01 October 2005 and 30
October 2005 that did not have a OrderDate, ie if there were no Orders with
an OrderDate recorded on the 5 October then the query would return 5 October.
Tina
Below is one way you could achieve this using a Calender Reference Table:
CREATE TABLE orders
(
OrderID INT,
OrderDate SMALLDATETIME
)
INSERT orders SELECT 1, '1 Oct 2005'
INSERT orders SELECT 2, '2 Oct 2005'
INSERT orders SELECT 3, '3 Oct 2005'
INSERT orders SELECT 4, '4 Oct 2005'
-- Create Calender Reference Table
CREATE TABLE calender
(
CalenderDate SMALLDATETIME
)
DECLARE @.dt SMALLDATETIME
SET @.dt = '1 Jan 2005'
WHILE @.dt < DATEADD(YEAR, 1, '1 Jan 2005')
BEGIN
INSERT calender SELECT @.dt
SET @.dt = DATEADD(DAY, 1, @.dt)
END
SELECT calenderdate
FROM Calender
WHERE calenderdate BETWEEN '1 Oct 2005' AND '31 Oct 2005'
AND NOT EXISTS (SELECT 1 FROM orders WHERE OrderDate = calenderdate)
- Peter Ward
WARDY IT Solutions
"Tina" wrote:
> Just wondering if anyone could help with returning dates not in a table eg
> Orders table has a number of Orders with OrderDate stored. I would like to
> write a query that displays all dates between say 01 October 2005 and 30
> October 2005 that did not have a OrderDate, ie if there were no Orders with
> an OrderDate recorded on the 5 October then the query would return 5 October.
sql
Showing posts with label table. Show all posts
Showing posts with label table. Show all posts
Friday, March 30, 2012
Return Dates Not in Table
Just wondering if anyone could help with returning dates not in a table eg
Orders table has a number of Orders with OrderDate stored. I would like to
write a query that displays all dates between say 01 October 2005 and 30
October 2005 that did not have a OrderDate, ie if there were no Orders with
an OrderDate recorded on the 5 October then the query would return 5 October
.Tina
Below is one way you could achieve this using a Calender Reference Table:
CREATE TABLE orders
(
OrderID INT,
OrderDate SMALLDATETIME
)
INSERT orders SELECT 1, '1 Oct 2005'
INSERT orders SELECT 2, '2 Oct 2005'
INSERT orders SELECT 3, '3 Oct 2005'
INSERT orders SELECT 4, '4 Oct 2005'
-- Create Calender Reference Table
CREATE TABLE calender
(
CalenderDate SMALLDATETIME
)
DECLARE @.dt SMALLDATETIME
SET @.dt = '1 Jan 2005'
WHILE @.dt < DATEADD(YEAR, 1, '1 Jan 2005')
BEGIN
INSERT calender SELECT @.dt
SET @.dt = DATEADD(DAY, 1, @.dt)
END
SELECT calenderdate
FROM Calender
WHERE calenderdate BETWEEN '1 Oct 2005' AND '31 Oct 2005'
AND NOT EXISTS (SELECT 1 FROM orders WHERE OrderDate = calenderdate)
- Peter Ward
WARDY IT Solutions
"Tina" wrote:
[vbcol=seagreen]
> Just wondering if anyone could help with returning dates not in a table eg
> Orders table has a number of Orders with OrderDate stored. I would like t
o
> write a query that displays all dates between say 01 October 2005 and 30
> October 2005 that did not have a OrderDate, ie if there were no Orders wit
h
> an OrderDate recorded on the 5 October then the query would return 5 October.[/vbc
ol]
Orders table has a number of Orders with OrderDate stored. I would like to
write a query that displays all dates between say 01 October 2005 and 30
October 2005 that did not have a OrderDate, ie if there were no Orders with
an OrderDate recorded on the 5 October then the query would return 5 October
.Tina
Below is one way you could achieve this using a Calender Reference Table:
CREATE TABLE orders
(
OrderID INT,
OrderDate SMALLDATETIME
)
INSERT orders SELECT 1, '1 Oct 2005'
INSERT orders SELECT 2, '2 Oct 2005'
INSERT orders SELECT 3, '3 Oct 2005'
INSERT orders SELECT 4, '4 Oct 2005'
-- Create Calender Reference Table
CREATE TABLE calender
(
CalenderDate SMALLDATETIME
)
DECLARE @.dt SMALLDATETIME
SET @.dt = '1 Jan 2005'
WHILE @.dt < DATEADD(YEAR, 1, '1 Jan 2005')
BEGIN
INSERT calender SELECT @.dt
SET @.dt = DATEADD(DAY, 1, @.dt)
END
SELECT calenderdate
FROM Calender
WHERE calenderdate BETWEEN '1 Oct 2005' AND '31 Oct 2005'
AND NOT EXISTS (SELECT 1 FROM orders WHERE OrderDate = calenderdate)
- Peter Ward
WARDY IT Solutions
"Tina" wrote:
[vbcol=seagreen]
> Just wondering if anyone could help with returning dates not in a table eg
> Orders table has a number of Orders with OrderDate stored. I would like t
o
> write a query that displays all dates between say 01 October 2005 and 30
> October 2005 that did not have a OrderDate, ie if there were no Orders wit
h
> an OrderDate recorded on the 5 October then the query would return 5 October.[/vbc
ol]
Return Dates Not in Table
Just wondering if anyone could help with returning dates not in a table eg
Orders table has a number of Orders with OrderDate stored. I would like to
write a query that displays all dates between say 01 October 2005 and 30
October 2005 that did not have a OrderDate, ie if there were no Orders with
an OrderDate recorded on the 5 October then the query would return 5 October.Tina
Below is one way you could achieve this using a Calender Reference Table:
CREATE TABLE orders
(
OrderID INT,
OrderDate SMALLDATETIME
)
INSERT orders SELECT 1, '1 Oct 2005'
INSERT orders SELECT 2, '2 Oct 2005'
INSERT orders SELECT 3, '3 Oct 2005'
INSERT orders SELECT 4, '4 Oct 2005'
-- Create Calender Reference Table
CREATE TABLE calender
(
CalenderDate SMALLDATETIME
)
DECLARE @.dt SMALLDATETIME
SET @.dt = '1 Jan 2005'
WHILE @.dt < DATEADD(YEAR, 1, '1 Jan 2005')
BEGIN
INSERT calender SELECT @.dt
SET @.dt = DATEADD(DAY, 1, @.dt)
END
SELECT calenderdate
FROM Calender
WHERE calenderdate BETWEEN '1 Oct 2005' AND '31 Oct 2005'
AND NOT EXISTS (SELECT 1 FROM orders WHERE OrderDate = calenderdate)
- Peter Ward
WARDY IT Solutions
"Tina" wrote:
> Just wondering if anyone could help with returning dates not in a table eg
> Orders table has a number of Orders with OrderDate stored. I would like to
> write a query that displays all dates between say 01 October 2005 and 30
> October 2005 that did not have a OrderDate, ie if there were no Orders with
> an OrderDate recorded on the 5 October then the query would return 5 October.
Orders table has a number of Orders with OrderDate stored. I would like to
write a query that displays all dates between say 01 October 2005 and 30
October 2005 that did not have a OrderDate, ie if there were no Orders with
an OrderDate recorded on the 5 October then the query would return 5 October.Tina
Below is one way you could achieve this using a Calender Reference Table:
CREATE TABLE orders
(
OrderID INT,
OrderDate SMALLDATETIME
)
INSERT orders SELECT 1, '1 Oct 2005'
INSERT orders SELECT 2, '2 Oct 2005'
INSERT orders SELECT 3, '3 Oct 2005'
INSERT orders SELECT 4, '4 Oct 2005'
-- Create Calender Reference Table
CREATE TABLE calender
(
CalenderDate SMALLDATETIME
)
DECLARE @.dt SMALLDATETIME
SET @.dt = '1 Jan 2005'
WHILE @.dt < DATEADD(YEAR, 1, '1 Jan 2005')
BEGIN
INSERT calender SELECT @.dt
SET @.dt = DATEADD(DAY, 1, @.dt)
END
SELECT calenderdate
FROM Calender
WHERE calenderdate BETWEEN '1 Oct 2005' AND '31 Oct 2005'
AND NOT EXISTS (SELECT 1 FROM orders WHERE OrderDate = calenderdate)
- Peter Ward
WARDY IT Solutions
"Tina" wrote:
> Just wondering if anyone could help with returning dates not in a table eg
> Orders table has a number of Orders with OrderDate stored. I would like to
> write a query that displays all dates between say 01 October 2005 and 30
> October 2005 that did not have a OrderDate, ie if there were no Orders with
> an OrderDate recorded on the 5 October then the query would return 5 October.
Return Date not DateTime
I am trying to count the amount of distinct dates (not datetime) in a table row. The call below returns the amount of distinct datetimes. How do I strip off the time when doing the SQL call?
SELECT COUNT(DISTINCT DT) FROM Event
SELECTConvert(Varchar,DT,101),Count(*))FROM EventGroup byConvert(Varchar,DT,101)|||
SELECTCOUNT(DISTINCTDAY(DT)+' /'+MONTH(DT)+' /'+YEAR(DT))FROMEvent
return data from two tables
Hi,
hope im posting in right group...
Im trying to return a value from 1 of two tables depending on the value in
one table.
I.e. TableA has an action_id and action_type fields. Depending on if
action_type = S, return the Desc field from TableB, where action_id =
Desc_ID. if action_type = L return the Desc field from TableC, where
action_id = Desc_ID
i.e.
select a.Type_id, (either b.Desc or c.Desc)
from TableA a, TableB b, TableC c
where 1=1
if a.action_type = 'S' then return b.Desc where a.action_id = b.Desc_id
or if a.action_type = 'L' then return c.Desc where a.action_id = c.Desc_id
i just dont know how to get this into an sql query. i can do it VBA, but
that isn't an option.
Any help or direction would be greatly appreciated.
Thanks
Tony Z.select a.Type_id, Descr=case when a.action_type='S' then b.desc else c.desc
end
from TableA a, TableB b, TableC
where 1=1
"Tony Zappal" wrote:
> Hi,
> hope im posting in right group...
> Im trying to return a value from 1 of two tables depending on the value in
> one table.
> I.e. TableA has an action_id and action_type fields. Depending on if
> action_type = S, return the Desc field from TableB, where action_id =
> Desc_ID. if action_type = L return the Desc field from TableC, where
> action_id = Desc_ID
> i.e.
> select a.Type_id, (either b.Desc or c.Desc)
> from TableA a, TableB b, TableC c
> where 1=1
> if a.action_type = 'S' then return b.Desc where a.action_id = b.Desc_id
> or if a.action_type = 'L' then return c.Desc where a.action_id = c.Desc_i
d
> i just dont know how to get this into an sql query. i can do it VBA, but
> that isn't an option.
> Any help or direction would be greatly appreciated.
> Thanks
> Tony Z.|||>select a.Type_id, Descr=case when a.action_type='S' then b.desc else c.desc endred">
> from TableA a, TableB b, TableC
> where 1=1
Those three tables, listed without any joining, will result in a cross
product result set. If there were 100 rows each, the result set would
have 100*100*100 = 1000000 rows.
Roy|||There are a few ways this could be written. Here is one.
SELECT A.Type_ID,
CASE WHEN A.action_type = 'S'
THEN (select B.desc from TableB as B
where A.something = B.something)
WHEN A.action_type = 'L'
THEN (select C.desc from TableC as C
where A.something = C.something)
FROM TableA as A
And another.
SELECT A.Type_ID, B.desc
FROM TableA as A
JOIN TableB as B
ON A.something = B.something
WHERE A.action_type = 'S'
UNION ALL
SELECT A.Type_ID, B.desc
FROM TableA as A
JOIN TableC as C
ON A.something = C.something
WHERE A.action_type = 'L'
And a third.
SELECT A.Type_ID,
CASE WHEN A.action_type = 'S' THEN B.desc
WHEN A.action_type = 'L' THEN C.desc
END
FROM TableA as A
JOIN TableB as B
ON A.something = B.something
JOIN TableC as C
ON A.something = C.something
Roy
On Tue, 21 Feb 2006 18:16:27 -0800, "Tony Zappal"
<TonyZappal@.discussions.microsoft.com> wrote:
>Hi,
>hope im posting in right group...
>Im trying to return a value from 1 of two tables depending on the value in
>one table.
>I.e. TableA has an action_id and action_type fields. Depending on if
>action_type = S, return the Desc field from TableB, where action_id =
>Desc_ID. if action_type = L return the Desc field from TableC, where
>action_id = Desc_ID
>i.e.
>select a.Type_id, (either b.Desc or c.Desc)
>from TableA a, TableB b, TableC c
>where 1=1
> if a.action_type = 'S' then return b.Desc where a.action_id = b.Desc_id
> or if a.action_type = 'L' then return c.Desc where a.action_id = c.Desc_id
>i just dont know how to get this into an sql query. i can do it VBA, but
>that isn't an option.
>Any help or direction would be greatly appreciated.
>Thanks
>Tony Z.|||Thank you John and Roy.
I've got my query returning exactly what i need.
Big thanks.
Tony.sql
hope im posting in right group...
Im trying to return a value from 1 of two tables depending on the value in
one table.
I.e. TableA has an action_id and action_type fields. Depending on if
action_type = S, return the Desc field from TableB, where action_id =
Desc_ID. if action_type = L return the Desc field from TableC, where
action_id = Desc_ID
i.e.
select a.Type_id, (either b.Desc or c.Desc)
from TableA a, TableB b, TableC c
where 1=1
if a.action_type = 'S' then return b.Desc where a.action_id = b.Desc_id
or if a.action_type = 'L' then return c.Desc where a.action_id = c.Desc_id
i just dont know how to get this into an sql query. i can do it VBA, but
that isn't an option.
Any help or direction would be greatly appreciated.
Thanks
Tony Z.select a.Type_id, Descr=case when a.action_type='S' then b.desc else c.desc
end
from TableA a, TableB b, TableC
where 1=1
"Tony Zappal" wrote:
> Hi,
> hope im posting in right group...
> Im trying to return a value from 1 of two tables depending on the value in
> one table.
> I.e. TableA has an action_id and action_type fields. Depending on if
> action_type = S, return the Desc field from TableB, where action_id =
> Desc_ID. if action_type = L return the Desc field from TableC, where
> action_id = Desc_ID
> i.e.
> select a.Type_id, (either b.Desc or c.Desc)
> from TableA a, TableB b, TableC c
> where 1=1
> if a.action_type = 'S' then return b.Desc where a.action_id = b.Desc_id
> or if a.action_type = 'L' then return c.Desc where a.action_id = c.Desc_i
d
> i just dont know how to get this into an sql query. i can do it VBA, but
> that isn't an option.
> Any help or direction would be greatly appreciated.
> Thanks
> Tony Z.|||>select a.Type_id, Descr=case when a.action_type='S' then b.desc else c.desc endred">
> from TableA a, TableB b, TableC
> where 1=1
Those three tables, listed without any joining, will result in a cross
product result set. If there were 100 rows each, the result set would
have 100*100*100 = 1000000 rows.
Roy|||There are a few ways this could be written. Here is one.
SELECT A.Type_ID,
CASE WHEN A.action_type = 'S'
THEN (select B.desc from TableB as B
where A.something = B.something)
WHEN A.action_type = 'L'
THEN (select C.desc from TableC as C
where A.something = C.something)
FROM TableA as A
And another.
SELECT A.Type_ID, B.desc
FROM TableA as A
JOIN TableB as B
ON A.something = B.something
WHERE A.action_type = 'S'
UNION ALL
SELECT A.Type_ID, B.desc
FROM TableA as A
JOIN TableC as C
ON A.something = C.something
WHERE A.action_type = 'L'
And a third.
SELECT A.Type_ID,
CASE WHEN A.action_type = 'S' THEN B.desc
WHEN A.action_type = 'L' THEN C.desc
END
FROM TableA as A
JOIN TableB as B
ON A.something = B.something
JOIN TableC as C
ON A.something = C.something
Roy
On Tue, 21 Feb 2006 18:16:27 -0800, "Tony Zappal"
<TonyZappal@.discussions.microsoft.com> wrote:
>Hi,
>hope im posting in right group...
>Im trying to return a value from 1 of two tables depending on the value in
>one table.
>I.e. TableA has an action_id and action_type fields. Depending on if
>action_type = S, return the Desc field from TableB, where action_id =
>Desc_ID. if action_type = L return the Desc field from TableC, where
>action_id = Desc_ID
>i.e.
>select a.Type_id, (either b.Desc or c.Desc)
>from TableA a, TableB b, TableC c
>where 1=1
> if a.action_type = 'S' then return b.Desc where a.action_id = b.Desc_id
> or if a.action_type = 'L' then return c.Desc where a.action_id = c.Desc_id
>i just dont know how to get this into an sql query. i can do it VBA, but
>that isn't an option.
>Any help or direction would be greatly appreciated.
>Thanks
>Tony Z.|||Thank you John and Roy.
I've got my query returning exactly what i need.
Big thanks.
Tony.sql
Return data from a temp table
I have the following stored proc
CREATE PROCEDURE [dbo].[GetPartHistory]
@.PartID int
AS
DECLARE @.tmpStepData table(Station_Name varchar(20), Step int,
Step_Description varchar(200), Station_Step_Description varchar(200), Result
varchar(100), Operator_Name varchar(50), Result_Date datetime)
INSERT INTO @.tmpStepData
(Station_Name, Step, Step_Description, Station_Step_Description, Result,
Operator_Name, Result_Date)
SELECT
Station.Station_Name,
Traveler_Step.Step,
Traveler_Step.Step_Description,
Station_Step_Data.Station_step_Description,
Step_Result.Result,
Operator.Operator_Name,
Step_Result.Result_Date
FROM
Step_Result
INNER JOIN
Operator ON Step_Result.Operator_# = Operator.Operator_#
INNER JOIN
Station_Step_Data ON Step_Result.Station_Step_Data_# =
Station_Step_Data.Station_Step_Data_#
INNER JOIN
Traveler_Step ON Station_Step_Data.Traveler_Step_# =
Traveler_Step.Traveler_Step_#
INNER JOIN
Station ON Traveler_Step.Station_# = Station.Station_#
WHERE
Step_Result.Product_# = @.PartID
INSERT INTO @.tmpStepData
(Station_Name, Step, Step_Description, Station_Step_Description, Result,
Operator_Name, Result_Date)
SELECT
Station.Station_Name,
Traveler_Step.Step,
Traveler_Step.Step_Description,
Station_Step_Data.Station_step_Description,
Weight.Weight,
Operator.Operator_Name,
Weight.Weight_Date
FROM Traveler_Step INNER JOIN
Station_Step_Data ON Traveler_Step.Traveler_Step_# =
Station_Step_Data.Traveler_Step_# INNER JOIN
Station ON Traveler_Step.Station_# = Station.Station_#
INNER JOIN
Weight ON Station_Step_Data.Station_Step_Data_# =
Weight.Station_Step_Data_# INNER JOIN
Operator ON Weight.Operator_# = Operator.Operator_#
WHERE
Weight.Product_# = @.PartID
SELECT Station_Name, Step, Step_Description, Station_Step_Description,
Result, Operator_Name, Result_Date FROM @.tmpStepData ORDER BY STEP
GO
when executed in query analyser it returns the data I am after, but when I
try to access it via ADO I get nothing. What should I do to return data
from the temp table.
John WrightTry adding SET NOCOUNT ON in the beginning of the proc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"john wright" <riley_wright@.hotmail.com> wrote in message
news:uuuVlR37FHA.2600@.tk2msftngp13.phx.gbl...
>I have the following stored proc
> CREATE PROCEDURE [dbo].[GetPartHistory]
> @.PartID int
> AS
> DECLARE @.tmpStepData table(Station_Name varchar(20), Step int, Step_Descri
ption varchar(200),
> Station_Step_Description varchar(200), Result varchar(100), Operator_Name
varchar(50), Result_Date
> datetime)
>
> INSERT INTO @.tmpStepData
> (Station_Name, Step, Step_Description, Station_Step_Description, Result,
Operator_Name,
> Result_Date)
> SELECT
> Station.Station_Name,
> Traveler_Step.Step,
> Traveler_Step.Step_Description,
> Station_Step_Data.Station_step_Description,
> Step_Result.Result,
> Operator.Operator_Name,
> Step_Result.Result_Date
> FROM
> Step_Result
> INNER JOIN
> Operator ON Step_Result.Operator_# = Operator.Operator_#
> INNER JOIN
> Station_Step_Data ON Step_Result.Station_Step_Data_# =
> Station_Step_Data.Station_Step_Data_#
> INNER JOIN
> Traveler_Step ON Station_Step_Data.Traveler_Step_# = Traveler_
Step.Traveler_Step_#
> INNER JOIN
> Station ON Traveler_Step.Station_# = Station.Station_#
> WHERE
> Step_Result.Product_# = @.PartID
> INSERT INTO @.tmpStepData
> (Station_Name, Step, Step_Description, Station_Step_Description, Result
, Operator_Name,
> Result_Date)
> SELECT
> Station.Station_Name,
> Traveler_Step.Step,
> Traveler_Step.Step_Description,
> Station_Step_Data.Station_step_Description,
> Weight.Weight,
> Operator.Operator_Name,
> Weight.Weight_Date
> FROM Traveler_Step INNER JOIN
> Station_Step_Data ON Traveler_Step.Traveler_Step_# =
> Station_Step_Data.Traveler_Step_# INNER JOIN
> Station ON Traveler_Step.Station_# = Station.Station_
# INNER JOIN
> Weight ON Station_Step_Data.Station_Step_Data_# = Wei
ght.Station_Step_Data_#
> INNER JOIN
> Operator ON Weight.Operator_# = Operator.Operator_#
> WHERE
> Weight.Product_# = @.PartID
> SELECT Station_Name, Step, Step_Description, Station_Step_Description, Res
ult, Operator_Name,
> Result_Date FROM @.tmpStepData ORDER BY STEP
> GO
>
> when executed in query analyser it returns the data I am after, but when I
try to access it via
> ADO I get nothing. What should I do to return data from the temp table.
> John Wright
>|||Yea that did it. I remembered just after I posted the message.
John
"john wright" <riley_wright@.hotmail.com> wrote in message
news:uuuVlR37FHA.2600@.tk2msftngp13.phx.gbl...
>I have the following stored proc
> CREATE PROCEDURE [dbo].[GetPartHistory]
> @.PartID int
> AS
> DECLARE @.tmpStepData table(Station_Name varchar(20), Step int,
> Step_Description varchar(200), Station_Step_Description varchar(200),
> Result varchar(100), Operator_Name varchar(50), Result_Date datetime)
>
> INSERT INTO @.tmpStepData
> (Station_Name, Step, Step_Description, Station_Step_Description, Result,
> Operator_Name, Result_Date)
> SELECT
> Station.Station_Name,
> Traveler_Step.Step,
> Traveler_Step.Step_Description,
> Station_Step_Data.Station_step_Description,
> Step_Result.Result,
> Operator.Operator_Name,
> Step_Result.Result_Date
> FROM
> Step_Result
> INNER JOIN
> Operator ON Step_Result.Operator_# = Operator.Operator_#
> INNER JOIN
> Station_Step_Data ON Step_Result.Station_Step_Data_# =
> Station_Step_Data.Station_Step_Data_#
> INNER JOIN
> Traveler_Step ON Station_Step_Data.Traveler_Step_# =
> Traveler_Step.Traveler_Step_#
> INNER JOIN
> Station ON Traveler_Step.Station_# = Station.Station_#
> WHERE
> Step_Result.Product_# = @.PartID
> INSERT INTO @.tmpStepData
> (Station_Name, Step, Step_Description, Station_Step_Description,
> Result, Operator_Name, Result_Date)
> SELECT
> Station.Station_Name,
> Traveler_Step.Step,
> Traveler_Step.Step_Description,
> Station_Step_Data.Station_step_Description,
> Weight.Weight,
> Operator.Operator_Name,
> Weight.Weight_Date
> FROM Traveler_Step INNER JOIN
> Station_Step_Data ON Traveler_Step.Traveler_Step_# =
> Station_Step_Data.Traveler_Step_# INNER JOIN
> Station ON Traveler_Step.Station_# =
> Station.Station_# INNER JOIN
> Weight ON Station_Step_Data.Station_Step_Data_# =
> Weight.Station_Step_Data_# INNER JOIN
> Operator ON Weight.Operator_# = Operator.Operator_#
> WHERE
> Weight.Product_# = @.PartID
> SELECT Station_Name, Step, Step_Description, Station_Step_Description,
> Result, Operator_Name, Result_Date FROM @.tmpStepData ORDER BY STEP
> GO
>
> when executed in query analyser it returns the data I am after, but when I
> try to access it via ADO I get nothing. What should I do to return data
> from the temp table.
> John Wright
>
CREATE PROCEDURE [dbo].[GetPartHistory]
@.PartID int
AS
DECLARE @.tmpStepData table(Station_Name varchar(20), Step int,
Step_Description varchar(200), Station_Step_Description varchar(200), Result
varchar(100), Operator_Name varchar(50), Result_Date datetime)
INSERT INTO @.tmpStepData
(Station_Name, Step, Step_Description, Station_Step_Description, Result,
Operator_Name, Result_Date)
SELECT
Station.Station_Name,
Traveler_Step.Step,
Traveler_Step.Step_Description,
Station_Step_Data.Station_step_Description,
Step_Result.Result,
Operator.Operator_Name,
Step_Result.Result_Date
FROM
Step_Result
INNER JOIN
Operator ON Step_Result.Operator_# = Operator.Operator_#
INNER JOIN
Station_Step_Data ON Step_Result.Station_Step_Data_# =
Station_Step_Data.Station_Step_Data_#
INNER JOIN
Traveler_Step ON Station_Step_Data.Traveler_Step_# =
Traveler_Step.Traveler_Step_#
INNER JOIN
Station ON Traveler_Step.Station_# = Station.Station_#
WHERE
Step_Result.Product_# = @.PartID
INSERT INTO @.tmpStepData
(Station_Name, Step, Step_Description, Station_Step_Description, Result,
Operator_Name, Result_Date)
SELECT
Station.Station_Name,
Traveler_Step.Step,
Traveler_Step.Step_Description,
Station_Step_Data.Station_step_Description,
Weight.Weight,
Operator.Operator_Name,
Weight.Weight_Date
FROM Traveler_Step INNER JOIN
Station_Step_Data ON Traveler_Step.Traveler_Step_# =
Station_Step_Data.Traveler_Step_# INNER JOIN
Station ON Traveler_Step.Station_# = Station.Station_#
INNER JOIN
Weight ON Station_Step_Data.Station_Step_Data_# =
Weight.Station_Step_Data_# INNER JOIN
Operator ON Weight.Operator_# = Operator.Operator_#
WHERE
Weight.Product_# = @.PartID
SELECT Station_Name, Step, Step_Description, Station_Step_Description,
Result, Operator_Name, Result_Date FROM @.tmpStepData ORDER BY STEP
GO
when executed in query analyser it returns the data I am after, but when I
try to access it via ADO I get nothing. What should I do to return data
from the temp table.
John WrightTry adding SET NOCOUNT ON in the beginning of the proc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"john wright" <riley_wright@.hotmail.com> wrote in message
news:uuuVlR37FHA.2600@.tk2msftngp13.phx.gbl...
>I have the following stored proc
> CREATE PROCEDURE [dbo].[GetPartHistory]
> @.PartID int
> AS
> DECLARE @.tmpStepData table(Station_Name varchar(20), Step int, Step_Descri
ption varchar(200),
> Station_Step_Description varchar(200), Result varchar(100), Operator_Name
varchar(50), Result_Date
> datetime)
>
> INSERT INTO @.tmpStepData
> (Station_Name, Step, Step_Description, Station_Step_Description, Result,
Operator_Name,
> Result_Date)
> SELECT
> Station.Station_Name,
> Traveler_Step.Step,
> Traveler_Step.Step_Description,
> Station_Step_Data.Station_step_Description,
> Step_Result.Result,
> Operator.Operator_Name,
> Step_Result.Result_Date
> FROM
> Step_Result
> INNER JOIN
> Operator ON Step_Result.Operator_# = Operator.Operator_#
> INNER JOIN
> Station_Step_Data ON Step_Result.Station_Step_Data_# =
> Station_Step_Data.Station_Step_Data_#
> INNER JOIN
> Traveler_Step ON Station_Step_Data.Traveler_Step_# = Traveler_
Step.Traveler_Step_#
> INNER JOIN
> Station ON Traveler_Step.Station_# = Station.Station_#
> WHERE
> Step_Result.Product_# = @.PartID
> INSERT INTO @.tmpStepData
> (Station_Name, Step, Step_Description, Station_Step_Description, Result
, Operator_Name,
> Result_Date)
> SELECT
> Station.Station_Name,
> Traveler_Step.Step,
> Traveler_Step.Step_Description,
> Station_Step_Data.Station_step_Description,
> Weight.Weight,
> Operator.Operator_Name,
> Weight.Weight_Date
> FROM Traveler_Step INNER JOIN
> Station_Step_Data ON Traveler_Step.Traveler_Step_# =
> Station_Step_Data.Traveler_Step_# INNER JOIN
> Station ON Traveler_Step.Station_# = Station.Station_
# INNER JOIN
> Weight ON Station_Step_Data.Station_Step_Data_# = Wei
ght.Station_Step_Data_#
> INNER JOIN
> Operator ON Weight.Operator_# = Operator.Operator_#
> WHERE
> Weight.Product_# = @.PartID
> SELECT Station_Name, Step, Step_Description, Station_Step_Description, Res
ult, Operator_Name,
> Result_Date FROM @.tmpStepData ORDER BY STEP
> GO
>
> when executed in query analyser it returns the data I am after, but when I
try to access it via
> ADO I get nothing. What should I do to return data from the temp table.
> John Wright
>|||Yea that did it. I remembered just after I posted the message.
John
"john wright" <riley_wright@.hotmail.com> wrote in message
news:uuuVlR37FHA.2600@.tk2msftngp13.phx.gbl...
>I have the following stored proc
> CREATE PROCEDURE [dbo].[GetPartHistory]
> @.PartID int
> AS
> DECLARE @.tmpStepData table(Station_Name varchar(20), Step int,
> Step_Description varchar(200), Station_Step_Description varchar(200),
> Result varchar(100), Operator_Name varchar(50), Result_Date datetime)
>
> INSERT INTO @.tmpStepData
> (Station_Name, Step, Step_Description, Station_Step_Description, Result,
> Operator_Name, Result_Date)
> SELECT
> Station.Station_Name,
> Traveler_Step.Step,
> Traveler_Step.Step_Description,
> Station_Step_Data.Station_step_Description,
> Step_Result.Result,
> Operator.Operator_Name,
> Step_Result.Result_Date
> FROM
> Step_Result
> INNER JOIN
> Operator ON Step_Result.Operator_# = Operator.Operator_#
> INNER JOIN
> Station_Step_Data ON Step_Result.Station_Step_Data_# =
> Station_Step_Data.Station_Step_Data_#
> INNER JOIN
> Traveler_Step ON Station_Step_Data.Traveler_Step_# =
> Traveler_Step.Traveler_Step_#
> INNER JOIN
> Station ON Traveler_Step.Station_# = Station.Station_#
> WHERE
> Step_Result.Product_# = @.PartID
> INSERT INTO @.tmpStepData
> (Station_Name, Step, Step_Description, Station_Step_Description,
> Result, Operator_Name, Result_Date)
> SELECT
> Station.Station_Name,
> Traveler_Step.Step,
> Traveler_Step.Step_Description,
> Station_Step_Data.Station_step_Description,
> Weight.Weight,
> Operator.Operator_Name,
> Weight.Weight_Date
> FROM Traveler_Step INNER JOIN
> Station_Step_Data ON Traveler_Step.Traveler_Step_# =
> Station_Step_Data.Traveler_Step_# INNER JOIN
> Station ON Traveler_Step.Station_# =
> Station.Station_# INNER JOIN
> Weight ON Station_Step_Data.Station_Step_Data_# =
> Weight.Station_Step_Data_# INNER JOIN
> Operator ON Weight.Operator_# = Operator.Operator_#
> WHERE
> Weight.Product_# = @.PartID
> SELECT Station_Name, Step, Step_Description, Station_Step_Description,
> Result, Operator_Name, Result_Date FROM @.tmpStepData ORDER BY STEP
> GO
>
> when executed in query analyser it returns the data I am after, but when I
> try to access it via ADO I get nothing. What should I do to return data
> from the temp table.
> John Wright
>
Return attributes from all child nodes
Ok this is the last step I hope. Trying to return a table based on
this XML:
<PARENT>
<ITEM spec="spec1 specval="red" /ITEM>
<ITEM spec="spec2 specval="white" /ITEM>
<ITEM spec="spec3 specval="blue" /ITEM>
</PARENT>
I need to return a table:
SPEC SPECVAL
spec1 red
spec2 white
spec3 blue
Thanks for any help on this.
-RichardHow about something like this?
DECLARE @.selxml xml
SET @.selxml ='
<PARENT>
<ITEM spec="spec1" specval="red" />
<ITEM spec="spec2" specval="white" />
<ITEM spec="spec3" specval="blue" />
</PARENT>'
SELECT x.value('data(./@.spec)', 'nvarchar(MAX)') as SPEC,
x.value('data(./@.specval)', 'nvarchar(MAX)') as SPECVAL
FROM @.selxml.nodes('/PARENT/ITEM') T(x)
Denis Ruckebusch
http://blogs.msdn.com/denisruc
--
This posting is provided "AS IS" with no warranties, and confers no
rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"sk8man31" <me@.aol.com> wrote in message
news:dqk3925c44br94nrn7m3msru3glgpq7h2c@.
4ax.com...
> Ok this is the last step I hope. Trying to return a table based on
> this XML:
> <PARENT>
> <ITEM spec="spec1 specval="red" /ITEM>
> <ITEM spec="spec2 specval="white" /ITEM>
> <ITEM spec="spec3 specval="blue" /ITEM>
> </PARENT>
> I need to return a table:
> SPEC SPECVAL
> spec1 red
> spec2 white
> spec3 blue
> Thanks for any help on this.
> -Richard
this XML:
<PARENT>
<ITEM spec="spec1 specval="red" /ITEM>
<ITEM spec="spec2 specval="white" /ITEM>
<ITEM spec="spec3 specval="blue" /ITEM>
</PARENT>
I need to return a table:
SPEC SPECVAL
spec1 red
spec2 white
spec3 blue
Thanks for any help on this.
-RichardHow about something like this?
DECLARE @.selxml xml
SET @.selxml ='
<PARENT>
<ITEM spec="spec1" specval="red" />
<ITEM spec="spec2" specval="white" />
<ITEM spec="spec3" specval="blue" />
</PARENT>'
SELECT x.value('data(./@.spec)', 'nvarchar(MAX)') as SPEC,
x.value('data(./@.specval)', 'nvarchar(MAX)') as SPECVAL
FROM @.selxml.nodes('/PARENT/ITEM') T(x)
Denis Ruckebusch
http://blogs.msdn.com/denisruc
--
This posting is provided "AS IS" with no warranties, and confers no
rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"sk8man31" <me@.aol.com> wrote in message
news:dqk3925c44br94nrn7m3msru3glgpq7h2c@.
4ax.com...
> Ok this is the last step I hope. Trying to return a table based on
> this XML:
> <PARENT>
> <ITEM spec="spec1 specval="red" /ITEM>
> <ITEM spec="spec2 specval="white" /ITEM>
> <ITEM spec="spec3 specval="blue" /ITEM>
> </PARENT>
> I need to return a table:
> SPEC SPECVAL
> spec1 red
> spec2 white
> spec3 blue
> Thanks for any help on this.
> -Richard
return an id while doing an insert\update to a table
Hi people,
i Have a small issue. I need to be able to retrive an id number of a new row to a table using the the insert into command. I was able to do this in sql 2000 but the same sql does not work now in 2005. here is the code
"Set NoCount On; select user_id from users insert into users (username) values('" & CurrentUser & "')"
This used to work in sql2000,
I am woundering if anyone could help me or point me in the right direction for doing this with SQL 2005
Best regards
RBowden
Did you try putting a semincolumn between the statements (before the insert) ?HTH, jens Suessmeyer.
http://www.sqlserver2005.de
|||
I tried putting the ; before the insert function it is still returns 0
any other ideas?
|||Ah, ok now I know what you mean. You are refering to the OUTPUT clause in SQL Server 2005.
"Set NoCount On; DECLARE @.Somevar VARCHAR(10);insert into users (username) OUTPUT user_id INTO @.SomeVar values('" & CurrentUser & "')"; SELECT @.SomeVar"
Look in the BOL, there should me some examples around that. If you are using an IDENTITY Column for the userid cou can also query the SCOPE_IDENTITY() function for the new identity value.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
Cheers,
thank you very much for your help, that worked a treat.
keep up the good advice
all the best
Return all rows works partially
I encounter a problem where returning all rows from a certain table in
SQL server 2000, the records sometimes can be return sometimes not.
In Enterprise Manager, I browse to a table, then I right click on it,
click "Open table > Return all rows" ... it just keep waiting until it
timeout. It didn't return any result. But when I "Return top" ... with
returning top 71, the records will shown. When I try to return top 72,
it happen again, with no result returned and timeout. I'd try a few
times with different value, it always happen when I try to return rows
until row 72.
I'd try it in Query Analyzer, it's the same.
It return rows when "SELECT TOP 71 * FROM theTableName"
but no response when "SELECT TOP 72 * FROM theTableName"
Anyone encounter this problem before? Is it the index of that table
corrupted? Or values inside that rows cannot be return?
Any idea how to solve this?
Thanks.
Peter CCH
Possibly someone has a lock on the row which will be read as row number 72 by the selected execution
plan. Check using sp_lock, sp_who etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1133516672.695691.143760@.g14g2000cwa.googlegr oups.com...
>I encounter a problem where returning all rows from a certain table in
> SQL server 2000, the records sometimes can be return sometimes not.
> In Enterprise Manager, I browse to a table, then I right click on it,
> click "Open table > Return all rows" ... it just keep waiting until it
> timeout. It didn't return any result. But when I "Return top" ... with
> returning top 71, the records will shown. When I try to return top 72,
> it happen again, with no result returned and timeout. I'd try a few
> times with different value, it always happen when I try to return rows
> until row 72.
> I'd try it in Query Analyzer, it's the same.
> It return rows when "SELECT TOP 71 * FROM theTableName"
> but no response when "SELECT TOP 72 * FROM theTableName"
> Anyone encounter this problem before? Is it the index of that table
> corrupted? Or values inside that rows cannot be return?
> Any idea how to solve this?
> Thanks.
>
> Peter CCH
>
|||But I'm the only one user who access to that database while I doing
that.
Peter CCH
|||I'd still check for blocking. It could be an open transaction hanging around or something, you never
know. Other possible reasons:
73 vs 72 rows lead to different execution plans. Check using estimated execution plan.
Table corruption. Check using DBCC CHECKDB or DBCC CHECKTABLE.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1133521175.165593.145330@.g49g2000cwa.googlegr oups.com...
> But I'm the only one user who access to that database while I doing
> that.
>
> Peter CCH
>
|||Tibor Karaszi wrote:
> I'd still check for blocking. It could be an open transaction hanging
> around or something, you never know. Other possible reasons:
> 73 vs 72 rows lead to different execution plans. Check using
> estimated execution plan.
> Table corruption. Check using DBCC CHECKDB or DBCC CHECKTABLE.
Could as well be a too low timeout value, couldn't it?
robert
SQL server 2000, the records sometimes can be return sometimes not.
In Enterprise Manager, I browse to a table, then I right click on it,
click "Open table > Return all rows" ... it just keep waiting until it
timeout. It didn't return any result. But when I "Return top" ... with
returning top 71, the records will shown. When I try to return top 72,
it happen again, with no result returned and timeout. I'd try a few
times with different value, it always happen when I try to return rows
until row 72.
I'd try it in Query Analyzer, it's the same.
It return rows when "SELECT TOP 71 * FROM theTableName"
but no response when "SELECT TOP 72 * FROM theTableName"
Anyone encounter this problem before? Is it the index of that table
corrupted? Or values inside that rows cannot be return?
Any idea how to solve this?
Thanks.
Peter CCH
Possibly someone has a lock on the row which will be read as row number 72 by the selected execution
plan. Check using sp_lock, sp_who etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1133516672.695691.143760@.g14g2000cwa.googlegr oups.com...
>I encounter a problem where returning all rows from a certain table in
> SQL server 2000, the records sometimes can be return sometimes not.
> In Enterprise Manager, I browse to a table, then I right click on it,
> click "Open table > Return all rows" ... it just keep waiting until it
> timeout. It didn't return any result. But when I "Return top" ... with
> returning top 71, the records will shown. When I try to return top 72,
> it happen again, with no result returned and timeout. I'd try a few
> times with different value, it always happen when I try to return rows
> until row 72.
> I'd try it in Query Analyzer, it's the same.
> It return rows when "SELECT TOP 71 * FROM theTableName"
> but no response when "SELECT TOP 72 * FROM theTableName"
> Anyone encounter this problem before? Is it the index of that table
> corrupted? Or values inside that rows cannot be return?
> Any idea how to solve this?
> Thanks.
>
> Peter CCH
>
|||But I'm the only one user who access to that database while I doing
that.
Peter CCH
|||I'd still check for blocking. It could be an open transaction hanging around or something, you never
know. Other possible reasons:
73 vs 72 rows lead to different execution plans. Check using estimated execution plan.
Table corruption. Check using DBCC CHECKDB or DBCC CHECKTABLE.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1133521175.165593.145330@.g49g2000cwa.googlegr oups.com...
> But I'm the only one user who access to that database while I doing
> that.
>
> Peter CCH
>
|||Tibor Karaszi wrote:
> I'd still check for blocking. It could be an open transaction hanging
> around or something, you never know. Other possible reasons:
> 73 vs 72 rows lead to different execution plans. Check using
> estimated execution plan.
> Table corruption. Check using DBCC CHECKDB or DBCC CHECKTABLE.
Could as well be a too low timeout value, couldn't it?
robert
Return all rows works partially
I encounter a problem where returning all rows from a certain table in
SQL server 2000, the records sometimes can be return sometimes not.
In Enterprise Manager, I browse to a table, then I right click on it,
click "Open table > Return all rows" ... it just keep waiting until it
timeout. It didn't return any result. But when I "Return top" ... with
returning top 71, the records will shown. When I try to return top 72,
it happen again, with no result returned and timeout. I'd try a few
times with different value, it always happen when I try to return rows
until row 72.
I'd try it in Query Analyzer, it's the same.
It return rows when "SELECT TOP 71 * FROM theTableName"
but no response when "SELECT TOP 72 * FROM theTableName"
Anyone encounter this problem before? Is it the index of that table
corrupted? Or values inside that rows cannot be return?
Any idea how to solve this?
Thanks.
Peter CCHPossibly someone has a lock on the row which will be read as row number 72 by the selected execution
plan. Check using sp_lock, sp_who etc.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1133516672.695691.143760@.g14g2000cwa.googlegroups.com...
>I encounter a problem where returning all rows from a certain table in
> SQL server 2000, the records sometimes can be return sometimes not.
> In Enterprise Manager, I browse to a table, then I right click on it,
> click "Open table > Return all rows" ... it just keep waiting until it
> timeout. It didn't return any result. But when I "Return top" ... with
> returning top 71, the records will shown. When I try to return top 72,
> it happen again, with no result returned and timeout. I'd try a few
> times with different value, it always happen when I try to return rows
> until row 72.
> I'd try it in Query Analyzer, it's the same.
> It return rows when "SELECT TOP 71 * FROM theTableName"
> but no response when "SELECT TOP 72 * FROM theTableName"
> Anyone encounter this problem before? Is it the index of that table
> corrupted? Or values inside that rows cannot be return?
> Any idea how to solve this?
> Thanks.
>
> Peter CCH
>|||But I'm the only one user who access to that database while I doing
that.
Peter CCH|||I'd still check for blocking. It could be an open transaction hanging around or something, you never
know. Other possible reasons:
73 vs 72 rows lead to different execution plans. Check using estimated execution plan.
Table corruption. Check using DBCC CHECKDB or DBCC CHECKTABLE.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1133521175.165593.145330@.g49g2000cwa.googlegroups.com...
> But I'm the only one user who access to that database while I doing
> that.
>
> Peter CCH
>|||Tibor Karaszi wrote:
> I'd still check for blocking. It could be an open transaction hanging
> around or something, you never know. Other possible reasons:
> 73 vs 72 rows lead to different execution plans. Check using
> estimated execution plan.
> Table corruption. Check using DBCC CHECKDB or DBCC CHECKTABLE.
Could as well be a too low timeout value, couldn't it?
robertsql
SQL server 2000, the records sometimes can be return sometimes not.
In Enterprise Manager, I browse to a table, then I right click on it,
click "Open table > Return all rows" ... it just keep waiting until it
timeout. It didn't return any result. But when I "Return top" ... with
returning top 71, the records will shown. When I try to return top 72,
it happen again, with no result returned and timeout. I'd try a few
times with different value, it always happen when I try to return rows
until row 72.
I'd try it in Query Analyzer, it's the same.
It return rows when "SELECT TOP 71 * FROM theTableName"
but no response when "SELECT TOP 72 * FROM theTableName"
Anyone encounter this problem before? Is it the index of that table
corrupted? Or values inside that rows cannot be return?
Any idea how to solve this?
Thanks.
Peter CCHPossibly someone has a lock on the row which will be read as row number 72 by the selected execution
plan. Check using sp_lock, sp_who etc.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1133516672.695691.143760@.g14g2000cwa.googlegroups.com...
>I encounter a problem where returning all rows from a certain table in
> SQL server 2000, the records sometimes can be return sometimes not.
> In Enterprise Manager, I browse to a table, then I right click on it,
> click "Open table > Return all rows" ... it just keep waiting until it
> timeout. It didn't return any result. But when I "Return top" ... with
> returning top 71, the records will shown. When I try to return top 72,
> it happen again, with no result returned and timeout. I'd try a few
> times with different value, it always happen when I try to return rows
> until row 72.
> I'd try it in Query Analyzer, it's the same.
> It return rows when "SELECT TOP 71 * FROM theTableName"
> but no response when "SELECT TOP 72 * FROM theTableName"
> Anyone encounter this problem before? Is it the index of that table
> corrupted? Or values inside that rows cannot be return?
> Any idea how to solve this?
> Thanks.
>
> Peter CCH
>|||But I'm the only one user who access to that database while I doing
that.
Peter CCH|||I'd still check for blocking. It could be an open transaction hanging around or something, you never
know. Other possible reasons:
73 vs 72 rows lead to different execution plans. Check using estimated execution plan.
Table corruption. Check using DBCC CHECKDB or DBCC CHECKTABLE.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1133521175.165593.145330@.g49g2000cwa.googlegroups.com...
> But I'm the only one user who access to that database while I doing
> that.
>
> Peter CCH
>|||Tibor Karaszi wrote:
> I'd still check for blocking. It could be an open transaction hanging
> around or something, you never know. Other possible reasons:
> 73 vs 72 rows lead to different execution plans. Check using
> estimated execution plan.
> Table corruption. Check using DBCC CHECKDB or DBCC CHECKTABLE.
Could as well be a too low timeout value, couldn't it?
robertsql
Wednesday, March 28, 2012
Return all rows works partially
I encounter a problem where returning all rows from a certain table in
SQL server 2000, the records sometimes can be return sometimes not.
In Enterprise Manager, I browse to a table, then I right click on it,
click "Open table > Return all rows" ... it just keep waiting until it
timeout. It didn't return any result. But when I "Return top" ... with
returning top 71, the records will shown. When I try to return top 72,
it happen again, with no result returned and timeout. I'd try a few
times with different value, it always happen when I try to return rows
until row 72.
I'd try it in Query Analyzer, it's the same.
It return rows when "SELECT TOP 71 * FROM theTableName"
but no response when "SELECT TOP 72 * FROM theTableName"
Anyone encounter this problem before? Is it the index of that table
corrupted? Or values inside that rows cannot be return?
Any idea how to solve this?
Thanks.
Peter CCHPossibly someone has a lock on the row which will be read as row number 72 b
y the selected execution
plan. Check using sp_lock, sp_who etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1133516672.695691.143760@.g14g2000cwa.googlegroups.com...
>I encounter a problem where returning all rows from a certain table in
> SQL server 2000, the records sometimes can be return sometimes not.
> In Enterprise Manager, I browse to a table, then I right click on it,
> click "Open table > Return all rows" ... it just keep waiting until it
> timeout. It didn't return any result. But when I "Return top" ... with
> returning top 71, the records will shown. When I try to return top 72,
> it happen again, with no result returned and timeout. I'd try a few
> times with different value, it always happen when I try to return rows
> until row 72.
> I'd try it in Query Analyzer, it's the same.
> It return rows when "SELECT TOP 71 * FROM theTableName"
> but no response when "SELECT TOP 72 * FROM theTableName"
> Anyone encounter this problem before? Is it the index of that table
> corrupted? Or values inside that rows cannot be return?
> Any idea how to solve this?
> Thanks.
>
> Peter CCH
>|||But I'm the only one user who access to that database while I doing
that.
Peter CCH|||I'd still check for blocking. It could be an open transaction hanging around
or something, you never
know. Other possible reasons:
73 vs 72 rows lead to different execution plans. Check using estimated execu
tion plan.
Table corruption. Check using DBCC CHECKDB or DBCC CHECKTABLE.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1133521175.165593.145330@.g49g2000cwa.googlegroups.com...
> But I'm the only one user who access to that database while I doing
> that.
>
> Peter CCH
>|||Tibor Karaszi wrote:
> I'd still check for blocking. It could be an open transaction hanging
> around or something, you never know. Other possible reasons:
> 73 vs 72 rows lead to different execution plans. Check using
> estimated execution plan.
> Table corruption. Check using DBCC CHECKDB or DBCC CHECKTABLE.
Could as well be a too low timeout value, couldn't it?
robert
SQL server 2000, the records sometimes can be return sometimes not.
In Enterprise Manager, I browse to a table, then I right click on it,
click "Open table > Return all rows" ... it just keep waiting until it
timeout. It didn't return any result. But when I "Return top" ... with
returning top 71, the records will shown. When I try to return top 72,
it happen again, with no result returned and timeout. I'd try a few
times with different value, it always happen when I try to return rows
until row 72.
I'd try it in Query Analyzer, it's the same.
It return rows when "SELECT TOP 71 * FROM theTableName"
but no response when "SELECT TOP 72 * FROM theTableName"
Anyone encounter this problem before? Is it the index of that table
corrupted? Or values inside that rows cannot be return?
Any idea how to solve this?
Thanks.
Peter CCHPossibly someone has a lock on the row which will be read as row number 72 b
y the selected execution
plan. Check using sp_lock, sp_who etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1133516672.695691.143760@.g14g2000cwa.googlegroups.com...
>I encounter a problem where returning all rows from a certain table in
> SQL server 2000, the records sometimes can be return sometimes not.
> In Enterprise Manager, I browse to a table, then I right click on it,
> click "Open table > Return all rows" ... it just keep waiting until it
> timeout. It didn't return any result. But when I "Return top" ... with
> returning top 71, the records will shown. When I try to return top 72,
> it happen again, with no result returned and timeout. I'd try a few
> times with different value, it always happen when I try to return rows
> until row 72.
> I'd try it in Query Analyzer, it's the same.
> It return rows when "SELECT TOP 71 * FROM theTableName"
> but no response when "SELECT TOP 72 * FROM theTableName"
> Anyone encounter this problem before? Is it the index of that table
> corrupted? Or values inside that rows cannot be return?
> Any idea how to solve this?
> Thanks.
>
> Peter CCH
>|||But I'm the only one user who access to that database while I doing
that.
Peter CCH|||I'd still check for blocking. It could be an open transaction hanging around
or something, you never
know. Other possible reasons:
73 vs 72 rows lead to different execution plans. Check using estimated execu
tion plan.
Table corruption. Check using DBCC CHECKDB or DBCC CHECKTABLE.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1133521175.165593.145330@.g49g2000cwa.googlegroups.com...
> But I'm the only one user who access to that database while I doing
> that.
>
> Peter CCH
>|||Tibor Karaszi wrote:
> I'd still check for blocking. It could be an open transaction hanging
> around or something, you never know. Other possible reasons:
> 73 vs 72 rows lead to different execution plans. Check using
> estimated execution plan.
> Table corruption. Check using DBCC CHECKDB or DBCC CHECKTABLE.
Could as well be a too low timeout value, couldn't it?
robert
Return all records in 15 min intervals.
Hello All,
First off thanks to all who try to help me.
I have a table with a date timestamp that includes the minute in 24 hour format..
I need to return all the records in the date range between '1/1/2007' and '1/31/2007' which is very simple.
The part I am having a hard time with is in the where clause.
I retrieve all the records for the date range into a #temptable
Now I need to sort these into 15 minute intervals based on the timestamp, but the sproc does'nt get passed a time only two dates also I need to provide a count of the records returned.
I have tried to create a temp table with 96 records being that there are 96 intervals of 15 minutes in 24 hours.
and using that as kinda a join.
I have tried to do something with datediff(_) with a dateadd(_)+15 in the where clause.
I also tried a loop 96 times.
I know there are a few ways to do it.I just dont know which ones.
I've only been using tsql for 2 months and have come pretty far, but this situation evades me.
Please enlighten me with your infinite wisdom of the tsql language. Help !
Is this the idea?
|||declare @.sampleData table
( rid integer primary key,
sampleDate datetime
)insert into @.sampleData
select iter,
cast ('12/25/6' as datetime) + 70 * dbo.rand()
from small_iterator (nolock) -- A table of integers 1-32767select top 5
rid,
sampleDate,
p.startOfInterval,
p.endOfInterval
from @.sampleData a
inner join
( select iter,
cast ((iter-1) * cast (cast ('0:15:00.000' as datetime) as float) as datetime)
as startOfInterval,
cast (iter * cast (cast ('0:15:00.000' as datetime) as float) as datetime)
as endOfInterval
from small_iterator (nolock) -- a table of integers 1-32767
where iter <= 96
) p
on sampleDate >= '1/1/7'
and sampleDate < '2/1/7'
and cast(cast(sampleDate as binary(4)) as datetime) >= p.startOfInterval
and cast(cast(sampleDate as binary(4)) as datetime) < p.endOfInterval
order by rid-- rid sampleDate startOfInterval endOfInterval
-- -- - -
-- 1 2007-01-02 14:58:52.183 1900-01-01 14:44:59.997 1900-01-01 15:00:00.000
-- 3 2007-01-30 08:00:32.787 1900-01-01 08:00:00.000 1900-01-01 08:15:00.000
-- 4 2007-01-16 19:59:58.257 1900-01-01 19:45:00.000 1900-01-01 19:59:59.997
-- 5 2007-01-04 10:34:13.883 1900-01-01 10:30:00.000 1900-01-01 10:44:59.997
-- 8 2007-01-22 08:14:12.333 1900-01-01 08:00:00.000 1900-01-01 08:15:00.000
select left(convert (varchar(8), p.startOfInterval, 108), 5) as startOfInterval,
left(convert (varchar(8), p.endOfInterval, 108), 5) as endOfInterval,
count(*) as intervalCount
from @.sampleData a
inner join
( select iter,
cast ((iter-1) * cast (cast ('0:15:00.000' as datetime) as float) as datetime)
as startOfInterval,
cast (iter * cast (cast ('0:15:00.000' as datetime) as float) as datetime)
as endOfInterval
from small_iterator (nolock) -- a table of integers 1-32767
where iter <= 96
) p
on sampleDate >= '1/1/7'
and sampleDate < '2/1/7'
and cast(cast(sampleDate as binary(4)) as datetime) >= p.startOfInterval
and cast(cast(sampleDate as binary(4)) as datetime) < p.endOfInterval
group by p.startOfInterval, p.endOfInterval
order by p.StartOfInterval-- startOfInterval endOfInterval intervalCount
-- - -
-- 00:00 00:15 151
-- 00:15 00:30 158
-- 00:30 00:45 134
-- ...
-- 23:30 23:44 145
-- 23:44 00:00 158
Getting Closer..
Thank you very much Kent.
I wish I could write code like that in Tsql and actually "Get it to work" for me.
I need the data to look like this which for the most part the above does.
Date 1/1/2007 <Whatever user chooses
Time Interval Number Calls
12:00 12:15 147
12:15 12:30 117
12:30 12:45 215
The existing data I have looks like this for the timestamp field.
12/22/2003 9:29:00 AM
1/5/2004 1:31:00 PM
7/2/2003 3:53:00 PM
7/7/2003 1:27:00 PM
I have thought about parsing out the strings then back converting them but with sql we shouldnt have to do that.
I'm going to attempt to plug the above code in and see what I can do with it. More suggestions are welcome !
You could change the SELECT list to that shown below.
Displaying the date above the output results (i.e. handling presentation) isn't what SQL Server is good at. It's far better to handle this in the client code or in Reporting Services.
Chris
SELECT CAST(DATEPART(hh, p.startOfInterval) AS VARCHAR(2)) + ':'
+ CAST(DATEPART(mi, p.startOfInterval) AS VARCHAR(2)) + ' '
+ CAST(DATEPART(hh, e.endOfInterval) AS VARCHAR(2)) + ':'
+ CAST(DATEPART(mi, e.endOfInterval) AS VARCHAR(2)) AS [Time Interval],
COUNT(*) AS [Number Calls]
|||Kent - I know you weren't talking to me, but it would be good if you could. I've noticed you use small_iterator in a number of your posts.
Is it just a table of sequential integers?
Cheers
Chris
Yes, small_iterator is a list of integers 1-32767; stand by and I will post this stuff. Here is another shot at your summary (the SMALL_ITERATOR and dbo.rand() will follow in a minute):
|||declare @.searchDate datetime
set @.searchDate = '3/1/7'declare @.sampleData table
( rid integer primary key,
sampleDate datetime
)insert into @.sampleData
select iter,
cast ('12/25/6' as datetime) + 70 * dbo.rand()
from small_iterator (nolock) -- A table of integers 1-32767
select left(convert (varchar(8), p.startOfInterval, 108), 5) + ' ' +
left(convert (varchar(8), p.endOfInterval, 108), 5) + ' ' as [ Time Interval],
count(*) as intervalCount
from @.sampleData a
inner join
( select iter,
cast ((iter-1) * cast (cast ('0:15:00.000' as datetime) as float) as datetime)
as startOfInterval,
cast (iter * cast (cast ('0:15:00.000' as datetime) as float) as datetime)
as endOfInterval
from small_iterator (nolock) -- a table of integers 1-32767
where iter <= 96
) p
on sampleDate >= @.searchDate
and sampleDate < (@.searchDate + 1)
and cast(cast(sampleDate as binary(4)) as datetime) >= p.startOfInterval
and cast(cast(sampleDate as binary(4)) as datetime) < p.endOfInterval
group by p.startOfInterval, p.endOfInterval
order by p.StartOfInterval-- Time Interval intervalCount
-- -- -
-- 00:00 00:15 4
-- 00:15 00:30 6
-- 00:30 00:45 6
-- ...
-- 23:30 23:44 2
-- 23:44 00:00 2
Here is my small_iterator table; it is simply a table of numbers. To get some ideas about tables of numbers, give this website a look:
http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html
create table dbo.SMALL_ITERATOR
( iter smallint not null
constraint PK_SMALL_ITERATOR primary key
)
go
/* -- */
/* This routine is used to populate the small_iterator table. */
/* This query ran in 1 second in development and should run at */
/* a similar speed in production. */
/* -- */
truncate table SMALL_ITERATOR
insert into small_iterator
select number from master.dbo.spt_values (nolock)
where name is null
and number <= 255
insert into small_iterator
select 256 * j.iter + i.iter
from small_iterator i
inner join small_iterator j
on j.iter > 0
and j.iter <= 127
order by 256 * j.iter + i.iter
delete from small_iterator where iter = 0
select count(*) [count],
min (iter) [min iterator],
max (iter) [max iterator]
from SMALL_ITERATOR
go
dbcc dbreindex (small_iterator, '', 100)
go
update statistics small_iterator
go
exec sp_recompile small_iterator
go
Here is my RAND scalar UDF; it comes in handy at times for generating mock data:
create view dbo.vRand
as
select rand () as vRand
go
create function dbo.rand ()
returns float
as
begin
return (select vRand from dbo.vRand)
end
go
create function dbo.randList
( @.pm_listSize integer
)
returns @.randList table
( rid integer,
iRand float
)
as
begin
declare @.upperBound integer
set @.upperBound = ceiling (convert(float, (@.pm_listSize+1))
/ convert (float, 32767))
insert into @.randList
select 32767*(j.iter-1) + i.iter - 1 as rid,
dbo.rand() as iRand
from small_iterator i (nolock)
inner join small_iterator j (nolock)
on j.iter <= @.upperBound
and 32767*(j.iter-1) + i.iter - 1 <= @.pm_listSize
and 32767*(j.iter-1) + i.iter - 1 > 0
return
end
go
|||http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=81913&SiteID=1
declare @.interval int
set @.interval=30
select dateadd(minute,floor(datediff(minute,0,OrderDate)/@.interval)*@.interval,0) [dt],
count(*) [cnt]
from Northwind..Orders
group by dateadd(minute,floor(datediff(minute,0,OrderDate)/@.interval)*@.interval,0)
order by 1
This is looking like it's going to work for my needs just have to tweak it a bit more..
Thanks to all who helped. One day I'll be able to help with tsql instead of just VB and ASP
Return a value after insert the query
Hi!
create table testReturn
(
id int identity(100,1),
name varchar(10)
)
How can I return the value of identity column after inserting the value.
Dim objConn As SqlConnection
Dim SQLCmd As SqlClient.SqlCommand
Dim ds As New DataSet
Dim strsql As String
Try
objConn = New SqlConnection
objConn.ConnectionString = _
"Network Library=DBMSSOCN;" & _
"Data Source=localhost;" & _
"Initial Catalog=mydb;" & _
"User ID=userid;" & _
"Password=pass"
objConn.Open()
strsql = "insert into testReturn values ('a')"
SQLCmd = New SqlClient.SqlCommand(strsql, objConn)
Dim rowsAffected As Integer = 0
rowsAffected = SQLCmd.ExecuteNonQuery
Dim rv As String
rv = SQLCmd.Parameters(0).Value.ToString()
Response.Write(rv)
Catch ex As Exception
Response.Write(ex.ToString)
End Try
SeeHow do I get the IDENTITY / AUTONUMBER value for the row I inserted?
strsql = "insert into testReturn values ('a'); select @.@.Identity"
SQLCmd = New SqlClient.SqlCommand(strsql, objConn)
dim Identiy as Object
Identity = SQLCmd.ExecuteScalar
You need to put "Select @.@.identity" statement immediately after your insert query so it would return the ID value of the record inserted, using the preceding Insert statement.
Cheers
Ritesh
Using @.@.IDENTITY is an vulnerable approach, since it returns the last inserted id of any table. If you are using SQL 2005 then you can use the OUTPUT clause.
SeeHow to get an Identity value with SQL Server 2005
You can either use @.@.identity or scope_identity().
@.@.identity gives you the last generated identity value.
scope_identity() gives you the last generated identity value for current scope.
Just have a quick look at BOL for further understanding.
Hope this will help.
return a TABLE
hello all
I need a function with a return value TABLE...
this is not the problem..
but i need the returned table dynamic..
example:
i call the function getTable(schema_name, TableName)
the 1st value is the schema. the 2nd is the table i need...
now i must have a return value like this
select * from schema_name.TableName
is this possible? can i build by return value dynamically?
i tried a lot but nothing worked..
thx
greg
Give a look to CREATE FUNCTION in books online and you will find that in functions two things that you are not allowed to do are:
EXEC ( ' (any SQL statement)' ) EXEC aStoredProceduresqlReturn a row with columns for each day in a date range
Given in a record in from a Table called WorkSchedule:
idWorkSchedul StartDate EndDate HoursWorked
1 1/1/2000 1/1/2006 8
I need to return for each record in the WorkSchedule Table
1/1/2000 1/2/2000 1/3/2000 1/4/2000..........1/1/2006
8 8 8 8..................8
Please help.
Thank you.
-Robert
Hi Robert,
Let's assume your source table was called "WS":
with Hours (MinDate, MaxDate, WorkDate, WorkHrs)
AS
(
SELECT StartDate as [MinDt], EndDate AS [MaxDt], StartDate AS [WorkDt], HoursWorked
FROM WS
WHERE idWorkSchedul = 1
UNIONALL
SELECT MinDate, MaxDate,DATEADD(day,1, WorkDate)AS [WorkDate], WorkHrs
FROM Hours h
WHERE WorkDate <= MaxDate
)
select*from Hours
This will I think give you a few ideas anyway (you can pivot the resultset if indeed you needed the resultset to mimic the example output you supplied). Also note that we need to return the MaxDt and MinDt so we can limit the recursive function via the WHERE WorkDate <= MaxDate clause as a recursive CTE will not allow a sub query in the where clause.
Cheers,
Rob
|||is the employee column needed
|||hi,
Sql server has a limitation of 1024 columns
your requirements exceeds that limitations
regards
joey
here's a tests script. its not finished becaused i encountered the limitation
|||use northwind
create table dates
(
dateid int identity(1,1),
date1 datetime
)
godeclare @.mydate datetime
select @.mydate ='1/1/2000'
while @.mydate<>'1/31/2010'
Begin
insert dates(date1) values ( @.mydate)
select @.mydate=dateadd(day,1,@.mydate)
end
goselect * from dates
gocreate table worksched(
idWorkSchedul int identity(1,1),
StartDate datetime,
EndDate datetime,
HoursWorked int
)
insert worksched(startdate,enddate,hoursworked)
values( '1/1/2000','1/1/2006',8)declare @.startdate datetime
declare @.enddate datetime
select @.startdate='1/1/2000'
select @.enddate='1/1/2006'
select IDENTITY(int, 1,1) AS ID_Num,
date1 INTO #MYTEMP from dates where date1
between @.startdate and @.enddate--drop table mytest
CREATE TABLE MYTEST1
(EMPLOYEE_ID VARCHAR(10)
)DECLARE @.CMD nVARCHAR(200)
DECLARE @.CTR INT
DECLARE @.NAME VARCHAR(10)
SELECT @.CTR=0
WHILE @.CTR<>(SELECT MAX (ID_NUM) FROM #MYTEMP)
BEGIN
SELECT @.CTR=@.CTR+1
SELECT @.NAME = CONVERT( VARCHAR(10), DATE1 ,110) FROM #MYTEMP WHERE ID_NUM=@.CTR
select @.cmd ='ALTER TABLE MYTEST1 ADD ['+ @.NAME +'] INT'
--select @.cmd
exec sp_executesql @.cmd
ENDselect * from mytest1
Here's an idea that may be of use, though I wouldn't really call it 'rows and columns', it's more of a play-with-strings for display purposes only. Each date will not be a separate column, it's just one long formatted string for the specific purpose.
Using the following example, to generate the days in the range is pretty straight forward with a number table.
create table #workSched
( id int not null, StartDate datetime not null, EndDate datetime not null, hrs int not null )
insert #workSched
select 1, '20060101', '20060331', 8 union all
select 2, '20060401', '20060831', 8
Assuming we have these two rows, then this query would produce a 'normal' resultset for each day between start and end
(the 'n - 1' is due to my numberstable starts with one, not zero)
Also, it's necessary to do this one workid at a time, it won't work for all in one go with just a straight query. However, it may be possible to package the idea into a UDF to get a simulation of a 'single-pass' (though performance may still be an issue)
select id,
dateadd(day, n -1, startDate) as workDay,
hrs
from #workSched
join nums
on n -1 <= datediff(day, startdate, enddate)
and id = 1
We could use this and build two strings, one with days and the other with the hours, keeping formatting in mind so that the two would be spaced accordingly.
declare @.workDay varchar(8000), @.hrs varchar(8000)
select @.workDay = '', @.hrs = ''
-- build the 'row' of dates
select @.workDay = @.workDay + convert(char(10), dateadd(day, n -1, startDate), 121) + ' '
from #workSched
join nums
on n -1 <= datediff(day, startdate, enddate)
and id = 1
-- buld the 'row' of hours, evenly spaced according to date
select @.hrs = @.hrs + convert(char(10), hrs) + ' '
from #workSched
join nums
on n -1 <= datediff(day, startdate, enddate)
and id = 1
-- display
select @.workDay
union all
select @.hrs
-- ....
2006-01-01 2006-01-02 2006-01-03 ....
8 8 8 ....
If you're looking for something for display or reporting use, then perhaps this idea could work for you..?
(it's not that pretty, but it works.. =;o)
/Kenneth
|||You can do the pivoting on the client side easily especially since you may have large number of date values. If you are building a report then it is a very trivial operation. So send the data as rows (dates as rows) and pivot on the client side. Solutions in TSQL will require dynamic SQL or fixed column names and other procedural techniques which will slow in terms of performance.Return a horizontal table for result
Has any one had luck writing a query that returns a result as follows:
CheckID PayAmtDeductAmt Tax Amt
1Admin200.00401k70.00FUTA13.00
1SAL1500.00FICA25.00
1MED30.00
1SOC107.00
I have three tables (Pay, Dedcut, and Tax) that are connected to a check table
Check Table
pcKey
pcEmployee
PayCheckDeduction
pcdKey
pcdPayCheck
pcdDeductionDescription
pcdAmount
etc...
the diffuculty I'm having is getting a horizontal return as describe above
for two or more tables connected to one table.
Please post the exact DDL (CREATE TABLE statements) for all of the tables
involved. It sounds like what you want is a pivot table, which can be
easily done.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"John" <John@.discussions.microsoft.com> wrote in message
news:05C2EB45-F667-4968-858A-D545DB9D8F41@.microsoft.com...
Has any one had luck writing a query that returns a result as follows:
CheckID Pay Amt Deduct Amt Tax Amt
1 Admin 200.00 401k 70.00 FUTA 13.00
1 SAL 1500.00 FICA 25.00
1 MED 30.00
1 SOC 107.00
I have three tables (Pay, Dedcut, and Tax) that are connected to a check
table
Check Table
pcKey
pcEmployee
PayCheckDeduction
pcdKey
pcdPayCheck
pcdDeductionDescription
pcdAmount
etc...
the diffuculty I'm having is getting a horizontal return as describe above
for two or more tables connected to one table.
|||Here you go Tom, I hope this helps
====================
DROP database zzPayroll
GO
CREATE database zzPayroll
GO
USE zzPayroll
GO
CREATE TABLE PayCheck (
pcKeyintNOT NULL ,
pcPayroll int NULL,
pcEmployee int NOT NULL
)
GO
CREATE TABLE PayType (
paytKeyintNOT NULL,
paytDescriptionvarchar(80)NOT NULL DEFAULT '',
-- 0 = Regular
-- 1 = Salary
-- 2 = Premium 1
-- 3 = Premium 2
-- 4 = Premium 3
-- 5 = Sick pay
-- 6 = Vacation pay
-- 7 = Other pay
-- 8 = Other in/out
-- 9 = Reimbursement
-- 10 = Accrual
paytType tinyint NOT NULL DEFAULT 0
)
GO
CREATE TABLE PayCheckTax (
pctKeyintNOT NULL ,
pctPayCheckintNOT NULL,
pctTaxTypeintNOT NULL,
pctAmount decimal(19,2) NOT NULL DEFAULT 0
)
GO
CREATE TABLE DeductionType (
dedtKeyintNOT NULL,
dedtDescriptionvarchar(80)NOT NULL DEFAULT ''
)
GO
CREATE TABLE TaxType (
taxtKeyintNOT NULL ,
taxtDescriptionvarchar(80)NOT NULL DEFAULT '',
-- 0 = Federal withholding
-- 1 = Federal unemployment
-- 2 = Earned income credit
-- 3 = Social Security
-- 4 = Medicare
-- 5 = State withholding,
-- 6 = State disability
-- 7 = State unemployment
-- 8 = Workers compensation
-- 9 = Local
-- 10 = Other
taxtTypetinyintNOT NULL DEFAULT 10
)
GO
CREATE TABLE PayCheckDeduction (
pcdKeyintNOT NULL,
pcdPayCheckintNOT NULL,
pcdDeductionTypeintNOT NULL,
pcdAmount decimal(19,2)NOT NULL DEFAULT 0
)
GO
CREATE TABLE Pays (
pKey int NOT NULL ,
pPayCheck int NULL,
pPayType int NULL,
pHours decimal(19,2) NOT NULL DEFAULT 0,
pAmount decimal(19,4) NULL
)
GO
INSERT PayType(paytKey,paytDescription,paytType) VALUES(0,'Regular',0)
INSERT PayType(paytKey,paytDescription,paytType) VALUES(1,'Salary',1)
INSERT PayType(paytKey,paytDescription,paytType) VALUES(2,'Overtime',2)
INSERT PayType(paytKey,paytDescription,paytType) VALUES(3,'Doubletime',3)
INSERT PayType(paytKey,paytDescription,paytType)
VALUES(4,'Vacation_Accrual',4)
INSERT DeductionType(dedtKey,dedtDescription) VALUES(0,'401k')
INSERT DeductionType(dedtKey,dedtDescription) VALUES(1,'Employee Medical')
INSERT TaxType(taxtKey,taxtDescription, taxtType) VALUES(0,'FUTA',1)
INSERT TaxType(taxtKey,taxtDescription, taxtType) VALUES(1,'FWH',0)
INSERT TaxType(taxtKey,taxtDescription, taxtType) VALUES(2,'Medicare',4)
INSERT TaxType(taxtKey,taxtDescription, taxtType) VALUES(3,'SOC',3)
INSERT TaxType(taxtKey,taxtDescription, taxtType) VALUES(4,'ORSDI',6)
INSERT TaxType(taxtKey,taxtDescription, taxtType) VALUES(5,'ORSWH',5)
INSERT Pays(pKey,pPayCheck, pPayType, pHours,pAmount)
VALUES(0,1,0,40.00,900.00)
INSERT Pays(pKey,pPayCheck, pPayType, pHours,pAmount)
VALUES(1,1,2,2.00,60.00)
INSERT Pays(pKey,pPayCheck, pPayType, pHours,pAmount)
VALUES(3,1,4,8.00,0.00)
INSERT PayCheckDeduction(pcdKey, pcdPayCheck, pcdDeductionType, pcdAmount)
VALUES(0,1,0,72.00)
INSERT PayCheckDeduction(pcdKey, pcdPayCheck, pcdDeductionType, pcdAmount)
VALUES(0,1,1,25.00)
INSERT PayCheckTax(pctKey, pctPayCheck, pctTaxType, pctAmount)
VALUES(0,1,0,25.00)
INSERT PayCheckTax(pctKey, pctPayCheck, pctTaxType, pctAmount)
VALUES(0,1,1,100.00)
INSERT PayCheckTax(pctKey, pctPayCheck, pctTaxType, pctAmount)
VALUES(0,1,2,35.00)
INSERT PayCheckTax(pctKey, pctPayCheck, pctTaxType, pctAmount)
VALUES(0,1,4,75.00)
INSERT PayCheckTax(pctKey, pctPayCheck, pctTaxType, pctAmount)
VALUES(0,1,5,105.00)
INSERT PayCheck(pcKey, pcPayroll,pcEmployee) VALUES(1,2,3)
================================================
"Tom Moreau" wrote:
> Please post the exact DDL (CREATE TABLE statements) for all of the tables
> involved. It sounds like what you want is a pivot table, which can be
> easily done.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "John" <John@.discussions.microsoft.com> wrote in message
> news:05C2EB45-F667-4968-858A-D545DB9D8F41@.microsoft.com...
> Has any one had luck writing a query that returns a result as follows:
> CheckID Pay Amt Deduct Amt Tax Amt
> 1 Admin 200.00 401k 70.00 FUTA 13.00
> 1 SAL 1500.00 FICA 25.00
> 1 MED 30.00
> 1 SOC 107.00
> I have three tables (Pay, Dedcut, and Tax) that are connected to a check
> table
>
> Check Table
> pcKey
> pcEmployee
> PayCheckDeduction
> pcdKey
> pcdPayCheck
> pcdDeductionDescription
> pcdAmount
> etc...
> the diffuculty I'm having is getting a horizontal return as describe above
> for two or more tables connected to one table.
>
>
|||Looking at the original post, you have an assortment of results, with an
inconsistent number or columns in each row. What exactly are the business
requirements? Do you want a row for each type of pay - Reg, Overtime, etc.?
Do you want all deductions spread horizontally?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"John" <John@.discussions.microsoft.com> wrote in message
news:60E58185-B97D-4891-9ED2-0785A79E51B9@.microsoft.com...
Here you go Tom, I hope this helps
====================
DROP database zzPayroll
GO
CREATE database zzPayroll
GO
USE zzPayroll
GO
CREATE TABLE PayCheck (
pcKey int NOT NULL ,
pcPayroll int NULL,
pcEmployee int NOT NULL
)
GO
CREATE TABLE PayType (
paytKey int NOT NULL,
paytDescription varchar(80) NOT NULL DEFAULT '',
-- 0 = Regular
-- 1 = Salary
-- 2 = Premium 1
-- 3 = Premium 2
-- 4 = Premium 3
-- 5 = Sick pay
-- 6 = Vacation pay
-- 7 = Other pay
-- 8 = Other in/out
-- 9 = Reimbursement
-- 10 = Accrual
paytType tinyint NOT NULL DEFAULT 0
)
GO
CREATE TABLE PayCheckTax (
pctKey int NOT NULL ,
pctPayCheck int NOT NULL,
pctTaxType int NOT NULL,
pctAmount decimal(19,2) NOT NULL DEFAULT 0
)
GO
CREATE TABLE DeductionType (
dedtKey int NOT NULL,
dedtDescription varchar(80) NOT NULL DEFAULT ''
)
GO
CREATE TABLE TaxType (
taxtKey int NOT NULL ,
taxtDescription varchar(80) NOT NULL DEFAULT '',
-- 0 = Federal withholding
-- 1 = Federal unemployment
-- 2 = Earned income credit
-- 3 = Social Security
-- 4 = Medicare
-- 5 = State withholding,
-- 6 = State disability
-- 7 = State unemployment
-- 8 = Workers compensation
-- 9 = Local
-- 10 = Other
taxtType tinyint NOT NULL DEFAULT 10
)
GO
CREATE TABLE PayCheckDeduction (
pcdKey int NOT NULL,
pcdPayCheck int NOT NULL,
pcdDeductionType int NOT NULL,
pcdAmount decimal(19,2) NOT NULL DEFAULT 0
)
GO
CREATE TABLE Pays (
pKey int NOT NULL ,
pPayCheck int NULL,
pPayType int NULL,
pHours decimal(19,2) NOT NULL DEFAULT 0,
pAmount decimal(19,4) NULL
)
GO
INSERT PayType (paytKey,paytDescription,paytType) VALUES(0,'Regular',0)
INSERT PayType (paytKey,paytDescription,paytType) VALUES(1,'Salary',1)
INSERT PayType (paytKey,paytDescription,paytType) VALUES(2,'Overtime',2)
INSERT PayType (paytKey,paytDescription,paytType) VALUES(3,'Doubletime',3)
INSERT PayType (paytKey,paytDescription,paytType)
VALUES(4,'Vacation_Accrual',4)
INSERT DeductionType (dedtKey,dedtDescription) VALUES(0,'401k')
INSERT DeductionType (dedtKey,dedtDescription) VALUES(1,'Employee Medical')
INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(0,'FUTA',1)
INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(1,'FWH',0)
INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(2,'Medicare',4)
INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(3,'SOC',3)
INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(4,'ORSDI',6)
INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(5,'ORSWH',5)
INSERT Pays (pKey,pPayCheck, pPayType, pHours,pAmount)
VALUES(0,1,0,40.00,900.00)
INSERT Pays (pKey,pPayCheck, pPayType, pHours,pAmount)
VALUES(1,1,2,2.00,60.00)
INSERT Pays (pKey,pPayCheck, pPayType, pHours,pAmount)
VALUES(3,1,4,8.00,0.00)
INSERT PayCheckDeduction (pcdKey, pcdPayCheck, pcdDeductionType, pcdAmount)
VALUES(0,1,0,72.00)
INSERT PayCheckDeduction (pcdKey, pcdPayCheck, pcdDeductionType, pcdAmount)
VALUES(0,1,1,25.00)
INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
VALUES(0,1,0,25.00)
INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
VALUES(0,1,1,100.00)
INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
VALUES(0,1,2,35.00)
INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
VALUES(0,1,4,75.00)
INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
VALUES(0,1,5,105.00)
INSERT PayCheck (pcKey, pcPayroll,pcEmployee) VALUES(1,2,3)
================================================
"Tom Moreau" wrote:
> Please post the exact DDL (CREATE TABLE statements) for all of the tables
> involved. It sounds like what you want is a pivot table, which can be
> easily done.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "John" <John@.discussions.microsoft.com> wrote in message
> news:05C2EB45-F667-4968-858A-D545DB9D8F41@.microsoft.com...
> Has any one had luck writing a query that returns a result as follows:
> CheckID Pay Amt Deduct Amt Tax Amt
> 1 Admin 200.00 401k 70.00 FUTA 13.00
> 1 SAL 1500.00 FICA 25.00
> 1 MED 30.00
> 1 SOC 107.00
> I have three tables (Pay, Dedcut, and Tax) that are connected to a check
> table
>
> Check Table
> pcKey
> pcEmployee
> PayCheckDeduction
> pcdKey
> pcdPayCheck
> pcdDeductionDescription
> pcdAmount
> etc...
> the diffuculty I'm having is getting a horizontal return as describe above
> for two or more tables connected to one table.
>
>
|||I am looking for a way show for a given check all the deductions, Pays, and
Taxes where the Deductions, Pays and Taxes appear in there own columns.
Chk Pay Ded Tax
1 x y z
1 null y z
1 null null z
"Tom Moreau" wrote:
> Looking at the original post, you have an assortment of results, with an
> inconsistent number or columns in each row. What exactly are the business
> requirements? Do you want a row for each type of pay - Reg, Overtime, etc.?
> Do you want all deductions spread horizontally?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "John" <John@.discussions.microsoft.com> wrote in message
> news:60E58185-B97D-4891-9ED2-0785A79E51B9@.microsoft.com...
> Here you go Tom, I hope this helps
> ====================
> DROP database zzPayroll
> GO
> CREATE database zzPayroll
> GO
> USE zzPayroll
> GO
> CREATE TABLE PayCheck (
> pcKey int NOT NULL ,
> pcPayroll int NULL,
> pcEmployee int NOT NULL
> )
> GO
> CREATE TABLE PayType (
> paytKey int NOT NULL,
> paytDescription varchar(80) NOT NULL DEFAULT '',
> -- 0 = Regular
> -- 1 = Salary
> -- 2 = Premium 1
> -- 3 = Premium 2
> -- 4 = Premium 3
> -- 5 = Sick pay
> -- 6 = Vacation pay
> -- 7 = Other pay
> -- 8 = Other in/out
> -- 9 = Reimbursement
> -- 10 = Accrual
> paytType tinyint NOT NULL DEFAULT 0
> )
> GO
> CREATE TABLE PayCheckTax (
> pctKey int NOT NULL ,
> pctPayCheck int NOT NULL,
> pctTaxType int NOT NULL,
> pctAmount decimal(19,2) NOT NULL DEFAULT 0
> )
> GO
> CREATE TABLE DeductionType (
> dedtKey int NOT NULL,
> dedtDescription varchar(80) NOT NULL DEFAULT ''
> )
> GO
> CREATE TABLE TaxType (
> taxtKey int NOT NULL ,
> taxtDescription varchar(80) NOT NULL DEFAULT '',
> -- 0 = Federal withholding
> -- 1 = Federal unemployment
> -- 2 = Earned income credit
> -- 3 = Social Security
> -- 4 = Medicare
> -- 5 = State withholding,
> -- 6 = State disability
> -- 7 = State unemployment
> -- 8 = Workers compensation
> -- 9 = Local
> -- 10 = Other
> taxtType tinyint NOT NULL DEFAULT 10
> )
> GO
> CREATE TABLE PayCheckDeduction (
> pcdKey int NOT NULL,
> pcdPayCheck int NOT NULL,
> pcdDeductionType int NOT NULL,
> pcdAmount decimal(19,2) NOT NULL DEFAULT 0
> )
> GO
> CREATE TABLE Pays (
> pKey int NOT NULL ,
> pPayCheck int NULL,
> pPayType int NULL,
> pHours decimal(19,2) NOT NULL DEFAULT 0,
> pAmount decimal(19,4) NULL
> )
> GO
> INSERT PayType (paytKey,paytDescription,paytType) VALUES(0,'Regular',0)
> INSERT PayType (paytKey,paytDescription,paytType) VALUES(1,'Salary',1)
> INSERT PayType (paytKey,paytDescription,paytType) VALUES(2,'Overtime',2)
> INSERT PayType (paytKey,paytDescription,paytType) VALUES(3,'Doubletime',3)
> INSERT PayType (paytKey,paytDescription,paytType)
> VALUES(4,'Vacation_Accrual',4)
> INSERT DeductionType (dedtKey,dedtDescription) VALUES(0,'401k')
> INSERT DeductionType (dedtKey,dedtDescription) VALUES(1,'Employee Medical')
> INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(0,'FUTA',1)
> INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(1,'FWH',0)
> INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(2,'Medicare',4)
> INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(3,'SOC',3)
> INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(4,'ORSDI',6)
> INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(5,'ORSWH',5)
> INSERT Pays (pKey,pPayCheck, pPayType, pHours,pAmount)
> VALUES(0,1,0,40.00,900.00)
> INSERT Pays (pKey,pPayCheck, pPayType, pHours,pAmount)
> VALUES(1,1,2,2.00,60.00)
> INSERT Pays (pKey,pPayCheck, pPayType, pHours,pAmount)
> VALUES(3,1,4,8.00,0.00)
> INSERT PayCheckDeduction (pcdKey, pcdPayCheck, pcdDeductionType, pcdAmount)
> VALUES(0,1,0,72.00)
> INSERT PayCheckDeduction (pcdKey, pcdPayCheck, pcdDeductionType, pcdAmount)
> VALUES(0,1,1,25.00)
> INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
> VALUES(0,1,0,25.00)
> INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
> VALUES(0,1,1,100.00)
> INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
> VALUES(0,1,2,35.00)
> INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
> VALUES(0,1,4,75.00)
> INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
> VALUES(0,1,5,105.00)
> INSERT PayCheck (pcKey, pcPayroll,pcEmployee) VALUES(1,2,3)
> ================================================
> "Tom Moreau" wrote:
>
>
|||So, IOW, there is no direct correlation between a given deduction and a
given pay. Rather the correlation is between the deduction and the check
only. Is that right?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"John" <John@.discussions.microsoft.com> wrote in message
news:8ACE7E95-F5E3-4A74-8813-BEE2C9D2DDEF@.microsoft.com...
I am looking for a way show for a given check all the deductions, Pays, and
Taxes where the Deductions, Pays and Taxes appear in there own columns.
Chk Pay Ded Tax
1 x y z
1 null y z
1 null null z
"Tom Moreau" wrote:
> Looking at the original post, you have an assortment of results, with an
> inconsistent number or columns in each row. What exactly are the business
> requirements? Do you want a row for each type of pay - Reg, Overtime,
> etc.?
> Do you want all deductions spread horizontally?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "John" <John@.discussions.microsoft.com> wrote in message
> news:60E58185-B97D-4891-9ED2-0785A79E51B9@.microsoft.com...
> Here you go Tom, I hope this helps
> ====================
> DROP database zzPayroll
> GO
> CREATE database zzPayroll
> GO
> USE zzPayroll
> GO
> CREATE TABLE PayCheck (
> pcKey int NOT NULL ,
> pcPayroll int NULL,
> pcEmployee int NOT NULL
> )
> GO
> CREATE TABLE PayType (
> paytKey int NOT NULL,
> paytDescription varchar(80) NOT NULL DEFAULT '',
> -- 0 = Regular
> -- 1 = Salary
> -- 2 = Premium 1
> -- 3 = Premium 2
> -- 4 = Premium 3
> -- 5 = Sick pay
> -- 6 = Vacation pay
> -- 7 = Other pay
> -- 8 = Other in/out
> -- 9 = Reimbursement
> -- 10 = Accrual
> paytType tinyint NOT NULL DEFAULT 0
> )
> GO
> CREATE TABLE PayCheckTax (
> pctKey int NOT NULL ,
> pctPayCheck int NOT NULL,
> pctTaxType int NOT NULL,
> pctAmount decimal(19,2) NOT NULL DEFAULT 0
> )
> GO
> CREATE TABLE DeductionType (
> dedtKey int NOT NULL,
> dedtDescription varchar(80) NOT NULL DEFAULT ''
> )
> GO
> CREATE TABLE TaxType (
> taxtKey int NOT NULL ,
> taxtDescription varchar(80) NOT NULL DEFAULT '',
> -- 0 = Federal withholding
> -- 1 = Federal unemployment
> -- 2 = Earned income credit
> -- 3 = Social Security
> -- 4 = Medicare
> -- 5 = State withholding,
> -- 6 = State disability
> -- 7 = State unemployment
> -- 8 = Workers compensation
> -- 9 = Local
> -- 10 = Other
> taxtType tinyint NOT NULL DEFAULT 10
> )
> GO
> CREATE TABLE PayCheckDeduction (
> pcdKey int NOT NULL,
> pcdPayCheck int NOT NULL,
> pcdDeductionType int NOT NULL,
> pcdAmount decimal(19,2) NOT NULL DEFAULT 0
> )
> GO
> CREATE TABLE Pays (
> pKey int NOT NULL ,
> pPayCheck int NULL,
> pPayType int NULL,
> pHours decimal(19,2) NOT NULL DEFAULT 0,
> pAmount decimal(19,4) NULL
> )
> GO
> INSERT PayType (paytKey,paytDescription,paytType) VALUES(0,'Regular',0)
> INSERT PayType (paytKey,paytDescription,paytType) VALUES(1,'Salary',1)
> INSERT PayType (paytKey,paytDescription,paytType) VALUES(2,'Overtime',2)
> INSERT PayType (paytKey,paytDescription,paytType) VALUES(3,'Doubletime',3)
> INSERT PayType (paytKey,paytDescription,paytType)
> VALUES(4,'Vacation_Accrual',4)
> INSERT DeductionType (dedtKey,dedtDescription) VALUES(0,'401k')
> INSERT DeductionType (dedtKey,dedtDescription) VALUES(1,'Employee
> Medical')
> INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(0,'FUTA',1)
> INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(1,'FWH',0)
> INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(2,'Medicare',4)
> INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(3,'SOC',3)
> INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(4,'ORSDI',6)
> INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(5,'ORSWH',5)
> INSERT Pays (pKey,pPayCheck, pPayType, pHours,pAmount)
> VALUES(0,1,0,40.00,900.00)
> INSERT Pays (pKey,pPayCheck, pPayType, pHours,pAmount)
> VALUES(1,1,2,2.00,60.00)
> INSERT Pays (pKey,pPayCheck, pPayType, pHours,pAmount)
> VALUES(3,1,4,8.00,0.00)
> INSERT PayCheckDeduction (pcdKey, pcdPayCheck, pcdDeductionType,
> pcdAmount)
> VALUES(0,1,0,72.00)
> INSERT PayCheckDeduction (pcdKey, pcdPayCheck, pcdDeductionType,
> pcdAmount)
> VALUES(0,1,1,25.00)
> INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
> VALUES(0,1,0,25.00)
> INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
> VALUES(0,1,1,100.00)
> INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
> VALUES(0,1,2,35.00)
> INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
> VALUES(0,1,4,75.00)
> INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
> VALUES(0,1,5,105.00)
> INSERT PayCheck (pcKey, pcPayroll,pcEmployee) VALUES(1,2,3)
> ================================================
> "Tom Moreau" wrote:
>
>
|||Yes that is correct.
"Tom Moreau" wrote:
> So, IOW, there is no direct correlation between a given deduction and a
> given pay. Rather the correlation is between the deduction and the check
> only. Is that right?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "John" <John@.discussions.microsoft.com> wrote in message
> news:8ACE7E95-F5E3-4A74-8813-BEE2C9D2DDEF@.microsoft.com...
> I am looking for a way show for a given check all the deductions, Pays, and
> Taxes where the Deductions, Pays and Taxes appear in there own columns.
> Chk Pay Ded Tax
> 1 x y z
> 1 null y z
> 1 null null z
>
> "Tom Moreau" wrote:
>
>
|||Well, that is an odd requirement. The following may suffice:
select
pc.pcKey
, x.Type
, p.pAmount Pay
, pcd.pcdAmount Deduction
, pct.pctAmount Tax
from
(
select 0 union all
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9 union all
select 10
) as x (Type)
cross
join
PayCheck pc
left
join
Pays p on p.pPayType = x.Type
and p.pPayCheck = pc.pcKey
left
join PayCheckDeduction pcd on pcd.pcdPayCheck = pc.pcKey
and pcd.pcdDeductionType = x.Type
left
join PayCheckTax pct on pct.pctPayCheck = pc.pcKey
and pct.pctTaxType = x.Type
where not
(
p.pAmount is null
and pcd.pcdAmount is null
and pct.pctAmount is null
)
Note that you will get gaps, i.e. there may be nulls interspersed within a
column.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"John" <John@.discussions.microsoft.com> wrote in message
news:BB0D6BE3-1081-4381-9473-345B93DFA54B@.microsoft.com...
Yes that is correct.
"Tom Moreau" wrote:
> So, IOW, there is no direct correlation between a given deduction and a
> given pay. Rather the correlation is between the deduction and the check
> only. Is that right?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "John" <John@.discussions.microsoft.com> wrote in message
> news:8ACE7E95-F5E3-4A74-8813-BEE2C9D2DDEF@.microsoft.com...
> I am looking for a way show for a given check all the deductions, Pays,
> and
> Taxes where the Deductions, Pays and Taxes appear in there own columns.
> Chk Pay Ded Tax
> 1 x y z
> 1 null y z
> 1 null null z
>
> "Tom Moreau" wrote:
>
>
|||Thank you very much!
While working through this I found something similar using the union all but
this is more straight forward.
Thanks for you time on this!
"Tom Moreau" wrote:
> Well, that is an odd requirement. The following may suffice:
> select
> pc.pcKey
> , x.Type
> , p.pAmount Pay
> , pcd.pcdAmount Deduction
> , pct.pctAmount Tax
> from
> (
> select 0 union all
> select 1 union all
> select 2 union all
> select 3 union all
> select 4 union all
> select 5 union all
> select 6 union all
> select 7 union all
> select 8 union all
> select 9 union all
> select 10
> ) as x (Type)
> cross
> join
> PayCheck pc
> left
> join
> Pays p on p.pPayType = x.Type
> and p.pPayCheck = pc.pcKey
> left
> join PayCheckDeduction pcd on pcd.pcdPayCheck = pc.pcKey
> and pcd.pcdDeductionType = x.Type
> left
> join PayCheckTax pct on pct.pctPayCheck = pc.pcKey
> and pct.pctTaxType = x.Type
> where not
> (
> p.pAmount is null
> and pcd.pcdAmount is null
> and pct.pctAmount is null
> )
> Note that you will get gaps, i.e. there may be nulls interspersed within a
> column.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "John" <John@.discussions.microsoft.com> wrote in message
> news:BB0D6BE3-1081-4381-9473-345B93DFA54B@.microsoft.com...
> Yes that is correct.
> "Tom Moreau" wrote:
>
>
|||My pleasure. Speaking of UNION ALL, have you considered FOR XML EXPLICIT?
select
1 as Tag
, NULL as Parent
, pcKey as [Check!1!CheckID]
, NULL as [Pay!2!Pay]
, NULL as [Deduction!3!Deduction]
, NULL as [Tax!4!Tax]
from
PayCheck
union all
select
2
, 1
, pPayCheck
, pAmount
, NULL
, NULL
from
Pays
union all
select
3
, 1
, pcdPayCheck
, NULL
, pcdAmount
, NULL
from
PayCheckDeduction
union all
select
4
, 1
, pctPayCheck
, NULL
, NULL
, pctAmount Tax
from
PayCheckTax
order by
[Check!1!CheckID]
for xml explicit
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"John" <John@.discussions.microsoft.com> wrote in message
news:B68BD197-6E5A-4A8A-8A98-A81CBF848F46@.microsoft.com...
Thank you very much!
While working through this I found something similar using the union all but
this is more straight forward.
Thanks for you time on this!
"Tom Moreau" wrote:
> Well, that is an odd requirement. The following may suffice:
> select
> pc.pcKey
> , x.Type
> , p.pAmount Pay
> , pcd.pcdAmount Deduction
> , pct.pctAmount Tax
> from
> (
> select 0 union all
> select 1 union all
> select 2 union all
> select 3 union all
> select 4 union all
> select 5 union all
> select 6 union all
> select 7 union all
> select 8 union all
> select 9 union all
> select 10
> ) as x (Type)
> cross
> join
> PayCheck pc
> left
> join
> Pays p on p.pPayType = x.Type
> and p.pPayCheck = pc.pcKey
> left
> join PayCheckDeduction pcd on pcd.pcdPayCheck = pc.pcKey
> and pcd.pcdDeductionType = x.Type
> left
> join PayCheckTax pct on pct.pctPayCheck = pc.pcKey
> and pct.pctTaxType = x.Type
> where not
> (
> p.pAmount is null
> and pcd.pcdAmount is null
> and pct.pctAmount is null
> )
> Note that you will get gaps, i.e. there may be nulls interspersed within a
> column.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "John" <John@.discussions.microsoft.com> wrote in message
> news:BB0D6BE3-1081-4381-9473-345B93DFA54B@.microsoft.com...
> Yes that is correct.
> "Tom Moreau" wrote:
>
>
sql
CheckID PayAmtDeductAmt Tax Amt
1Admin200.00401k70.00FUTA13.00
1SAL1500.00FICA25.00
1MED30.00
1SOC107.00
I have three tables (Pay, Dedcut, and Tax) that are connected to a check table
Check Table
pcKey
pcEmployee
PayCheckDeduction
pcdKey
pcdPayCheck
pcdDeductionDescription
pcdAmount
etc...
the diffuculty I'm having is getting a horizontal return as describe above
for two or more tables connected to one table.
Please post the exact DDL (CREATE TABLE statements) for all of the tables
involved. It sounds like what you want is a pivot table, which can be
easily done.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"John" <John@.discussions.microsoft.com> wrote in message
news:05C2EB45-F667-4968-858A-D545DB9D8F41@.microsoft.com...
Has any one had luck writing a query that returns a result as follows:
CheckID Pay Amt Deduct Amt Tax Amt
1 Admin 200.00 401k 70.00 FUTA 13.00
1 SAL 1500.00 FICA 25.00
1 MED 30.00
1 SOC 107.00
I have three tables (Pay, Dedcut, and Tax) that are connected to a check
table
Check Table
pcKey
pcEmployee
PayCheckDeduction
pcdKey
pcdPayCheck
pcdDeductionDescription
pcdAmount
etc...
the diffuculty I'm having is getting a horizontal return as describe above
for two or more tables connected to one table.
|||Here you go Tom, I hope this helps
====================
DROP database zzPayroll
GO
CREATE database zzPayroll
GO
USE zzPayroll
GO
CREATE TABLE PayCheck (
pcKeyintNOT NULL ,
pcPayroll int NULL,
pcEmployee int NOT NULL
)
GO
CREATE TABLE PayType (
paytKeyintNOT NULL,
paytDescriptionvarchar(80)NOT NULL DEFAULT '',
-- 0 = Regular
-- 1 = Salary
-- 2 = Premium 1
-- 3 = Premium 2
-- 4 = Premium 3
-- 5 = Sick pay
-- 6 = Vacation pay
-- 7 = Other pay
-- 8 = Other in/out
-- 9 = Reimbursement
-- 10 = Accrual
paytType tinyint NOT NULL DEFAULT 0
)
GO
CREATE TABLE PayCheckTax (
pctKeyintNOT NULL ,
pctPayCheckintNOT NULL,
pctTaxTypeintNOT NULL,
pctAmount decimal(19,2) NOT NULL DEFAULT 0
)
GO
CREATE TABLE DeductionType (
dedtKeyintNOT NULL,
dedtDescriptionvarchar(80)NOT NULL DEFAULT ''
)
GO
CREATE TABLE TaxType (
taxtKeyintNOT NULL ,
taxtDescriptionvarchar(80)NOT NULL DEFAULT '',
-- 0 = Federal withholding
-- 1 = Federal unemployment
-- 2 = Earned income credit
-- 3 = Social Security
-- 4 = Medicare
-- 5 = State withholding,
-- 6 = State disability
-- 7 = State unemployment
-- 8 = Workers compensation
-- 9 = Local
-- 10 = Other
taxtTypetinyintNOT NULL DEFAULT 10
)
GO
CREATE TABLE PayCheckDeduction (
pcdKeyintNOT NULL,
pcdPayCheckintNOT NULL,
pcdDeductionTypeintNOT NULL,
pcdAmount decimal(19,2)NOT NULL DEFAULT 0
)
GO
CREATE TABLE Pays (
pKey int NOT NULL ,
pPayCheck int NULL,
pPayType int NULL,
pHours decimal(19,2) NOT NULL DEFAULT 0,
pAmount decimal(19,4) NULL
)
GO
INSERT PayType(paytKey,paytDescription,paytType) VALUES(0,'Regular',0)
INSERT PayType(paytKey,paytDescription,paytType) VALUES(1,'Salary',1)
INSERT PayType(paytKey,paytDescription,paytType) VALUES(2,'Overtime',2)
INSERT PayType(paytKey,paytDescription,paytType) VALUES(3,'Doubletime',3)
INSERT PayType(paytKey,paytDescription,paytType)
VALUES(4,'Vacation_Accrual',4)
INSERT DeductionType(dedtKey,dedtDescription) VALUES(0,'401k')
INSERT DeductionType(dedtKey,dedtDescription) VALUES(1,'Employee Medical')
INSERT TaxType(taxtKey,taxtDescription, taxtType) VALUES(0,'FUTA',1)
INSERT TaxType(taxtKey,taxtDescription, taxtType) VALUES(1,'FWH',0)
INSERT TaxType(taxtKey,taxtDescription, taxtType) VALUES(2,'Medicare',4)
INSERT TaxType(taxtKey,taxtDescription, taxtType) VALUES(3,'SOC',3)
INSERT TaxType(taxtKey,taxtDescription, taxtType) VALUES(4,'ORSDI',6)
INSERT TaxType(taxtKey,taxtDescription, taxtType) VALUES(5,'ORSWH',5)
INSERT Pays(pKey,pPayCheck, pPayType, pHours,pAmount)
VALUES(0,1,0,40.00,900.00)
INSERT Pays(pKey,pPayCheck, pPayType, pHours,pAmount)
VALUES(1,1,2,2.00,60.00)
INSERT Pays(pKey,pPayCheck, pPayType, pHours,pAmount)
VALUES(3,1,4,8.00,0.00)
INSERT PayCheckDeduction(pcdKey, pcdPayCheck, pcdDeductionType, pcdAmount)
VALUES(0,1,0,72.00)
INSERT PayCheckDeduction(pcdKey, pcdPayCheck, pcdDeductionType, pcdAmount)
VALUES(0,1,1,25.00)
INSERT PayCheckTax(pctKey, pctPayCheck, pctTaxType, pctAmount)
VALUES(0,1,0,25.00)
INSERT PayCheckTax(pctKey, pctPayCheck, pctTaxType, pctAmount)
VALUES(0,1,1,100.00)
INSERT PayCheckTax(pctKey, pctPayCheck, pctTaxType, pctAmount)
VALUES(0,1,2,35.00)
INSERT PayCheckTax(pctKey, pctPayCheck, pctTaxType, pctAmount)
VALUES(0,1,4,75.00)
INSERT PayCheckTax(pctKey, pctPayCheck, pctTaxType, pctAmount)
VALUES(0,1,5,105.00)
INSERT PayCheck(pcKey, pcPayroll,pcEmployee) VALUES(1,2,3)
================================================
"Tom Moreau" wrote:
> Please post the exact DDL (CREATE TABLE statements) for all of the tables
> involved. It sounds like what you want is a pivot table, which can be
> easily done.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "John" <John@.discussions.microsoft.com> wrote in message
> news:05C2EB45-F667-4968-858A-D545DB9D8F41@.microsoft.com...
> Has any one had luck writing a query that returns a result as follows:
> CheckID Pay Amt Deduct Amt Tax Amt
> 1 Admin 200.00 401k 70.00 FUTA 13.00
> 1 SAL 1500.00 FICA 25.00
> 1 MED 30.00
> 1 SOC 107.00
> I have three tables (Pay, Dedcut, and Tax) that are connected to a check
> table
>
> Check Table
> pcKey
> pcEmployee
> PayCheckDeduction
> pcdKey
> pcdPayCheck
> pcdDeductionDescription
> pcdAmount
> etc...
> the diffuculty I'm having is getting a horizontal return as describe above
> for two or more tables connected to one table.
>
>
|||Looking at the original post, you have an assortment of results, with an
inconsistent number or columns in each row. What exactly are the business
requirements? Do you want a row for each type of pay - Reg, Overtime, etc.?
Do you want all deductions spread horizontally?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"John" <John@.discussions.microsoft.com> wrote in message
news:60E58185-B97D-4891-9ED2-0785A79E51B9@.microsoft.com...
Here you go Tom, I hope this helps
====================
DROP database zzPayroll
GO
CREATE database zzPayroll
GO
USE zzPayroll
GO
CREATE TABLE PayCheck (
pcKey int NOT NULL ,
pcPayroll int NULL,
pcEmployee int NOT NULL
)
GO
CREATE TABLE PayType (
paytKey int NOT NULL,
paytDescription varchar(80) NOT NULL DEFAULT '',
-- 0 = Regular
-- 1 = Salary
-- 2 = Premium 1
-- 3 = Premium 2
-- 4 = Premium 3
-- 5 = Sick pay
-- 6 = Vacation pay
-- 7 = Other pay
-- 8 = Other in/out
-- 9 = Reimbursement
-- 10 = Accrual
paytType tinyint NOT NULL DEFAULT 0
)
GO
CREATE TABLE PayCheckTax (
pctKey int NOT NULL ,
pctPayCheck int NOT NULL,
pctTaxType int NOT NULL,
pctAmount decimal(19,2) NOT NULL DEFAULT 0
)
GO
CREATE TABLE DeductionType (
dedtKey int NOT NULL,
dedtDescription varchar(80) NOT NULL DEFAULT ''
)
GO
CREATE TABLE TaxType (
taxtKey int NOT NULL ,
taxtDescription varchar(80) NOT NULL DEFAULT '',
-- 0 = Federal withholding
-- 1 = Federal unemployment
-- 2 = Earned income credit
-- 3 = Social Security
-- 4 = Medicare
-- 5 = State withholding,
-- 6 = State disability
-- 7 = State unemployment
-- 8 = Workers compensation
-- 9 = Local
-- 10 = Other
taxtType tinyint NOT NULL DEFAULT 10
)
GO
CREATE TABLE PayCheckDeduction (
pcdKey int NOT NULL,
pcdPayCheck int NOT NULL,
pcdDeductionType int NOT NULL,
pcdAmount decimal(19,2) NOT NULL DEFAULT 0
)
GO
CREATE TABLE Pays (
pKey int NOT NULL ,
pPayCheck int NULL,
pPayType int NULL,
pHours decimal(19,2) NOT NULL DEFAULT 0,
pAmount decimal(19,4) NULL
)
GO
INSERT PayType (paytKey,paytDescription,paytType) VALUES(0,'Regular',0)
INSERT PayType (paytKey,paytDescription,paytType) VALUES(1,'Salary',1)
INSERT PayType (paytKey,paytDescription,paytType) VALUES(2,'Overtime',2)
INSERT PayType (paytKey,paytDescription,paytType) VALUES(3,'Doubletime',3)
INSERT PayType (paytKey,paytDescription,paytType)
VALUES(4,'Vacation_Accrual',4)
INSERT DeductionType (dedtKey,dedtDescription) VALUES(0,'401k')
INSERT DeductionType (dedtKey,dedtDescription) VALUES(1,'Employee Medical')
INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(0,'FUTA',1)
INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(1,'FWH',0)
INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(2,'Medicare',4)
INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(3,'SOC',3)
INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(4,'ORSDI',6)
INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(5,'ORSWH',5)
INSERT Pays (pKey,pPayCheck, pPayType, pHours,pAmount)
VALUES(0,1,0,40.00,900.00)
INSERT Pays (pKey,pPayCheck, pPayType, pHours,pAmount)
VALUES(1,1,2,2.00,60.00)
INSERT Pays (pKey,pPayCheck, pPayType, pHours,pAmount)
VALUES(3,1,4,8.00,0.00)
INSERT PayCheckDeduction (pcdKey, pcdPayCheck, pcdDeductionType, pcdAmount)
VALUES(0,1,0,72.00)
INSERT PayCheckDeduction (pcdKey, pcdPayCheck, pcdDeductionType, pcdAmount)
VALUES(0,1,1,25.00)
INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
VALUES(0,1,0,25.00)
INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
VALUES(0,1,1,100.00)
INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
VALUES(0,1,2,35.00)
INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
VALUES(0,1,4,75.00)
INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
VALUES(0,1,5,105.00)
INSERT PayCheck (pcKey, pcPayroll,pcEmployee) VALUES(1,2,3)
================================================
"Tom Moreau" wrote:
> Please post the exact DDL (CREATE TABLE statements) for all of the tables
> involved. It sounds like what you want is a pivot table, which can be
> easily done.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "John" <John@.discussions.microsoft.com> wrote in message
> news:05C2EB45-F667-4968-858A-D545DB9D8F41@.microsoft.com...
> Has any one had luck writing a query that returns a result as follows:
> CheckID Pay Amt Deduct Amt Tax Amt
> 1 Admin 200.00 401k 70.00 FUTA 13.00
> 1 SAL 1500.00 FICA 25.00
> 1 MED 30.00
> 1 SOC 107.00
> I have three tables (Pay, Dedcut, and Tax) that are connected to a check
> table
>
> Check Table
> pcKey
> pcEmployee
> PayCheckDeduction
> pcdKey
> pcdPayCheck
> pcdDeductionDescription
> pcdAmount
> etc...
> the diffuculty I'm having is getting a horizontal return as describe above
> for two or more tables connected to one table.
>
>
|||I am looking for a way show for a given check all the deductions, Pays, and
Taxes where the Deductions, Pays and Taxes appear in there own columns.
Chk Pay Ded Tax
1 x y z
1 null y z
1 null null z
"Tom Moreau" wrote:
> Looking at the original post, you have an assortment of results, with an
> inconsistent number or columns in each row. What exactly are the business
> requirements? Do you want a row for each type of pay - Reg, Overtime, etc.?
> Do you want all deductions spread horizontally?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "John" <John@.discussions.microsoft.com> wrote in message
> news:60E58185-B97D-4891-9ED2-0785A79E51B9@.microsoft.com...
> Here you go Tom, I hope this helps
> ====================
> DROP database zzPayroll
> GO
> CREATE database zzPayroll
> GO
> USE zzPayroll
> GO
> CREATE TABLE PayCheck (
> pcKey int NOT NULL ,
> pcPayroll int NULL,
> pcEmployee int NOT NULL
> )
> GO
> CREATE TABLE PayType (
> paytKey int NOT NULL,
> paytDescription varchar(80) NOT NULL DEFAULT '',
> -- 0 = Regular
> -- 1 = Salary
> -- 2 = Premium 1
> -- 3 = Premium 2
> -- 4 = Premium 3
> -- 5 = Sick pay
> -- 6 = Vacation pay
> -- 7 = Other pay
> -- 8 = Other in/out
> -- 9 = Reimbursement
> -- 10 = Accrual
> paytType tinyint NOT NULL DEFAULT 0
> )
> GO
> CREATE TABLE PayCheckTax (
> pctKey int NOT NULL ,
> pctPayCheck int NOT NULL,
> pctTaxType int NOT NULL,
> pctAmount decimal(19,2) NOT NULL DEFAULT 0
> )
> GO
> CREATE TABLE DeductionType (
> dedtKey int NOT NULL,
> dedtDescription varchar(80) NOT NULL DEFAULT ''
> )
> GO
> CREATE TABLE TaxType (
> taxtKey int NOT NULL ,
> taxtDescription varchar(80) NOT NULL DEFAULT '',
> -- 0 = Federal withholding
> -- 1 = Federal unemployment
> -- 2 = Earned income credit
> -- 3 = Social Security
> -- 4 = Medicare
> -- 5 = State withholding,
> -- 6 = State disability
> -- 7 = State unemployment
> -- 8 = Workers compensation
> -- 9 = Local
> -- 10 = Other
> taxtType tinyint NOT NULL DEFAULT 10
> )
> GO
> CREATE TABLE PayCheckDeduction (
> pcdKey int NOT NULL,
> pcdPayCheck int NOT NULL,
> pcdDeductionType int NOT NULL,
> pcdAmount decimal(19,2) NOT NULL DEFAULT 0
> )
> GO
> CREATE TABLE Pays (
> pKey int NOT NULL ,
> pPayCheck int NULL,
> pPayType int NULL,
> pHours decimal(19,2) NOT NULL DEFAULT 0,
> pAmount decimal(19,4) NULL
> )
> GO
> INSERT PayType (paytKey,paytDescription,paytType) VALUES(0,'Regular',0)
> INSERT PayType (paytKey,paytDescription,paytType) VALUES(1,'Salary',1)
> INSERT PayType (paytKey,paytDescription,paytType) VALUES(2,'Overtime',2)
> INSERT PayType (paytKey,paytDescription,paytType) VALUES(3,'Doubletime',3)
> INSERT PayType (paytKey,paytDescription,paytType)
> VALUES(4,'Vacation_Accrual',4)
> INSERT DeductionType (dedtKey,dedtDescription) VALUES(0,'401k')
> INSERT DeductionType (dedtKey,dedtDescription) VALUES(1,'Employee Medical')
> INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(0,'FUTA',1)
> INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(1,'FWH',0)
> INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(2,'Medicare',4)
> INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(3,'SOC',3)
> INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(4,'ORSDI',6)
> INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(5,'ORSWH',5)
> INSERT Pays (pKey,pPayCheck, pPayType, pHours,pAmount)
> VALUES(0,1,0,40.00,900.00)
> INSERT Pays (pKey,pPayCheck, pPayType, pHours,pAmount)
> VALUES(1,1,2,2.00,60.00)
> INSERT Pays (pKey,pPayCheck, pPayType, pHours,pAmount)
> VALUES(3,1,4,8.00,0.00)
> INSERT PayCheckDeduction (pcdKey, pcdPayCheck, pcdDeductionType, pcdAmount)
> VALUES(0,1,0,72.00)
> INSERT PayCheckDeduction (pcdKey, pcdPayCheck, pcdDeductionType, pcdAmount)
> VALUES(0,1,1,25.00)
> INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
> VALUES(0,1,0,25.00)
> INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
> VALUES(0,1,1,100.00)
> INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
> VALUES(0,1,2,35.00)
> INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
> VALUES(0,1,4,75.00)
> INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
> VALUES(0,1,5,105.00)
> INSERT PayCheck (pcKey, pcPayroll,pcEmployee) VALUES(1,2,3)
> ================================================
> "Tom Moreau" wrote:
>
>
|||So, IOW, there is no direct correlation between a given deduction and a
given pay. Rather the correlation is between the deduction and the check
only. Is that right?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"John" <John@.discussions.microsoft.com> wrote in message
news:8ACE7E95-F5E3-4A74-8813-BEE2C9D2DDEF@.microsoft.com...
I am looking for a way show for a given check all the deductions, Pays, and
Taxes where the Deductions, Pays and Taxes appear in there own columns.
Chk Pay Ded Tax
1 x y z
1 null y z
1 null null z
"Tom Moreau" wrote:
> Looking at the original post, you have an assortment of results, with an
> inconsistent number or columns in each row. What exactly are the business
> requirements? Do you want a row for each type of pay - Reg, Overtime,
> etc.?
> Do you want all deductions spread horizontally?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "John" <John@.discussions.microsoft.com> wrote in message
> news:60E58185-B97D-4891-9ED2-0785A79E51B9@.microsoft.com...
> Here you go Tom, I hope this helps
> ====================
> DROP database zzPayroll
> GO
> CREATE database zzPayroll
> GO
> USE zzPayroll
> GO
> CREATE TABLE PayCheck (
> pcKey int NOT NULL ,
> pcPayroll int NULL,
> pcEmployee int NOT NULL
> )
> GO
> CREATE TABLE PayType (
> paytKey int NOT NULL,
> paytDescription varchar(80) NOT NULL DEFAULT '',
> -- 0 = Regular
> -- 1 = Salary
> -- 2 = Premium 1
> -- 3 = Premium 2
> -- 4 = Premium 3
> -- 5 = Sick pay
> -- 6 = Vacation pay
> -- 7 = Other pay
> -- 8 = Other in/out
> -- 9 = Reimbursement
> -- 10 = Accrual
> paytType tinyint NOT NULL DEFAULT 0
> )
> GO
> CREATE TABLE PayCheckTax (
> pctKey int NOT NULL ,
> pctPayCheck int NOT NULL,
> pctTaxType int NOT NULL,
> pctAmount decimal(19,2) NOT NULL DEFAULT 0
> )
> GO
> CREATE TABLE DeductionType (
> dedtKey int NOT NULL,
> dedtDescription varchar(80) NOT NULL DEFAULT ''
> )
> GO
> CREATE TABLE TaxType (
> taxtKey int NOT NULL ,
> taxtDescription varchar(80) NOT NULL DEFAULT '',
> -- 0 = Federal withholding
> -- 1 = Federal unemployment
> -- 2 = Earned income credit
> -- 3 = Social Security
> -- 4 = Medicare
> -- 5 = State withholding,
> -- 6 = State disability
> -- 7 = State unemployment
> -- 8 = Workers compensation
> -- 9 = Local
> -- 10 = Other
> taxtType tinyint NOT NULL DEFAULT 10
> )
> GO
> CREATE TABLE PayCheckDeduction (
> pcdKey int NOT NULL,
> pcdPayCheck int NOT NULL,
> pcdDeductionType int NOT NULL,
> pcdAmount decimal(19,2) NOT NULL DEFAULT 0
> )
> GO
> CREATE TABLE Pays (
> pKey int NOT NULL ,
> pPayCheck int NULL,
> pPayType int NULL,
> pHours decimal(19,2) NOT NULL DEFAULT 0,
> pAmount decimal(19,4) NULL
> )
> GO
> INSERT PayType (paytKey,paytDescription,paytType) VALUES(0,'Regular',0)
> INSERT PayType (paytKey,paytDescription,paytType) VALUES(1,'Salary',1)
> INSERT PayType (paytKey,paytDescription,paytType) VALUES(2,'Overtime',2)
> INSERT PayType (paytKey,paytDescription,paytType) VALUES(3,'Doubletime',3)
> INSERT PayType (paytKey,paytDescription,paytType)
> VALUES(4,'Vacation_Accrual',4)
> INSERT DeductionType (dedtKey,dedtDescription) VALUES(0,'401k')
> INSERT DeductionType (dedtKey,dedtDescription) VALUES(1,'Employee
> Medical')
> INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(0,'FUTA',1)
> INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(1,'FWH',0)
> INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(2,'Medicare',4)
> INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(3,'SOC',3)
> INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(4,'ORSDI',6)
> INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(5,'ORSWH',5)
> INSERT Pays (pKey,pPayCheck, pPayType, pHours,pAmount)
> VALUES(0,1,0,40.00,900.00)
> INSERT Pays (pKey,pPayCheck, pPayType, pHours,pAmount)
> VALUES(1,1,2,2.00,60.00)
> INSERT Pays (pKey,pPayCheck, pPayType, pHours,pAmount)
> VALUES(3,1,4,8.00,0.00)
> INSERT PayCheckDeduction (pcdKey, pcdPayCheck, pcdDeductionType,
> pcdAmount)
> VALUES(0,1,0,72.00)
> INSERT PayCheckDeduction (pcdKey, pcdPayCheck, pcdDeductionType,
> pcdAmount)
> VALUES(0,1,1,25.00)
> INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
> VALUES(0,1,0,25.00)
> INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
> VALUES(0,1,1,100.00)
> INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
> VALUES(0,1,2,35.00)
> INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
> VALUES(0,1,4,75.00)
> INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
> VALUES(0,1,5,105.00)
> INSERT PayCheck (pcKey, pcPayroll,pcEmployee) VALUES(1,2,3)
> ================================================
> "Tom Moreau" wrote:
>
>
|||Yes that is correct.
"Tom Moreau" wrote:
> So, IOW, there is no direct correlation between a given deduction and a
> given pay. Rather the correlation is between the deduction and the check
> only. Is that right?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "John" <John@.discussions.microsoft.com> wrote in message
> news:8ACE7E95-F5E3-4A74-8813-BEE2C9D2DDEF@.microsoft.com...
> I am looking for a way show for a given check all the deductions, Pays, and
> Taxes where the Deductions, Pays and Taxes appear in there own columns.
> Chk Pay Ded Tax
> 1 x y z
> 1 null y z
> 1 null null z
>
> "Tom Moreau" wrote:
>
>
|||Well, that is an odd requirement. The following may suffice:
select
pc.pcKey
, x.Type
, p.pAmount Pay
, pcd.pcdAmount Deduction
, pct.pctAmount Tax
from
(
select 0 union all
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9 union all
select 10
) as x (Type)
cross
join
PayCheck pc
left
join
Pays p on p.pPayType = x.Type
and p.pPayCheck = pc.pcKey
left
join PayCheckDeduction pcd on pcd.pcdPayCheck = pc.pcKey
and pcd.pcdDeductionType = x.Type
left
join PayCheckTax pct on pct.pctPayCheck = pc.pcKey
and pct.pctTaxType = x.Type
where not
(
p.pAmount is null
and pcd.pcdAmount is null
and pct.pctAmount is null
)
Note that you will get gaps, i.e. there may be nulls interspersed within a
column.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"John" <John@.discussions.microsoft.com> wrote in message
news:BB0D6BE3-1081-4381-9473-345B93DFA54B@.microsoft.com...
Yes that is correct.
"Tom Moreau" wrote:
> So, IOW, there is no direct correlation between a given deduction and a
> given pay. Rather the correlation is between the deduction and the check
> only. Is that right?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "John" <John@.discussions.microsoft.com> wrote in message
> news:8ACE7E95-F5E3-4A74-8813-BEE2C9D2DDEF@.microsoft.com...
> I am looking for a way show for a given check all the deductions, Pays,
> and
> Taxes where the Deductions, Pays and Taxes appear in there own columns.
> Chk Pay Ded Tax
> 1 x y z
> 1 null y z
> 1 null null z
>
> "Tom Moreau" wrote:
>
>
|||Thank you very much!
While working through this I found something similar using the union all but
this is more straight forward.
Thanks for you time on this!
"Tom Moreau" wrote:
> Well, that is an odd requirement. The following may suffice:
> select
> pc.pcKey
> , x.Type
> , p.pAmount Pay
> , pcd.pcdAmount Deduction
> , pct.pctAmount Tax
> from
> (
> select 0 union all
> select 1 union all
> select 2 union all
> select 3 union all
> select 4 union all
> select 5 union all
> select 6 union all
> select 7 union all
> select 8 union all
> select 9 union all
> select 10
> ) as x (Type)
> cross
> join
> PayCheck pc
> left
> join
> Pays p on p.pPayType = x.Type
> and p.pPayCheck = pc.pcKey
> left
> join PayCheckDeduction pcd on pcd.pcdPayCheck = pc.pcKey
> and pcd.pcdDeductionType = x.Type
> left
> join PayCheckTax pct on pct.pctPayCheck = pc.pcKey
> and pct.pctTaxType = x.Type
> where not
> (
> p.pAmount is null
> and pcd.pcdAmount is null
> and pct.pctAmount is null
> )
> Note that you will get gaps, i.e. there may be nulls interspersed within a
> column.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "John" <John@.discussions.microsoft.com> wrote in message
> news:BB0D6BE3-1081-4381-9473-345B93DFA54B@.microsoft.com...
> Yes that is correct.
> "Tom Moreau" wrote:
>
>
|||My pleasure. Speaking of UNION ALL, have you considered FOR XML EXPLICIT?
select
1 as Tag
, NULL as Parent
, pcKey as [Check!1!CheckID]
, NULL as [Pay!2!Pay]
, NULL as [Deduction!3!Deduction]
, NULL as [Tax!4!Tax]
from
PayCheck
union all
select
2
, 1
, pPayCheck
, pAmount
, NULL
, NULL
from
Pays
union all
select
3
, 1
, pcdPayCheck
, NULL
, pcdAmount
, NULL
from
PayCheckDeduction
union all
select
4
, 1
, pctPayCheck
, NULL
, NULL
, pctAmount Tax
from
PayCheckTax
order by
[Check!1!CheckID]
for xml explicit
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"John" <John@.discussions.microsoft.com> wrote in message
news:B68BD197-6E5A-4A8A-8A98-A81CBF848F46@.microsoft.com...
Thank you very much!
While working through this I found something similar using the union all but
this is more straight forward.
Thanks for you time on this!
"Tom Moreau" wrote:
> Well, that is an odd requirement. The following may suffice:
> select
> pc.pcKey
> , x.Type
> , p.pAmount Pay
> , pcd.pcdAmount Deduction
> , pct.pctAmount Tax
> from
> (
> select 0 union all
> select 1 union all
> select 2 union all
> select 3 union all
> select 4 union all
> select 5 union all
> select 6 union all
> select 7 union all
> select 8 union all
> select 9 union all
> select 10
> ) as x (Type)
> cross
> join
> PayCheck pc
> left
> join
> Pays p on p.pPayType = x.Type
> and p.pPayCheck = pc.pcKey
> left
> join PayCheckDeduction pcd on pcd.pcdPayCheck = pc.pcKey
> and pcd.pcdDeductionType = x.Type
> left
> join PayCheckTax pct on pct.pctPayCheck = pc.pcKey
> and pct.pctTaxType = x.Type
> where not
> (
> p.pAmount is null
> and pcd.pcdAmount is null
> and pct.pctAmount is null
> )
> Note that you will get gaps, i.e. there may be nulls interspersed within a
> column.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "John" <John@.discussions.microsoft.com> wrote in message
> news:BB0D6BE3-1081-4381-9473-345B93DFA54B@.microsoft.com...
> Yes that is correct.
> "Tom Moreau" wrote:
>
>
sql
Monday, March 26, 2012
Retrun all rows on Any Table from EM prompts for Username/Password .. Why ?
Hello -
I dont know something weired happened on our MSSQL server today. We are able to connect to any Databases from Enterprise Manager from a Remote server.
Once we logged in and connected to a database on my database server, it keep prompting for the user id and password as we browsing through different tables in that database?
(Right click on the table then select Open Table -> Return all rows -> and it prompts for password )
Now when we enter password the contents of Table are displayed and now when we try to see contents of another table in the database it again prompts for password.
Any Idea whats wrong? How this can be resolved as it was not happening before .. :(Weird, I'd try changing the registration of the server in EP and see if that fixes it.
I dont know something weired happened on our MSSQL server today. We are able to connect to any Databases from Enterprise Manager from a Remote server.
Once we logged in and connected to a database on my database server, it keep prompting for the user id and password as we browsing through different tables in that database?
(Right click on the table then select Open Table -> Return all rows -> and it prompts for password )
Now when we enter password the contents of Table are displayed and now when we try to see contents of another table in the database it again prompts for password.
Any Idea whats wrong? How this can be resolved as it was not happening before .. :(Weird, I'd try changing the registration of the server in EP and see if that fixes it.
Retrning XML raw from Stored procedure - (by using dual table)
Hi ,
I have a problem returning the @.CustomerNo from dual for one of my stored procedures. (by the way I do a some manipulations and assign a value to @.CustomerNo everytime this stored procedure runs)
SELECT @.CustomerNo AS CustomerNumber from dual FOR XML RAW
What I figured out is that the reason no values is returned in my XML file is because the dual table does not have any rows. But ,the dual table always have a dummy row by default. Doesnt it.
Alsoif possible please let me know any alternative way to returnthe following XML :
<row>
<CustomerNumber >
value of @.CustomerNo
<CustomerNumber >
Thanks in advance.
The dual table is an Oracle (shudders) feature.
If you are using SQL Server 2005, you can do the following:
Code Snippet
SELECT @.CustomerNo AS CustomerNumber
FOR XML PATH('row')
--
Peter DeBetta
MVP - SQL Server
http://sqlblog.com
|||Super.. Thanks for the soln.sql
Retrning XML raw from Stored procedure - (by using dual table)
Hi ,
I have a problem returning the @.CustomerNo from dual for one of my stored procedures. (by the way I do a some manipulations and assign a value to @.CustomerNo everytime this stored procedure runs)
SELECT @.CustomerNo AS CustomerNumber from dual FOR XML RAW
What I figured out is that the reason no values is returned in my XML file is because the dual table does not have any rows. But ,the dual table always have a dummy row by default. Doesnt it.
Alsoif possible please let me know any alternative way to returnthe following XML :
<row>
<CustomerNumber >
value of @.CustomerNo
<CustomerNumber >
Thanks in advance.
The dual table is an Oracle (shudders) feature.
If you are using SQL Server 2005, you can do the following:
Code Snippet
SELECT @.CustomerNo AS CustomerNumber
FOR XML PATH('row')
--
Peter DeBetta
MVP - SQL Server
http://sqlblog.com
|||Super.. Thanks for the soln.
Retriving data with same ID into single row
Hi,
I have a problem with retriving data from one table.
Table looks like this:
ID CONTACT EMPLOYEE_ID
And now, one employee can how more than one contact, like this
ID CONTACT EMPLOYEE_ID
15 e-mail 553
16 phone 553
..and so on.
How can I retrive this contacts, for same employee into single row, and divi
ded with 'coma'?
Now a can retrive them but I recieve multiple rows?
Thanks for helpHi
Do such reports on the client side.
It you want to do that by T-SQL be aware that it is not relyable solution
create table w
(
id int,
t varchar(50)
)
insert into w values (1,'abc')
insert into w values (1,'def')
insert into w values (1,'ghi')
insert into w values (2,'ABC')
insert into w values (2,'DEF')
select * from w
create function dbo.fn_my ( @.id int)
returns varchar(100)
as
begin
declare @.w varchar(100)
set @.w=''
select @.w=@.w+coalesce(t,'')+',' from w where id=@.id
return @.w
end
select id,
dbo.fn_my (dd.id)
from
(
select distinct id from w
)
as dd
drop function dbo.fn_my
"s3v3n" <s3v3n.1yuclt@.mail.codecomments.com> wrote in message
news:s3v3n.1yuclt@.mail.codecomments.com...
> Hi,
> I have a problem with retriving data from one table.
> Table looks like this:
> ID CONTACT EMPLOYEE_ID
> And now, one employee can how more than one contact, like this
> ID CONTACT EMPLOYEE_ID
> 15 e-mail 553
> 16 phone 553
> ..and so on.
> How can I retrive this contacts, for same employee into single row, and
> divided with 'coma'?
> Now a can retrive them but I recieve multiple rows?
> Thanks for help
>
> --
> s3v3n
> ---
> Posted via http://www.codecomments.com
> ---
>|||Check this good article with source code "Returning a Comma-Delimited List o
f
Related Records"
Best Regards
Vadivel
http://vadivel.blogspot.com
http://thinkingms.com/vadivel
"s3v3n" wrote:
> Hi,
> I have a problem with retriving data from one table.
> Table looks like this:
> ID CONTACT EMPLOYEE_ID
> And now, one employee can how more than one contact, like this
> ID CONTACT EMPLOYEE_ID
> 15 e-mail 553
> 16 phone 553
> ...and so on.
> How can I retrive this contacts, for same employee into single row, and
> divided with 'coma'?
> Now a can retrive them but I recieve multiple rows?
> Thanks for help
>
> --
> s3v3n
> ---
> Posted via http://www.codecomments.com
> ---
>|||[Based on the 4guysrolla article .. ]
Create table empTest
(
[ID] int identity,
Contact varchar(100),
Employee_Id int
)
Go
Insert into empTest values ( 'abc@.email.com', 554)
Insert into empTest values ( '090909090', 554)
Go
Create function dbo.udf_GetEmpDetails(@.EmpID int)
Returns Varchar(1000) as
BEGIN
DECLARE @.Contact varchar(1000)
SELECT @.Contact = COALESCE(@.Contact + ', ', '') + s.Contact
FROM empTest s
WHERE s.EMPLOYEE_ID = @.EmpID
RETURN @.Contact
END
Go
Select distinct Employee_ID,
dbo.udf_GetEmpDetails(EMPLOYEE_ID) as ListOfContacts
From empTest f
Go
Hope this helps!
Best Regards
Vadivel
http://vadivel.blogspot.com
http://thinkingms.com/vadivel
"s3v3n" wrote:
> Hi,
> I have a problem with retriving data from one table.
> Table looks like this:
> ID CONTACT EMPLOYEE_ID
> And now, one employee can how more than one contact, like this
> ID CONTACT EMPLOYEE_ID
> 15 e-mail 553
> 16 phone 553
> ...and so on.
> How can I retrive this contacts, for same employee into single row, and
> divided with 'coma'?
> Now a can retrive them but I recieve multiple rows?
> Thanks for help
>
> --
> s3v3n
> ---
> Posted via http://www.codecomments.com
> ---
>
I have a problem with retriving data from one table.
Table looks like this:
ID CONTACT EMPLOYEE_ID
And now, one employee can how more than one contact, like this
ID CONTACT EMPLOYEE_ID
15 e-mail 553
16 phone 553
..and so on.
How can I retrive this contacts, for same employee into single row, and divi
ded with 'coma'?
Now a can retrive them but I recieve multiple rows?
Thanks for helpHi
Do such reports on the client side.
It you want to do that by T-SQL be aware that it is not relyable solution
create table w
(
id int,
t varchar(50)
)
insert into w values (1,'abc')
insert into w values (1,'def')
insert into w values (1,'ghi')
insert into w values (2,'ABC')
insert into w values (2,'DEF')
select * from w
create function dbo.fn_my ( @.id int)
returns varchar(100)
as
begin
declare @.w varchar(100)
set @.w=''
select @.w=@.w+coalesce(t,'')+',' from w where id=@.id
return @.w
end
select id,
dbo.fn_my (dd.id)
from
(
select distinct id from w
)
as dd
drop function dbo.fn_my
"s3v3n" <s3v3n.1yuclt@.mail.codecomments.com> wrote in message
news:s3v3n.1yuclt@.mail.codecomments.com...
> Hi,
> I have a problem with retriving data from one table.
> Table looks like this:
> ID CONTACT EMPLOYEE_ID
> And now, one employee can how more than one contact, like this
> ID CONTACT EMPLOYEE_ID
> 15 e-mail 553
> 16 phone 553
> ..and so on.
> How can I retrive this contacts, for same employee into single row, and
> divided with 'coma'?
> Now a can retrive them but I recieve multiple rows?
> Thanks for help
>
> --
> s3v3n
> ---
> Posted via http://www.codecomments.com
> ---
>|||Check this good article with source code "Returning a Comma-Delimited List o
f
Related Records"
Best Regards
Vadivel
http://vadivel.blogspot.com
http://thinkingms.com/vadivel
"s3v3n" wrote:
> Hi,
> I have a problem with retriving data from one table.
> Table looks like this:
> ID CONTACT EMPLOYEE_ID
> And now, one employee can how more than one contact, like this
> ID CONTACT EMPLOYEE_ID
> 15 e-mail 553
> 16 phone 553
> ...and so on.
> How can I retrive this contacts, for same employee into single row, and
> divided with 'coma'?
> Now a can retrive them but I recieve multiple rows?
> Thanks for help
>
> --
> s3v3n
> ---
> Posted via http://www.codecomments.com
> ---
>|||[Based on the 4guysrolla article .. ]
Create table empTest
(
[ID] int identity,
Contact varchar(100),
Employee_Id int
)
Go
Insert into empTest values ( 'abc@.email.com', 554)
Insert into empTest values ( '090909090', 554)
Go
Create function dbo.udf_GetEmpDetails(@.EmpID int)
Returns Varchar(1000) as
BEGIN
DECLARE @.Contact varchar(1000)
SELECT @.Contact = COALESCE(@.Contact + ', ', '') + s.Contact
FROM empTest s
WHERE s.EMPLOYEE_ID = @.EmpID
RETURN @.Contact
END
Go
Select distinct Employee_ID,
dbo.udf_GetEmpDetails(EMPLOYEE_ID) as ListOfContacts
From empTest f
Go
Hope this helps!
Best Regards
Vadivel
http://vadivel.blogspot.com
http://thinkingms.com/vadivel
"s3v3n" wrote:
> Hi,
> I have a problem with retriving data from one table.
> Table looks like this:
> ID CONTACT EMPLOYEE_ID
> And now, one employee can how more than one contact, like this
> ID CONTACT EMPLOYEE_ID
> 15 e-mail 553
> 16 phone 553
> ...and so on.
> How can I retrive this contacts, for same employee into single row, and
> divided with 'coma'?
> Now a can retrive them but I recieve multiple rows?
> Thanks for help
>
> --
> s3v3n
> ---
> Posted via http://www.codecomments.com
> ---
>
Subscribe to:
Posts (Atom)