quote:

---

I've learned from reading that for a stored procedure to return records, I should use a RETURN codes or OUTPUT parameters.

---

USE databasename
GO
select distinct
m.medicaidid
, m.firstname
, m.lastname
, m.dateOfbirth
,database.dbo.fnAgeOnExactDate(database.dbo.FnConvDateNull(m.DateOfBirth), getdate()) as 'Age'
, max(c.admitdate)as 'last visit'
,originalbillingname

from claims c
join claimsdetail cd on c.claimsseqnbr = cd.claimsseqnbr join members m on c.MemberNbr = m.MemberNbr join providers p on m.currassignedprovider1 = p.providerNbr where cd.providernumberhcfa = 'value'
and c.admitdate between  20150101 and 20150430
and c.claimtype ='E'
and m.medicaidid <>'999999999'
group by
m.medicaidid
, m.firstname
, m.lastname
, m.dateOfbirth
,originalbillingname

USE [dbname]
GO
/****** Object:  StoredProcedure [dbo].[spname]    Script Date: 5/5/2015 3:46:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[PU_REPORTING_LOB-PHP_PRVDRUTILIZATION](@ClaimsFromDate date, @ClaimsToDate date, @ProviderNumber Varchar)

AS
BEGIN

    -- Insert statements for procedure here
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
--(the data in our table for dates is YYYYMMDD as int so we always have to get to the point of using an INT like that)

declare @claimsFromDateINT int
declare @claimsToDateINT int
set @claimsFromDateINT = cast(cast(year(@ClaimsFromDate) as varchar) + right('0' + cast(month(@ClaimsFromDate) as varchar),2)
				   + right('0' + cast(day(@ClaimsFromDate) as varchar),2) as int)
set @claimsToDateINT = cast(cast(year(@ClaimsToDate) as varchar) + right('0' + cast(month(@ClaimsToDate) as varchar),2)
				   + right('0' + cast(day(@ClaimsToDate) as varchar),2) as int) 

select distinct 
m.medicaidid
, m.firstname
, m.lastname
, m.dateOfbirth
, c.Memberage
, max(c.admitdate)as 'last visit'
,originalbillingname

 from [dbname].dbo.claims c
join [dbname].dbo.claimsdetail cd on c.claimsseqnbr = cd.claimsseqnbr
join [dbname].dbo.members m on c.MemberNbr = m.MemberNbr
join [dbname].dbo.providers p on m.currassignedprovider1 = p.providerNbr
where cd.providernumberhcfa = @ProviderNumber
and c.admitdate between  @claimsFromDateINT and @claimsToDateINT
and c.claimtype ='E'
and m.medicaidid <>'999999999'
group by
m.medicaidid
, m.firstname
, m.lastname
, m.dateOfbirth
, c.Memberage
,originalbillingname

order by 5

--finally, log report run for troubleshooting and many other useful purposes
insert [dbo].[tablename] (dateran,userran,reportname)
values (getdate(),SUSER_NAME(),'reportname')

END

quote:

---

Originally posted by ipisors

even when I execute it with date '2015-01-01' through '2015-04-30'

---

quote:

---

set @claimsFromDateINT = cast(cast(year(@ClaimsFromDate) as varchar) + right('0' + cast(month(@ClaimsFromDate) as varchar),2)
+ right('0' + cast(day(@ClaimsFromDate) as varchar),2) as int)

---

	CONVERT(INT, convert(VARCHAR(8), @ClaimsFromDate, 112))

quote:

---

@ProviderNumber Varchar)
cast(year(@ClaimsFromDate) as varchar)

---

quote:

---

I couldn't agree with you more about the table column data type

---

PRINT '@ProviderNumber='+COALESCE(@ProviderNumber+'[', '[NULL]')
    + ', @claimsFromDateINT='+COALESCE(CONVERT(varchar(20), @claimsFromDateINT)+'[', '[NULL]')
    + ', @claimsToDateINT='+COALESCE(CONVERT(varchar(20), @claimsToDateINT)+'[', '[NULL]')

quote:

---

Originally posted by ipisors

I finally had read a comment from some very authoritative-seeming person on stackoverflow who had published books (so their advice HAS to be good, right? <jk grin>), who told me exactly that the only unambiguous string value for date was 'YYYY-MM-DD'.

---

SET LANGUAGE ENGLISH
GO
SELECT CONVERT(date, '20150102'), CONVERT(datetime, '20150102'), CONVERT(date, '2015-01-02'), CONVERT(datetime, '2015-01-02')
-- 2015-01-02 2015-01-02 00:00:00.000 2015-01-02 2015-01-02 00:00:00.000
GO
SET LANGUAGE FRENCH
GO
SELECT CONVERT(date, '20150102'), CONVERT(datetime, '20150102'), CONVERT(date, '2015-01-02'), CONVERT(datetime, '2015-01-02')	-- This will be February!! ...
-- 2015-01-02 2015-01-02 00:00:00.000 2015-01-02 2015-02-01 00:00:00.000
SET LANGUAGE ENGLISH
GO
SET DATEFORMAT YDM
GO
-- DATETIME 'yyyy-dd-mm' successfully converted using DATEFORMAT, conversion to DATE did NOT use DATEFORMAT , 'yyyymmdd' ignores DATEFORMAT as expected
SELECT CONVERT(date, '20150102'), CONVERT(datetime, '20150102'), CONVERT(date, '2015-01-02'), CONVERT(datetime, '2015-01-02')
-- 2015-01-02 2015-01-02 00:00:00.000 2015-01-02 2015-02-01 00:00:00.000
GO

quote:

---

I'm testing my procedure and his procedure basically the EXACT same way

---

quote:

---

I have zero control over this table, so while the conversation about nulls vs. dummy values is profitable (and I realize this myself), I can't do anything about it - just solve to the existing structure.

---