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
Showing posts with label luck. Show all posts
Showing posts with label luck. Show all posts
Wednesday, March 28, 2012
Saturday, February 25, 2012
retrieve list of tables
How can I get a list of table names or SPs in my database?
Not having much luck with BOL.
thanksHi
They are stored in sysobjects. BOL has info on that.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"shank" <shank@.tampabay.rr.com> wrote in message
news:uCjpNQCLFHA.3332@.TK2MSFTNGP15.phx.gbl...
> How can I get a list of table names or SPs in my database?
> Not having much luck with BOL.
> thanks
>|||Query system views information_schema.'
Example:
use northwind
go
select
*
from
information_schema.tables
where
table_type = 'base table'
select
*
from
information_schema.routines
where
routine_type = 'procedure'
go
AMB
"shank" wrote:
> How can I get a list of table names or SPs in my database?
> Not having much luck with BOL.
> thanks
>
>|||Go to the index tab in Books Online and type in "INFORMATION_SCHEMA". That
should get you a great start.
You can find information on how to view them by typing this in: "meta data,
obtaining with information schema views"
"shank" <shank@.tampabay.rr.com> wrote in message
news:uCjpNQCLFHA.3332@.TK2MSFTNGP15.phx.gbl...
> How can I get a list of table names or SPs in my database?
> Not having much luck with BOL.
> thanks
>|||SELECT Objects.name FROM dbo.sysobjects Objects
WHERE ((OBJECTPROPERTY(id, 'IsView') = 1) OR (OBJECTPROPERTY(id,
'IsProcedure') = 1) OR (OBJECTPROPERTY(id, 'IsUserTable') = 1))
This will give you the list of all tables, views and stored procedures of
your database.
Thank you
Baiju
"shank" <shank@.tampabay.rr.com> wrote in message
news:uCjpNQCLFHA.3332@.TK2MSFTNGP15.phx.gbl...
> How can I get a list of table names or SPs in my database?
> Not having much luck with BOL.
> thanks
>|||--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Use the SQL Standard views in the INFORMATION_SCHEMA.
Tables (and Views in some systems):
SELECT TABLE_TYPE, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME NOT LIKE 'sys%' -- avoid system views
ORDER BY TABLE_TYPE, TABLE_NAME
--Views:
SELECT TABLE_NAME as view_name
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME NOT LIKE 'sys%' -- avoid system views
ORDER BY TABLE_NAME
--Stored Procedures:
SELECT ROUTINE_TYPE, ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_SCHEMA = 'dbo'
AND ROUTINE_NAME NOT LIKE 'dt_%' -- avoid system routines
ORDER BY ROUTINE_TYPE, ROUTINE_NAME
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQjzMAYechKqOuFEgEQIEBwCfaOyb9ABeCNUL
hWCStiSNslhLlc4AoNAB
bbFnxkmvwTs6FwJ73wVNPqRV
=+MLw
--END PGP SIGNATURE--
Baiju wrote:
> SELECT Objects.name FROM dbo.sysobjects Objects
> WHERE ((OBJECTPROPERTY(id, 'IsView') = 1) OR (OBJECTPROPERTY(id,
> 'IsProcedure') = 1) OR (OBJECTPROPERTY(id, 'IsUserTable') = 1))
> This will give you the list of all tables, views and stored procedures of
> your database.
> Thank you
> Baiju
> "shank" <shank@.tampabay.rr.com> wrote in message
> news:uCjpNQCLFHA.3332@.TK2MSFTNGP15.phx.gbl...
>
Not having much luck with BOL.
thanksHi
They are stored in sysobjects. BOL has info on that.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"shank" <shank@.tampabay.rr.com> wrote in message
news:uCjpNQCLFHA.3332@.TK2MSFTNGP15.phx.gbl...
> How can I get a list of table names or SPs in my database?
> Not having much luck with BOL.
> thanks
>|||Query system views information_schema.'
Example:
use northwind
go
select
*
from
information_schema.tables
where
table_type = 'base table'
select
*
from
information_schema.routines
where
routine_type = 'procedure'
go
AMB
"shank" wrote:
> How can I get a list of table names or SPs in my database?
> Not having much luck with BOL.
> thanks
>
>|||Go to the index tab in Books Online and type in "INFORMATION_SCHEMA". That
should get you a great start.
You can find information on how to view them by typing this in: "meta data,
obtaining with information schema views"
"shank" <shank@.tampabay.rr.com> wrote in message
news:uCjpNQCLFHA.3332@.TK2MSFTNGP15.phx.gbl...
> How can I get a list of table names or SPs in my database?
> Not having much luck with BOL.
> thanks
>|||SELECT Objects.name FROM dbo.sysobjects Objects
WHERE ((OBJECTPROPERTY(id, 'IsView') = 1) OR (OBJECTPROPERTY(id,
'IsProcedure') = 1) OR (OBJECTPROPERTY(id, 'IsUserTable') = 1))
This will give you the list of all tables, views and stored procedures of
your database.
Thank you
Baiju
"shank" <shank@.tampabay.rr.com> wrote in message
news:uCjpNQCLFHA.3332@.TK2MSFTNGP15.phx.gbl...
> How can I get a list of table names or SPs in my database?
> Not having much luck with BOL.
> thanks
>|||--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Use the SQL Standard views in the INFORMATION_SCHEMA.
Tables (and Views in some systems):
SELECT TABLE_TYPE, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME NOT LIKE 'sys%' -- avoid system views
ORDER BY TABLE_TYPE, TABLE_NAME
--Views:
SELECT TABLE_NAME as view_name
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME NOT LIKE 'sys%' -- avoid system views
ORDER BY TABLE_NAME
--Stored Procedures:
SELECT ROUTINE_TYPE, ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_SCHEMA = 'dbo'
AND ROUTINE_NAME NOT LIKE 'dt_%' -- avoid system routines
ORDER BY ROUTINE_TYPE, ROUTINE_NAME
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQjzMAYechKqOuFEgEQIEBwCfaOyb9ABeCNUL
hWCStiSNslhLlc4AoNAB
bbFnxkmvwTs6FwJ73wVNPqRV
=+MLw
--END PGP SIGNATURE--
Baiju wrote:
> SELECT Objects.name FROM dbo.sysobjects Objects
> WHERE ((OBJECTPROPERTY(id, 'IsView') = 1) OR (OBJECTPROPERTY(id,
> 'IsProcedure') = 1) OR (OBJECTPROPERTY(id, 'IsUserTable') = 1))
> This will give you the list of all tables, views and stored procedures of
> your database.
> Thank you
> Baiju
> "shank" <shank@.tampabay.rr.com> wrote in message
> news:uCjpNQCLFHA.3332@.TK2MSFTNGP15.phx.gbl...
>
Subscribe to:
Posts (Atom)