Click here to Skip to main content
15,945,119 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
create proc select_report5
@y int,@z nvarchar(12),@x nvarchar(50)
as
select [Import year],[import month],[Sender City],[Receiver City],(select count([Acctual Time]) from report5
where (([Acctual Time]='d+1') and ([Acctual Time]!='d+2')and ([Acctual Time]!='d+3')and ([Acctual Time]!='d+4')and ([Acctual Time]!='d+5')and ([Acctual Time]!='d+6')and ([Acctual Time]!='>d+6')) and ([Sender City]=[Receiver City]) and ([Import year]=@y) and ([Import month]=@z) and ([Sender City]=@x)) as [d+1],(select count([Acctual Time])from report5
where (([Acctual Time]='d+2') and ([Acctual Time]!='d+1')and ([Acctual Time]!='d+3')and ([Acctual Time]!='d+4')and ([Acctual Time]!='d+5')and ([Acctual Time]!='d+6')and ([Acctual Time]!='>d+6')) and ([Sender City]=[Receiver City]) and ([Import year]=@y) and ([Import month]=@z) and ([Sender City]=@x)) as [d+2],(select count([Acctual Time])from report5
where (([Acctual Time]='d+3') and ([Acctual Time]!='d+1')and ([Acctual Time]!='d+2')and ([Acctual Time]!='d+4')and ([Acctual Time]!='d+5')and ([Acctual Time]!='d+6')and ([Acctual Time]!='>d+6')) and ([Sender City]=[Receiver City]) and ([Import year]=@y) and ([Import month]=@z) and ([Sender City]=@x)) as [d+3],(select count([Acctual Time])from report5
where (([Acctual Time]='d+4') and ([Acctual Time]!='d+1')and ([Acctual Time]!='d+2')and ([Acctual Time]!='d+3')and ([Acctual Time]!='d+5')and ([Acctual Time]!='d+6')and ([Acctual Time]!='>d+6')) and ([Sender City]=[Receiver City]) and ([Import year]=@y) and ([Import month]=@z) and ([Sender City]=@x)) as [d+4],(select count([Acctual Time])from report5
where (([Acctual Time]='d+5') and ([Acctual Time]!='d+1')and ([Acctual Time]!='d+2')and ([Acctual Time]!='d+3')and ([Acctual Time]!='d+4')and ([Acctual Time]!='d+6')and ([Acctual Time]!='>d+6')) and ([Sender City]=[Receiver City]) and ([Import year]=@y) and ([Import month]=@z) and ([Sender City]=@x)) as [d+5],(select count([Acctual Time])from report5
where (([Acctual Time]='d+6') and ([Acctual Time]!='d+1')and ([Acctual Time]!='d+2')and ([Acctual Time]!='d+3')and ([Acctual Time]!='d+4')and ([Acctual Time]!='d+5')and ([Acctual Time]!='>d+6')) and ([Sender City]=[Receiver City]) and ([Import year]=@y) and ([Import month]=@z) and ([Sender City]=@x)) as [d+6],(select count([Acctual Time])from report5
where (([Acctual Time]='>d+6') and ([Acctual Time]!='d+1')and ([Acctual Time]!='d+2')and ([Acctual Time]!='d+3')and ([Acctual Time]!='d+4')and ([Acctual Time]!='d+5')and ([Acctual Time]!='d+6')) and ([Sender City]=[Receiver City]) and ([Import year]=@y) and ([Import month]=@z) and ([Sender City]=@x)) as [>d+6]
from report5
where [Sender City]=@x and [Import year]=@y and [Import month]=@z
group by [Sender City],[Receiver City],[Import year],[import month]
============================================
this code for show report which show the number of leetters after one day [d+1] ,after two day [d+2],...,after more than six day [>d+6]
but the problem this code can't clssification my data depend on the receiver city
note : when i choose the sender city and the month of arriving letters i must find all city which recive letters from this sender city in the same report also the number of letters
Posted

I think something like this would work, you don't need to pass the last value otherwise you could also make the Sender where clause same as the Receiver and send null if you only want to filter by the Receiver. Plus I trimmed your code since you probably don't need all those nested selects and are counting.

create proc select_report5
@y int,@z nvarchar(12),@x nvarchar(50), @u nvarchar(50) default null
as
select [Import year],[import month],[Sender City],[Receiver City],
sum( case [Acctual Time] when 'd+1' then 1 else 0 end ) as [d+1],
sum( case [Acctual Time] when 'd+2' then 1 else 0 end ) as [d+2],
sum( case [Acctual Time] when 'd+3' then 1 else 0 end ) as [d+3],
sum( case [Acctual Time] when 'd+4' then 1 else 0 end ) as [d+4],
sum( case [Acctual Time] when 'd+5' then 1 else 0 end ) as [d+5],
sum( case [Acctual Time] when 'd+6' then 1 else 0 end ) as [d+6],
sum( case [Acctual Time] when '>d+6' then 1 else 0 end ) as [>d+6]
from report5
where [Sender City]=@x
and [Receiver City] = isnull(@u, [Receiver City])
and [Import year]=@y
and [Import month]=@z
group by [Sender City],
[Receiver City],
[Import year],
[import month]
 
Share this answer
 
thanks alot it's run ok
without the last variable
:rose:
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900