根据条件引用两列中的数据字段 [英] Referencing a data field in two columns based on condition

问题描述

我有一个要求，我需要显示以下内容： -



网格标题如下： -



'日期''Cash_Sale_Qty''Credit_Sale_Qty''评分''Total_Amount'



我需要在日期显示总结结果：---



请帮我代码.....





谢谢...



复制的其他信息从下面的非解决方案

请参阅下面的代码： -

  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  和 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 .. 

此代码没问题，但无法提供如下结果： -

 02/04/2013 120 450 230.00 4500789 
 03/04/2013 000 300 150.00 678009 
 

解决方案

您的结果集可能如下：

 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 

这是因为日期时间数据类型字段包含日期和时间值。



您需要使用 CONVERT（）或CAST（）函数来删除时间部分所需的id [ ^ ]



注：数据类型 [ ^ ]集合取决于MS SQL Server版本。



MS SQL 2008及更高版本有 date [ ^ ]数据类型。所以，你需要做的就是使用类似的东西：

  SELECT   CONVERT （ DATE ，sp.tran_date） AS  billdate ... 
 ... 
  GROUP   BY   CONVERT （ DATE ，sp.tran_date）... 

对于MS SQL 2005，您需要使用一些技巧：

  SELECT   CONVERT （ DATETIME ， CONVERT （ VARCHAR （ 30 ），sp.tran_date， 112 ）） AS  billdate ... 
  GROUP   BY   CONVERT （ DATETIME ， CONVERT （ VARCHAR （ 30 ），sp.tran_date， 112 ））... 

所以...最后看起来应该是（使用别名的MS SQL 2005版本）：

 < span class =code-keyword> SELECT   CONVERT （ DATETIME ， CONVERT （ VARCHAR （ 30 ），sp.tran_date，< span class =code-digit> 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 

嗨Rupai99 ...



如果你想要结果基于datewise .....只是组按日期排序和按顺序排序...在您编写的查询中，您基于tran_date，tran_no，irate进行分组...因此只需按tran_date分组，按剩余项目排序...您只提供了outputdata ..提供一些样本输入数据....所以任何人都可以正确回答...



查看此查询...

< pre lang =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 < span class =code-keyword> 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 和 dbo.SalePurchase .tran_type = ' RS'
GROUP BY dbo.SalePu rchase.tran_date
订单 按 dbo.SalePurchase.tran_no，dbo.IT.iRate，dbo .IT.iRate * dbo.IT.iQty，dbo.IT.iQty ..

您可以使用别名而不是整个表连接中的名称...

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:-

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

解决方案

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:

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:

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):

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

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...

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...

这篇关于根据条件引用两列中的数据字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！