For SQL gurus... how to manage % with fields value

Hi!


I've a table "Progetti" with a list of projects
for each project I can have many steps in the table "ProgettiFasi" (one project many steps)
for each step I've a value "ProgettiFasi.ValoreImponibile"
I can insert each step in a "proforma invoice" (pre-invoice), that I find in "EstrattiConto" table
In each "proforma invoce" (EstrattiConto table) I can have many steps of differents projects but about the same customer, I see all steps in the detail table "EstrattiContoDettaglio"
each "proforma invoce" has a total (the sum of all steps of the proforma) and I can split tha total in X delays, using the table "EstrattiContoDilazioni"
for each delay I will create an invoice (with 1:1 relation ... 1 delay - 1 invoice)


My problem:

I've to create a query that allow me to see the state of *** each project *** (please note for each project):
 
A) "Total Proforma with invoice already created" for each project using the field EstrattiContoDilazioni.EmessaFT that allow me to see if I've created the invoice for the delay (true)
B) "Total Proforma with invoce NOT created" for each project using the field EstrattiContoDilazioni.EmessaFT that allow me to see if I've created the invoice for the delay (false)
C) "Total Steps with direct invoice" I've not create the proforma but I've automatically created an invoice frmo the step


I've a problem with A) and B)

I don't have a relation between Delay "EstrattiContoDilazioni" and Project "Progetti" because each delay could have differents steps/projects inserted in the proforma ... I've to calculate the % of each project ... using this example:

Table: "ProgettiFasi"
Step A - P1 500 (Project 1)
Step B - P2 1500 (Project 2)

Table: "EstrattiConto" (header) and "EstrattiContoDettaglio" (details)
Total of the proforma invoice 2000

I've to calculate the % of each project in the proforma  "EstrattiConto" (header) and "EstrattiContoDettaglio" (details)
P1 33,33%
P2 66,66%

Now... if in my EC I had 2 delays of:
delay 1 1000 (I will create an invoice for this delay)
delay 2 1000 (I will create an invoice for this delay)

and I can know that with 1000 I will have
33,33% for project P1 (In P1 project's container I will insert 333)
66,66% for project P2 (In P2 project's container I will insert 666)

I've only to choose if insert the value in the column A or in column B using the field
EstrattiContoDilazioni.EmessaFT

if = true I've already created the invoice for this delay (use column A)
if = false I've not created the invoice for this delay (use column B)



The column C is mooooore simple: I've only to check all invocies details "FattureDettaglio" with IDFase different to zero ... this allow me to know that this is a "direct invoice" from a steps (without using proforma invoice" ... and using ProgettiFasi.IDProgetto I can to know the project


I hope that you can help me!!!


CREATE TABLE [dbo].[Progetti](
[IDProgetto] [int] IDENTITY(1,1) NOT NULL,
[CodiceProgetto] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
[Descrizione] [varchar](200) COLLATE Latin1_General_CI_AS NULL
CONSTRAINT [PK_Documenti] PRIMARY KEY CLUSTERED
(
[IDProgetto] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]



CREATE TABLE [dbo].[ProgettiFasi](
[IDProgettoFase] [int] IDENTITY(1,1) NOT NULL,
[IDProgetto] [int] NULL,
[FaseInserireEC] [bit] NULL CONSTRAINT [DF_ProgettiFasi_FaseInserireEC] DEFAULT ((0)),
[FaseInserireFT] [bit] NULL CONSTRAINT [DF_ProgettiFasi_FaseInserireFT] DEFAULT ((0)),
[FaseEmessoEC] [bit] NULL CONSTRAINT [DF_ProgettiFasi_FaseEmessoEC] DEFAULT ((0)),
[FaseEmessaFT] [bit] NULL CONSTRAINT [DF_ProgettiFasi_FaseEmessaFT] DEFAULT ((0)),
[DescrizioneFase] [varchar](max) COLLATE Latin1_General_CI_AS NULL,
[DescrizioneIVA] [varchar](200) COLLATE Latin1_General_CI_AS NULL,
[IDAliquota] [int] NULL,
[ValoreImponibile] [decimal](18, 2) NULL CONSTRAINT [DF_ProgettiFasi_ValoreImponibile] DEFAULT ((0)),
[IDAzienda] [int] NULL,
CONSTRAINT [PK_Fasi] PRIMARY KEY CLUSTERED
(
[IDProgettoFase] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]



CREATE TABLE [dbo].[EstrattiConto](
[IDEstrattoConto] [int] IDENTITY(1,1) NOT NUL
[IDCliente] [int] NULL,
[Incassato] [bit] NULL CONSTRAINT [DF_EstrattiConto_Incassato] DEFAULT ((0)),
[EmessaFT] [bit] NULL CONSTRAINT [DF_EstrattiConto_EmessaFT] DEFAULT ((0))
CONSTRAINT [PK_EstrattiConto] PRIMARY KEY CLUSTERED
(
[IDEstrattoConto] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]




CREATE TABLE [dbo].[EstrattiContoDettaglio](
[IDEstrattoContoDettaglio] [int] IDENTITY(1,1) NOT NULL,
[IDEstrattoConto] [int] NULL,
[IDAliquota] [int] NULL,
[IDProgettoFase] [int] NULL,
[Descrizione] [varchar](200) COLLATE Latin1_General_CI_AS NULL,
[Imponibile] [decimal](18, 2) NULL,
CONSTRAINT [PK_EstrattoContoDettaglio] PRIMARY KEY CLUSTERED
(
[IDEstrattoContoDettaglio] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]




CREATE TABLE [dbo].[EstrattiContoDilazioni](
[IDEstrattoContoDilazione] [int] IDENTITY(1,1) NOT NULL,
[IDEstrattoConto] [int] NULL,
[DataDilazione] [datetime] NULL,
[ValoreDilazione] [decimal](18, 2) NULL,
[Incassata] [bit] NULL CONSTRAINT [DF_EstrattiContoDilazioni_Incassata] DEFAULT ((0)),
[EmessaFT] [bit] NULL CONSTRAINT [DF_EstrattiContoDilazioni_EmessaFT] DEFAULT ((0)),
CONSTRAINT [PK_EstrattiContoDilazione] PRIMARY KEY CLUSTERED
(
[IDEstrattoContoDilazione] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]



CREATE TABLE [dbo].[FattureDettaglio](
[IDFatturaDettaglio] [int] IDENTITY(1,1) NOT NULL,
[IDFattura] [int] NULL CONSTRAINT [DF_FattureDettaglio_IDFattura] DEFAULT ((0)),
[IDFase] [int] NULL CONSTRAINT [DF_FattureDettaglio_IDFase] DEFAULT ((0)),
[IDAliquota] [int] NULL,
[DescrizioneRiga] [varchar](max) COLLATE Latin1_General_CI_AS NULL,
[ValoreAliquota] [decimal](10, 4) NULL,
[ValoreRiga] [decimal](18, 4) NULL,
[IDProgetto] [int] NULL,
[Qt] [int] NULL CONSTRAINT [DF_FattureDettaglio_Qt] DEFAULT ((1)),
CONSTRAINT [PK_FattureDettaglio] PRIMARY KEY CLUSTERED
(
[IDFatturaDettaglio] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

0
nixor
12/22/2008 1:47:51 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

14 Replies
581 Views

Similar Articles

[PageSpeed] 39

HI,

I am very sorry to say that ur problem is not clear to us because of local language. It will be better to write this problem by using example & first post ur one problem not all then ask another question in this thread. Please transate ur tables column name by english then post like:

TABLE Project:
prjid, prjname, description
1,abc,'....'

Table Steps:
Step,prjid 

NOW QU LIKE: I WANT TO SEE HOW MANY STEPS EACH PROJECT HAS. after resolve this problem u can ask 2nd question.

Here one can easily understand that project & steps has a relationship. No need to write the datatype of column name just use true/false for bit datatype.

Hope u can understand what i want to say.

Sorry for the inconvinience.


Shawpnendu Bikash Maloroy
http://shawpnendu.blogspot.com
0
shawpnendu
12/22/2008 6:20:59 PM

 

Thanks for your reply and thanks for help me!!!

I still have this problem and I don't know how to resolve it :-((

My post is very long, Can you tell what is not clear?? I will re-write the content...



Thanks!!!
0
nixor
12/25/2008 9:44:39 PM

HI,
I WANT TO SAY THAT FIRST PUT TABLE WITH SAMPLE DATA. THEN WRITE YOUR FIRST PROBLEM IN UNDERSTANDABLE WAY. KEEP IN MIND THAT COLUMN NAME & DATA  IN ENGLISH. WHICH WILL HELP US TO UNDERSTAND EXACTLY WHAT U WANT.


Shawpnendu Bikash Maloroy
http://shawpnendu.blogspot.com
0
shawpnendu
12/26/2008 6:05:13 AM

Thanks for help me!!


Tables in ENG version

 


CREATE TABLE [dbo].[Projects](
[IDProject] [int] IDENTITY(1,1) NOT NULL,
[ProjectCode] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
[Description] [varchar](200) COLLATE Latin1_General_CI_AS NULL
CONSTRAINT [PK_Documenti] PRIMARY KEY CLUSTERED
(
[IDProject] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]




CREATE TABLE [dbo].[ProjectSteps](
[IDProjectStep] [int] IDENTITY(1,1) NOT NULL,
[IDProject] [int] NULL,
[InsertInProforma] [bit] NULL CONSTRAINT [DF_ProgettiFasi_FaseInserireEC] DEFAULT ((0)),
[InsertInInvoice] [bit] NULL CONSTRAINT [DF_ProgettiFasi_FaseInserireFT] DEFAULT ((0)),
[InsertedInProforma] [bit] NULL CONSTRAINT [DF_ProgettiFasi_FaseEmessoEC] DEFAULT ((0)),
[InsertedInInvoice] [bit] NULL CONSTRAINT [DF_ProgettiFasi_FaseEmessaFT] DEFAULT ((0)),
[StepDescription] [varchar](max) COLLATE Latin1_General_CI_AS NULL,
[Value] [decimal](18, 2) NULL CONSTRAINT [DF_ProgettiFasi_ValoreImponibile] DEFAULT ((0)),
[IDCustomer] [int] NULL,
CONSTRAINT [PK_Fasi] PRIMARY KEY CLUSTERED
(
[IDProjectStep] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]



CREATE TABLE [dbo].[Proforma](
[IDProforma] [int] IDENTITY(1,1) NOT NULL,
[IDCustomer] [int] NULL,
[Payed] [bit] NULL CONSTRAINT [DF_EstrattiConto_Incassato] DEFAULT ((0)),
[CreatedInvoice] [bit] NULL CONSTRAINT [DF_EstrattiConto_EmessaFT] DEFAULT ((0))
CONSTRAINT [PK_EstrattiConto] PRIMARY KEY CLUSTERED
(
[IDProforma] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]




CREATE TABLE [dbo].[ProformaDetail](
[IDProformaDetail] [int] IDENTITY(1,1) NOT NULL,
[IDProforma] [int] NULL,
[IDProjectStep] [int] NULL,
[Description] [varchar](200) COLLATE Latin1_General_CI_AS NULL,
[Value] [decimal](18, 2) NULL,
CONSTRAINT [PK_EstrattoContoDettaglio] PRIMARY KEY CLUSTERED
(
[IDProformaDetail] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]




CREATE TABLE [dbo].[ProformaDelays](
[IDProformaDelay] [int] IDENTITY(1,1) NOT NULL,
[IDProforma] [int] NULL,
[DelayDate] [datetime] NULL,
[Value] [decimal](18, 2) NULL,
[Payed] [bit] NULL CONSTRAINT [DF_EstrattiContoDilazioni_Incassata] DEFAULT ((0)),
[CreatedInvoice] [bit] NULL CONSTRAINT [DF_EstrattiContoDilazioni_EmessaFT] DEFAULT ((0)),
CONSTRAINT [PK_EstrattiContoDilazione] PRIMARY KEY CLUSTERED
(
[IDProformaDelay] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]



CREATE TABLE [dbo].[InvoiceDatail](
[IDInvoiceDatail] [int] IDENTITY(1,1) NOT NULL,
[IDInvoice] [int] NULL CONSTRAINT [DF_FattureDettaglio_IDFattura] DEFAULT ((0)),
[IDProjectStep] [int] NULL CONSTRAINT [DF_FattureDettaglio_IDFase] DEFAULT ((0)),
[Description] [varchar](max) COLLATE Latin1_General_CI_AS NULL,
[Value] [decimal](18, 4) NULL,
[Quantities] [int] NULL CONSTRAINT [DF_FattureDettaglio_Qt] DEFAULT ((1)),
CONSTRAINT [PK_FattureDettaglio] PRIMARY KEY CLUSTERED
(
[IDInvoiceDatail] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] 

 

Now I'll send the insert

0
nixor
12/26/2008 9:04:13 PM

 And here the inserts

 

INSERT INTO [master].[dbo].[Projects]
           ([ProjectCode]
           ,[Description])
     VALUES
           ('A'
           ,'PRG A')

 

INSERT INTO [master].[dbo].[Projects]
           ([ProjectCode]
           ,[Description])
     VALUES
           ('B'
           ,'PRG B')

 

 

INSERT INTO [master].[dbo].[ProjectSteps]
           ([IDProject]
           ,[InsertInProforma]
           ,[InsertInInvoice]
           ,[InsertedInProforma]
           ,[InsertedInInvoice]
           ,[StepDescription]
           ,[Value]
           ,[IDCustomer])
     VALUES
           (1
           ,1
           ,1
           ,0
           ,0
           ,'Step PRG A'
           ,2000
           ,1)

 

INSERT INTO [master].[dbo].[ProjectSteps]
           ([IDProject]
           ,[InsertInProforma]
           ,[InsertInInvoice]
           ,[InsertedInProforma]
           ,[InsertedInInvoice]
           ,[StepDescription]
           ,[Value]
           ,[IDCustomer])
     VALUES
           (2
           ,1
           ,1
           ,0
           ,0
           ,'Step PRG B'
           ,500
           ,1)

 

INSERT INTO [master].[dbo].[ProjectSteps]
           ([IDProject]
           ,[InsertInProforma]
           ,[InsertInInvoice]
           ,[InsertedInProforma]
           ,[InsertedInInvoice]
           ,[StepDescription]
           ,[Value]
           ,[IDCustomer])
     VALUES
           (2
           ,1
           ,1
           ,0
           ,0
           ,'Step PRG B'
           ,500
           ,1)

 

INSERT INTO [master].[dbo].[ProjectSteps]
           ([IDProject]
           ,[InsertInProforma]
           ,[InsertInInvoice]
           ,[InsertedInProforma]
           ,[InsertedInInvoice]
           ,[StepDescription]
           ,[Value]
           ,[IDCustomer])
     VALUES
           (2
           ,1
           ,1
           ,0
           ,0
           ,'Step PRG B'
           ,200
           ,1)

 

 

INSERT INTO [master].[dbo].[ProjectSteps]
           ([IDProject]
           ,[InsertInProforma]
           ,[InsertInInvoice]
           ,[InsertedInProforma]
           ,[InsertedInInvoice]
           ,[StepDescription]
           ,[Value]
           ,[IDCustomer])
     VALUES
           (2
           ,1
           ,0
           ,0
           ,0
           ,'Step PRG B'
           ,800
           ,1)

 

Now I've 4 steps

2000 of Project A
500 of Project B
500 of Project B
200 of Project B

That I will insert in proforma (steps with ID 1,2,3,4)

And one step (with ID 5) that I will insert directly in an invoice (without use a proforma)



I've  "proforma invoices" for the steps

INSERT INTO [master].[dbo].[ProformaDetail]
           ([IDProforma]
           ,[IDProjectStep]
           ,[Description]
           ,[Value])
     VALUES
           (1
           ,1
           ,'proforma of a step of Project A'
           ,2000)


INSERT INTO [master].[dbo].[ProformaDetail]
           ([IDProforma]
           ,[IDProjectStep]
           ,[Description]
           ,[Value])
     VALUES
           (1
           ,2
           ,'proforma of a step of Project B'
           ,500)

INSERT INTO [master].[dbo].[ProformaDetail]
           ([IDProforma]
           ,[IDProjectStep]
           ,[Description]
           ,[Value])
     VALUES
           (1
           ,3
           ,'proforma of a step of Project B'
           ,500)


INSERT INTO [master].[dbo].[ProformaDetail]
           ([IDProforma]
           ,[IDProjectStep]
           ,[Description]
           ,[Value])
     VALUES
           (2
           ,3
           ,'proforma of a step of Project B ... this is a new Proforma with only one step'
           ,200)

 

and the delays of each proforma
first proforma: 2 delays
second proforma: 1 delay

INSERT INTO [master].[dbo].[ProformaDelays]
           ([IDProforma]
           ,[DelayDate]
           ,[Value]
           ,[Payed]
           ,[CreatedInvoice])
     VALUES
           (1
           ,'20081212'
           ,1500
           ,0
           ,1)

INSERT INTO [master].[dbo].[ProformaDelays]
           ([IDProforma]
           ,[DelayDate]
           ,[Value]
           ,[Payed]
           ,[CreatedInvoice])
     VALUES
           (1
           ,'20081212'
           ,1500
           ,0
           ,0)


INSERT INTO [master].[dbo].[ProformaDelays]
           ([IDProforma]
           ,[DelayDate]
           ,[Value]
           ,[Payed]
           ,[CreatedInvoice])
     VALUES
           (2
           ,'20081212'
           ,200
           ,0
           ,0)

 
I create an invoice for the step 5

INSERT INTO [master].[dbo].[InvoiceDatail]
           ([IDInvoice]
           ,[IDProjectStep]
           ,[Description]
           ,[Value]
           ,[Quantities])
     VALUES
           (1
           ,5
           ,'Invoice created without using a proforma'
           ,800
           ,1)


In the first proforma I've
2000 for Project A
1000 for Project B
The % using the total (3000):
66% for A
33% for B

 

In the second proforma I've
200 for Project B
The % using the total (200):
100% for B

 

Now I've to create a report for split all informations that I've in  ProformaDelays for each project (using the % created)

 In the delays I've

1500: proforma 1
1500: proforma 1
200: proforma 2

in the first delay I know that the % are 66% and 33% ...
990 for project A
495 for project B

etcc...

 

At the end I want to see:

for each project
A) "Total Proforma with invoice already created" for each project using the field ProformaDelays.CreatedInvoice that allow me to see if I've created the invoice for the delay (true)
B) "Total Proforma with invoce NOT created" for each project using the field ProformaDelays.CreatedInvoice that allow me to see if I've created the invoice for the delay (false)
C) "Total Steps with direct invoice" I've not created the proforma but I've automatically created an invoice from the step


in the A column I will have these rows:
Project A 990 (all ProformaDelays that have CreatedInvoice set to true)
Project B 495 (all ProformaDelays that have CreatedInvoice set to true)

in the B column I will have these rows:
Project A 990 + 132 = 1122 (all ProformaDelays that have CreatedInvoice set to false)
Project B 495 + 66 = 561 (all ProformaDelays that have CreatedInvoice set to false)


in the C column I will have these rows:
Project B = 800 (all the invoices created with IDProjectStep > zero)

 

Thanks for help me!!

0
nixor
12/26/2008 9:42:00 PM

 Hi,NIxor

There Is some confusion understanding ur problem 

 

U have written 

nixor:
in the first delay I know that the % are 66% and 33% ...
990 for project A
495 for project B
 

 

nixor:
in the A column I will have these rows:
Project A 990 (all ProformaDelays that have CreatedInvoice set to true)
Project B 495 (all ProformaDelays that have CreatedInvoice set to true)
 

 

From Where The Above 990 and 495 are coming Confused

 

 

Other Thing

nixor:
all ProformaDelays that have CreatedInvoice set to true
 

U r saying about  CreatedInvoice column which is in [Proforma] tablebut it is empty as per ur say then how would we assume that

 

Please MAke that clear to us.

 

 

 


RAGHAV

MVP ASP/ASP.Net Read My Blog


MARK THE POST AS ANSWER IF IT HELPS U.


"Success doesn't come to you…you go to it."--Marva Collins




"Success does not come to those who wait . . . and it does not wait for anyone to come to it." Anonymous


0
raghav_khunger
12/27/2008 4:46:20 AM

<<

 From Where The Above 990 and 495 are coming

>>

 

if I've a delay of 1.500 about a specific Proforma

and I know that the project in the Proforma has 66% (for A) and 33% (for B)

I can calculate that the delay of 1.500 has

1500 * 66 % for Project A =  990
1500 * 33 % for Project B = 495

This for each Proforma Delay


Let me know

0
nixor
12/27/2008 11:37:42 AM

raghav_khunger:
U r saying about  CreatedInvoice column which is in [Proforma] tablebut it is empty as per ur say then how would we assume that
 

 

I don't use the CreatedInvoice field of "Proforma" table ... in my post I've used 

ProformaDelays.CreatedInvoice

Have you seen??

Let me know!  And thanks for help me!!!

 

 

0
nixor
12/27/2008 11:40:25 AM

Hi,nixor

 

Till Now this Picture is coming in my mind

;with cte as

(

Select

profdtl.IDProforma,sum(profdtl.Value) as [Val] --into temp

from [Projects]

inner join [ProjectSteps] prostep

on [Projects].IDProject=prostep.IDProject

inner join [ProformaDetail] profdtl

on prostep.IDProjectstep=profdtl.IDProjectstep

--where IDProforma=1

group by profdtl.IDProforma

)

, cte1 as

(

Select

prostep.IDProject,profdtl.IDProforma,sum(profdtl.Value) as [Val],

(sum(profdtl.Value)/cte.[Val] ) as per

from [Projects]

inner join [ProjectSteps] prostep

on [Projects].IDProject=prostep.IDProject

inner join [ProformaDetail] profdtl

on prostep.IDProjectstep=profdtl.IDProjectstep

inner join cte

on cte.IDProforma=profdtl.IDProforma

group by prostep.IDProject,profdtl.IDProforma,cte.[Val]

)

,cte2 as

(

Select cte1.IDProject,(sum(prfDelay.[Value]) * cte1.[per]) as col1

from [ProformaDelays] prfDelay

inner join cte1

on prfDelay.[IDProforma]=cte1.[IDProforma]

where prfDelay.CreatedInvoice=1

group by cte1.IDProject,prfDelay.CreatedInvoice,cte1.[per]

)

,cte3 as

(

Select cte1.IDProject,(sum(prfDelay.[Value]) * cte1.[per]) as col1

from [ProformaDelays] prfDelay

inner join cte1

on prfDelay.[IDProforma]=cte1.[IDProforma]

where prfDelay.CreatedInvoice=0

group by cte1.IDProject,prfDelay.[Value],cte1.[per]

)

 

Select * from cte3

 

GO

 

 

 

There is still some confusion related ur problem to understand me

U are writing

 

>in the B column I will have these rows:
Project A 990 + 132 = 1122 (all ProformaDelays that have CreatedInvoice set to false)
Project B 495 + 66 = 561 (all ProformaDelays that have CreatedInvoice set to false)

 BUt There is no linking of 132  as calcluated from percent with Project AConfused

 

Thats Why I am not able to get ur exact answer wht u r asking

Also Please Show The Test Output Result u want


RAGHAV

MVP ASP/ASP.Net Read My Blog


MARK THE POST AS ANSWER IF IT HELPS U.


"Success doesn't come to you…you go to it."--Marva Collins




"Success does not come to those who wait . . . and it does not wait for anyone to come to it." Anonymous


0
raghav_khunger
12/27/2008 6:59:28 PM

raghav_khunger:
>in the B column I will have these rows:
Project A 990 + 132 = 1122 (all ProformaDelays that have CreatedInvoice set to false)
Project B 495 + 66 = 561 (all ProformaDelays that have CreatedInvoice set to false)
 

 

My error!!!

The delay of 200 is 100% for project B (because in delay's Proforma I've only steps (one step) about the same project (B Project))

<<
In the second proforma I've
200 for Project B
The % using the total (200):
100% for B
>>

 

Your example is OK! But I would like to see 3 columns

and in the first column don't want to see 1999 for PRJ A and 999 for PRJ B

but...

990 for PRJ A and 495 for PRJ B  (ProformaDelays.[CreatedInvoice] set to 1)

 and in the second column:

 990 for PRJ A and 495+200 for PRJ B  (ProformaDelays.[CreatedInvoice] set to 0)

 and in the third column:

Project B = 800 (all the invoices created with IDProjectStep > zero)

 

Thanks for help me!!!

0
nixor
12/28/2008 10:57:26 AM

Hi,nixor

Try This I ahve Shoen U The The Output of three columns u want at last

U might Have to play with decision to take inner join s or left join for few cases as I havnot seen ur all data

 

;with cte as

(

Select

profdtl.IDProforma,sum(profdtl.Value) as [Val]

from [Projects]

inner join [ProjectSteps] prostep

on [Projects].IDProject=prostep.IDProject

inner join [ProformaDetail] profdtl

on prostep.IDProjectstep=profdtl.IDProjectstep

group by profdtl.IDProforma

)

, cte1 as

(

Select

prostep.IDProject,profdtl.IDProforma,sum(profdtl.Value) as [Val],

(sum(profdtl.Value)/cte.[Val] ) as per

from [Projects]

inner join [ProjectSteps] prostep

on [Projects].IDProject=prostep.IDProject

inner join [ProformaDetail] profdtl

on prostep.IDProjectstep=profdtl.IDProjectstep

inner join cte

on cte.IDProforma=profdtl.IDProforma

group by prostep.IDProject,profdtl.IDProforma,cte.[Val]

)

,cte2 as

(

Select cte1.IDProject,(sum(prfDelay.[Value]) * cte1.[per]) as col1

from [ProformaDelays] prfDelay

inner join cte1

on prfDelay.[IDProforma]=cte1.[IDProforma]

where prfDelay.CreatedInvoice=1

group by cte1.IDProject,prfDelay.CreatedInvoice,cte1.[per]

)

,cte3 as

(

Select cte1.IDProject,(sum(prfDelay.[Value]) * cte1.[per]) as col1

from [ProformaDelays] prfDelay

inner join cte1

on prfDelay.[IDProforma]=cte1.[IDProforma]

where prfDelay.CreatedInvoice=0

group by cte1.IDProject,prfDelay.[Value],cte1.[per]

),

cte4 as

(

Select

[Projects].IDProject,sum(invdtl.[value]) as val

from [Projects]

inner join [ProjectSteps] prostep

on [Projects].IDProject=prostep.IDProject

inner join [InvoiceDatail] invdtl

on prostep.IDProjectstep=invdtl.IDProjectstep

where prostep.IDProjectstep not in

(

Select IDProjectstep from [ProformaDetail]

)

group by [Projects].IDProject

 

)

Select [Projects].ProjectCode,(cte2.col1) as col1,sum (cte3.col1) as[col2],

coalesce(cte4.val,0) as col3

from cte3

inner join cte2 on

cte3.IDProject=cte2.IDProject

left outer join cte4

on cte3.IDProject=cte4.IDProject

inner join [Projects]

on cte3.IDProject=[Projects].IDProject

group by [Projects].ProjectCode,cte2.col1,cte4.val

 

GO

 

--Output

ProjectCode                       col1                               col2                    col3

A                                       999.999000             999.999000           0.0000

B                                     499.999500               699.999500         800.0000

(2 row(s) affected)

 


RAGHAV

MVP ASP/ASP.Net Read My Blog


MARK THE POST AS ANSWER IF IT HELPS U.


"Success doesn't come to you…you go to it."--Marva Collins




"Success does not come to those who wait . . . and it does not wait for anyone to come to it." Anonymous


0
raghav_khunger
12/28/2008 7:15:29 PM

Greeeeeat!!! You're a GURU!!!

 
This query is very complex!!! Now I'll study it!!

If I choose the Left Join ... could I've some problems??

Do you have used INNER or LEFT??

Thanks

0
nixor
12/28/2008 8:59:19 PM
Hi!! Can you tell me the differences between using INNER of LEFT join?

Thanks
0
nixor
1/3/2009 10:57:51 AM

 Hi,nixor

nixor:
Hi!! Can you tell me the differences between using INNER of LEFT join?

Thanks

It Would be better  That I f u post Ur  query of diff In new thread So that Others Can Also Help U.


RAGHAV

MVP ASP/ASP.Net Read My Blog


MARK THE POST AS ANSWER IF IT HELPS U.


"Success doesn't come to you…you go to it."--Marva Collins




"Success does not come to those who wait . . . and it does not wait for anyone to come to it." Anonymous


0
raghav_khunger
1/3/2009 10:59:45 AM
Reply:

Similar Artilces:

linking a sql field to point to the value of another sql field
is it possible to create hyperlinks in powerbuilder such that if you retrieve two fields from a database.. and want to display the first field on the screen and have it link to the value of the other field (when clicked) which is actually a URL address, you can? any help will be appreciated. thank you! ...

SQL query that works in SQL Server Management Studio, but doesn't on .NET 2.0 page
SELECT favorites.FID, favorites.filename, favorites.username, files.status, files.private, files.views, files.title FROM favorites INNER JOIN files ON favorites.filename = files.filename WHERE (favorites.username = @username) AND (files.status IS NULL) AND (files.private = @private)@private is manually set to 'no'@username is set to profile.usernamewhen I run the above query in microsoft sql server mgmt studio express on the database, it presents all the information i'm asking for very easily. however when i try to implement this query on a aspx .net2.0 page with teh sqldatasourc...

saw that some coders insert encrypted fields which are users passwords in the SQL database. How do we do that. Is that a feature of SQL server or do we do that in the code. also , how do we manage to
 saw that some coders insert encrypted fields which are users passwords in the SQL database. How do we do that. Is that a feature of SQL server or do we do that in the code. also , how do we manage to read the password value from the database when it s all encrypted and looks like weird characters in the password users table. What .Net feature allows that and is there any way to do that in SQL server itself. ThanksThanks a lot, I appreciate your taking the time to help me. If you're talking about saving passwords in aspnet_db, this Encryption is done by MembershipProvider automati...

Converting a gridView using an Sql Datasource to a gridview who is not using an SQL DataSource
How can i rewrite this page so that i doesnt make use of an SqlDataSource?  <%@ Control Language="C#" AutoEventWireup="true" CodeFile="LoginHistory.ascx.cs" Inherits="Private_UserControls_WebUserControl" %> <html> <head> <%--<link rel="stylesheet" href="BaldwinPortal.css" mce_href="BaldwinPortal.css" type="text/css" />--%> <title>Login History</title> </head> <body style="margin:0 0 0 0;padding:0 0 0 0"> ...

How to save a picture in a SQL image field and how to show a picture from a SQL image field
HI! I am searching now a real long time in the forum and I found a lot of stuff to this topic. But please help me it doesn't work! On my webapplication there is a possibility to save a private picture.If a new user is saved in the database I want to save a fixed picture as startpicture.Dim picturePath As String = Request.ServerVariables("APPL_PHYSICAL_PATH") & "img\anonymous.jpg" Please tell me the way how to save this anonymous.jpg from the picturePath into the database and after that how to display the picture. Thank you! .         ...

Managing SQL Express Databases with standard version of SQL Server Management Studio
I have a windows 2003 server which has SQL 2005 Express with advanced services installed on it. Then a few weeks back the company purchased SQL server Standard Edition which comes with SQL Server Management Studio (which has more features than SQL Server Management Studio Express currently installed on the server where sql express is running)I have been trying to schedule a maintanance plan on the SQLExpress Instance database from the SQL Server Management Studio that came with the standard version of sql but i have not been able to have all SQL Server Management Studio functionality availab...

I imported a SQL Table into SQL DataBase, But I can not update this table even with SQL Server management Studio
I imported a SQL Table into SQL DataBase, But I can not update this table even with SQL Server management Studio When I change any data on mentioned table above, Red exclamation sign appears left of the record . How can I correct this problem?  Thanks. Try running the UPDATE via Query Analyzer...and see if you get an error.***********************Dinakar NethiLife is short. Enjoy it.***********************...

ADO.NET returns different colum value when compared to View results in SQL 2005 Management Studio
I have a complex view in my sql 2005 database. The view returns a column that could be null (as the result of a left outer join). The coulmn that is returned is an integer. Everything works fine if I run the view from SQL 2005 Management Studio. My column value is always null if I use ADO.NET's SqlAdapter to return a DataTable. Has anybody seen this behaviour before? Any help appreciated. Regards, Paul. Hi Paul, From you description, I understand that when you query data from the database view, the destination column in the DataSet is always null. This could be caused by a w...

UserID field in SQL Express won't match to SQL 2000 field
Hi - I am using .net2, with Sql Express for the Users/Membership etc, and a SQL 2000 database for my actual web application.I want to associate my user in the aspnet_Users table of the SQL Express database, with records from the SQL 2000 database.In SQL Express the datatype for the UserID field is uniqueidentifier, length 16.  In SQL 2000 therefore, I added a table, with a column name of UserID as uniqueidentifier, length 16.When I try to insert the UserID from SQL Express into the UserID field of the SQL 2000 table, I get the error: Guid should containt 32 digits with 4 dashes (xxxxxxx...

This version of Microsoft Sql Server Management Studio can only be used to connect to Sql Server 2000 and Sql Server 2005 servers
When I connect SQL Server 2005 to a remote database i get the error: "This version of Microsoft Sql Server Management Studio can only be used to connect to Sql Server 2000 and Sql Server 2005 servers". Can I confirm it is because: the remote db is of SQL Server 2008? There is a soln mentioned in http://forums.asp.net/t/1324047.aspx Did anybody try this (installing 2008 express: http://www.microsoft.com/express/sql/download/)? I am just checking again, whether it will disturb my existing SQL Server installation, in any means...? Though it is mentioned that it will g...

SQL Advantage and SQL Server Manager download
I would like to download SQL Advantage and SQL Server Manager (client applications to run on NT 4.0) for Sybase system 11. Somehow I am not able to find the download link. Any help will be appreciated. ...

SQL Schema and Managed Code (SQL-CLR)
I have a SQL Server project in Visual Studio 2005 which deploys an assembly to SQL Server 2005 containing various stored procedures user defined functions.  Is there any way to tell Visual Studio to drop/create the stored procedures in a schema other than dbo?    ie:  User.ChangePassword instead of dbo.ChangePassword.  Well, you could catch more fish! dbo is the default schema of the user. You can specify the schema of stored procedure: CREATE { PROC | PROCEDURE } [schema_name.] procedure_name ....Sincerely,Young Fang When I deploy using Visual Studio, ...

Updating non-nullable field in SQL with default value through dynamic field
Hi, I'm running Server 2008 and the 3.5 sp 1 Dynamic Data. I've got a Dynamic Data application using Entity Framework for its model, and I have a non-nullable datetime field that I don't want to scaffold; I want the database's default value to go in every time. In my partial class in the entity project, I've decorated the field with [Scaffold(false)], and I've also set the field setter to be private. The field does not render out to the page (good), but it still expects a value and breaks when I try to submit the rest of my fields exposed in a DetailsView control. I...

How do I check to see if a value returned from a sql datasource equals a listitem value
I'm trying to see if this listitem equals a value returned in my sql data source, but I get keep getting a error saying  'Item' is not a member of 'System.Web.UI.WebControls.SqlDataSource'.    How do I check if the employeeID returned is equal to the li.value? For Each li As ListItem In BillingEmployeeID.Items If li.Value = getEmployeewithMostOpenClient.Item("employeeID") ThenxxxxxxxEnd If NextNever make important decisions on a Monday! Try Items.FindByValue() or Items.FindByText() method. Something like this... string szValueFromDataso...

Web resources about - For SQL gurus... how to manage % with fields value - asp.net.sql-datasource

Manage Well
indie thoughts on strategy, management, leadership and execution of software development...

Manage, Belgium - Wikipedia, the free encyclopedia
... 50°30′N 04°14′E  /  50.500°N 4.233°E  / 50.500; 4.233 Coordinates : 50°30′N 04°14′E  /  50.500°N 4.233°E  / 50.500; 4.233 Manage is a ...

How To Manage Your Social Media Interns And Facebook ROI
Social media interns aren’t just constant reminders of how little you know about the independent music scene: They’re also excellent resources ...

Manage multiple store Facebook Pages using Locations - Facebook for Business
... any of your stores to be found with your main Facebook Page or within a Facebook search. With Facebook Locations, you can connect and manage ...

A new way for people to manage app activity
... actions . From the plugin on sites such as Airbnb , people can set the default Facebook audience for activity shared from your app, or manage ...

Facebook SPMD Kenshoo hires Michel van Woudenberg to manage APJ region
Kenshoo, a Facebook Strategic Preferred Marketing Developer, announced that the company has hired Michel van Woudenberg as Asia Pacific Japan ...

A new dashboard to help you monitor and manage your Twitter account
Today, we’re continuing to roll out to all users the Twitter data dashboard — a new tool to help you monitor and manage your account. From the ...

Manage Your Leads - LinkedIn
Manage Your Leads is the online meeting place for B2B Sales professionals and business owners to exchange ideas on innovative sales strategies ...

How do Bill Gates, Larry Page, Mark Zuckerberg and Jack Dorsey manage their email? - Quora
I am curious how many hundreds or thousands of emails they receive daily and how do they manage them? Do they have an assistant that filters ...

Photos+: The best way to manage photos on your phone. on the App Store on iTunes
Get Photos+: The best way to manage photos on your phone. on the App Store. See screenshots and ratings, and read customer reviews.

resources last updated: 11/20/2015 3:26:51 PM