Viewing 15 posts - 76 through 90 (of 210 total)
October 7, 2018 at 12:21 pm
Order By
...
October 7, 2018 at 12:00 pm
USE TEEST;
GO
SET NOCOUNT ON;
DECLARE @NAME...
October 7, 2018 at 11:14 am
October 7, 2018 at 5:02 am
October 1, 2018 at 6:24 am
I need to find the running total for each group order by date.
So the query is select.......,...
October 1, 2018 at 2:28 am
IF Object_id('tempdb..#t') IS NOT NULL
DROP TABLE #t; SELECT '0010970115' AS EMP,
'20160701' AS EffectiveDate,
'20170131' AS TermDate,
'Elig_Change' AS SpanReason
INTO #t
INSERT INTO #t
(emp,
effectivedate,
termdate,
spanreason)
SELECT '0010970115',
'20170201',
'20171130',
'Elig_Change'
INSERT INTO #t
(emp,
effectivedate,
termdate,
spanreason)
SELECT '0010970115',
'20171201',
'20171231',
'Elig_Change'
INSERT INTO #t
(emp,
effectivedate,
termdate,
spanreason)
SELECT '0010970115',
'20180101',
'20180930',
'Elig_Change'
INSERT INTO #t
(emp,
effectivedate,
termdate,
spanreason)
SELECT '0010970115',
'20180407',
'20180811',
'LTI'
INSERT INTO #t
(emp,
effectivedate,
termdate,
spanreason)
SELECT '0010970115',
'20180609',
'20180708',
'HOSPICE';
SELECT table1.emp,
Min(table1.effectivedate) AS startdate,
Max(table2.termdate) AS enddate,
table1.spanreason
FROM (SELECT *,
Row_number()
OVER (
partition BY emp, spanreason
ORDER BY effectivedate) AS rnk
FROM #t)table1
INNER JOIN (SELECT *,
Row_number()
OVER (
partition BY emp, spanreason
ORDER BY effectivedate) AS rnk
FROM #t)table2
ON table1.rnk + 1 = table2.rnk
GROUP BY table1.emp,
table1.spanreason ;
Output:
emp SpanReason startdate enddate
0010970115 Elig_Change 20160701 20180930
0010970115 HOSPICE 20180609 20180708
0010970115 LTI 20180407 20180811
September 30, 2018 at 10:40 am
| Pkey | RequestId | StateName | ResolverGroup | DateCreated |
|---|---|---|---|---|
| 1 | 123 | Resolved | 29 | 9/27/18 15:16 |
| 2 | 123 | Acknowledge | 29 | 9/27/18 15:16 |
| 3 | 123 | Assign To | 29 | 9/27/18 15:08 |
| 4 | 123 | Assign... |
September 30, 2018 at 9:37 am
I have a View, [ViewName] when I select from this view I get 10 rows back.
Now I...
September 26, 2018 at 8:15 am
September 22, 2018 at 11:07 am
I am trying to build sql statement, but got struck. Any inputs please?
August 21, 2018 at 1:22 pm
I am trying to build sql statement, but got struck. Any inputs please?