Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

?? on get info when last date was today - 80 ?? Expand / Collapse
Author
Message
Posted Monday, April 14, 2014 9:00 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, November 24, 2014 2:55 PM
Points: 301, Visits: 500
Hi

I have the following below to get the info i need.
Except I only want the records when the most recent recorded service starttime is now()-80

Thanks
Joe

SELECT dbo.Client.ID, dbo.RECORDED_SERVICE.STARTTIME
FROM dbo.Recorded_Service_Helper INNER JOIN
dbo.RECORDED_SERVICE INNER JOIN
dbo.Provider ON dbo.RECORDED_SERVICE.PRIMARY_PROVIDER_MONIKER = dbo.Provider.OID ON
dbo.Recorded_Service_Helper.Recorded_Service_OID = dbo.RECORDED_SERVICE.OID INNER JOIN
dbo.CostCtrGrp ON dbo.Recorded_Service_Helper.CostCtrGrp_OID = dbo.CostCtrGrp.OID INNER JOIN
dbo.CostCenter INNER JOIN
dbo.AGENCY_COST_CENTER ON dbo.CostCenter.OID = dbo.AGENCY_COST_CENTER.COST_CENTER_MONIKER ON
dbo.CostCtrGrp.CostCenter = dbo.AGENCY_COST_CENTER.OID INNER JOIN
dbo.Client ON dbo.Recorded_Service_Helper.Client_OID = dbo.Client.OID
WHERE (dbo.RECORDED_SERVICE.STARTTIME < { fn NOW() } - 80)
ORDER BY dbo.RECORDED_SERVICE.STARTTIME DESC, dbo.Client.ID
Post #1561510
Posted Monday, April 14, 2014 9:24 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 9:25 AM
Points: 953, Visits: 2,626
change this

WHERE (dbo.RECORDED_SERVICE.STARTTIME < { fn NOW() } - 80)


to

WHERE (dbo.RECORDED_SERVICE.STARTTIME < DATEADD(day,-80, GetDATE())

Be aware this will not affect the time.


_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1561523
Posted Monday, April 14, 2014 9:44 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, November 24, 2014 2:55 PM
Points: 301, Visits: 500
Thanks for the info

But I think I have explained this wrong...

I need clients that their last record on the recorded_Service table is today - 80

if anyone has any examples that would be great

Thanks
Joe
Post #1561532
Posted Monday, April 14, 2014 9:51 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 9:25 AM
Points: 953, Visits: 2,626
You can still use part of what Ive posted, as we don't have access to your data etc its difficult to be specific.


WHERE dbo.RECORDED_SERVICE.STARTTIME Between CONVERT(DATE, DATEADD(day,-80, GetDATE()))
AND CONVERT(DATE, DATEADD(day,-79, GetDATE()))


Alternatively you could do


WHERE CONVERT(DATE,dbo.RECORDED_SERVICE.STARTTIME) = CONVERT(DATE, DATEADD(day,-80, GetDATE()))



The performance may be an issue depending on how big the data set is and you may need to play around with the dates a little.


_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1561536
Posted Monday, April 14, 2014 9:51 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, November 24, 2014 2:55 PM
Points: 301, Visits: 500
I think I need something like this

But I really have my where clause screwed up !!!


sELECT distinct dbo.RECORDED_SERVICE.OID, MAX(dbo.RECORDED_SERVICE.STARTTIME) AS Expr1, dbo.SERVICE_ITEM.SERVICE_ITEM_DESCRIPTION, dbo.CostCenter.AbbrName,
dbo.Recorded_Service_Helper.Client_ID
FROM dbo.CostCenter INNER JOIN
dbo.AGENCY_COST_CENTER ON dbo.CostCenter.OID = dbo.AGENCY_COST_CENTER.COST_CENTER_MONIKER INNER JOIN
dbo.RECORDED_SERVICE INNER JOIN
dbo.Recorded_Service_Helper ON dbo.RECORDED_SERVICE.OID = dbo.Recorded_Service_Helper.Recorded_Service_OID INNER JOIN
dbo.CostCtrGrp ON dbo.Recorded_Service_Helper.CostCtrGrp_OID = dbo.CostCtrGrp.OID INNER JOIN
dbo.SERVICE_ITEM ON dbo.RECORDED_SERVICE.SERVICE_ITEM_MONIKER = dbo.SERVICE_ITEM.OID ON
dbo.AGENCY_COST_CENTER.OID = dbo.CostCtrGrp.CostCenter


where STARTTIME <
(select MAX(starttime) from RECORDED_SERVICE where OID = RECORDED_SERVICE.OID)
Post #1561538
Posted Monday, April 14, 2014 10:13 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:05 PM
Points: 13,327, Visits: 12,820
jbalbo (4/14/2014)
I think I need something like this

But I really have my where clause screwed up !!!


sELECT distinct dbo.RECORDED_SERVICE.OID, MAX(dbo.RECORDED_SERVICE.STARTTIME) AS Expr1, dbo.SERVICE_ITEM.SERVICE_ITEM_DESCRIPTION, dbo.CostCenter.AbbrName,
dbo.Recorded_Service_Helper.Client_ID
FROM dbo.CostCenter INNER JOIN
dbo.AGENCY_COST_CENTER ON dbo.CostCenter.OID = dbo.AGENCY_COST_CENTER.COST_CENTER_MONIKER INNER JOIN
dbo.RECORDED_SERVICE INNER JOIN
dbo.Recorded_Service_Helper ON dbo.RECORDED_SERVICE.OID = dbo.Recorded_Service_Helper.Recorded_Service_OID INNER JOIN
dbo.CostCtrGrp ON dbo.Recorded_Service_Helper.CostCtrGrp_OID = dbo.CostCtrGrp.OID INNER JOIN
dbo.SERVICE_ITEM ON dbo.RECORDED_SERVICE.SERVICE_ITEM_MONIKER = dbo.SERVICE_ITEM.OID ON
dbo.AGENCY_COST_CENTER.OID = dbo.CostCtrGrp.CostCenter


where STARTTIME <
(select MAX(starttime) from RECORDED_SERVICE where OID = RECORDED_SERVICE.OID)


You should get in the habit of aliasing your tables in your query. It makes life about a gazillion time easier. Also, it is rumored that referencing by full name in the select list is going to be deprecated.

Here is what your query looks like as you posted it using aliases.

SELECT distinct rs.OID, 
MAX(rs.STARTTIME) AS Expr1,
si.SERVICE_ITEM_DESCRIPTION,
cc.AbbrName,
rsh.Client_ID
FROM dbo.CostCenter cc
INNER JOIN dbo.AGENCY_COST_CENTER acc ON cc.OID = acc.COST_CENTER_MONIKER
INNER JOIN dbo.RECORDED_SERVICE rs --Missing one here
INNER JOIN dbo.Recorded_Service_Helper rsh ON rs.OID = rsh.Recorded_Service_OID
INNER JOIN dbo.CostCtrGrp ccg ON rsh.CostCtrGrp_OID = ccg.OID
INNER JOIN dbo.SERVICE_ITEM si ON rs.SERVICE_ITEM_MONIKER = si.OID
ON dbo.AGENCY_COST_CENTER.OID = dbo.CostCtrGrp.CostCenter --??? What is this???

where STARTTIME < (select MAX(starttime) from RECORDED_SERVICE where OID = RECORDED_SERVICE.OID)

There are some definite issues with the sql you posted. You have a join with no ON clause. You have an extra ON clause at the end. You have an aggregate but no group by. You are using distinct and aggregate in the same query. You have a subquery that I am assuming you want to reference the OID of the inner query but you don't reference it in your subquery.

A complete shot in the dark since we have no idea what these tables are like...

SELECT rs.OID, 
MAX(rs.STARTTIME) AS Expr1,
si.SERVICE_ITEM_DESCRIPTION,
cc.AbbrName,
rsh.Client_ID
FROM dbo.CostCenter cc
INNER JOIN dbo.AGENCY_COST_CENTER acc ON cc.OID = acc.COST_CENTER_MONIKER
INNER JOIN dbo.RECORDED_SERVICE rs on rs.OID = cc.OID --Don't know if this is right or not????
INNER JOIN dbo.Recorded_Service_Helper rsh ON rs.OID = rsh.Recorded_Service_OID
INNER JOIN dbo.CostCtrGrp ccg ON rsh.CostCtrGrp_OID = ccg.OID
INNER JOIN dbo.SERVICE_ITEM si ON rs.SERVICE_ITEM_MONIKER = si.OID

where STARTTIME < (select MAX(starttime) from RECORDED_SERVICE rs2 where OID = rs.OID)
--using an alias here we can now reference the outer table to get the value.
group by rs.OID,
si.SERVICE_ITEM_DESCRIPTION,
cc.AbbrName,
rsh.Client_ID



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1561553
Posted Monday, April 14, 2014 10:35 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, November 24, 2014 2:55 PM
Points: 301, Visits: 500
Hi Sean

Thanks for the info

Sorry having a hard time with grouping...

but after looking at yours I did come up with this which giving me the records I need (I Think so far!!)

BUT.. I need to add rs.OID but keek the same grouping and order ...
Thanks Again

SELECT
rsh.Client_ID, MAX(rs.STARTTIME) AS MaxDate
FROM RECORDED_SERVICE rs
INNER JOIN Recorded_Service_Helper rsh ON rs.OID = rsh.Recorded_Service_OID
GROUP BY rsh.Client_ID
HAVING(MAX(rs.STARTTIME) < { fn NOW() } - 80)
ORDER BY rsh.Client_ID, MaxDate DESC
Post #1561570
Posted Monday, April 14, 2014 11:10 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, November 24, 2014 2:55 PM
Points: 301, Visits: 500
so this gives me the Client ID and the max Starttime for the client id using now()-80
what I'd like to add is the RS.OID for that MAX Starttime
Post #1561581
Posted Monday, April 14, 2014 11:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:05 PM
Points: 13,327, Visits: 12,820
jbalbo (4/14/2014)
so this gives me the Client ID and the max Starttime for the client id using now()-80
what I'd like to add is the RS.OID for that MAX Starttime


I can help if you need it but I can't really offer much because I have no idea what your table structure is like. Post up some ddl and sample data along with the desired output and we can have a crack at it.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1561604
Posted Monday, April 14, 2014 12:03 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, November 24, 2014 2:55 PM
Points: 301, Visits: 500
sorry for the confusion...

SO this code

SELECT
rsh.Client_ID, MAX(rs.STARTTIME) AS MaxDate
FROM RECORDED_SERVICE rs
INNER JOIN Recorded_Service_Helper rsh ON rs.OID = rsh.Recorded_Service_OID
GROUP BY rsh.Client_ID
HAVING(MAX(rs.STARTTIME) < { fn NOW() } - 80)
ORDER BY rsh.Client_ID, MaxDate DESC

is getting this output ...
Client_ID MaxDate
2 2011-10-14 16:25:00.000
4 2012-02-14 11:00:00.000
5 2012-04-02 09:00:00.000
6 2012-02-15 11:00:00.000
7 2011-08-08 11:00:00.000
8 2011-04-29 14:15:00.000
9 2012-06-28 14:15:00.000
10 2011-05-26 08:45:00.000
11 2013-04-17 14:15:00.000


and the max date on the RS table is 10/14/11 for client ID #2 etc....

What I want to add is the RS.OID that matches the MAXDATE record

SO the output would look like

Client ID Max Date OID
2 2011-10-14 16:25:00.000 283902303
4 2012-02-14 11:00:00.000 904404040 etc.....
Post #1561617
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse