November 15, 2010 at 12:59 am
Hi Sql Experts
in this query My OB Bale Qty on 1st date Shud get add with pcs qty
AND update my OB Bale Qty on 2nd date Shud get add with pcs qty on 2nd date AND update my OB Bale Qty on 3nd date Shud get add with pcs qty on 3nd date
WHERE my PCS qty remains constant
Here is my query nd iam missing some dates also so pls see dates also if on particular date no data is their then it shud show null
SELECT T2.[U_CHAP] 'ChapterId',
(SELECT [U_Quantity] FROM [dbo].[@RGINPUT] WHERE [Name]= T2.[U_CHAP])'Opening Bale Qty',
(SELECT [U_Bale] FROM [dbo].[@RGINPUT] WHERE [Name]= T2.[U_CHAP])'Bale',
CONVERT(VARCHAR(10),T0.[DocDate],103) AS PcsDate ,SUM( T1.[Quantity]) as PcsQuantity,
(SELECT COUnt(A.[U_BaleNoNew]) AS DATE FROM OIBT A
WHERE A.[InDate] >= '09/01/2010' AND A.[InDate] < ='09/30/2010' AND A.[ItemCode] like 'FF%' AND
A.[InDate]= T0.[DocDate] AND A.[U_Flag] is Not Null
GROUP BY A.[InDate])'BaleCount'
FROM OIGN T0 INNER JOIN IGN1 T1 ON T0.DocEntry = T1.DocEntry,
OWOR T2 WHERE T0.[DocDate] >= '09/01/2010' AND T0.[DocDate] < ='09/30/2010' AND
T1.[ItemCode] like 'FF%' AND T0.[Ref2] =CONVERT(VARCHAR,T2.[DocNum]) AND T2.[U_CHAP] ='52093190'
GROUP BY T0.[DocDate],T2.[U_CHAP]
ORDER BY T2.[U_CHAP]ASC,T0.[DocDate]ASC
Its very Urgent please
Regards
Giri Prakash
November 15, 2010 at 2:48 am
I dont think anyone is going to dig into your query and try understanding what you are trying to acheive.
You need to post some sample data and expected o/p.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
November 15, 2010 at 2:54 am
Hi
this is my query
SELECT T2.[U_CHAP] 'ChapterId',
(SELECT [U_Quantity] FROM [dbo].[@RGINPUT] WHERE [Name]= T2.[U_CHAP])'Opening Bale Qty',
(SELECT [U_Bale] FROM [dbo].[@RGINPUT] WHERE [Name]= T2.[U_CHAP])'Bale',
CONVERT(VARCHAR(10),T0.[DocDate],103) AS PcsDate ,SUM( T1.[Quantity]) as PcsQuantity,
(SELECT COUnt(A.[U_BaleNoNew]) AS DATE FROM OIBT A
WHERE A.[InDate] >= '09/01/2010' AND A.[InDate] < ='09/30/2010' AND A.[ItemCode] like 'FF%' AND
A.[InDate]= T0.[DocDate] AND A.[U_Flag] is Not Null
GROUP BY A.[InDate])'BaleCount'
FROM OIGN T0 INNER JOIN IGN1 T1 ON T0.DocEntry = T1.DocEntry,
OWOR T2 WHERE T0.[DocDate] >= '09/01/2010' AND T0.[DocDate] < ='09/30/2010' AND
T1.[ItemCode] like 'FF%' AND T0.[Ref2] =CONVERT(VARCHAR,T2.[DocNum]) AND T2.[U_CHAP] ='52093190'
GROUP BY T0.[DocDate],T2.[U_CHAP]
ORDER BY T2.[U_CHAP]ASC,T0.[DocDate]ASC
e query lo My OB Bale Qty on 1st date Shud get add with pcs qty
AND update my OB Bale Qty on 2nd date Shud get add with pcs qty on 2nd date AND update my OB Bale Qty on 3nd date Shud get add with pcs qty on 3nd date
WHERE my PCS qty remains constant
Here is my query nd iam missing some dates also so pls see dates also if on particular date no data is their then it shud show null
this is my output
chapter id OB bale Qty Bale pcs date pcs quantity balecount
52093190131237501/09/20105072.00000012
52093190131237502/09/20105294.00000036
52093190131237503/09/201021008.00000078
52093190131237504/09/201049033.000000153
52093190131237506/09/201064956.000000176
52093190131237507/09/201067378.000000204
AND i want this output to be like this
chapter id OB bale Qty Bale pcs date pcs quantity balecount
5209319013123 7501/09/2010507212
5209319018195 8702/09/2010529436
5209319039203 12303/09/20102100878
November 15, 2010 at 6:14 am
This query looks as if it was originally written by a programmer then messed about with by someone who is very unsure of what they are doing. The end result is a mess which can only get worse. If the problem is urgent then you may be better off getting someone in to help. If you have a little time then it shouldn't be too much of a problem making this work for you, but it will require some effort and patience on your part.
Re-writing you query somewhat to make it more readable yields the following:
SELECT
[ChapterId]= T2.U_CHAP,
[Opening Bale Qty]= MAX(i.U_Quantity),
[Bale]= MAX(i.U_Bale),
PcsDate= CONVERT(VARCHAR(10), T0.DocDate, 103),
PcsQuantity= SUM(T1.Quantity),
[DATE]= COUnt(A.U_BaleNoNew)
FROM OIGN T0
INNER JOIN IGN1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OWOR T2 ON T0.[Ref2] = CONVERT(VARCHAR,T2.[DocNum])
LEFT JOIN [dbo].[@RGINPUT] i ON i.[Name]= T2.[U_CHAP])
LEFT JOIN OIBT A ON A.[InDate] = T0.[DocDate]
AND A.[ItemCode] LIKE 'FF%' AND A.[U_Flag] is Not Null
WHERE T0.[DocDate] >= '09/01/2010'
AND T0.[DocDate] < = '09/30/2010'
AND T1.[ItemCode] like 'FF%' -- see also Table OIBT
AND T2.[U_CHAP] = '52093190'
GROUP BY T0.[DocDate], T2.[U_CHAP]
ORDER BY T2.[U_CHAP] ASC, T0.[DocDate] ASC
You should test and see if this works as per your original query. More importantly, click on the word 'this' in my sig to get more effective help.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 16, 2010 at 12:11 am
hello chriss
its no were use for me iam getting all wrong data with ur query so please change my query as per my requirement
regards
Prakash
November 16, 2010 at 1:52 am
chintalagiriprakash (11/16/2010)
hello chrissits no were use for me iam getting all wrong data with ur query so please change my query as per my requirement
regards
Prakash
Have you read the Best Practices [/url]article yet?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply