April 5, 2004 at 12:41 pm
i have a table called TEMP
CREATE TEMP
(ID INT,
stdate DATETIME,
enddate DATETIME,
CODE CHAR(10))
INSERT INTO TEMP VALUES(1,'2004-04-01','2004-04-01','TEST1')
INSERT INTO TEMP VALUES(2,'2004-04-02','2004-04-02','TEST1')
INSERT INTO TEMP VALUES(3,'2004-04-03','2004-04-03','TEST1')
INSERT INTO TEMP VALUES(4,'2004-04-01','2004-04-01','TEST2')
INSERT INTO TEMP VALUES(5,'2004-04-02','2004-04-02','TEST2')
INSERT INTO TEMP VALUES(6,'2004-04-03','2004-04-03','TEST2')
INSERT INTO TEMP VALUES(7,'2004-04-01','2004-04-01','TEST3')
INSERT INTO TEMP VALUES(8,'2004-04-02','2004-04-02','TEST3')
INSERT INTO TEMP VALUES(9,'2004-04-03','2004-04-03','TEST3')
INSERT INTO TEMP VALUES(10,'2004-04-01','2004-04-01','TEST4')
INSERT INTO TEMP VALUES(11,'2004-04-02','2004-04-02','TEST4')
INSERT INTO TEMP VALUES(12,'2004-04-03','2004-04-03','TEST4')
SELECT CODE,
CASE WHEN CREATE_DATE BETWEEN '04/01/2004'AND '04/01/2004' THEN qty ELSE 0 END) AS '04/01/2004',
CASE WHEN CREATE_DATE BETWEEN '04/02/2004'AND '04/02/2004' THEN qty ELSE 0 END) AS '04/02/2004',
CASE WHEN CREATE_DATE BETWEEN '04/03/2004'AND '04/02/2004' THEN qty ELSE 0 END) AS '04/03/2004'
FROM
(SELECT stdate,enddate,CODE,COUNT(*) as qty FROM TEMP GROUP BY stdate,enddate,CODE) AS A
GROUP BY CODE ORDEY BY 1
Result
CODE 04/01/2004 04/02/2004 04/03/2004
--------------------------------------------------
TEST1 1 1 1
TEST2 1 1 1
TEST3 1 1 1
TEST4 1 1 1
But i need a result like this
CODE 04/01/2004 04/02/2004 04/03/2004 TOTAL
--------------------------------------------------------------------
TEST1 1 1 1 3
TEST2 1 1 1 3
TEST3 1 1 1 3
TEST4 1 1 1 3
TEST4 4 4 4 12
Number of columns not static. Please help me if you have any ideas.
April 5, 2004 at 6:25 pm
SELECT CODE, SUM(C1) AS '04/01/2004', SUM(C2) AS '04/02/2004', SUM(C3) AS '04/03/2004', SUM(TOTAL) AS 'TOTAL'
FROM
(SELECT CODE,
SUM(CASE WHEN stdate BETWEEN '04/01/2004'AND '04/01/2004' THEN qty ELSE 0 END) AS C1,
SUM(CASE WHEN stdate BETWEEN '04/02/2004'AND '04/02/2004' THEN qty ELSE 0 END) AS C2,
SUM(CASE WHEN stdate BETWEEN '04/03/2004'AND '04/03/2004' THEN qty ELSE 0 END) AS C3,
SUM(CASE WHEN stdate BETWEEN '04/01/2004'AND '04/03/2004' THEN qty ELSE 0 END) AS C4
FROM
(SELECT stdate,enddate,CODE,COUNT(*) as qty FROM TEMP GROUP BY stdate,enddate,CODE) AS A
GROUP BY CODE) AS B
GROUP BY CODE WITH CUBE
RESULT
CODE 04/01/2004 04/02/2004 04/03/2004 TOTAL
---------- ----------- ----------- ----------- -----------
TEST1 1 1 1 3
TEST2 1 1 1 3
TEST3 1 1 1 3
TEST4 1 1 1 3
NULL 4 4 4 12
April 6, 2004 at 10:27 am
Thanks for your response, but some times we skip week ends. when we skip week ends the total will be wrong.
Thanks.
April 7, 2004 at 7:02 am
Can you explain what you mean by 'skip weekends' and 'total will be wrong'.
Are you trying to output total qty per day for a calendar month or between two dates that can span a month?
Far away is close at hand in the images of elsewhere.
Anon.
April 7, 2004 at 10:27 am
David,
Let me explain my problem here
CREATE TABLE TEMP
(ID INT,
stdate DATETIME,
CODE CHAR(10))
INSERT INTO TEMP VALUES(1,'2004-04-01','TEST1')
INSERT INTO TEMP VALUES(2,'2004-04-02','TEST1')
INSERT INTO TEMP VALUES(3,'2004-04-03','TEST1')
INSERT INTO TEMP VALUES(4,'2004-04-01','TEST2')
INSERT INTO TEMP VALUES(5,'2004-04-02','TEST2')
INSERT INTO TEMP VALUES(6,'2004-04-03','TEST2')
INSERT INTO TEMP VALUES(7,'2004-04-01','TEST3')
INSERT INTO TEMP VALUES(8,'2004-04-02','TEST3')
INSERT INTO TEMP VALUES(9,'2004-04-03','TEST3')
INSERT INTO TEMP VALUES(10,'2004-04-01','TEST4')
INSERT INTO TEMP VALUES(11,'2004-04-02','TEST4')
INSERT INTO TEMP VALUES(12,'2004-04-03','TEST4')
INSERT INTO TEMP VALUES(13,'2004-04-04','TEST4')
INSERT INTO TEMP VALUES(14,'2004-04-05','TEST1')
INSERT INTO TEMP VALUES(15,'2004-04-06','TEST1')
INSERT INTO TEMP VALUES(16,'2004-04-06','TEST2')
INSERT INTO TEMP VALUES(17,'2004-04-01','TEST1')
If user asks report stdate between 2004-04-01 and 2004-04-06,means he wants to ignore the week end.
CODE 2004-04-01 2004-04-02 2004-04-05 2004-04-06 TOTAL
------------------------------------------------------------------------------
TEST1 2 1 1 1 5
TEST2 1 1 0 1 3
TEST3 1 1 0 0 2
TEST4 1 1 0 0 2
TOTAL 5 4 1 2 12
April 7, 2004 at 10:31 pm
SELECT CODE, SUM(C1) AS '04/01/2004', SUM(C2) AS '04/02/2004', SUM(C3) AS '04/05/2004', SUM(C4) AS '04/06/2004', SUM(C5) AS 'TOTAL'
FROM
(SELECT CODE,
SUM(CASE WHEN stdate BETWEEN '04/01/2004'AND '04/01/2004' THEN qty ELSE 0 END) AS C1,
SUM(CASE WHEN stdate BETWEEN '04/02/2004'AND '04/02/2004' THEN qty ELSE 0 END) AS C2,
SUM(CASE WHEN stdate BETWEEN '04/05/2004'AND '04/05/2004' THEN qty ELSE 0 END) AS C3,
SUM(CASE WHEN stdate BETWEEN '04/06/2004'AND '04/06/2004' THEN qty ELSE 0 END) AS C4,
SUM(CASE WHEN stdate in ('04/01/2004', '04/02/2004', '04/05/2004', '04/06/2004') THEN qty ELSE 0 END) AS C5
FROM
(SELECT stdate,CODE,COUNT(*) as qty FROM TEMP GROUP BY stdate,CODE) AS A
GROUP BY CODE) AS B
GROUP BY CODE WITH CUBE
Result:
CODE 04/01/2004 04/02/2004 04/05/2004 04/06/2004 TOTAL
---------- ----------- ----------- ----------- ----------- -----------
TEST1 2 1 1 1 5
TEST2 1 1 0 1 3
TEST3 1 1 0 0 2
TEST4 1 1 0 0 2
NULL 5 4 1 2 12
You can use either in or not in.
April 8, 2004 at 3:52 am
Try This
DECLARE @stdate datetime,@enddate datetime,@wkdate datetime,@sql nvarchar(4000),@sql2 nvarchar(4000)
SET @stdate = '2004-04-01'
SET @enddate = '2004-04-06'
SET DATEFIRST 1
CREATE TABLE #temp_sum (CODE CHAR(10))
SET @wkdate = @stdate
WHILE (@wkdate <= @enddate)
BEGIN
IF DATEPART(weekday,@wkdate) < 6
BEGIN
SET @sql = 'ALTER TABLE #temp_sum ADD ['+
CONVERT(varchar(10),@wkdate,120)+'] int DEFAULT 0'
EXEC sp_executesql @sql
END
SET @wkdate = DATEADD(day,1,@wkdate)
END
SET @sql = 'ALTER TABLE #temp_sum ADD [TOTAL] int DEFAULT 0'
EXEC sp_executesql @sql
INSERT INTO #temp_sum (CODE)
SELECT DISTINCT CODE FROM [TEMP]
INSERT INTO #temp_sum (CODE)
VALUES ('TOTAL')
SET @wkdate = @stdate
WHILE (@wkdate <= @enddate)
BEGIN
IF DATEPART(weekday,@wkdate) < 6
BEGIN
SET @sql = 'UPDATE t SET t.['+CONVERT(varchar(10),@wkdate,120)+
'] = ISNULL(s.ct,0),TOTAL = TOTAL + ISNULL(s.ct,0) FROM #temp_sum t '+
'LEFT OUTER JOIN (SELECT CODE,COUNT(*) AS ct FROM [TEMP] WHERE stdate = '''+
CONVERT(varchar(10),@wkdate,120)+''' GROUP BY CODE) s ON s.CODE = t.CODE'
EXEC sp_executesql @sql
SET @sql = 'UPDATE t SET t.['+CONVERT(varchar(10),@wkdate,120)+
'] = ISNULL(s.ct,0),TOTAL = TOTAL + ISNULL(s.ct,0) FROM #temp_sum t '+
'INNER JOIN (SELECT ''TOTAL'' AS CODE,COUNT(*) AS ct FROM [TEMP] WHERE stdate = '''+
CONVERT(varchar(10),@wkdate,120)+''') s ON s.CODE = t.CODE'
EXEC sp_executesql @sql
END
SET @wkdate = DATEADD(day,1,@wkdate)
END
SELECT * FROM #temp_sum
DROP TABLE #temp_sum
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply