January 24, 2012 at 7:46 pm
I have this query working with union all for 2 periods but I cannot get it to work with multiple columns (I need at least 4) can someone tell me what I am doing wrong.
I think I am using the wrong item to make this work.
thanks in advance
This is what works
SELECT EmployeeName,SUM(CT_EFT) AS CT_EFT,SUM(ADV_EFT) AS ADV_EFT
FROM
(SELECT EmployeeName, SUM(Reg_Transactions.quantity) as [CT_EFT],0 AS [ADV_EFT]
FROM Reg_Transactions
WHERE (DateofSale > CONVERT(DATETIME, '2011-01-01 00:00:00', 102)) AND (DateofSale < CONVERT(DATETIME, '2012-01-01 00:00:00', 102)) AND
(Subitem LIKE N'CT EFT%') and (NOT (Reg_Transactions.subitem = N'EFT DRAFT SINGLE UPGRADE'))
GROUP BY EmployeeName, Refunded, Deleted
HAVING (reg_transactions.Deleted = 0) AND (reg_transactions.Refunded = 0)
UNION ALL
SELECT EmployeeName,0, SUM(Quantity)
FROM Reg_Transactions
WHERE (DateofSale > CONVERT(DATETIME, '2011-01-01 00:00:00', 102)) AND (DateofSale < CONVERT(DATETIME, '2012-01-01 00:00:00', 102)) AND
(Subitem LIKE N'ADV EFT%') AND (NOT (Subitem = N'EFT DRAFT SINGLE UPGRADE'))
GROUP BY EmployeeName, Refunded, Deleted
HAVING (Deleted = 0) AND (Refunded = 0))t
GROUP BY EmployeeName
ORDER BY EmployeeName
Thanks Again
January 25, 2012 at 2:43 am
Not enough information.
Please post table DDL scripts, some sample data and expected results based on sample data.
See the first article in my signature line to discover how to post to get the best help on the forums.
-- Gianluca Sartori
January 25, 2012 at 12:28 pm
Whenever you have an aggregate of any type in a UNION statement, verify that the datatype for that aggregate is the same in all sections of the UNION.
0 AS [ADV_EFT] sets the column as an integar. What is SUM(Quantity)? A number? A float? You may have to make that first zero a 0.0 or 0.00 or something in order to make it work.
That aside, you do need to tell us exactly what's not working (specific error messages help) plus include your DDL code, sample data, and expected results if you want a more specific answer.
FYI: Showing us the working code without showing us the bad code, does not help us help you find the answer.
January 25, 2012 at 12:54 pm
This what I am trying to get.
A table that shows the employee name on the left and then the # of the specific packages that they sold for any given service.
Emp name CT_EFT ADV_EFT
AFTON DITINGO 4 2
ALISON MORGAN 6 7
ALLEN HOLT 3 0
ALLISON DEAN 1 0
ALLISON SCHRAFT 1 1
ALYSA PHILLIPS 2 1
The query produces the above results, but I would like to have it extend for out by a couple of more columns for additional packages.
SELECT EmployeeName,SUM(CT_EFT) AS CT_EFT, SUM(ADV_EFT) AS ADV_EFT
FROM
(
SELECT EmployeeName, SUM(Reg_Transactions.quantity) as [CT_EFT],0 AS [ADV_EFT]
FROM Reg_Transactions
WHERE (DateofSale > CONVERT(DATETIME, '2012-01-01 00:00:00', 102)) AND (DateofSale < CONVERT(DATETIME, '2012-01-25 00:00:00', 102)) AND
(Subitem LIKE N'%CT EFT%') and (NOT (Reg_Transactions.subitem = N'EFT DRAFT SINGLE UPGRADE'))
GROUP BY EmployeeName, Refunded, Deleted
HAVING (reg_transactions.Deleted = 0) AND (reg_transactions.Refunded = 0)
UNION ALL
SELECT EmployeeName,0, SUM(Quantity)
FROM Reg_Transactions
WHERE (DateofSale > CONVERT(DATETIME, '2012-01-01 00:00:00', 102)) AND (DateofSale < CONVERT(DATETIME, '2012-01-25 00:00:00', 102)) AND
(Subitem LIKE N'%ADV EFT%') AND (NOT (Subitem = N'EFT DRAFT SINGLE UPGRADE'))
GROUP BY EmployeeName, Refunded, Deleted
HAVING (Deleted = 0) AND (Refunded = 0)
)t
GROUP BY EmployeeName
ORDER BY EmployeeName
Emp name CT_EFT ADV_EFT VSPA_EFT PP_EFT
AFTON DITINGO 4 2 x x
ALISON MORGAN 6 7 x x
ALLEN HOLT 3 0 x x
ALLISON DEAN 1 0 x x
ALLISON SCHRAFT 1 1 x x
ALYSA PHILLIPS 2 1 x x
I want to add 2 more columns that cover the addional packages.
Does this help?
thanks again.
So this is the table script - I think this is what your asking for.
USE [SalonTouchDB]
GO
/****** Object: Table [dbo].[Reg_Transactions] Script Date: 01/25/2012 14:46:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Reg_Transactions](
[Position] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF_Reg_Transactions_Position] DEFAULT (newsequentialid()),
[ItemPosition] [float] NULL CONSTRAINT [DF__Reg_Trans__ItemP__76CBA758] DEFAULT ((0)),
[ClientUID] [nvarchar](50) NULL,
[SalonUID] [nvarchar](50) NULL,
[ComputerID] [nvarchar](50) NULL,
[DateofSale] [datetime] NULL,
[ReceiptNumber] [int] NULL CONSTRAINT [DF__Reg_Trans__Recei__77BFCB91] DEFAULT ((0)),
[TransType] [nvarchar](50) NULL,
[Quantity] [int] NULL CONSTRAINT [DF__Reg_Trans__Quant__78B3EFCA] DEFAULT ((0)),
[Barcode] [nvarchar](50) NULL,
[Groups] [nvarchar](50) NULL,
[Subgroup] [nvarchar](50) NULL,
[Item] [nvarchar](50) NULL,
[Subitem] [nvarchar](50) NULL,
[Price] [money] NULL CONSTRAINT [DF__Reg_Trans__Price__79A81403] DEFAULT ((0)),
[Cost] [money] NULL CONSTRAINT [DF__Reg_Transa__Cost__7A9C383C] DEFAULT ((0)),
[Discount] [money] NULL CONSTRAINT [DF__Reg_Trans__Disco__7B905C75] DEFAULT ((0)),
[Tax1] [float] NULL CONSTRAINT [DF__Reg_Transa__Tax1__7C8480AE] DEFAULT ((0)),
[Tax2] [float] NULL CONSTRAINT [DF__Reg_Transa__Tax2__7D78A4E7] DEFAULT ((0)),
[Tax3] [float] NULL CONSTRAINT [DF__Reg_Transa__Tax3__7E6CC920] DEFAULT ((0)),
[Tax4] [float] NULL CONSTRAINT [DF__Reg_Transa__Tax4__7F60ED59] DEFAULT ((0)),
[Tax1Adjustment] [float] NULL CONSTRAINT [DF__Reg_Trans__Tax1A__00551192] DEFAULT ((0)),
[Tax2Adjustment] [float] NULL CONSTRAINT [DF__Reg_Trans__Tax2A__014935CB] DEFAULT ((0)),
[Tax3Adjustment] [float] NULL CONSTRAINT [DF__Reg_Trans__Tax3A__023D5A04] DEFAULT ((0)),
[Tax4Adjustment] [float] NULL CONSTRAINT [DF__Reg_Trans__Tax4A__03317E3D] DEFAULT ((0)),
[Refunded] [int] NULL CONSTRAINT [DF__Reg_Trans__Refun__0425A276] DEFAULT ((0)),
[ItemSoldType] [nvarchar](50) NULL,
[ItemSoldUID] [nvarchar](50) NULL,
[Deleted] [bit] NOT NULL CONSTRAINT [DF__Reg_Trans__Delet__0519C6AF] DEFAULT ((0)),
[EmployeeUID] [nvarchar](50) NULL,
[EmployeeName] [nvarchar](50) NULL,
[PurchasePts] [float] NULL CONSTRAINT [DF__Reg_Trans__Purch__07020F21] DEFAULT ((0)),
[PurchasePtsValue] [money] NULL CONSTRAINT [DF__Reg_Trans__Purch__07F6335A] DEFAULT ((0)),
[PurchasePtsExpire] [datetime] NULL,
[Moved] [nvarchar](50) NULL,
[ManuallyAdded] [bit] NOT NULL CONSTRAINT [DF__Reg_Trans__Manua__08EA5793] DEFAULT ((0)),
[ComboID] [nvarchar](50) NULL,
[ConvertedData] [bit] NOT NULL CONSTRAINT [DF__Reg_Trans__Conve__09DE7BCC] DEFAULT ((0)),
[RealComputerid] [nvarchar](50) NULL,
[BPDisc] [money] NULL CONSTRAINT [DF__Reg_Trans__BPDis__0AD2A005] DEFAULT ((0)),
[PPDisc] [money] NULL CONSTRAINT [DF__Reg_Trans__PPDis__0BC6C43E] DEFAULT ((0)),
[CDisc] [money] NULL CONSTRAINT [DF__Reg_Trans__CDisc__0CBAE877] DEFAULT ((0)),
[PPTax] [money] NULL CONSTRAINT [DF__Reg_Trans__PPTax__0DAF0CB0] DEFAULT ((0)),
[EFTType] [nvarchar](255) NULL,
[TDISC] [money] NULL CONSTRAINT [DF__Reg_Trans__TDISC__5DA5C806] DEFAULT ((0)),
[PORTION1] [float] NULL CONSTRAINT [DF__Reg_Trans__PORTI__5C2D2349] DEFAULT ((100)),
[PORTION2] [float] NULL CONSTRAINT [DF__Reg_Trans__PORTI__70341BF6] DEFAULT ((100)),
[PORTION3] [float] NULL CONSTRAINT [DF__Reg_Trans__PORTI__043B14A3] DEFAULT ((100)),
[PORTION4] [float] NULL CONSTRAINT [DF__Reg_Trans__PORTI__18420D50] DEFAULT ((100)),
[PPDISCPRETAX] [money] NOT NULL CONSTRAINT [DF__Reg_Trans__PPDIS__2B822C52] DEFAULT ((0)),
[RPDISCPRETAX] [money] NOT NULL CONSTRAINT [DF__Reg_Trans__RPDIS__3F8924FF] DEFAULT ((0)),
[PURPTSPRETAX] [float] NOT NULL CONSTRAINT [DF__Reg_Trans__PURPT__53901DAC] DEFAULT ((0)),
[REFPTSPRETAX] [float] NOT NULL CONSTRAINT [DF__Reg_Trans__REFPT__67971659] DEFAULT ((0)),
CONSTRAINT [PK_Reg_Transactions_1] PRIMARY KEY NONCLUSTERED
(
[Position] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
January 26, 2012 at 5:54 am
Please be more specific about what you've tried. Show us the code where you added another column and it didn't work. Post the error that occurred. Give us an INSERT statement with dummy data for that table so we can test the process ourselves and tell you why it isn't working.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply