Want to get the values of shipment Date (Document No. wise) just before the @REPORT_DATE parameter( when @REPORT_DATE >= Max(Shiment Date). Please help!!!!!

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008R2

SQL

Alter procedure [SO_PENDING_ORDERS]
@REPORT_DATE [DATETIME]
,@Order_Type NVARCHAR (50)

AS
select 
Case when [Order Type]=2 then 'CISCO'
       when [Order Type]=3 then 'AMC'
        when [Order Type]=1 then 'CABLE'
          Else '[Indirect Order]'
           end as [Order Type],
[Document Type],[Document No],[Customer Name],[Order Date],[Order Amount]
,[Total Order Quantity],
 
CASE WHEN (@REPORT_DATE >=MAX([Shipment Date]))
THEN 
SUM([total Shipped Qty])
  Else (Select SUM([total Shipped Qty]) From [SO SHIPMENT/PENDING STATUS] where [Shipment Date]<=@REPORT_DATE)
End as [TOTAL SHIPPED QUANTITY],

Case WHEN (@REPORT_DATE >= MAX([Shipment Date]))
then
MAX([Shipment Date])
Else (Select MAX([Shipment Date]) From [SO SHIPMENT/PENDING STATUS] where [Shipment Date]<=@REPORT_DATE)
 --Else [Shipment Date]
 End as [Shipment Date]


--,Case 
--when @REPORT_DATE > MAX([Shipment Date])
--then
--MAX([Shipment Date]) END AS [SHIPMENT DATE]

from 
[SO SHIPMENT/PENDING STATUS]
where ([Order Type] IN (SELECT * FROM Split(@Order_Type,',')))

GROUP BY
[Document Type],[Order Type],[Document No],[Customer Name],[Order Date],[Order Amount]
,[Total Order Quantity]
--, [total Shipped Qty],[Shipment Date]

Posted 8-Sep-14 20:06pm

Member 11068310

Updated 8-Sep-14 21:26pm

Magic Wonder

v2

Add a Solution

Comments

OriginalGriff 9-Sep-14 2:17am

And?
What does it do that you didn't expect, or not do that you did?

Magic Wonder 9-Sep-14 3:29am

What is the error you are getting?

Member 11068310 9-Sep-14 5:06am

When I add group by statement as below:

Else (Select SUM([total Shipped Qty]) From [SO SHIPMENT/PENDING STATUS] where [Shipment Date]<=@REPORT_DATE Group by [Document No)
It gives the error (returns multiple row)
Although what I want is, to get the total i.e Sum of [total Shipped Qty] where [Shipment Date] is < @REPORT_DATE(Variable) and group by [Document No]. Please help , i am stuck for last 4 days..

Magic Wonder 9-Sep-14 5:28am

Since you are having actual data with you, you simply try to execute that piece of code separately and check what is the output you are getting? May be that will help you check and rectify the issue.

Member 11068310 9-Sep-14 5:45am

Sorry, I said, Its giving the correct result when @Report_Date>= Max[Shipment Date].
However, when @Report_Date< Max[Shipment Date] then its giving @Report_Date as Shipment Date for all the [document No].
Although I want in this condition that biggest date before the @Report_Date should be the [Shipment Date] that would be different for different Document No

Magic Wonder 9-Sep-14 6:08am

It will return multiple rows as per your details provided. And it will give error if you will use it in sub query. Better to try it in main query using some joining conditions. Or Put a check for document No.

Member 11068310 9-Sep-14 6:18am

How to put check for document No??

Magic Wonder 9-Sep-14 6:28am

Add one more parameter to your proc as document no and add a flag in where condition.

Member 11068310 9-Sep-14 6:21am

I have 2 Conditions 1> @Order_Date>= Max(Shipment Date) 2>@Order_Date< Max(Shipment Date).

Magic Wonder 9-Sep-14 6:29am

i think it is @Report_Date...

Member 11068310 9-Sep-14 7:09am

O yeah, Again sorry.. Its @Report_Date

Member 11068310 9-Sep-14 7:11am

Yess.. I can do but as per management instruction, I can not add Document No Parameter..

Magic Wonder 9-Sep-14 7:21am

In your case, sub query have to return single value for success.

One more suggestion that have a re-look to your business requirement and then check the output. Also, mean while if possible than share your tables structure with required requirements. Sample data will be more helpful if possible.

Member 11068310 10-Sep-14 2:22am

Hey Magic.. Thanks a lot for ur support.
Now, I passed Document No Parameter and did it. The code is now like this:

Alter Procedure [dbo].[SO SHIPMENT/PENDING STATUS2]
@REPORT_DATE [DATETIME]
,@Order_Type NVARCHAR (50)
,@Document_No NVARCHAR (max)
as

if

(@REPORT_DATE >=(Select MAX([Shipment Date]) From [SO SHIPMENT/PENDING_STATUS]))
select
Case when [Order Type]=2 then 'CISCO'
when [Order Type]=3 then 'AMC'
when [Order Type]=1 then 'CABLE'
Else '[Indirect Order]'
end as [Order Type],
[Document Type],[Document No],[Customer Name],[Order Date],[Order Amount]
,[Total Order Quantity],SUM([total Shipped Qty])as [TOTAL SHIPPED QUANTITY],
SUM([Total Shipped Amount])as [Total Shipped Amount],MAX([Shipment Date]) as [Shipment Date]
from
[SO SHIPMENT/PENDING_STATUS]
where ([Order Type] IN (SELECT * FROM Split(@Order_Type,',')))
and ([Document No] IN (SELECT * From Split(@Document_No,',')))
GROUP BY
[Document Type],[Order Type],[Document No],[Customer Name],[Order Date],[Order Amount]
,[Total Order Quantity]

Else

select
Case when [Order Type]=2 then 'CISCO'
when [Order Type]=3 then 'AMC'
when [Order Type]=1 then 'CABLE'
Else '[Indirect Order]'
end as [Order Type],
[Document Type],[Document No],[Customer Name],[Order Date],[Order Amount]
,[Total Order Quantity],SUM([total Shipped Qty])as [TOTAL SHIPPED QUANTITY],
SUM([Total Shipped Amount])as [Total Shipped Amount],MAX([Shipment Date]) as [Shipment Date]
from
[SO SHIPMENT/PENDING_STATUS]
where [Shipment Date]<=@REPORT_DATE
--and [Document No] = @Document_No
and([Order Type] IN (SELECT * FROM Split(@Order_Type,',')))
and ([Document No] IN (SELECT * From Split(@Document_No,',')))
GROUP BY
[Document Type],[Order Type],[Document No],[Customer Name],[Order Date],[Order Amount]
,[Total Order Quantity]

Magic Wonder 10-Sep-14 2:29am

Cheers...your always welcome.

Member 11068310 10-Sep-14 4:54am

Hello Magic,
from
[SO SHIPMENT/PENDING_STATUS]
where [Shipment Date]<=@REPORT_DATE

Here, It retrieves record where there is a shipment date, in case if there is no shipment date before @Report_Date rather the shipment date is after the @Order_Date, how to retrieve those records?? Any Idea??

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)

Magic Wonder · Answer 1 · 2014-09-09T23:32:00

Hi,

for your comment....

from [SO SHIPMENT/PENDING_STATUS] where [Shipment Date]<=@REPORT_DATE

try this ...

SQL

Select * from [SO SHIPMENT/PENDING_STATUS] 
where 
CASE WHEN ISNULL([Shipment Date],'')='' then ISNULL([Shipment Date],'') 
ELSE  ISNULL([Shipment Date],'') <=@REPORT_DATE END

Hope this will help you.

Cheers