Stored Procedure Problem

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hi,

I have a stored procedure that I am having a problem with. Who can help me?

my code is :

{---Start

Select [samples].[month],
[samples].[year],
[samples].[money],
Tvarizi.[money] AS TVM,
Tvarizi.BillNum AS TVB,
Tvarizi.DateOfPayBill AS TDOPB,
Tcash.[Money] AS TCM,
Tcash.BillNum AS TCB,
samples.[month] AS samMon,
Tcash.Recievedate AS TCRD,
case isnull(Tvarizi.WCashID,0) when 0 then TCashTCompany.CompanyName else TvariziTCompany.CompanyName end AS TCom
--case isnull(Tvarizi.WCashID,0) when 0 then TCashMoney.TotallValue else TvariziMoney.TotallValue end AS TMon
case isnull(Tvarizi.WCashID,0) when 0 then TCashSample.TotallValue else TvariziSample.TotallValue end AS TSample

From [samples]

Left outer join TVarizi On substring( TVarizi.DateOfPayBill,3,5)=[samples].Date
Left outer join TCash On substring( TCash.Recievedate,3,5) = [samples].Date
Left outer join TCompany as TCashTCompany On (TCashTCompany.CompanyName = TCash.companyName) 
Left outer join TCompany as TvariziTCompany On (TvariziTCompany.CompanyName = Tvarizi.companyName) 
--Left outer join [Money] as TCashMoney On (TCashTCompany.CompanyName = TCashMoney.companyName) 
--Left outer join [Money] as TvariziMoney On (TvariziTCompany.CompanyName = TvariziMoney.companyName) 
Left outer join samples as TCashsamples On (samples.CompanyName = TCashsamples.companyName) 
Left outer join samples as Tvarizisamples On (samples.CompanyName = Tvarizisamples.companyName) 
where  [samples].Date between '86/01' and '89/01'
---End code}

that Tvarizi,Tcash and samples are my tables that I attach their code in below link:

http://www.4shared.com/file/QrZdzOR_/CodeOfTables.html[^]

I need to make a report between two dates by repeating months. For example: between two months March until September.(I need data in each month also in Iran we use Persian calender)

Please help me it is urgent.

Thanks.

Posted 20-Dec-10 22:51pm

dearabi

Updated 20-Dec-10 22:56pm

Slacker007

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

nortee · Answer 1 · 2010-12-20T23:52:00

Hello,

NOTE: I have not looked at the code for the sql statements yet, nor do I know how the persian calender works.

I reckon one of the more easier solutions that you could implement would be to have 2 fields int the table structure, one to represent the first field of your date and the other for the second field. Then you could do something like this:

SQL

/*
  You are passing in 2 dates (in Persian calender format). I'm not sure what that is so I am assuming that it is in the form [firstpart][seperator][secondpart] which basically would be a 'string'... I'm assuming that the from and to date parameters are declared as @FromDate and @ToDate respectively and that they are strings... Consider this implementation based on the suggestion of 2 fields in your table structure in your stored procedure.
*/
DECLARE
  @SeparatorPos int,
  @FromDateField1 int,
  @FromDateField2 int,
  @ToDateField1 int,
  @ToDateField2 int ;

SET @SeparatorPos = SELECT CHARINDEX('/', @FromDate);
SET @FromDateField1 = CAST(SUBSTRING(@FromDate, 1, (@SeparatorPos - 1)) AS int);
SET @FromDateField2 = CAST(SUBSTRING(@FromDate, (@SeparatorPos + 1), LEN(@FromDate)) AS int);

SET @SeparatorPos = SELECT CHARINDEX('/', @ToDate);
SET @ToDateField1 = CAST(SUBSTRING(@ToDate, 1, (@SeparatorPos - 1)) AS int);
SET @ToDateField2 = CAST(SUBSTRING(@ToDate, (@SeparatorPos + 1), LEN(@ToDate)) AS int);

/*
  Some SQL like a select or update or whatever
  then use something like this...
*/
WHERE
  (
    ([TableDateFirstField] BETWEEN @FromDateField1 AND @ToDateField1)
    AND
    ([TableDateSecondField] BETWEEN @FromDateField2 AND @ToDateField2)
  )
/*
  Some other filter criteria that you want to add in...
*/

Hope this helps :)...

DISCLAIMER: This is by no means the 'most correct' way of doing this. I take no responsiblity if this code, for some or other reason, causes the lifecycle of the common sea cucumber to become irrevocably broken... ;P