Help needded to get this result

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Help needded to get this result

Author

Topic

Pete_N
Posting Yak Master

181 Posts

Posted - 2010-11-10 : 06:16:48

CREATE TABLE [dbo].[ClientSummary](
[ID] [uniqueidentifier] NOT NULL,
[Licence] [varchar](6) NOT NULL,
[Description] [varchar](30) NOT NULL,
[Sale] [bit] NOT NULL,
[Discount1] [bit] NOT NULL,
[Discount2] [bit] NOT NULL,
[Discount3] [bit] NOT NULL,
[Discount4] [bit] NOT NULL,
[Discount5] [bit] NOT NULL,
[CeditValue] [money] NOT NULL,
[DebitValue] [money] NOT NULL,
[AppliedDate] [datetime] NOT NULL,
[Reconcilled] [bit] NOT NULL,
[Reconcilleddate] [datetime] NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

INSERT INTO [ClientSummary]
([ID] ,[Licence],[Description] ,[Sale] ,[Discount1] ,[Discount2] ,[Discount3] ,[Discount4] ,[Discount5] ,[CeditValue] ,[DebitValue] ,[AppliedDate]
,[Reconcilled] ,[Reconcilleddate])
VALUES (NEWID(), '000001' ,'FULL SYS' , 1, 0 ,0 ,0 ,0 ,0, 300.00 , 0.00, CONVERT(datetime, '01/01/2010' , 103) , 0, NULL)

INSERT INTO [ClientSummary]
([ID] ,[Licence],[Description] ,[Sale] ,[Discount1] ,[Discount2] ,[Discount3] ,[Discount4] ,[Discount5] ,[CeditValue] ,[DebitValue] ,[AppliedDate]
,[Reconcilled] ,[Reconcilleddate])
VALUES (NEWID(), '000002' ,'FULL SYS' , 1, 0 ,0 ,0 ,0 ,0, 729.00 , 0.00, CONVERT(datetime, '01/01/2010' , 103) , 0, NULL)

INSERT INTO [ClientSummary]
([ID] ,[Licence],[Description] ,[Sale] ,[Discount1] ,[Discount2] ,[Discount3] ,[Discount4] ,[Discount5] ,[CeditValue] ,[DebitValue] ,[AppliedDate]
,[Reconcilled] ,[Reconcilleddate])
VALUES (NEWID(), '000002' ,'CREDIT' , 0, 1 ,0 ,0 ,0 ,0, 0.00 , 29.00, CONVERT(datetime, '02/03/2010' , 103) , 0, NULL)

INSERT INTO [ClientSummary]
([ID] ,[Licence],[Description] ,[Sale] ,[Discount1] ,[Discount2] ,[Discount3] ,[Discount4] ,[Discount5] ,[CeditValue] ,[DebitValue] ,[AppliedDate]
,[Reconcilled] ,[Reconcilleddate])
VALUES (NEWID(), '000003' ,'PRODUCT ACC' , 1, 0 ,0 ,0 ,0 ,0, 273.56 , 0.00, CONVERT(datetime, '01/06/2010' , 103) , 0, NULL)

INSERT INTO [ClientSummary]
([ID] ,[Licence],[Description] ,[Sale] ,[Discount1] ,[Discount2] ,[Discount3] ,[Discount4] ,[Discount5] ,[CeditValue] ,[DebitValue] ,[AppliedDate]
,[Reconcilled] ,[Reconcilleddate])
VALUES (NEWID(), '000002' ,'INCENTIVE' , 0, 0 ,0 ,1 ,0 ,0, 0.00 , 350.00, CONVERT(datetime, '17/07/2010' , 103) , 0, NULL)

INSERT INTO [ClientSummary]
([ID] ,[Licence],[Description] ,[Sale] ,[Discount1] ,[Discount2] ,[Discount3] ,[Discount4] ,[Discount5] ,[CeditValue] ,[DebitValue] ,[AppliedDate]
,[Reconcilled] ,[Reconcilleddate])
VALUES (NEWID(), '000001' ,'FULL SYS' , 1, 0 ,0 ,0 ,0 ,0, 300.00 , 0.00, CONVERT(datetime, '01/08/2010' , 103) , 0, NULL)

INSERT INTO [ClientSummary]
([ID] ,[Licence],[Description] ,[Sale] ,[Discount1] ,[Discount2] ,[Discount3] ,[Discount4] ,[Discount5] ,[CeditValue] ,[DebitValue] ,[AppliedDate]
,[Reconcilled] ,[Reconcilleddate])
VALUES (NEWID(), '000001' ,'SECOND SYS' , 0, 0 ,0 ,0 ,0 ,1, 0.00 , 150.00, CONVERT(datetime, '01/08/2010' , 103) , 0, NULL)

/* This table will only contain records where [Reconcilled] IS NULL */

/*
Required Result

[Licence] [Description] [Sale] [Discount1] [Discount2] [Discount3] [Discount4] [Discount5] [Balance]
000001 ACTIVITY 600.00 0 0 0 0 150.00 450.00
000002 ACTIVITY 729.00 29.00 0 350.00 0 0 350.00
000003 ACTIVITY 237.56 0 0 0 0 0 237.56

Sachin.Nand

2937 Posts

Posted - 2010-11-10 : 06:21:33

How are you getting 29.00 for Discount1 for Licence 000002 ?

PBUH

Pete_N
Posting Yak Master

181 Posts

Posted - 2010-11-10 : 06:41:26

quote:
Originally posted by Sachin.Nand

How are you getting 29.00 for Discount1 for Licence 000002 ?

PBUH

Discount(x) are bit fields.
Licence 0000002 has Discount1 = 1 for a debit value of 29.00
and Discount3 = 1 for a debit value of 350.00, therefore the sum of Discount1 for licence '000002' is 29.00 and for discount3 = 350.00

Sachin.Nand

2937 Posts

Posted - 2010-11-10 : 06:47:59

You need to post all possible scenarios first hand.

PBUH

Pete_N
Posting Yak Master

181 Posts

Posted - 2010-11-10 : 12:58:32

quote:
Originally posted by Sachin.Nand

You need to post all possible scenarios first hand.

PBUH

I'm sorry, I dont fully understand what you mean. I have supplied dummy data and the result I am trying to achieve .. all i need is a little help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources