Count of days - 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

Count of days

Author

Topic

gator8869
Starting Member

37 Posts

Posted - 2007-08-28 : 13:32:50

I am looking to do the following:
CurrentDate - admit date /24 (round down to whole number)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-28 : 13:42:04

SELECT DATEDIFF(DAY, AdmitDate, CURRENT_TIMESTAMP)
FROM Table1

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-28 : 13:43:13

What are you trying to accomplish?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=88487
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=88547

E 12°55'05.25"
N 56°04'39.16"

gator8869
Starting Member

37 Posts

Posted - 2007-08-28 : 14:09:50

quote:
Originally posted by Peso

What are you trying to accomplish?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=88487
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=88547

E 12°55'05.25"
N 56°04'39.16"

I am trying to get data so that I can run a Crystal Report that shows missing Nursing_Assessments between admit date and discharge date there needs to be 1 nursing assessment completed for every 24 hours

SELECT a.OP__FOLDERID, b.date1, CONVERT(CHAR(10), a.DATE_ADM, 110) AS date_adm, d.OP__TIERNAME, CONVERT(CHAR(5), a.admit_time, 108) AS admit_time,
a.unit_admit, CONVERT(CHAR(10), c.order_date, 110) AS order_date, a.folderid, CONVERT(CHAR(10), getdate(), 110) AS current_date1
FROM dbo.FD__ADMIT_ORDER a LEFT OUTER JOIN
dbo.FD__NURSING_ASSESSMENTS b ON a.folderid = b.OP__FOLDERID LEFT OUTER JOIN
dbo.FD__DISCHARGE_ORDERS c ON a.folderid = c.folderid INNER JOIN
dbo.T4W_FOLDERS d ON a.folderid = d.OP__ID
WHERE (a.unit_admit IN ('East', 'West', 'East Unit', 'West Unit', 'Observation')) AND (a.OP__DOCID =
(SELECT MAX(op__docid)
FROM fd__admit_order
WHERE folderid = a.folderid)) AND (c.order_date IS NULL) AND (a.DATE_ADM > CONVERT(DATETIME, '2007-08-01 00:00:00', 102)) OR
(a.unit_admit IN ('East', 'West', 'East Unit', 'West Unit', 'Observation')) AND (a.OP__DOCID =
(SELECT MAX(op__docid)
FROM fd__admit_order
WHERE folderid = a.folderid)) AND (a.DATE_ADM > CONVERT(DATETIME, '2007-08-01 00:00:00', 102)) AND (c.OP__DOCID =
(SELECT MAX(op__docid)
FROM fd__discharge_orders
WHERE folderid = a.folderid))
op__folderid|date1 |date_adm |op__tiername _time|unit_admit|order_date|folderid|current_date1
160423894|2007-08-2700:00:00.000|08-21-2007|GxxxA , FExxxxxxDO |11:09|West|06-29-2007|31506|08-28-2007

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-28 : 14:28:08

[code]SELECT a.OP__FOLDERID,
b.date1,
a.DATE_ADM, -- Let Crystal Reports format the date
d.OP__TIERNAME,
a.admit_time, -- Let Crystal Reports format the time
a.unit_admit,
c.order_date, -- Let Crystal Reports format the date
a.folderid,
CURRENT_TIMESTAMP AS current_date1 -- Let Crystal Reports format the date
FROM (
SELECT a.OP__FOLDERID,
b.date1,
a.DATE_ADM, -- Let Crystal Reports format the date
d.OP__TIERNAME,
a.admit_time, -- Let Crystal Reports format the time
a.unit_admit,
c.order_date, -- Let Crystal Reports format the date
a.folderid,
CURRENT_TIMESTAMP AS current_date1, -- Let Crystal Reports format the date
ROW_NUMBER() OVER (PARTITION BY a.folderid ORDER BY c.op__docid) AS RecID1,
ROW_NUMBER() OVER (PARTITION BY a.folderid ORDER BY a.op__docid) AS RecID2,
FROM dbo.FD__ADMIT_ORDER AS a
INNER JOIN dbo.T4W_FOLDERS AS d ON d.OP__ID = a.folderid
LEFT JOIN dbo.FD__NURSING_ASSESSMENTS AS b ON b.OP__FOLDERID = a.folderid
LEFT JOIN dbo.FD__DISCHARGE_ORDERS AS c ON c.folderid = a.folderid
WHERE c.order_date IS NULL
AND a.unit_admit IN ('East', 'West', 'East Unit', 'West Unit', 'Observation')
AND a.DATE_ADM > '20070801'
) AS d
WHERE (RecID1 = 1 AND Order_Date IS NULL)
OR (RecID1 = 1 AND RecID2 = 1)[/code]

E 12°55'05.25"
N 56°04'39.16"

gator8869
Starting Member

37 Posts

Posted - 2007-08-28 : 14:36:47

quote:

Originally posted by Peso

SELECT	a.OP__FOLDERID,
	b.date1,
	a.DATE_ADM, -- Let Crystal Reports format the date
	d.OP__TIERNAME,
	a.admit_time, -- Let Crystal Reports format the time
	a.unit_admit,
	c.order_date, -- Let Crystal Reports format the date
	a.folderid,
	CURRENT_TIMESTAMP AS current_date1 -- Let Crystal Reports format the date
FROM	(
		SELECT		a.OP__FOLDERID,
				b.date1,
				a.DATE_ADM, -- Let Crystal Reports format the date
				d.OP__TIERNAME,
				a.admit_time, -- Let Crystal Reports format the time
				a.unit_admit,
				c.order_date, -- Let Crystal Reports format the date
				a.folderid,
				CURRENT_TIMESTAMP AS current_date1, -- Let Crystal Reports format the date
				ROW_NUMBER() OVER (PARTITION BY a.folderid ORDER BY c.op__docid) AS RecID1,
				ROW_NUMBER() OVER (PARTITION BY a.folderid ORDER BY a.op__docid) AS RecID2,
		FROM		dbo.FD__ADMIT_ORDER AS a
		INNER JOIN	dbo.T4W_FOLDERS AS d ON d.OP__ID = a.folderid
		LEFT JOIN	dbo.FD__NURSING_ASSESSMENTS AS b ON b.OP__FOLDERID = a.folderid
		LEFT JOIN	dbo.FD__DISCHARGE_ORDERS AS c ON c.folderid = a.folderid
		WHERE		c.order_date IS NULL
				AND a.unit_admit IN ('East', 'West', 'East Unit', 'West Unit', 'Observation')
				AND a.DATE_ADM > '20070801'
	) AS d
WHERE	(RecID1 = 1 AND Order_Date IS NULL)
	OR (RecID1 = 1 AND RecID2 = 1)

E 12°55'05.25"
N 56°04'39.16"

Server: Msg 195, Level 15, State 10, Line 20
'ROW_NUMBER' is not a recognized function name.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-28 : 14:44:04

Oh, you forgot the include this vital piece of information that you are using SQL Server 2000?

E 12°55'05.25"
N 56°04'39.16"

gator8869
Starting Member

37 Posts

Posted - 2007-08-28 : 14:51:40

quote:
Originally posted by Peso

Oh, you forgot the include this vital piece of information that you are using SQL Server 2000?

E 12°55'05.25"
N 56°04'39.16"

Sorry

gator8869
Starting Member

37 Posts

Posted - 2007-08-28 : 15:19:29

I need to get the DATEDIFF in hours so that I see that 1 nursing assessment was completed within a 24 hour period and count the 24 hours periods from the date_adm to current datetime and if the client was discharged order_date minus adm_date.

SELECT a.OP__FOLDERID, b.date1, a.DATE_ADM, d.OP__TIERNAME, CONVERT(CHAR(5), a.admit_time, 108) AS admit_time, a.unit_admit, c.order_date, a.folderid,
CAST(a.DATE_ADM + ' ' + a.admit_time AS SMALLDATETIME) AS f.admit_date_time, DATEDIFF(DAY, admit_date_time, CURRENT_TIMESTAMP)
FROM dbo.FD__ADMIT_ORDER a LEFT OUTER JOIN
dbo.FD__NURSING_ASSESSMENTS b ON a.folderid = b.OP__FOLDERID LEFT OUTER JOIN
dbo.FD__DISCHARGE_ORDERS c ON a.folderid = c.folderid INNER JOIN
dbo.T4W_FOLDERS d ON a.folderid = d.OP__ID
WHERE (a.unit_admit IN ('East', 'West', 'East Unit', 'West Unit', 'Observation')) AND (a.OP__DOCID =
(SELECT MAX(op__docid)
FROM fd__admit_order
WHERE folderid = a.folderid)) AND (c.order_date IS NULL) AND (a.DATE_ADM > CONVERT(DATETIME, '2007-08-01 00:00:00', 102)) OR
(a.unit_admit IN ('East', 'West', 'East Unit', 'West Unit', 'Observation')) AND (a.OP__DOCID =
(SELECT MAX(op__docid)
FROM fd__admit_order
WHERE folderid = a.folderid)) AND (a.DATE_ADM > CONVERT(DATETIME, '2007-08-01 00:00:00', 102)) AND (c.OP__DOCID =
(SELECT MAX(op__docid)
FROM fd__discharge_orders
WHERE folderid = a.folderid))

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-28 : 15:27:08

[code]CAST(a.DATE_ADM + ' ' + a.admit_time AS SMALLDATETIME) AS f.admit_date_time,
DATEDIFF(DAY, CAST(a.DATE_ADM + ' ' + a.admit_time AS SMALLDATETIME), CURRENT_TIMESTAMP)[/code]

E 12°55'05.25"
N 56°04'39.16"

gator8869
Starting Member

37 Posts

Posted - 2007-08-28 : 15:56:38

quote:

Originally posted by Peso

CAST(a.DATE_ADM + ' ' + a.admit_time AS SMALLDATETIME) AS f.admit_date_time,
DATEDIFF(DAY, CAST(a.DATE_ADM + ' ' + a.admit_time AS SMALLDATETIME), CURRENT_TIMESTAMP)

E 12°55'05.25"
N 56°04'39.16"

If the order_date = discharge date and admit_date > orderdate then I need to get the count of days from admit_date to current date in a 24 hour periods
example: if client comes in at 5:30 they wont need another nursing_assessment until 5:30 the following day

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-28 : 15:59:50

I think you better post some sample data with DDL, and your expected output based on the sample data.

E 12°55'05.25"
N 56°04'39.16"

gator8869
Starting Member

37 Posts

Posted - 2007-08-28 : 16:25:12

SELECT a.OP__FOLDERID, b.date1, a.DATE_ADM, d.OP__TIERNAME, CONVERT(CHAR(5), a.admit_time, 108) AS admit_time, a.unit_admit, c.order_date, a.folderid,
CAST(a.DATE_ADM + ' ' + a.admit_time AS SMALLDATETIME) AS admit_date_time, DATEDIFF(DAY, CAST(a.DATE_ADM + ' ' + a.admit_time AS DATETIME), CURRENT_TIMESTAMP) AS days_from_admit
FROM dbo.FD__ADMIT_ORDER a LEFT OUTER JOIN
dbo.FD__NURSING_ASSESSMENTS b ON a.folderid = b.OP__FOLDERID LEFT OUTER JOIN
dbo.FD__DISCHARGE_ORDERS c ON a.folderid = c.folderid INNER JOIN
dbo.T4W_FOLDERS d ON a.folderid = d.OP__ID
WHERE (a.unit_admit IN ('East', 'West', 'East Unit', 'West Unit', 'Observation')) AND (a.OP__DOCID =
(SELECT MAX(op__docid)
FROM fd__admit_order
WHERE folderid = a.folderid)) AND (c.order_date IS NULL) AND (a.DATE_ADM > CONVERT(DATETIME, '2007-08-01 00:00:00', 102)) OR
(a.unit_admit IN ('East', 'West', 'East Unit', 'West Unit', 'Observation')) AND (a.OP__DOCID =
(SELECT MAX(op__docid)
FROM fd__admit_order
WHERE folderid = a.folderid)) AND (a.DATE_ADM > CONVERT(DATETIME, '2007-08-01 00:00:00', 102)) AND (c.OP__DOCID =
(SELECT MAX(op__docid)
FROM fd__discharge_orders
WHERE folderid = a.folderid))

op__folderid|date1|date_adm|op_tiername|admit_time|unit_admit|order_date|folderid|admit_date_time|days_from_admit|
160423894|2007-08-27 00:00:00.000|2007-08-21 00:00:00.000|X,X|11:09|West|2007-06-29 00:00:00.000|31506|2007-08-21 11:09:00|7
160423894|2007-08-28 00:00:00.000|2007-08-21 00:00:00.000|X,X|11:09|West|2007-06-29 00:00:00.000|31506|2007-08-21 11:09:00|7
160422760|2007-08-27 00:00:00.000|2007-08-20 00:00:00.000|X,X|16:49|West|2007-07-31 00:00:00.000|9335 |2007-08-20 16:49:00|8
160390699|NULL |2007-08-03 00:00:00.000|X,X|00:39|East|2007-08-03 00:00:00.000|33763|2007-08-03 00:39:00|25
160390466|NULL |2007-08-02 00:00:00.000|X,X|20:15|West|2007-08-03 00:00:00.000|19620|2007-08-02 20:15:00|26

gator8869
Starting Member

37 Posts

Posted - 2007-08-28 : 16:30:25

quote:
Originally posted by Peso

I think you better post some sample data with DDL, and your expected output based on the sample data.

E 12°55'05.25"
N 56°04'39.16"

I really appreciate the help

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-28 : 16:33:00

Good for you!

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources