this is my DB table:
CREATE TABLE IF NOT EXISTS `inspection_report` (
`Inspection_datetime` datetime NOT NULL,
`Line` char(5) NOT NULL,
`S` int(11) NOT NULL,
`A` int(11) NOT NULL,
`B` int(11) NOT NULL,
`C` int(11) NOT NULL,
INSERT INTO `inspection_report` (`Inspection_datetime`,`Line`,`S`, `A`, `B`, `C`) VALUES
('2010-09-01 09:08:01','FA 05',0, 0, 0, 0),('2010-09-02 14:24:35','FA 07',0, 0, 1, 0),('2010-09-01 09:08:01','fa 05',0, 1, 1, 0),('2010-09-01 16:24:04','FA 03', 0, 1, 0, 0);
I have a lot of data for this table.how do i do if i want show the result like:
Line 1st week 2nd week 3rd week 4th week 5th week total
FA 03 20 32 10 12 35 109
FA 05 12 5 10 10 25 62
FA 07 0 0 1 1 0 2
there are a lot of data for a month. i want separate them counting for a week.if there is data that has reached about a week, then the script will automatically count them and share them in the 1st week,2nd week,3rd week,and so on. how do i do that? or are you have any idea? How about using YEARweek()
command?
You would need to do something like this
SELECT
n.name,
w1.amount,
w2.amount,
...
IFNULL(w1.amount,0) + IFNULL(w2.amount,0) + .... AS total
FROM
yourTable AS n
LEFT JOIN (
SELECT name, SUM(qty) AS amount FROM yourTable WHERE DAY(date) BETWEEN 1 AND 7 GROUP BY name
) AS w1 USING (name)
LEFT JOIN (
SELECT name, SUM(qty) AS amount FROM yourTable WHERE DAY(date) BETWEEN 8 AND 14 GROUP BY name
) AS w2 USING (name)
...
please take a look at the following :
http://www.artfulsoftware.com/infotree/queries.php?bw=1280#78
the index page is here:
http://www.artfulsoftware.com/infotree/queries.php?bw=1280
example pivot query:
SELECT
IFNULL(empId,'Totals') AS EmpId,
sums.2005, sums.2006, sums.2007,
sums.2005 + sums.2006 + sums.2007 AS Sums
FROM (
SELECT
EmpID,
SUM(IF(Yr=2005,sales,0)) As '2005',
SUM(IF(Yr=2006,sales,0)) As '2006',
SUM(IF(Yr=2007,sales,0)) As '2007'
FROM Sales
GROUP BY EmpID WITH ROLLUP
) AS sums;
+--------+----------+----------+----------+-----------+
| EmpId | 2005 | 2006 | 2007 | Sums |
+--------+----------+----------+----------+-----------+
| 1 | 12000.00 | 18000.00 | 25000.00 | 55000.00 |
| 2 | 15000.00 | 6000.00 | 0.00 | 21000.00 |
| 3 | 0.00 | 20000.00 | 24000.00 | 44000.00 |
| Totals | 27000.00 | 44000.00 | 49000.00 | 120000.00 |
+--------+----------+----------+----------+-----------+
SELECT
A.Line,
week1.1stweek,
week2.2ndweek,
...
IFNULL(week1.1stweek,0) + IFNULL(week2.2ndweek,0) + .... AS total
FROM
inspection_report AS A
LEFT JOIN (
SELECT Line, (SUM(S) + SUM(A) + SUM(B)*0.4 + SUM(C)*0.1)/COUNT(Serial_number) AS 1stweek FROM inspection_report WHERE DAY(Inspection_datetime) BETWEEN 1 AND 7 GROUP BY Line, WEEK, YEAR) AS week1 USING (Line)
LEFT JOIN (
SELECT Line, (SUM(S) + SUM(A) + SUM(B)*0.4 + SUM(C)*0.1)/COUNT(Serial_number) AS 2ndweek FROM inspection_report WHERE DAY(Inspection_datetime) BETWEEN 8 AND 14 GROUP BY Line, WEEK, YEAR) AS week2 USING (Line)
...
GROUP BY Line
You can do using Pivot feature of SQL Server. Refer following example.
CREATE TABLE MyData
(
ItemDate DATETIME,
ItemName VARCHAR(200),
ItemQty INT
)
GO
DECLARE @I INT
SET @i = 1
WHILE @i < 1000
BEGIN
INSERT INTO MyData VALUES(GETDATE()-@I, 'NAME ' + CONVERT(VARCHAR(5),@I % 5), RAND()*100)
SET @I = @I + 1
END
GO
SELECT ItemName, [1] AS 'WEEK-1', [2] AS 'WEEK-2', [3] AS 'WEEK-3', [4] AS 'WEEK-4'
FROM (SELECT ItemName, DATEPART(wk, ItemDate) WeekNum, ItemQty
FROM MyData) AS SourceTable
PIVOT
(
SUM(ItemQty)
FOR WeekNum IN ([1], [2], [3], [4])
) AS PivotTable;