January 11, 2015 at 4:31 am
I have a table named activity with fields activityId,activitydate. How can I write a query to select activityId from the table between two dates in such a way that it returns values as a single row and if there is no activityId for a date in a month it should be 0
ie.
activityId activitydate
15 2015-01-01 00:00:00.000
16 2015-01-03 00:00:00.000
17 2015-01-04 00:00:00.000
18 2015-01-06 00:00:00.000
19 2015-01-10 00:00:00.000
20 2015-01-12 00:00:00.000
it show return the values like
15 0 16 17 0 18 0 0 0 10 0 20
January 11, 2015 at 7:24 am
anu.anu4u (1/11/2015)
I have a table named activity with fields activityId,activitydate. How can I write a query to select activityId from the table between two dates in such a way that it returns values as a single row and if there is no activityId for a date in a month it should be 0ie.
activityId activitydate
15 2015-01-01 00:00:00.000
16 2015-01-03 00:00:00.000
17 2015-01-04 00:00:00.000
18 2015-01-06 00:00:00.000
19 2015-01-10 00:00:00.000
20 2015-01-12 00:00:00.000
it show return the values like
15 0 16 17 0 18 0 0 0 10 0 20
Quick solution using an inline calendar table and dynamic cross-tab, should be sufficient to get you passed this hurdle.
😎
USE tempdb;
GO
SET NOCOUNT ON;
/* SAMPLE DATA */
IF OBJECT_ID(N'dbo.TBL_ACTIVITY') IS NOT NULL DROP TABLE dbo.TBL_ACTIVITY;
CREATE TABLE dbo.TBL_ACTIVITY
(
activityId INT NOT NULL
,activitydate DATETIME NOT NULL
);
INSERT INTO dbo.TBL_ACTIVITY (activityId,activitydate)
VALUES
(15,'2015-01-01 00:00:00.000')
,(16,'2015-01-03 00:00:00.000')
,(17,'2015-01-04 00:00:00.000')
,(18,'2015-01-06 00:00:00.000')
,(19,'2015-01-10 00:00:00.000')
,(20,'2015-01-12 00:00:00.000');
/* DYNAMIC CROSS-TAB */
DECLARE @CAL_START DATETIME = '2015-01-01';
DECLARE @CAL_END DATETIME = '2015-01-11';
DECLARE @PARAM_STR NVARCHAR(MAX) = N'@CAL_START DATETIME';
DECLARE @SQL_STR NVARCHAR(MAX) = N'';
;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
,CAL_CONFIG(TD,SD) AS
(
SELECT
(DATEDIFF(DAY,@CAL_START,@CAL_END)) AS TD
,@CAL_START AS SD
)
,NUMS(N) AS (SELECT 0 AS N UNION ALL SELECT TOP((SELECT TD FROM CAL_CONFIG)) ROW_NUMBER() OVER
(ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4) -- /* 1001 Days */,T T5,T T6,T T7,T T8,T T9)
,SIMPLE_CAL AS
(
SELECT
NM.N
,(@CAL_START + NM.N) AS CAL_DATE
,CONVERT(VARCHAR(10),(@CAL_START + NM.N),126) AS CHR_DATE
,QUOTENAME(CONVERT(VARCHAR(32),(@CAL_START + NM.N),112)) AS COL_DATE
FROM NUMS NM
)
SELECT @SQL_STR = N'
;WITH CAL(START_DATE) AS
( SELECT
CONVERT(DATE,@CAL_START,0) AS START_DATE
)
SELECT
C.START_DATE
' + (
SELECT
N',MAX(ISNULL(CASE WHEN TA.activitydate = ' + NCHAR(39) + SC.CHR_DATE + NCHAR(39)
+ N' THEN TA.activityId END,0)) AS ' + SC.COL_DATE + NCHAR(13) + NCHAR(10)
FROM SIMPLE_CAL SC
FOR XML PATH(''), TYPE).value('.[1]','NVARCHAR(MAX)') + N'
FROM dbo.TBL_ACTIVITY TA
CROSS JOIN CAL C
GROUP BY C.START_DATE;'
/* SELECT @SQL_STR */
EXECUTE SP_EXECUTESQL @SQL_STR, @PARAM_STR, @CAL_START;
GO
Results
START_DATE 20150101 20150102 20150103 20150104 20150105 20150106 20150107 20150108 20150109 20150110 20150111
---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
2015-01-01 15 0 16 17 0 18 0 0 0 19 0
January 11, 2015 at 9:18 am
anu.anu4u (1/11/2015)
I have a table named activity with fields activityId,activitydate. How can I write a query to select activityId from the table between two dates in such a way that it returns values as a single row and if there is no activityId for a date in a month it should be 0ie.
activityId activitydate
15 2015-01-01 00:00:00.000
16 2015-01-03 00:00:00.000
17 2015-01-04 00:00:00.000
18 2015-01-06 00:00:00.000
19 2015-01-10 00:00:00.000
20 2015-01-12 00:00:00.000
it show return the values like
15 0 16 17 0 18 0 0 0 10 0 20
Is it guaranteed that there will be one and only one ActivityID per ActivityDate?
--Jeff Moden
Change is inevitable... Change for the better is not.
January 11, 2015 at 10:41 am
yes there will be only one entry for a date
January 11, 2015 at 12:02 pm
Then Eirikur's code will do it for you. Do you understand how it works?
--Jeff Moden
Change is inevitable... Change for the better is not.
January 11, 2015 at 12:08 pm
Thanks for the code but I didnt understand the code ... and also there is also a condition - select only salespersonId =1 (salespersonId is another field which i didnt add in the above question for simplicity)
January 11, 2015 at 12:24 pm
anu.anu4u (1/11/2015)
Thanks for the code but I didnt understand the code ... and also there is also a condition - select only salespersonId =1 (salespersonId is another field which i didnt add in the above question for simplicity)
Ah. I thought there might be more to the story. Are you going to have to do this for each sales person ID?
--Jeff Moden
Change is inevitable... Change for the better is not.
January 11, 2015 at 12:43 pm
Actually my table is
[activityid] [int] IDENTITY(1,1) NOT NULL,
[salespersonid] [int] NULL,
[activitydate] [datetime] NULL
I am trying to make a calender using a gridview in asp.net like
1 2 3 4 5 6 ................
Ramu 15 0 16 17 0 18...............
John 19 20 0 21 0 0 ...................
0 represents there is no activity on that date. activityId will be shown hidden in gridview with color green if there is an activity and red if no activity so that if clicked in the corresponding cell it will show the activity details of that activityid from another table. (sales persons details like name Ramu etc is saved in salesmanmaster whose id salespersonid is refers in the above table)
January 11, 2015 at 3:40 pm
anu.anu4u (1/11/2015)
Actually my table is[activityid] [int] IDENTITY(1,1) NOT NULL,
[salespersonid] [int] NULL,
[activitydate] [datetime] NULL
I am trying to make a calender using a gridview in asp.net like
1 2 3 4 5 6 ................
Ramu 15 0 16 17 0 18...............
John 19 20 0 21 0 0 ...................
0 represents there is no activity on that date. activityId will be shown hidden in gridview with color green if there is an activity and red if no activity so that if clicked in the corresponding cell it will show the activity details of that activityid from another table. (sales persons details like name Ramu etc is saved in salesmanmaster whose id salespersonid is refers in the above table)
This is even simpler than before, here is the code with the changes, let us know if you have any problems with it.
😎
USE tempdb;
GO
SET NOCOUNT ON;
/* SAMPLE DATA */
IF OBJECT_ID(N'dbo.TBL_ACTIVITY') IS NOT NULL DROP TABLE dbo.TBL_ACTIVITY;
CREATE TABLE dbo.TBL_ACTIVITY
(
activityId INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
,salespersonid INT NOT NULL
,activitydate DATETIME NOT NULL
);
INSERT INTO dbo.TBL_ACTIVITY (salespersonid,activitydate)
VALUES
(101,'2015-01-01 00:00:00.000')
,(101,'2015-01-03 00:00:00.000')
,(101,'2015-01-04 00:00:00.000')
,(101,'2015-01-06 00:00:00.000')
,(101,'2015-01-10 00:00:00.000')
,(101,'2015-01-12 00:00:00.000')
,(102,'2015-01-02 00:00:00.000')
,(102,'2015-01-03 00:00:00.000')
,(102,'2015-01-04 00:00:00.000')
,(102,'2015-01-07 00:00:00.000')
,(102,'2015-01-11 00:00:00.000')
,(102,'2015-01-13 00:00:00.000')
;
IF OBJECT_ID(N'dbo.TBL_SALESPERSON') IS NOT NULL DROP TABLE dbo.TBL_SALESPERSON;
CREATE TABLE dbo.TBL_SALESPERSON
(
SP_ID INT NOT NULL PRIMARY KEY CLUSTERED
,SP_NAME VARCHAR(20) NOT NULL
);
INSERT INTO dbo.TBL_SALESPERSON(SP_ID,SP_NAME)
VALUES
(101,'Bill')
,(102,'John')
;
/* DYNAMIC CROSS-TAB */
DECLARE @CAL_START DATETIME = '2015-01-01';
DECLARE @CAL_END DATETIME = '2015-01-15';
DECLARE @SQL_STR NVARCHAR(MAX) = N'';
;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
,CAL_CONFIG(TD,SD) AS
(
SELECT
(DATEDIFF(DAY,@CAL_START,@CAL_END)) AS TD
,@CAL_START AS SD
)
,NUMS(N) AS (SELECT 0 AS N UNION ALL SELECT TOP((SELECT TD FROM CAL_CONFIG)) ROW_NUMBER() OVER
(ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4) -- /* 1001 Days */,T T5,T T6,T T7,T T8,T T9)
,SIMPLE_CAL AS
(
SELECT
NM.N
,(@CAL_START + NM.N) AS CAL_DATE
,CONVERT(VARCHAR(10),(@CAL_START + NM.N),126) AS CHR_DATE
,QUOTENAME(CONVERT(VARCHAR(32),(@CAL_START + NM.N),112)) AS COL_DATE
FROM NUMS NM
)
SELECT @SQL_STR = N'
SELECT
SP.SP_NAME
' + (
SELECT
N',MAX(ISNULL(CASE WHEN TA.activitydate = ' + NCHAR(39) + SC.CHR_DATE + NCHAR(39)
+ N' THEN TA.activityId END,0)) AS ' + SC.COL_DATE + NCHAR(13) + NCHAR(10)
FROM SIMPLE_CAL SC
FOR XML PATH(''), TYPE).value('.[1]','NVARCHAR(MAX)') + N'
FROM dbo.TBL_ACTIVITY TA
INNER JOIN dbo.TBL_SALESPERSON SP
ON TA.salespersonid = SP.SP_ID
GROUP BY SP.SP_NAME;'
/* SELECT @SQL_STR */
EXECUTE SP_EXECUTESQL @SQL_STR;
GO
Results
SP_NAME 20150101 20150102 20150103 20150104 20150105 20150106 20150107 20150108 20150109 20150110 20150111 20150112 20150113 20150114 20150115
-------------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
Bill 1 0 2 3 0 4 0 0 0 5 0 6 0 0 0
John 0 7 8 9 0 0 10 0 0 0 11 0 12 0 0
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply