文章/答案/技术大牛

发布

社区首页 >问答首页 >用多个数据字段填充表单的正确方法是什么？

问用多个数据字段填充表单的正确方法是什么？
EN

Stack Overflow用户

提问于 2017-05-09 15:05:26

回答 2查看 87关注 0票数 2

我被要求创建一个自动创建电子表格的vb.net WinForms应用程序：

显然，手动创建电子表格是一项中断任务。

我很乐意编写SQL来提取每个单独的数据字段，但是我必须相信有一个更好的方法。这样的方法需要144次查询!！

我已经考虑过为每个字段创建一个带有单独标签的表单，或者使用一个网格视图。这两种方法都是最终用户可以接受的，但我真的不确定如何编写一个查询来生成一个看起来像最终产品的数据集。

我没有要求任何人为我编写任何代码，我在寻求帮助的是我应该如何攻击这个任务的概念。

下面是我编写的一个查询，它返回电子表格上第一周的Business #1字段。我不认为它有多大帮助，但我加入它是为了表明我在这方面付出了一些努力。

DECLARE @WeekEnding AS DATE
DECLARE @DIV AS INT

SET @WeekEnding = '11/13/2015'
SET @DIV = 20

-- A/R downpayments OPCH
SELECT 
SUM(OPCH.DocTotal - OPCH.VatSum - OPCH.TotalExpns) AS 'ARDownPaymentInvoice'  

FROM OPCH 
LEFT JOIN INV1 ON OPCH.DocEntry = INV1.DocEntry AND INV1.VisOrder = 0
LEFT JOIN OITM ON INV1.ItemCode = OITM.ItemCode 
LEFT JOIN OITB ON OITM.ItmsGrpCod = OITB.ItmsGrpCod 
LEFT JOIN OACT AS CurrCode (NOLOCK) ON OITB.RevenuesAc = CurrCode.AcctCode 

WHERE DATEPART(WEEK, OPCH.DocDate) = DATEPART(WEEK, @WeekEnding) AND YEAR(OPCH.DocDate) = YEAR(@WeekEnding)
AND CurrCode.Segment_4 = @DIV


-- Credits ORIN 
SELECT 
SUM(ORIN.DocTotal - ORIN.VatSum - ORIN.TotalExpns) * -1 AS 'Credit'  

FROM ORIN 
LEFT JOIN INV1 ON ORIN.DocEntry = INV1.DocEntry AND INV1.VisOrder = 0
LEFT JOIN OITM ON INV1.ItemCode = OITM.ItemCode 
LEFT JOIN OITB ON OITM.ItmsGrpCod = OITB.ItmsGrpCod 
LEFT JOIN OACT AS CurrCode (NOLOCK) ON OITB.RevenuesAc = CurrCode.AcctCode 

WHERE DATEPART(WEEK, ORIN.DocDate) = DATEPART(WEEK, @WeekEnding) AND YEAR(ORIN.DocDate) = YEAR(@WeekEnding)
AND CurrCode.Segment_4 = @DIV

--Invoices
SELECT 
SUM(OINV.DocTotal - OINV.VatSum - OINV.TotalExpns) AS 'Invoice'  

FROM OINV 
LEFT JOIN INV1 ON OINV.DocEntry = INV1.DocEntry AND INV1.VisOrder = 0
LEFT JOIN OITM ON INV1.ItemCode = OITM.ItemCode 
LEFT JOIN OITB ON OITM.ItmsGrpCod = OITB.ItmsGrpCod 
LEFT JOIN OACT AS CurrCode (NOLOCK) ON OITB.RevenuesAc = CurrCode.AcctCode 

WHERE DATEPART(WEEK, OINV.DocDate) = DATEPART(WEEK, @WeekEnding) AND YEAR(OINV.DocDate) = YEAR(@WeekEnding)
AND CurrCode.Segment_4 = @DIV`

谢谢你的建议。

vb.net

winforms

tsql

回答 2

Stack Overflow用户

回答已采纳

发布于 2017-05-09 15:42:23

实现这一目标的一种方法是：

CREATE TABLE #tmpData
(column1 varchar(50),
column2 varchar(50),
column3 varchar(50),
column4 varchar(50),
column5 varchar(50),
column6 varchar(50),
column7 varchar(50),
column8 varchar(50),
column9 varchar(50),
column10 varchar(50),
column11 varchar(50),
column12 varchar(50))

然后，可以插入一行，显示每列的日期。

INSERT INTO #tmpData
   --Write your select query here for filling in all the dates

然后，您将插入行中每一行项的数据。

 INSERT INTO #tmpdata
 SELECT 'Business Unit 1', 
 -- insert remaining column data here

票数 1

Stack Overflow用户

发布于 2017-05-09 16:13:39

我会做一个支点，而不是做多个查询。不过，我认为您应该问问自己或最终用户：“您真的希望按日期查看老化报告为列吗？”如果是这样的话，我是否每次报告老化时都手动添加一个增量？下面是一个自提取示例FYI。第一组只是显示我的数据，在这里我模拟一个用户、他们所属的部门以及他们的最后一次访问。然后我假装我想要一份关于衰老的报告，看看总共有多少人访问了这个系统，并且每个虚拟部门都访问了这个系统。在SQL中执行多个查询是昂贵的，而且通常可以通过稍微了解一些支点、联合以及如何创造性地使用SQL保存数据来缓解这种情况。

DECLARE @Example TABLE  (PersonId INT IDENTITY, PersonName VARCHAR(128), DepartmentName VARCHAR(128), AccessedOn Date);

INSERT INTO @Example (PersonName, DepartmentName, AccessedOn) VALUES ('Brett', 'Dev', '1-1-2017'), ('John', 'Dev', '1-6-2017'), ('Mark', 'Dev', '1-8-2017'), ('Shawn', 'Ops', '1-15-2017'), ('Ryan', 'Ops', '1-16-2017'), ('Kevin', 'Ops', '1-21-2017');

--Data as is
SELECT *
From @Example

--I would use Date By row as it is far easier to maintain
SELECT
  DATEADD(WEEK, DATEDIFF(WEEK, 0, AccessedOn), 0) AS Grouping
, COUNT(PersonId) AS PeopleAccessedAtTime
, SUM(CASE WHEN DepartmentName = 'Dev' THEN 1 ELSE 0 END) AS DevCounts
, SUM(CASE WHEN DepartmentName = 'Ops' THEN 1 ELSE 0 END) AS OpsCounts
FROM @Example
GROUP BY DATEADD(WEEK, DATEDIFF(WEEK, 0, AccessedOn), 0)

--Aging Report you are asking for trouble as you need to manually pivot ELSE go down the road of doing dynamic sql to do a pivot which is No Bueno 
; WITH x AS 
  (
  SELECT
    DATEADD(WEEK, DATEDIFF(WEEK, 0, AccessedOn), 0) AS Grouping
  , COUNT(PersonId) AS PeopleAccessedAtTime
  , SUM(CASE WHEN DepartmentName = 'Dev' THEN 1 ELSE 0 END) AS DevCounts
  , SUM(CASE WHEN DepartmentName = 'Ops' THEN 1 ELSE 0 END) AS OpsCounts
  FROM @Example
  GROUP BY DATEADD(WEEK, DATEDIFF(WEEK, 0, AccessedOn), 0)
  )
Select 
  'PeopleAccessed' AS Header
, MAX([2017-01-02]) AS [2017-01-02]
, Max([2017-01-09]) AS [2017-01-09]
, Max([2017-01-16]) AS [2017-01-16]
From x
  PIVOT(MAX(PeopleAccessedAtTime) FOR Grouping IN ([2017-01-02], [2017-01-09], [2017-01-16])) AS pvt
UNION
Select 
  'DevDivisionAccessed'
, MAX([2017-01-02]) AS [2017-01-02]
, Max([2017-01-09]) AS [2017-01-09]
, Max([2017-01-16]) AS [2017-01-16]
From x
  PIVOT(MAX(DevCounts) FOR Grouping IN ([2017-01-02], [2017-01-09], [2017-01-16])) AS pvt
UNION
Select 
  'OpsDivisionAccessed'
, MAX([2017-01-02]) AS [2017-01-02]
, Max([2017-01-09]) AS [2017-01-09]
, Max([2017-01-16]) AS [2017-01-16]
From x
  PIVOT(MAX(OpsCounts) FOR Grouping IN ([2017-01-02], [2017-01-09], [2017-01-16])) AS pvt

票数 2

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/43873436

复制

相似问题

问用多个数据字段填充表单的正确方法是什么？
EN

回答 2

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问用多个数据字段填充表单的正确方法是什么？EN

回答 2

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问用多个数据字段填充表单的正确方法是什么？
EN