December 23, 2009 at 10:10 am
Paul Morris-1011726 (12/23/2009)
Thanks Greg for the feedback... Constructive criticism is always welcome. I am still new to this and I find the folks here very helpful. Let me see if I can get the correct info together.again thanks
No problem Paul. We've all been there, and I have certanly benefited from some helpful criticism myself.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
December 23, 2009 at 12:09 pm
Paul Morris-1011726 (12/23/2009)
SELECT d.pickdate, d.status, d.rownum,StatusDays =
CASE
WHEN d.rownum <> MAX(d.rownum) THEN DATEDIFF(day, d.pickdate, nextd.pickdate)
ELSE DATEDIFF(day, d.pickdate, GETDATE())
END
FROM #DaysinStatus AS d LEFT JOIN
#DaysinStatus as nextd ON nextd.rownum = d.rownum + 1
[/code]
The issue is that I need to also calculate the last row based against the day report is run... When I use max(rownum) with CASE I get the following error:
Column '#DaysinStatus.pickdate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
I tried GROUP BY but that didn't work....
As always any help would be great... thanks in advance!!!
Paul, I love the holiday season. Many people are off, and those of us who are here are not really wanting to take any action on anything that might cause some action to be taken by someone else. So personally, I'm doing my part by just lurking here all week. Anyhow, in the absense of the sample data I hope is coming, you could very well do what you are trying to do here with a CASE WHEN and GROUP BY and all that, and I would suggest reading up on aggregate functions to figure out why you were getting an error. However, your problem might be better addressed by taking a look at the DATEDIFF function, and seeing what happens when one of the input dates is NULL, which is what is happening to you. Try this...
SELECT DATEDIFF(dd,'12/1/2009',NULL)
See what I mean? So, based on some of the earlier posts on this thread, you already know how to fix this issue without the CASE and the GROUP BY. Now, I don't want to discourage you from trying it that way, but I think you will quickly see what I'm talking about.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
December 23, 2009 at 12:12 pm
OK let me give the big picture: This is for a subreport that will return the days a case is in a particular status with the final number of days being based on the day the report is run. I am using BIDS in SQL 2008 in text mode.
This is the case_pick table (hopefully this is part of what is needed. I right clicked table and scripted table with CREATE TO):
/****** Object: Table [dbo].[case_pick] Script Date: 12/23/2009 12:26:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[case_pick](
[case_pick_sk] [int] NOT NULL,
[case_sk] [int] NOT NULL,
[group_code] [varchar](6) NOT NULL,
[current_ind] [char](1) NOT NULL,
[item_sk] [int] NULL,
[pick_date] [datetime] NULL,
[comments] [text] NULL,
[create_date] [datetime] NULL,
[create_user_id] [varchar](10) NULL,
[maint_date] [datetime] NULL,
[maint_user_id] [varchar](10) NULL,
[editstamp] [timestamp] NULL,
CONSTRAINT [PK_case_pick] PRIMARY KEY CLUSTERED
(
[case_pick_sk] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
And this is the group_items table (created the same way as above):
/****** Object: Table [dbo].[group_items] Script Date: 12/23/2009 12:35:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[group_items](
[item_sk] [int] NOT NULL,
[group_code] [varchar](6) NOT NULL,
[description] [varchar](30) NULL,
[search_code_ind] [char](1) NULL,
[abbreviation] [varchar](6) NULL,
[statute_period] [int] NULL,
[statute_days] [int] NULL,
[use_package_sk] [int] NULL,
[pd_ind] [char](1) NULL,
[sequence] [int] NULL,
[dataobject_name] [varchar](30) NULL,
[picture_name] [varchar](30) NULL,
[sk_column] [varchar](20) NULL,
[create_user_id] [varchar](10) NULL,
[create_date] [datetime] NULL,
[maint_user_id] [varchar](10) NULL,
[maint_date] [datetime] NULL,
[new_case_ind] [char](1) NULL,
[default_search] [char](1) NULL,
[window_name] [varchar](30) NULL,
[dataobject_name_2] [varchar](30) NULL,
[dataobject_name_3] [varchar](30) NULL,
[menuobject_name] [varchar](30) NULL,
[userobject_name] [varchar](30) NULL,
[viewerobject_name] [varchar](30) NULL,
[journal_view_ind] [char](1) NULL,
[item_color] [varchar](15) NULL,
[notes] [text] NULL,
[unit_cost] [decimal](7, 2) NULL,
[checkout_view_ind] [char](1) NULL,
[EditStamp] [timestamp] NULL,
[report_services_ind] [char](1) NULL,
PRIMARY KEY NONCLUSTERED
(
[item_sk] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[group_items] WITH NOCHECK ADD CONSTRAINT [FK__group_ite__group__27F8EE98] FOREIGN KEY([group_code])
REFERENCES [dbo].[group_codes] ([group_code])
GO
ALTER TABLE [dbo].[group_items] NOCHECK CONSTRAINT [FK__group_ite__group__27F8EE98]
GO
I provided the entire structure of both tables even though I am only using couple parts so I created this code to simplify and adjusted the original code to accomidate the temp tables I am using for testing.
--=============================================================
-- temp table for test data
--========================================================
CREATE TABLE #case_pick
(
case_sk int,
group_code varchar(6),
item_sk int,
pick_date datetime,
)
INSERT INTO #case_pick (case_sk, group_code, item_sk, pick_date)
SELECT 34568, 'CSTATU', 4241, NULL UNION ALL
SELECT 34568, 'CSTATU', 1040, '2009-03-16' UNION ALL
SELECT 34568, 'CSTATU', 4247, '2009-01-28' UNION ALL
SELECT 34568, 'CSTATU', 4219, '2009-06-16'
-- select * from #case_pick
-- DROP TABLE #case_pick
--===============================================================
--temp table for test data
--===============================================================
CREATE TABLE #group_items
(
item_sk int,
description varchar(30),
)
INSERT INTO #group_items (item_sk, description)
SELECT 4241, 'New Case' UNION ALL
SELECT 1040, 'Treating' UNION ALL
SELECT 4247, 'Released' UNION ALL
SELECT 4219, 'Submitted'
-- select * from #group_items
-- drop table #group_items
modified code for above temp tables:
CREATE TABLE #DaysinStatus
(
casesk int,
pickdate datetime,
status varchar(30),
rownum int
)
INSERT INTO #DaysinStatus
SELECT case_sk, COALESCE(pick_date, '2009-01-1 00:00:00.000'), gi.description,
ROW_NUMBER() OVER (ORDER BY cp.pick_date) AS row
FROM #case_pick AS cp INNER JOIN
#group_items AS gi ON cp.item_sk = gi.item_sk
WHERE (cp.group_code = 'cstatu') AND (case_sk = 34568)
ORDER BY pick_date
SELECT d.pickdate, d.status, d.rownum, DATEDIFF(day, d.pickdate, nextd.pickdate) AS StatusDays
FROM #DaysinStatus AS d LEFT JOIN
#DaysinStatus as nextd ON nextd.rownum = d.rownum + 1
which displays this:
2009-01-01 00:00:00.000New Case127
2009-01-28 00:00:00.000Released247
2009-03-16 00:00:00.000Treating392
2009-06-16 00:00:00.000Submitted4NULL
so with all this back... to my original question. I need to calculate the number of days the last row has been in that status based on the day the report is run.
Any ideas on how to accomplish that?
Thanks! Let me know if this was over kill, just right or not enough 🙂
December 23, 2009 at 12:29 pm
LOL must have posted within a few seconds of you....
appreciate you trolling here... not that it is a big deal. This is more for 'extra credit' for the powers that be... It is a bit slow for me so trying to learn more on how to write t-sql code. I typically use BIDS GUI but can't accomplish some things with it.
Thanks! Let me know if the above is what is needed.....
December 23, 2009 at 1:15 pm
Paul Morris-1011726 (12/23/2009)
2009-01-01 00:00:00.000New Case127
2009-01-28 00:00:00.000Released247
2009-03-16 00:00:00.000Treating392
2009-06-16 00:00:00.000Submitted4NULL
If all you need to do is get rid of that NULL up there, see if this works...
SELECT
d.pickdate,
d.status,
d.rownum,
DATEDIFF(day, d.pickdate, ISNULL(nextd.pickdate,GETDATE())) AS StatusDays
FROM #DaysinStatus AS d LEFT JOIN
#DaysinStatus as nextd ON nextd.rownum = d.rownum + 1
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
December 23, 2009 at 1:36 pm
What I need it to do is return the datediff(day, <last row pickdate>, getdate()) for the last cell.
What yours returns is the datediff between the first row pickdate and getdate which is 190 days.
The actual difference is 90 days between the last row pickdate and getdate.
It also has to be dynamic. The row count will vary from 1 row to 12 rows.
I tried to figure out a loop that compares the rownum to MAX(rownum) (tried with WHILE and IF.. ELSE) but couldn't figure out how to get it to work.
December 23, 2009 at 2:32 pm
Ok, forgive me for seeming dense, but the greatest date of any in your sample data is '6/16/2009'. I don't see how you expect to return a value of 90 when this date is used with GETDATE() in any fashion. Are there supposed to be records in addition to ones you have posted, like one that has a value of, say, '9/24/2009'? I'm going home now, but maybe a light will come on when I look at it later. As for posting the data, that was good, it helps just to see all the pieces of the puzzle. Could you maybe post sample data for a scenario where there are the full 12 records, and the outcome you expect?
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
December 23, 2009 at 2:43 pm
No rush on this... Like i said this is just for extra credit 🙂
I will put together something Monday.
Maybe I am not being clear... I will be more so with the next sample set.
Have a good Holiday....
December 23, 2009 at 3:54 pm
Paul Morris-1011726 (12/23/2009)
hmmm ... I was reading Jeff Moden's article about Forum Etiquette: How to post data/code on a forum to get the best help and was wondering, am providing the information correctly to help you better help me? or how can I better provide the info/data?
You already have code to isolate the data... now you just need to make it "readily consumable" like the "etiquette" article demonstrates....
--Jeff Moden
Change is inevitable... Change for the better is not.
December 28, 2009 at 7:28 am
OK. Took some time this morning to add a few more rows of test data. Below is the test data, creating the test data tables and the code I am in need of help modifiying.
--=============================================================
-- temp table for test data
--========================================================
CREATE TABLE #case_pick
(
case_sk int,
group_code varchar(6),
item_sk int,
pick_date datetime,
)
INSERT INTO #case_pick (case_sk, group_code, item_sk, pick_date)
SELECT 34568, 'CSTATU', 4241, NULL UNION ALL
SELECT 34568, 'CSTATU', 1040, '2009-03-16' UNION ALL
SELECT 34568, 'CSTATU', 4247, '2009-01-28' UNION ALL
SELECT 34568, 'CSTATU', 4219, '2009-06-16' UNION ALL
SELECT 34568, 'CSTATU', 4220, '2009-07-16' UNION ALL
SELECT 34568, 'CSTATU', 4221, '2009-08-16' UNION ALL
SELECT 34568, 'CSTATU', 4222, '2009-12-16' UNION ALL
SELECT 34568, 'CSTATU', 4223, '2009-12-25'
-- select * from #case_pick
-- DROP TABLE #case_pick
--===============================================================
--temp table for test data
--===============================================================
CREATE TABLE #group_items
(
item_sk int,
description varchar(30),
)
INSERT INTO #group_items (item_sk, description)
SELECT 4241, 'New Case'UNION ALL
SELECT 1040, 'Treating'UNION ALL
SELECT 4247, 'Released'UNION ALL
SELECT 4219, 'Submitted'UNION ALL
SELECT 4220, 'Some Status'UNION ALL
SELECT 4221, 'Some Status1'UNION ALL
SELECT 4222, 'Some Status2'UNION ALL
SELECT 4223, 'Some Status3'
-- select * from #group_items
-- drop table #group_items
--======================================================================================================
-- Code to return the date the status was entered, the status, and the days in that status calculated
-- by difference in previous status. Ex: row 2 datediff row 3
--=======================================================================================================
CREATE TABLE #DaysinStatus
(
casesk int,
pickdate datetime,
status varchar(30),
rownum int
)
INSERT INTO #DaysinStatus
SELECT case_sk, COALESCE(pick_date, '2009-01-1 00:00:00.000'), gi.description,
ROW_NUMBER() OVER (ORDER BY cp.pick_date) AS row
FROM #case_pick AS cp INNER JOIN
#group_items AS gi ON cp.item_sk = gi.item_sk
WHERE (cp.group_code = 'cstatu') AND (case_sk = 34568)
ORDER BY pick_date
SELECT d.pickdate, d.status, d.rownum, DATEDIFF(day, d.pickdate, nextd.pickdate) AS StatusDays
FROM #DaysinStatus AS d LEFT JOIN
#DaysinStatus as nextd ON nextd.rownum = d.rownum + 1
-- Select * from #DaysinStatus
--DROP TABLE #DaysinStatus
This displays this:
pickdate status rownumStatusDays
2009-01-01 00:00:00.000New Case127
2009-01-28 00:00:00.000Released247
2009-03-16 00:00:00.000Treating392
2009-06-16 00:00:00.000Submitted430
2009-07-16 00:00:00.000Some Status531
2009-08-16 00:00:00.000Some Status16122
2009-12-16 00:00:00.000Some Status279
2009-12-25 00:00:00.000Some Status38NULL
What I need it to display is this:
pickdate status rownumStatusDays
2009-01-01 00:00:00.000New Case127
2009-01-28 00:00:00.000Released247
2009-03-16 00:00:00.000Treating392
2009-06-16 00:00:00.000Submitted430
2009-07-16 00:00:00.000Some Status531
2009-08-16 00:00:00.000Some Status16122
2009-12-16 00:00:00.000Some Status279
2009-12-25 00:00:00.000Some Status383
The calculation for that last "cell" on the last row is the calculation of the pickdate of the status of the last row and todays date (in the this case 2009-12-25 and today = 2009-12-28).
So the question is, how can I add that calculation for that in the #DaysinStatus?
and for Jeff:
Is the above what you were referring to as "readily consumable"?
I am going to need some help as I move along in learning and want to make sure I provide what is needed to better help me.
Thanks in advanced!
December 28, 2009 at 8:40 am
Paul Morris-1011726 (12/28/2009)
and for Jeff:Is the above what you were referring to as "readily consumable"?
I am going to need some help as I move along in learning and want to make sure I provide what is needed to better help me.
Yep.... that's the ticket, Paul. Well done. I'm at work and I'll take a look at this tonight. Hopefully, someone else will jump in in the meantime.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 28, 2009 at 1:39 pm
Paul Morris-1011726 (12/28/2009)
Is the above what you were referring to as "readily consumable"?I am going to need some help as I move along in learning and want to make sure I provide what is needed to better help me.
Thanks in advanced!
Paul, I don't intend to put words into anyone's mouth, but for me, that is *exactly* what I'm talking about. I was able to just copy and paste the whole thing in QA, and voila, there is the output. I should note that I do that in a test DB on my laptop, where it does not matter what happens. The only thing I would add is that you include the test and drop with the table create statements, like this...
IF OBJECT_ID('TempDB..#case_pick','u') IS NOT NULL
DROP TABLE #case_pick
CREATE TABLE #case_pick
(
case_sk int,
group_code varchar(6),
item_sk int,
pick_date datetime,
)
That way I can hit execute whenever I want, and I will still arrive at the desired output. There was one poster a couple of days ago that indicated he could not post the word 'DROP', so he left them out. Not sure if he found resolution. There are various other methods to test for a table's existance, this is just the first one I learned here.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
December 28, 2009 at 1:55 pm
Paul Morris-1011726 (12/28/2009)
What I need it to display is this:
pickdate status rownumStatusDays
2009-01-01 00:00:00.000New Case127
2009-01-28 00:00:00.000Released247
2009-03-16 00:00:00.000Treating392
2009-06-16 00:00:00.000Submitted430
2009-07-16 00:00:00.000Some Status531
2009-08-16 00:00:00.000Some Status16122
2009-12-16 00:00:00.000Some Status279
2009-12-25 00:00:00.000Some Status383
As far as I can tell it is what you wanted your output to be. Once again, I'm sorry I'm not gettin' it, so am I missing something?
The calculation for that last "cell" on the last row is the calculation of the pickdate of the status of the last row and todays date (in the this case 2009-12-25 and today = 2009-12-28).
So the question is, how can I add that calculation for that in the #DaysinStatus?
Ok, either I'm really dense, or I am missing something. The below is simply using ISNULL with GETDATE(), which will only kick in on the last line.
SELECT
d.pickdate,
d.status,
d.rownum,
DATEDIFF(day, d.pickdate, ISNULL(nextd.pickdate,GETDATE())) AS StatusDays
FROM #DaysinStatus AS d LEFT JOIN
#DaysinStatus as nextd ON nextd.rownum = d.rownum + 1
With output
pickdatestatusrownumStatusDays
2009-01-01 00:00:00.000New Case127
2009-01-28 00:00:00.000Released247
2009-03-16 00:00:00.000Treating392
2009-06-16 00:00:00.000Submitted430
2009-07-16 00:00:00.000Some Status531
2009-08-16 00:00:00.000Some Status16122
2009-12-16 00:00:00.000Some Status279
2009-12-25 00:00:00.000Some Status383
I apologize for asking for further explanation, but am I missing something obvious?
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
December 28, 2009 at 2:15 pm
I removed the ISNULL and GETDATE() part of the code since it wasn't working the way I needed.
SELECT
d.pickdate,
d.status,
d.rownum,
DATEDIFF(day, d.pickdate, ISNULL(nextd.pickdate,GETDATE())) AS StatusDays
FROM #DaysinStatus AS d LEFT JOIN
#DaysinStatus as nextd ON nextd.rownum = d.rownum + 1
I apologize for asking for further explanation, but am I missing something obvious?
On the last row(what is returned)
2009-12-25 00:00:00.000 Some Status3 8 NULL
vs
(what I need returned)
2009-12-25 00:00:00.000 Some Status3 8 3
I manually calculated the 3 days. The actual code does not do it for me. Tomorrow that calculation should be 4 = (GETDATE() - 2009-12-25 (Pickdate of last row 'some status3')).
I just don't know how to 'plug' that in to my above code correctly so the last row for column "StatusDays" is calculated for me.
Does that clarify?
December 28, 2009 at 2:35 pm
Paul Morris-1011726 (12/28/2009)
I removed the ISNULL and GETDATE() part of the code since it wasn't working the way I needed.On the last row(what is returned)
2009-12-25 00:00:00.000 Some Status3 8 NULL
vs
(what I need returned)
2009-12-25 00:00:00.000 Some Status3 8 3
I manually calculated the 3 days. The actual code does not do it for me. Tomorrow that calculation should be 4 = (GETDATE() - 2009-12-25 (Pickdate of last row 'some status3')).
I just don't know how to 'plug' that in to my above code correctly so the last row for column "StatusDays" is calculated for me.
Does that clarify?
Paul, it returned the NULL on the last line because you removed the ISNULL and GETDATE. I used the exact code I posted to generate your desired last row. When you say it was not working the way you needed, what was the problem? Did you get errors? If so, what were they.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply