Please help me out

I have written a sql procedure, i want to pass delared variable @.cnt in line131,char24
below is the PL Code:
CREATE PROCEDURE PL
@.dt varchar(10),
@.stock as bit --1/true for updated and 0/false for previous executed data
AS

set dateformat dmy
set nocount on

Declare @.sdate VARCHAR(10)
Declare @.clup as Decimal(18,2)
declare @.cnt as integer
DECLARE @.amt as decimal(18,2),@.gamt as decimal(18,2), @.damt as decimal(18,2), @.camt as decimal(18,2)

delete from temp_pl
delete from temp_bal

Select @.sdate = Convert(varchar(10),StartDate,103 ) From Param
exec show_trial @.dt
-- Now data is in temp_trial
if( @.Stock =1 )
begin
exec Closing @.sdate, @.dt
end

select party_nm as acc, dr_amt as Amtdr, cr_amt as Amtcr into #Tlist from temp_trial WHERE party_cd not in (150,152,151,38,36,33,32,35,34)
--select * from #tlist

SELECT Acc,AmtDr,AmtCr ,Type, odr into #tmpop FROM (
Select 'Opening Stock (UP)' as Acc ,'AmtDr'= case when oup >0 then oup else 0 end ,
'AmtCr'= case when oup <0 then abs(oup) else 0 end , 'T' as Type ,1 as odr From temp_stock_balance
union
Select 'Opening Stock (Ex UP)' as Acc ,'AmtDr'= case when oex >0 then oex else 0 end ,
'AmtCr'= case when oex <0 then abs(oex) else 0 end , 'T' as Type ,1 as odr From temp_stock_balance

union
Select 'Purchase (UP)' as Acc ,'AmtDr'= case when pup >0 then pup else 0 end ,
'AmtCr'= case when pup <0 then abs(pup) else 0 end , 'T' as Type ,2 as odr From temp_stock_balance
union
Select 'Purchase (Ex UP)' as Acc ,'AmtDr'= case when pex >0 then pex else 0 end ,
'AmtCr'= case when pex <0 then abs(pex) else 0 end , 'T' as Type ,2 as odr From temp_stock_balance
union
Select 'Sales (UP)' as Acc , 'AmtDr' =case when sUp < 0 then abs(sup) else 0 END ,
'AmtCr' =case when sup > 0 then sup else 0 eND, 'T' as Type ,2 as odr From temp_stock_balance
union
Select 'Sales (Ex-UP)' as Acc , 'AmtDr' =case when sex < 0 then abs(sex) else 0 END ,
'AmtCr' =case when sex > 0 then sex else 0 eND, 'T' as Type ,2 as odr From temp_stock_balance

union
Select 'Closing Stock(UP)' as Acc , 'AmtDr' =case when CUp < 0 then abs(cup) else 0 END ,
'AmtCr' =case when Cup > 0 then cup else 0 eND, 'T' as Type ,3 as odr From temp_stock_balance
union
Select 'Closing Stock(Ex-UP)' as Acc , 'AmtDr' =case when Cex < 0 then abs(cex) else 0 END ,
'AmtCr' =case when Cex > 0 then cex else 0 eND, 'T' as Type ,3 as odr From temp_stock_balance
)A

Select * into #tmpl from(
SELECT Acc, AmtDr, AmtCr ,Type ,odr
From (Select * from #tlist ) a INNER JOIN (Select party_nm,Ac_type as Type,odr From Party where Ac_type IN ('P','T') )
Acmast ON a.Acc = Acmast.Party_nm
UNION
select Acc,AmtDr,AmtCr ,Type , odr from #tmpop
) a

update #tmpl set Amtdr = abs(AmtCr) where AmtCr < 0
update #tmpl set AmtCr =abs( Amtdr) where AmtDr < 0
/*
--Gp
set @.clup = (Select 'GP' = case
when sum(AmtDr)>= sum(AmtCr) then (Sum(AmtDr) -Sum(AmtCr) )* -1

When sum(AmtDr) < Sum(AmtCr) then (Sum(AmtCr) -Sum(AmtDr) ) end From #tmpl where Type= 'T' )
*/
--TRADING A/C
SELECT Acc , AmtCr,type into #CT FROM #tmpL WHERE (AmtCr > 0 and Type ='T') order by odr Asc, Acc asc
SELECT Acc , AmtDr,type into #DT FROM #tmpL WHERE (AmtDr > 0 and Type ='T') order by odr Asc, Acc asc

SELECT IDENTITY(int, 1,1) AS ids,Acc , AmtCr,type into #CreditT FROM #CT
SELECT IDENTITY(int, 1,1) AS ids,Acc , AmtDr,type into #debitT FROM #DT

Select IDENTITY(int, 1,1) AS sr_no, * INTO #TRADE1 from
(Select d.Acc as Accdr , d.AmtDr as AmtDr , C.acc as AccCr ,C.amtCr as AmtCr ,'T' as Type from
#debitT d Full outer join #creditT c on c.ids =d.ids
)A
insert into #trade1 values(null,0,null,0,'T') --Line before sum

select @.amt=sum(isnull(amtcr,0))-sum(isnull(amtdr,0)) from #trade1

select @.damt=sum(isnull(amtdr,0)) from #trade1
select @.camt=sum(isnull(amtcr,0)) from #trade1
insert into #trade1 values (null,@.damt,null,@.camt,'T')
if @.amt>0
begin
select @.gamt=sum(isnull(amtcr,0)) from #trade1
insert into #trade1 (accdr,amtdr,type) values('Gross Profit C/F to P&L',@.amt,'T')
end
else if @.amt<0
begin
select @.gamt=sum(isnull(amtdr,0)) from #trade1
insert into #trade1 (acccr,amtcr,type) values('Gross Loss C/F to P&L',abs(@.amt),'T')
end
else
begin
select @.gamt=sum(isnull(amtdr,0)) from #trade1
end
insert into #trade1 values(null,0,null,0,'T') --Line after gross
insert into #trade1 values(null,@.gamt,null,@.gamt,'T')
insert into #trade1 values(null,0,null,0,'T') --Line after Trade gross
if @.amt>0
begin
insert into #trade1 (acccr,amtcr,type) values('Gross Profit B/F from P&L',@.amt,'P')
end
else if @.amt<0
begin
insert into #trade1 (accdr,amtdr,type) values('Gross Loss B/F from P&L',abs(@.amt),'P')
end
else
begin
insert into #trade1 values(null,null,null,null,'T')
end
Select * INTO #TRADE from #trade1 order by sr_no
------------------------------
--P/L A/C

SELECT Acc , AmtCr,type into #CP FROM #tmpL WHERE (AmtCr > 0 and Type ='P') order by odr Asc, Acc asc
SELECT Acc , AmtDr,type into #DP FROM #tmpL WHERE (AmtDr > 0 and Type ='P') order by odr Asc, Acc asc

SELECT IDENTITY(int, 1,1) AS ids,Acc , AmtCr,type into #CreditP FROM #CP
SELECT IDENTITY(int, 1,1) AS ids,Acc , AmtDr,type into #debitP FROM #DP

select @.cnt=count(SR_NO)+1 from #trade

select IDENTITY (int,@.cnt,1) AS sr_no,accdr,amtdr,acccr,amtcr,type INTO #PL_TEMP1 from #trade t WHERE t.type='P'

insert into #pl_temp1 (accdr,amtdr,acccr,amtcr,type)
Select d.Acc ,d.AmtDr, C.acc ,C.amtCr as AmtCr ,'P' as Type from
#debitP d Full outer join #creditP c on c.ids =d.ids

delete from #trade WHERE type='P'
select @.amt=sum(isnull(amtcr,0))-sum(isnull(amtdr,0)) from #PL_TEMP1

insert into #PL_TEMP1 values(null,0,null,0,'P') --line before sum

select @.amt=sum(isnull(amtcr,0))-sum(isnull(amtdr,0)) from #PL_TEMP1
insert into temp_bal values(@.amt)

select @.damt=sum(isnull(amtdr,0)) from #PL_TEMP1
select @.camt=sum(isnull(amtcr,0)) from #PL_TEMP1
insert into #PL_TEMP1 values (null,@.damt,null,@.camt,'P')
if @.amt>0
begin
select @.gamt=sum(isnull(amtcr,0)) from #PL_TEMP1
insert into #PL_TEMP1 (accdr,amtdr,type) values('Net Profit C/F to Balance Sheet',@.amt,'P')
end
else if @.amt<0
begin
select @.gamt=sum(isnull(amtdr,0)) from #PL_TEMP1
insert into #PL_TEMP1 (acccr,amtcr,type) values('Net Loss C/F to Balance Sheet',abs(@.amt),'P')
end
else
begin
select @.gamt=sum(isnull(amtdr,0)) from #PL_TEMP1
end
insert into #PL_TEMP1 values(null,0,null,0,'P') --Line after NET
insert into #PL_TEMP1 values(null,@.gamt,null,@.gamt,'P')
insert into #PL_TEMP1 values(null,0,null,0,'P') --Line after PL NET

Select * INTO #PL_TEMP from #PL_TEMP1 order by sr_no

INSERT INTO temp_PL
SELECT * FROM
(select * from #PL_TEMP
UNION
SELECT * FROM #TRADE
) a order by sr_no
GO

please help meDo not post your procedure. Just explain your problem in detail and post code in the pirticular error area.

Please help debug.

Hi,
What's wrong with the following code? Please help. I am using MS SQL
2000.
create table one (
a float NOT NULL,
b varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)
alter table one
alter column a {drop} NOT NULL
alter column b {drop} NOT NULL
;
Thanks,
Mike
Please do not multi post.
Syntax usage is incorrect. Please use the below code to modify NOT NULL to
NULL
alter table one alter column a Float NULL
alter table one alter column b Varchar(50) NULL
Thanks
Hari
"Michael" wrote:

> Hi,
> What's wrong with the following code? Please help. I am using MS SQL
> 2000.
> create table one (
> a float NOT NULL,
> b varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> )
> alter table one
> alter column a {drop} NOT NULL
> alter column b {drop} NOT NULL
> ;
> Thanks,
> Mike
>

Please help debug.

Hi,
What's wrong with the following code? Please help. I am using MS SQL
2000.
create table one (
a float NOT NULL,
b varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)
alter table one
alter column a {drop} NOT NULL
alter column b {drop} NOT NULL
;
Thanks,
MikePlease do not multi post.
Syntax usage is incorrect. Please use the below code to modify NOT NULL to
NULL
alter table one alter column a Float NULL
alter table one alter column b Varchar(50) NULL
Thanks
Hari
"Michael" wrote:
> Hi,
> What's wrong with the following code? Please help. I am using MS SQL
> 2000.
> create table one (
> a float NOT NULL,
> b varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> )
> alter table one
> alter column a {drop} NOT NULL
> alter column b {drop} NOT NULL
> ;
> Thanks,
> Mike
>

Please help debug.

Hi,
What's wrong with the following code? Please help. I am using MS SQL
2000.
create table one (
a float NOT NULL,
b varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)
alter table one
alter column a {drop} NOT NULL
alter column b {drop} NOT NULL
;
Thanks,
MikePlease do not multi post.
Syntax usage is incorrect. Please use the below code to modify NOT NULL to
NULL
alter table one alter column a Float NULL
alter table one alter column b Varchar(50) NULL
Thanks
Hari
"Michael" wrote:

> Hi,
> What's wrong with the following code? Please help. I am using MS SQL
> 2000.
> create table one (
> a float NOT NULL,
> b varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> )
> alter table one
> alter column a {drop} NOT NULL
> alter column b {drop} NOT NULL
> ;
> Thanks,
> Mike
>

PK Index name

I have table tipDok with primary key, which is varchar data type.
The name of the index(primary key) is generated automatically and is:
PK__TipDOK__1EC48A19
I select: ALL tasks- Export data in enterprice manager and copy objects
option and then select this table to be copied to some other database.
The table is copied successfully and is identical, only the name of index is
now different:
PK__TipDOK__267ABA7A
Why?
I would like that also the name is identical.
Because I have program for comparing the datatbases and it gives me the
difference between this table just because of the index name.
Then I have errors when I synchronize databases, because the index can't
be dropped.
Any idea?
lp,
Simonwhat you need is a program that recognises that the primary keys are the sam
e
and only the name is different so it renames the key. DB Ghost
(http://www.dbghost.com) does this and does it for foreign keys as well.
"simon" wrote:

> I have table tipDok with primary key, which is varchar data type.
> The name of the index(primary key) is generated automatically and is:
> PK__TipDOK__1EC48A19
> I select: ALL tasks- Export data in enterprice manager and copy objects
> option and then select this table to be copied to some other database.
> The table is copied successfully and is identical, only the name of index
is
> now different:
> PK__TipDOK__267ABA7A
> Why?
> I would like that also the name is identical.
> Because I have program for comparing the datatbases and it gives me the
> difference between this table just because of the index name.
> Then I have errors when I synchronize databases, because the index can't
> be dropped.
> Any idea?
> lp,
> Simon
>
>|||In Enterprise Manager, right-click on your database, select All Tasks, then
Generate SQL Script.
Select whatever tables you want to copy, then go to the Options tab and make
sure "Select PRIMARY keys, FOREIGN keys, defaults, and check constriants" is
selected.
Use the script this generates to create a new, empty database. Then you can
use the DTS wizard to copy the data in from the old database.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"simon" <simon.zupan@.iware.si> wrote in message
news:iQ%Td.9584$F6.1864547@.news.siol.net...
> I have table tipDok with primary key, which is varchar data type.
> The name of the index(primary key) is generated automatically and is:
> PK__TipDOK__1EC48A19
> I select: ALL tasks- Export data in enterprice manager and copy objects
> option and then select this table to be copied to some other database.
> The table is copied successfully and is identical, only the name of index
is
> now different:
> PK__TipDOK__267ABA7A
> Why?
> I would like that also the name is identical.
> Because I have program for comparing the datatbases and it gives me the
> difference between this table just because of the index name.
> Then I have errors when I synchronize databases, because the index can't
> be dropped.
> Any idea?
> lp,
> Simon
>

Pivot Query Question - Can I have 2 columns in a Pivot?

I want two columns in Pivot format. Is it possible.

I have the following table:
CREATE TABLE CONDITION DETAILS
(
CONDITIONID INT,
NAME VARCHAR(100),
DESCRIPTION VARCHAR(50),
USERNAME VARCHAR(50),
NOTIFY_FREQUENCY VARCHAR(10))

INSERT INTO [CONDITION] ([CONDITIONID],[NAME],[DESCRIPTION],[USERNAME],[NOTIFY_FREQUENCY])VALUES(1,'Receipt of statements','First Person Notified','JC','Daily')
INSERT INTO [CONDITION] ([CONDITIONID],[NAME],[DESCRIPTION],[USERNAME],[NOTIFY_FREQUENCY])VALUES(1,'Receipt of statements','Second Person Notified','BG','Weekly')
INSERT INTO [CONDITION] ([CONDITIONID],[NAME],[DESCRIPTION],[USERNAME],[NOTIFY_FREQUENCY])VALUES(1,'Receipt of statements','Third Person Notified','BG','Monthly')

Now I have the following statement
SELECT * FROM
(SELECT CONDITIONID, NAME, DESCRIPTION, USERNAME
--, NOTIFY_FREQUENCY
FROM CONDITION) SOURCEQUERY
PIVOT (MIN(USERNAME) FOR [DESCRIPTION] IN ([First Person Notified], [Second Person Notified], [Third Person Notified])
) AS PIVOTTABLE

Output:
CONDITIONID NAME First Person Notified Second Person Notified Third Person Notified
1 Receipt of statements JC BG BG

I also want the Frequency to be displayed, I want output to be like
1 Receipt of Statements Daily JC Weekly BG Monthly BG

Can anyone suggest. Thanks.

The first thing that I want to do is to give kudos for such an outstanding job of perparing your question. Having the table definition and the insert statements for the test data made this much easier to set up for testing. Nice Work!

I have to confess that my work on this might not be as good of an effort as the question. I have never done a double-pivot so I really am not sure what is the best way to go about it. This seems to work. I would really like commentary from Umachandar or someone similar who is familar with a "best approach."

SELECT conditionId,
name,
rtrim(substring([First Person Notified], 11, 50)) as [1st Person],
rtrim(left([First Person Notified], 10)) as [1st Frequency],
rtrim(substring([Second Person Notified],11, 50)) as [2nd Person],
rtrim(left([Second Person Notified],10)) as [2nd Frequency],
rtrim(substring([Third Person Notified], 11, 50)) as [3rd Frequency],
rtrim(left([Third Person Notified],10)) as [3rd Person]
FROM ( SELECT CONDITIONID,
NAME,
DESCRIPTION,
cast (isnull(NOTIFY_FREQUENCY, '') as char(10))
+ username
as pivotStuff
FROM CONDITION
) SOURCEQUERY
PIVOT ( MIN(pivotStuff) FOR [DESCRIPTION]
IN ( [First Person Notified],
[Second Person Notified],
[Third Person Notified]
) ) AS PIVOTTABLE

-- conditionId name 1st Person 1st Frequency 2nd Person 2nd Frequency 3rd Frequency 3rd Person
-- -- - -- - -- - -- -
-- 1 Receipt of statements JC Daily BG Weekly BG Monthly

|||

Thanks. This sure works. Had thought about this solution but was not sure if it was right way to do, so had posted my query on to the forums.

Please let me know if there is a better way to achive the above results.

Pivot query error

I'm trying to simulate an Access query in SQL and reached a dead end.

SELECT * FROM (SELECT
AccountNumber
, Convert(varchar, startDate, 101) AS startDate
, Convert(varchar, resultDate, 101) AS resultDate
FROM [TableA]) AS D
PIVOT(SUM(Amount) FOR resultDate IN([02/27/2006],[02/28/2006],[03/01/2006],[03/02/2006],[Outstanding])) AS P

Now I want to add two things:
1) Add one more column 'Total' containing the total of amount for each startDate
2) Add a column 'OutStanding' in PIVOT which should contain SUM(Amount) where resultDate IS NULL.

How do I do it?declare @.TableA table (
AccountNumber int,
StartDate datetime,
ResultDate datetime,
amount decimal(10,2)
)

insert into @.TableA values (100, '02/27/2006', '02/28/2006', 300)
insert into @.TableA values (100, '02/28/2006', null, 500)
insert into @.TableA values (100, '03/01/2006', null, 800)
insert into @.TableA values (100, '02/27/2006', null, 100)
insert into @.TableA values (100, '02/27/2006', null, 200);

insert into @.TableA values (200, '02/27/2006', null, 50)
insert into @.TableA values (200, '02/28/2006', null, 100)
insert into @.TableA values (200, '03/01/2006', null, 200)
insert into @.TableA values (200, '02/27/2006', null, 50)
insert into @.TableA values (200, '02/27/2006', null, 100);

insert into @.TableA values (300, '02/27/2006', '02/28/2006', 50)
insert into @.TableA values (300, '02/28/2006', '03/01/2006', 100)
insert into @.TableA values (300, '03/01/2006', '03/05/2006', 200)
insert into @.TableA values (300, '02/27/2006', null, 50)
insert into @.TableA values (300, '02/27/2006', null, 100);

insert into @.TableA values (400, '02/27/2006', '02/28/2006', 50)
insert into @.TableA values (400, '02/28/2006', '03/01/2006', 100)
insert into @.TableA values (400, '03/01/2006', '03/05/2006', 200)
insert into @.TableA values (400, '02/27/2006', '03/05/2006', 50)
insert into @.TableA values (400, '02/27/2006', '03/07/2006', 100);

WITH Tot_CTE (AccountNumber, Total, OutStanding)
AS
(
SELECT AccountNumber, SUM(Amount), SUM(case when ResultDate is null then Amount else 0 end)
FROM
@.TableA
GROUP BY AccountNumber
)
SELECT a.*,b.Total,b.OutStanding
FROM (
SELECT * FROM (SELECT
AccountNumber
, Convert(varchar, startDate, 101) AS startDate
--, Convert(varchar, resultDate, 101) AS resultDate
,Amount
FROM @.TableA) AS D
PIVOT (SUM(Amount) FOR StartDate IN([02/27/2006],[02/28/2006],[03/01/2006],[03/02/2006])) AS P
) as a
join
tot_CTE b
on
a.accountNumber = b.AccountNumber|||

PIVOT has lot of restrictions and you cannot generate multiple aggregates for example. So It is easier to do this using a standard SQL query. And it is much more efficient since you can compute all the necessary aggregates in one pass of the data. Try query below. I had to guess the details about your schema and what each column means. But you should get the idea.

SELECT t.AccountNumber

, t.StartDate

, SUM(CASE WHEN t.resultDate IS NULL THEN t.Amount END) as OutStanding

, SUM(CASE t.resultDate WHEN '20060227' THEN t.Amount END) as "20060227"

, SUM(CASE t.resultDate WHEN '20060228' THEN t.Amount END) as "20060228"

...

, SUM(t.Amount) AS Total

FROM TableA AS t

WHERE t.resultDate is null

or t.resultDate between '20060227' and '20060302' -- modify accordingly

GROUP BY t.AccountNumber, t.StartDate -- may need to strip time part depending on your data