Home All Groups Group Topic Archive Search About

Return a horizontal table for result

microsoft.public.sqlserver.server
Author
12 May 2005 12:48 AM
John
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.

Author
12 May 2005 12:54 AM
Tom Moreau
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" <J***@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.
Author
12 May 2005 5:48 PM
John
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)

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

Show quote
"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" <J***@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.
>
>
>
>
Author
13 May 2005 12:18 AM
Tom Moreau
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" <J***@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)

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

Show quote
"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" <J***@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.
>
>
>
>
Author
13 May 2005 4:57 PM
John
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     



Show quote
"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" <J***@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" <J***@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.
> >
> >
> >
> >
>
>
Author
14 May 2005 9:09 PM
Tom Moreau
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" <J***@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



Show quote
"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" <J***@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" <J***@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.
> >
> >
> >
> >
>
>
Author
16 May 2005 9:45 PM
John
Yes that is correct.

Show quote
"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" <J***@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" <J***@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" <J***@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.
> > >
> > >
> > >
> > >
> >
> >
>
>
Author
16 May 2005 11:45 PM
Tom Moreau
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" <J***@discussions.microsoft.com> wrote in message
news:BB0D6BE3-1081-4381-9473-345B93DFA54B@microsoft.com...
Yes that is correct.

Show quote
"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" <J***@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" <J***@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" <J***@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.
> > >
> > >
> > >
> > >
> >
> >
>
>
Author
17 May 2005 12:14 AM
John
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!

Show quote
"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" <J***@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" <J***@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" <J***@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" <J***@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.
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
Author
17 May 2005 1:07 AM
Tom Moreau
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" <J***@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!

Show quote
"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" <J***@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" <J***@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" <J***@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" <J***@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.
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
Author
18 May 2005 12:09 AM
John
I am using this query in a Crystal or RS report for output and I am
unfamiliar with how to use XML in this fashion...

Is there an approach to sequencing each of the Pays, Deductions and Taxes,
so that each grouping would appear top to bottom in my report? It would not
have to appear that way in the query result but if I had a seq order per
(Pay, Deduction, Tax etc) I could handle the grouping on the report side?


Thanks again.


Show quote
"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" <J***@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" <J***@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" <J***@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" <J***@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)
Author
18 May 2005 11:37 AM
Tom Moreau
It does add more complexity and will affect performance - especially if many
pay checks are involved.  What you'd have to do is populate work tables with
the running counts and amounts for each check, for each category - pay, tax
and deductions.  Use those tables in the original query I gave you.

If you're using RS, I can't recall if XML as a data source can be used, but
you can create linked reports, each of which could use a query to get the
required information.

--
   Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON   Canada
www.pinpub.com
..
"John" <J***@discussions.microsoft.com> wrote in message
news:46DE4398-6D2A-4579-9B4E-ACC2A5499B5F@microsoft.com...
I am using this query in a Crystal or RS report for output and I am
unfamiliar with how to use XML in this fashion...

Is there an approach to sequencing each of the Pays, Deductions and Taxes,
so that each grouping would appear top to bottom in my report? It would not
have to appear that way in the query result but if I had a seq order per
(Pay, Deduction, Tax etc) I could handle the grouping on the report side?


Thanks again.


Show quote
"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" <J***@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" <J***@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" <J***@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" <J***@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)

AddThis Social Bookmark Button

Post Other interesting topics
comparing text fields - Second try
Loading Status on Restore HELP!
getting back ownership of objects
Login Transfers
disable foreign keys