November 4, 2010 at 12:55 am
Hi all,
I have a transaction table. I'm filtering on date values to get a report. the situation is a customer may had many transaction on a day. but i want only one transaction for one customer. Distinct I'm not able to use because the other transaction details will be distinct so I get more than one record for a customer. Again taking only the master information is not enough I need atleast one transaction information.
CREATE TABLE [dbo].[Customer](
[CustId] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL,
[CustName] [varchar](100) NULL,
[CustAddress] [varchar](250) NULL
)
CREATE TABLE [dbo].[TblItem](
[ItemId] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL,
[ItemDescription] [varchar](50) NULL
)
CREATE TABLE [dbo].[CustTrans](
[TransId] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL,
[CustId] [int] NULL,
[ItemId] [int] NULL,
[TransDate] [datetime] NULL)
GO
ALTER TABLE [dbo].[CustTrans] WITH CHECK ADD CONSTRAINT [FK_CustTrans_Customer] FOREIGN KEY([CustId])
REFERENCES [dbo].[Customer] ([CustId])
GO
ALTER TABLE [dbo].[CustTrans] CHECK CONSTRAINT [FK_CustTrans_Customer]
GO
ALTER TABLE [dbo].[CustTrans] WITH CHECK ADD CONSTRAINT [FK_CustTrans_TblItem] FOREIGN KEY([ItemId])
REFERENCES [dbo].[TblItem] ([ItemId])
GO
ALTER TABLE [dbo].[CustTrans] CHECK CONSTRAINT [FK_CustTrans_TblItem]
Sample values
INSERT Customer(CustId,CustName,CustAddress) VALUES('1','Mark','31/5')
INSERT Customer(CustId,CustName,CustAddress) VALUES('2','Antony','33')
INSERT TblItem(ItemId,ItemDescription) VALUES('1','Item1')
INSERT TblItem(ItemId,ItemDescription) VALUES('2','Item2')
INSERT TblItem(ItemId,ItemDescription) VALUES('3','Item3')
INSERT CustTrans(TransId,CustId,ItemId,TransDate) VALUES('1','1','1',convert(datetime,'Nov 4 2010 12:04PM'))
INSERT CustTrans(TransId,CustId,ItemId,TransDate) VALUES('2','1','2',convert(datetime,'Nov 4 2010 12:14PM'))
INSERT CustTrans(TransId,CustId,ItemId,TransDate) VALUES('3','2','1',convert(datetime,'Nov 4 2010 12:24PM'))
INSERT CustTrans(TransId,CustId,ItemId,TransDate) VALUES('4','2','2',convert(datetime,'Nov 4 2010 12:34PM'))
to be more precise,
in the above code I have 2 customers doing 4 transaction on a given day.
I need only one transaction for one customer in the report. I'm trying with row_number() but not able to get the expected result.
Thanks,
Regards
Ami
November 4, 2010 at 1:16 am
Hi
In your code you have set as IDENTITY(1,1) then how you are inserting values to a colum with identity
INSERT Customer(CustId,CustName,CustAddress) VALUES('1','Mark','31/5')
INSERT TblItem(ItemId,ItemDescription) VALUES('1','Item1')
INSERT CustTrans(TransId,CustId,ItemId,TransDate) VALUES('1','1','1',convert(datetime,'Nov 4 2010 12:04PM'))
Here all your Ids are set as IDENTITY(1,1) you cant assign values for the column which is identity.
You will be getting some error some thing as
Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'Customer' when IDENTITY_INSERT is set to OFF.
Thanks
Parthi
Thanks
Parthi
November 4, 2010 at 1:35 am
Anamika (11/4/2010)
to be more precise,in the above code I have 2 customers doing 4 transaction on a given day.
I need only one transaction for one customer in the report. I'm trying with row_number() but not able to get the expected result.
Thanks,
Regards
Ami
You have said that you have tried with Row_number() did you try with CTE a fearture from 2005.It will help to solve your problem.
Look at this
;With CTE (TransId, CustId,ItemId,TransDate ,NewRowId) as
(
Select *,ROW_NUMBER() OVER(PARTITION BY CustId Order BY CustId) as NewRowId from [CustTrans])
Select TransId,CustId, from CTE Where NewRowId=1 -- it will filter the CTE value
Thanks
Parthi
Thanks
Parthi
November 4, 2010 at 4:33 am
Oh,
I'm sorry Parthy, I missed the IDENTITY_INSERT ON/OFF statements.
your query works for me. thanks
Best Regards,
Ami
November 5, 2010 at 5:35 pm
Annamika,
As I understand it you want each customer whether or not they have an order for a particular day and only 1 order. I would use a Calendar table to provide the 'anchor' for the particular day and use an OUTER APPLY for the customer transactions.
Here is an article I wrote about using a daily calendar table as the anchor for reports:
http://www.sqlservercentral.com/articles/T-SQL/70743/
Create the table like in the article, but use 2010 instead of 2009.
To understand how APPLY works here are 2 very good articles by one of the gurus on this site, Paul White:
http://www.sqlservercentral.com/articles/APPLY/69953/
http://www.sqlservercentral.com/articles/APPLY/69954/
In order to properly view the results of the query you should add this to your customer table:
INSERT Customer(CustName,CustAddress)VALUES('Julius', '12345')
Now you have a customer with no transactions for the date. Here is the query:
SELECT C.CustID, C.CustName, CD.DayDate
, TRN.ItemId, TRN.ItemDescription
FROM Customer C
CROSS JOIN CalDay CD
OUTER APPLY
(SELECT TOP 1 CT.ItemId, I.ItemDescription
FROM CustTrans CT
INNER JOIN TblItem I ON
CT.ItemId = I.ItemId
WHERE CT.CustId = C.CustId
AND CT.TransDate >= CD.DayDate
AND CT.TransDate < CD.NextDay
ORDER BY CT.ItemId
) AS TRN
WHERE CD.DayDate = 'Nov 4 2010'
Hope this helps.
Todd Fifield
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply