dynamic SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

dynamic SQL

Author

Topic

bluemagic74
Starting Member

15 Posts

Posted - 2012-02-01 : 12:33:13

Hi,

My table structure is

CREATE TABLE [dbo].[DMT050_DIAB_MED](
[ID] [int] NULL,
[DRUG_CD] [tinyint] NOT NULL,
[READ_TP_CD] [tinyint] NOT NULL,
[SQNC_NB] [tinyint] NOT NULL,
[DSG_PTS] [nvarchar](max) NOT NULL,
[PLLS_CNT] [decimal](2, 1) NULL,
[STRT_DT_DFC] [int] NULL,
[END_DT_DFC] [int] NULL

) ON [PRIMARY]

GO

Sample data :

ID,DRUG_CD,READ_TP_CD,SQNC_NB,DSG_PTS,PLLS_CNT,STRT_DT_DFC,END_DT_DFC
1327,5,1,1,500/2,1,0,5
1327,5,5,1,500/2,1,7,11
1327,13,1,1,500,1,1,3
1421,2,1,1,30,1,1,5
1421,13,1,1,1000,2,56,60
1421,13,3,1,1000,2,0,11
1554,2,1,1,45,1,361,432
1554,9,5,1,2.5,1,58,58

So I wrote below script to pull the weekly count. But I used only STRT_DT_DFC.

I need count between STRT_DT_DFC and END_DT_DFC.

FOR ex: for 1 user STRT_DT_DFC = 0 and END_DT_DFC = 7 , so week 1 count is 1

Need help to modify the script.

DECLARE @SQL NVARCHAR(MAX),
@I INT,
@J INT,
@WEEKDAYS INT

SET @SQL = 'SELECT ' + 'ID' + ' AS ID,'
SET @WEEKDAYS = 7
SET @J = 0
SET @I = 0
WHILE @I <= 121
BEGIN
SET @SQL = @SQL + ' CASE WHEN STRT_DT_DFC BETWEEN ' + CONVERT(VARCHAR,@J) + ' AND ' + CONVERT(VARCHAR, @J+6) + ' THEN COUNT(STRT_DT_DFC) ELSE 0 END AS [Week' + CONVERT(VARCHAR, @I+1) + '],'
SET @J = @J + 7
SET @I = @I + 1
END

SET @SQL = SUBSTRING(@SQL, 0, LEN(@SQL))
SET @SQL = @SQL + ' FROM DMT050_DIAB_MED o group by o.ID,o.STRT_DT_DFC'

SELECT @SQL

EXECUTE sp_executesql @SQL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-01 : 13:12:40

i think sql string should be modified like below


..
SET @SQL = @SQL + ' COUNT(CASE WHEN STRT_DT_DFC BETWEEN ' + CONVERT(VARCHAR,@J) + ' AND ' + CONVERT(VARCHAR, @J+6) + ' THEN STRT_DT_DFC ELSE 0 END) AS [Week' + CONVERT(VARCHAR, @I+1) + '],'
...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bluemagic74
Starting Member

15 Posts

Posted - 2012-02-03 : 14:32:25

Thanks Visakh, output is not correct with above change.

Let me put the question like this

my data is :

ID,STRT_DT_DFC,END_DT_DFC

1010,1,5

1010,1,10

1010,8,21

So I need result like this

So Start date and end date between 1-7 or 8-14 or 15-21

Weekly count per user

ID,Week1(1-7),week2(8-14),week3(15-21)

1010, 1,0,0

1010,1,1,0

1010,0,1,1

result should be...like below

so for user 1010 the week1 count is 2, week2 count is 2, week3 count is 1

I need a script to get weekly count per user. Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources