October 27, 2008 at 2:11 pm
I need to produce small reports as per Users request.
Here is my table structureCREATE TABLE [dbo].[PROV](
[PROV_KEY] [int] IDENTITY(1,1) NOT NULL,
[VEND_ID] [varchar](18) NULL,
[MCO_PROVIDER_ID_QLFR] [char](2) NULL,
[MCO_PROVIDER_EIN_SSN] [char](9) NULL,
[MCO_SORT_NAME] [varchar](35) NULL,
[MCO_PROV_NAME] [varchar](35) NULL,
[MCO_SVC_ADDR_LINE1] [varchar](30) NULL,
[MCO_SVC_ADDR_LINE2] [varchar](20) NULL,
[MCO_SVC_CITY] [varchar](20) NULL,
[MCO_SVC_STATE] [char](2) NULL,
[MCO_SVC_ZIP] [varchar](10) NOT NULL,
[MCO_SVC_GEO_COUNTY] [char](2) NULL,
[MCO_SERVICING_PROV_TELEPHONE] [varchar](10) NULL,
[MCO_MAIL_ADDR_LINE1] [varchar](30) NULL,
[MCO_MAIL_ADDR_LINE2] [varchar](20) NULL,
[MCO_MAIL_CITY] [varchar](20) NULL,
[MCO_MAIL_STATE] [char](2) NULL,
[MCO_MAIL_ZIP] [varchar](10) NULL,
[MCO_PCP_INDICATOR] [char](1) NULL,
[MCO_CONTRACT_TYPE] [char](2) NULL,
[MCO_AFF_PROV_TYPE] [varchar](3) NOT NULL,
[MCO_AFF_SPECIALTY_1] [char](3) NULL,
[MCO_AFF_SPECIALTY_2] [char](3) NULL,
[MCO_AFF_SPECIALTY_3] [char](3) NULL,
[MCO_AFF_SPECIALTY_4] [char](3) NULL,
[MCO_AFF_SPECIALTY_5] [char](3) NULL,
[MCO_AFF_SPECIALTY_6] [char](3) NULL,
[MCO_AFF_SPECIALTY_7] [char](3) NULL,
[MCO_AFF_SPECIALTY_8] [char](3) NULL,
[MCO_AFF_SPECIALTY_9] [char](3) NULL,
[MCO_AFF_SPECIALTY_10] [char](3) NULL,
[MCO_AFF_SPECIALTY_11] [char](3) NULL,
[MCO_AFF_SPECIALTY_12] [char](3) NULL,
[MCO_PROV_NPI_NO] [varchar](10) NULL,
[MCO_CONTRACT_STATUS_BEGIN_DATE] [char](8) NULL,
[MCO_CONTRACT_STATUS_END_DATE] [char](8) NULL,
[SUBCONTRACTOR_AFFILIATION_TYPE] [char](2) NULL,
[SUBCONTRACTOR_AFFIL_PROV_NUM] [varchar](15) NULL,
[PROV_STA_CD] [char](1) NULL,
[PROV_VRSN_NBR] [smallint] NOT NULL,
[LST_CHG_USR_ID] [varchar](50) NULL,
[LST_CHG_TS] [datetime] NULL,
[RCVD_FR_EDI_TS] [datetime] NULL,
[SNT_TO_EDI_TS] [datetime] NULL,
CONSTRAINT [PK_PROV] PRIMARY KEY CLUSTERED
(
[PROV_KEY] ASC
This is my Query that produces results like this in example.
SELECT DISTINCT VEND_ID,
CONVERT(char(12),RCVD_FR_EDI_TS) as [RECEIVED DATE],
PROV_STA_CD,
Count(*)as [COUNT]
FROM PROV
GROUP BY VEND_ID, PROV_STA_CD, CONVERT(char(12),RCVD_FR_EDI_TS)
ORDER BY VEND_ID, [RECEIVED DATE], PROV_STA_CD
VEND_ID RECEIVED DATE PROV_STA_CD COUNT
------------------ ------------- ----------- -----------
EMP030 Oct 20 2008 S 5394
EMP030 Oct 21 2008 S 5
EMP031 Oct 21 2008 G 1
EMP031 Oct 21 2008 H 1
EMP032 Oct 20 2008 G 2175
EMP032 Oct 20 2008 H 1
EMP032 Oct 21 2008 G 57
EMP033 Oct 20 2008 G 446
EMP033 Oct 20 2008 H 8
EMP035 Oct 20 2008 G 16981
EMP035 Oct 20 2008 N 276
EMP035 Oct 22 2008 N 45
EMP037 Oct 20 2008 G 956
EMP037 Oct 20 2008 H 102
(14 row(s) affected)
ANother Query that produces.....like this........
SELECT A.*,B.TOTAL_COUNT FROM (SELECT
DISTINCT VEND_ID,
CONVERT(char(12),
RCVD_FR_EDI_TS) as [RECEIVED DATE],
PROV_STA_CD, Count(*)as [COUNT]
FROM
PROV
GROUP
BY VEND_ID, PROV_STA_CD, CONVERT(char(12),RCVD_FR_EDI_TS) ) as a
INNER JOIN
(SELECT VEND_ID, sum([COUNT]) as TOTAL_COUNT
FROM
(SELECT
DISTINCT VEND_ID,
CONVERT(char(12),
RCVD_FR_EDI_TS) as [RECEIVED DATE],
PROV_STA_CD, Count(*)as [COUNT]
FROM
PROV
GROUP
BY VEND_ID, PROV_STA_CD, CONVERT(char(12),RCVD_FR_EDI_TS)
) A
GROUP BY VEND_ID) B ON A.VEND_ID = B.VEND_ID
ORDER BY A.VEND_ID
VEND_ID RECEIVED DATE PROV_STA_CD COUNT TOTAL_COUNT
------------------ ------------- ----------- ----------- -----------
EMP030 Oct 21 2008 S 5 5399
EMP030 Oct 20 2008 S 5394 5399
EMP031 Oct 21 2008 H 1 2
EMP031 Oct 21 2008 G 1 2
EMP032 Oct 21 2008 G 57 2233
EMP032 Oct 20 2008 H 1 2233
EMP032 Oct 20 2008 G 2175 2233
EMP033 Oct 20 2008 G 446 454
EMP033 Oct 20 2008 H 8 454
EMP035 Oct 20 2008 G 16981 17302
EMP035 Oct 20 2008 N 276 17302
EMP035 Oct 22 2008 N 45 17302
EMP037 Oct 20 2008 H 102 1058
EMP037 Oct 20 2008 G 956 1058
(14 row(s) affected)
Is it Possibl eto modify this single query so that resultsets someth like thisEND_ID RECEIVED DATE PROV_STA_CD COUNT TOTAL_COUNT
------------------ ------------- ----------- ----------- -----------
EMP030 Oct 21 2008 S 5
EMP030 Oct 20 2008 S 5394 5399
EMP031 Oct 21 2008 H 1
EMP031 Oct 21 2008 G 1 2
EMP032 Oct 21 2008 G 57
EMP032 Oct 20 2008 H 1
EMP032 Oct 20 2008 G 2175 2233
EMP033 Oct 20 2008 G 446
EMP033 Oct 20 2008 H 8 454
EMP035 Oct 20 2008 G 16981
EMP035 Oct 20 2008 N 276
EMP035 Oct 22 2008 N 45 17302
EMP037 Oct 20 2008 H 102
EMP037 Oct 20 2008 G 956 1058
(14 row(s) affected)
Or at least something like this
VEND_ID RECEIVED DATE PROV_STA_CD COUNT TOTAL_COUNT
------------------ ------------- ----------- ----------- -----------
EMP030 Oct 21 2008 S 5
EMP030 Oct 20 2008 S 5394
EMP0305399
EMP031 Oct 21 2008 H 1
EMP031 Oct 21 2008 G 1
EMP0312
EMP032 Oct 21 2008 G 57
EMP032 Oct 20 2008 H 1
EMP032 Oct 20 2008 G 2175
EMP0322233
DO we have other alternatives to achive this?.........
Or can some one have different idea and logic for this issue......
All my users want is reports.........of something like above nature
October 27, 2008 at 2:16 pm
What are you using to render the report? Normally that kind of detail/sum reporting would be best handled by using grouping in the reporting solution you use whether it is Access, Crystal Reports, or SSRS. You could do it in T-SQL, but most reporting solutions have something to do that work for you.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 27, 2008 at 2:21 pm
I am using T-SQL.
This will be a part of daily job through SSIS. I have to output the query results set to file and then send file to Users.......
October 27, 2008 at 2:51 pm
Have you looked a SSRS (it is part of the SQL Server license)? You can schedule reports to be sent via email.
I'd do something like this in T-SQL:
SELECT DISTINCT
P.VEND_ID,
CONVERT(char(12),P.RCVD_FR_EDI_TS) as [RECEIVED DATE],
P.PROV_STA_CD,
-- needed to change this to use the P. qualifier
Count(P.Prov_Key)as [COUNT],
Case
-- this only puts the total count on the last row
When Row_Number() Over(partition by vend_id
order by P.RCVD_FR_EDI_TS desc) = 1 Then Null
Else TOTAL.[Total Count]
End
FROM
PROV P JOin
-- use a derived table to get the total by vendor
(
Select
vend_id,
Count(*) as [total_count]
From
PROV
Group BY
vend_Id
) TOTAL ON
P.vend_id = TOTAL.vend_id
GROUP BY
P.VEND_ID,
P.PROV_STA_CD,
CONVERT(char(12),P.RCVD_FR_EDI_TS),
TOTAL.[Total Count]
ORDER BY
P.VEND_ID,
P.[RECEIVED DATE],
P.PROV_STA_CD
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 27, 2008 at 3:05 pm
Thanks Jack. I don't believe they will be ready for SSRS.
What I am gonna do is put the result set into a variable in SSIS and send email to users using this variable, or output the result set to flat file and sedn it as attachment.....
Ia m trying to figure out some syntatical problems .........
here it is
Msg 207, Level 16, State 1, Line 30
Invalid column name 'Total Count'.
Msg 209, Level 16, State 1, Line 9
Ambiguous column name 'vend_id'.
Msg 207, Level 16, State 1, Line 11
Invalid column name 'Total Count'.
Msg 207, Level 16, State 1, Line 33
Invalid column name 'RECEIVED DATE'.
Msg 145, Level 15, State 1, Line 33
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
October 27, 2008 at 3:15 pm
After Alteratiosn these are the errors.....
Msg 8120, Level 16, State 1, Line 1
Column 'PROV.RCVD_FR_EDI_TS' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1
Column 'PROV.RCVD_FR_EDI_TS' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
October 27, 2008 at 3:22 pm
gyanendra.khadka (10/27/2008)
Thanks Jack. I don't believe they will be ready for SSRS.
Do you mean your users or you? SSRS will give them the data in a easier to read and more aesthetically pleasing format while making things like this MUCH simpler.
As far as the syntax errors, they are mostly typos as I tried to duplicate your aliases, but habit kept me from putting spaces in column names.
For the other errors, because of the GROUP BY you shouldn't need the DISTINCT so I'd remove the DISTINCT. Then remove the CAST from the gorup by and just use the column
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 27, 2008 at 3:29 pm
USERS...........
October 27, 2008 at 3:37 pm
I modified ur query to look like this
SELECT
P.VEND_ID,
CAST(P.RCVD_FR_EDI_TS as Char(12)) as [RECEIVED DATE],
P.PROV_STA_CD,
-- needed to change this to use the P. qualifier
Count(P.Prov_Key)as [COUNT],
Case
-- this only puts the total count on the last row
When Row_Number() Over(partition by P.vend_id
order by P.RCVD_FR_EDI_TS desc) = 1 Then Null
Else TOTAL.[Total_Count]
End
FROM
PROV P JOin
-- use a derived table to get the total by vendor
(
Select
vend_id,
Count(*) as [total_count]
From
PROV
Group BY
vend_Id
) TOTAL ON
P.vend_id = TOTAL.vend_id
GROUP BY
P.VEND_ID,
P.PROV_STA_CD,
CAST(P.RCVD_FR_EDI_TS as Char(12)),
TOTAL.[Total_Count]
ORDER BY
P.VEND_ID,
[RECEIVED DATE],
P.PROV_STA_CD
Still I am getting this error............
Msg 8120, Level 16, State 1, Line 1
Column 'PROV.RCVD_FR_EDI_TS' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1
Column 'PROV.RCVD_FR_EDI_TS' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
October 28, 2008 at 8:13 am
hey Jack Ur Query gave me this result sets......there is no total on the last columns........results are random........
VEND_ID RECEIVED DATE PROV_STA_CD COUNT
------------------ ------------- ----------- ----------- -----------
EMP030 Oct 20 2008 S 5394 5399
EMP030 Oct 21 2008 S 5 NULL
EMP031 Oct 21 2008 G 1 NULL
EMP031 Oct 21 2008 H 1 2
EMP032 Oct 20 2008 G 2175 2233
EMP032 Oct 20 2008 H 1 2233
EMP032 Oct 21 2008 G 57 NULL
EMP033 Oct 20 2008 G 446 454
EMP033 Oct 20 2008 H 3 NULL
EMP033 Oct 20 2008 H 5 454
EMP035 Oct 20 2008 G 23 17461
EMP035 Oct 20 2008 G 16744 17461
EMP035 Oct 20 2008 N 491 17461
EMP035 Oct 22 2008 N 45 17461
EMP035 Oct 28 2008 N 158 NULL
EMP037 Oct 20 2008 G 955 1057
EMP037 Oct 20 2008 H 2 1057
EMP037 Oct 20 2008 H 5 NULL
EMP037 Oct 20 2008 H 95 1057
October 29, 2008 at 9:37 am
I have a hint for you. Use over clause with aggregate functions. On the top level of your statement please use something like the code below. You need to check whether row number in your output for a vend_id is the last row for that vend_id.
SELECT
A.*,
B.TOTAL_COUNT,
case
when count(A.VEND_ID) over (partition by A.VEND_ID) = row_number() over (partition by A.VEND_ID order by [Put here list of fields that define order of rows on you report])
then 'last row for VEND_ID'
else ''
end
[...]
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply