Click here to Skip to main content
15,868,141 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi,

I have a requirement where I need to show the following :-

Grid headers are as below:-

'Date' 'Cash_Sale_Qty' 'Credit_Sale_Qty' 'Rate' 'Total_Amount'

I need to show the summed up result datewise:---

Please help me with code.....


Thanks...

Additional information copied from non-solution below
See my code below:-


SQL
SELECT dbo.SalePurchase.tran_date AS billdate,SUM(dbo.IT.iQty) ,
 AVG(dbo.IT.iRate as Rate), SUM(dbo.IT.iRate * dbo.IT.iQty) AS amt 

FROM dbo.SPBillTerm RIGHT OUTER JOIN
 dbo.Item RIGHT OUTER JOIN
 dbo.SalePurchase LEFT OUTER JOIN
 dbo.GL ON dbo.SalePurchase.ac_code = dbo.GL.AcCode LEFT OUTER JOIN
 dbo.IT ON dbo.SalePurchase.Code = dbo.IT.SPCode ON dbo.Item.Code = dbo.IT.ItemCode ON dbo.SPBillTerm.SPCode = dbo.SalePurchase.Code
 WHERE dbo.SalePurchase.tran_date BETWEEN @frmdate AND 
@todate and dbo.SalePurchase.tran_type='RS'
 GROUP BY dbo.SalePurchase.tran_date, dbo.SalePurchase.tran_no, dbo.IT.iRate, dbo.IT.iRate * dbo.IT.iQty,dbo.IT.iQty..


This code is ok, but not been able to provide results as below:-
02/04/2013 120 450 230.00 4500789
03/04/2013 000 300 150.00 678009
Posted
Updated 8-Jul-13 4:53am
v4
Comments
Joezer BH 8-Jul-13 7:09am    
I almost understood, can you explain a little bit more?
Maciej Los 8-Jul-13 7:21am    
Agree!
We need more details and example data...
jaideepsinh 8-Jul-13 7:39am    
Rupai can you explain in brief?
rupai99 8-Jul-13 14:00pm    
I agree...this is a question...not a solution....

Your result set is probably like:
02/04/2013 12:00:20.000   120   450   230.00   4500789
02/04/2013 13:02:20.000   120   450   230.00   4500789
02/04/2013 15:12:22.000   120   450   230.00   4500789
03/04/2013 11:05:01.000   000   300   150.00   678009
03/04/2013 13:15:18.000   000   300   150.00   678009
03/04/2013 17:23:05.000   000   300   150.00   678009

It's because the datetime data type field contains a date and time value.

All you need to do id to remove the time part, using CONVERT() OR CAST() function[^]

Note: The data types[^] collection depends on MS SQL Server version.

MS SQL 2008 and higher has a date[^] data type. So, all you need to do is to use something like that:
SQL
SELECT CONVERT(DATE,sp.tran_date) AS billdate ...
...
GROUP BY CONVERT(DATE,sp.tran_date)...


For MS SQL 2005 you need to use some trick:
SQL
SELECT CONVERT(DATETIME, CONVERT(VARCHAR(30),sp.tran_date,112)) AS billdate ...
GROUP BY CONVERT(DATETIME,CONVERT(VARCHAR(30),sp.tran_date,112))...

So... Finally it should looks like (MS SQL 2005 version using aliases):
SQL
SELECT CONVERT(DATETIME,CONVERT(VARCHAR(30),sp.tran_date,112)) AS billdate, SUM(it.iQty), AVG(it.iRate as Rate), SUM(it.iRate * it.iQty) AS amt 
FROM dbo.SPBillTerm AS bt RIGHT OUTER JOIN dbo.Item AS im RIGHT OUTER JOIN dbo.SalePurchase AS sp LEFT OUTER JOIN
    dbo.GL AS gl ON sp.ac_code = gl.AcCode LEFT OUTER JOIN dbo.IT AS it ON sp.Code = it.SPCode ON
    im.Code = it.ItemCode ON bt.SPCode = sp.Code
WHERE CONVERT(DATETIME(CONVERT(VARCHAR(30), sp.tran_date,112)) BETWEEN @frmdate AND @todate AND sp.tran_type=@trantype
GROUP BY CONVERT(DATETIME,CONVERT(VARCHAR(30),sp.tran_date,112))
ORDER BY sp.tran_no, it.iRate, it.iRate * it.iQty, it.iQty
 
Share this answer
 
Comments
Raja Sekhar S 9-Jul-13 3:48am    
Agree..... i Completely forgot this in the answer which i posted....
nice one.... +5..! Maciej Los....
Maciej Los 9-Jul-13 4:00am    
Thank you, Raja ;)
I don't see your 5 ;(
Raja Sekhar S 9-Jul-13 4:03am    
U can see it....
Maciej Los 9-Jul-13 4:04am    
Now, i see...
Thank you one more time ;)
Raja Sekhar S 9-Jul-13 4:04am    
May be updating is taking some time..... You are welcome...
Hi Rupai99...

If you want the result Based on datewise..... just group by Date and Order by OrderList...In the Query which you wrote, you are Grouping based on tran_date,tran_no,irate... So Just Group by tran_date and Order by Remaining Items...You have Provided only outputdata... provide some sample input data.... so any one can answer properly...

Check this Query...
SQL
SELECT dbo.SalePurchase.tran_date AS billdate,SUM(dbo.IT.iQty) ,
AVG(dbo.IT.iRate as Rate), SUM(dbo.IT.iRate * dbo.IT.iQty) AS amt 
FROM dbo.SPBillTerm RIGHT OUTER JOIN
dbo.Item RIGHT OUTER JOIN
dbo.SalePurchase LEFT OUTER JOIN
dbo.GL ON dbo.SalePurchase.ac_code = dbo.GL.AcCode LEFT OUTER JOIN
dbo.IT ON dbo.SalePurchase.Code = dbo.IT.SPCode ON dbo.Item.Code = dbo.IT.ItemCode ON dbo.SPBillTerm.SPCode = dbo.SalePurchase.Code
WHERE dbo.SalePurchase.tran_date BETWEEN @frmdate AND 
@todate and dbo.SalePurchase.tran_type='RS'
GROUP BY dbo.SalePurchase.tran_date
Order By dbo.SalePurchase.tran_no, dbo.IT.iRate, dbo.IT.iRate * dbo.IT.iQty,dbo.IT.iQty..


you can use alias names instead of whole table name in joins...
 
Share this answer
 
v2
Comments
Maciej Los 9-Jul-13 4:01am    
Good advice to use aliases instead table names.
A 4!
Raja Sekhar S 9-Jul-13 4:02am    
Thank You....

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