My goal is to show the most recent 12 months of data including the current month based on a patients discharge date (relative to the query execution time). There could be many years of data so I want to be sure I am pulling the most recent 12 monthsThis seems to work (on the surface anyway … not quite sure if this is the best logic to use).
DATEDIFF(month,PAT_ENC_HSP_discharged.HOSP_DISCH_TIME,
GEtDate())
< 13
Code:
create table Channels (ChannelID int(5), other varchar(255));
create table Blogs (BlogID int(5), BlogXMLPath varchar(255));
create table BlogAssociations (BlogID int(5), ShowID int(5), ChannelID int (5));
create table Episodes (EpisodeID int(6), BlogID int(5), ShowID int(5), ChannelID int(5), Other varchar(30));
insert into Channels values (1, 'Channel 1');
insert into Channels values (2, 'Channel 2');
insert into Channels values (3, 'Channel 3');
insert into Channels values (4, 'Channel 4');
insert into Blogs values ('1', 'blah');
insert into Blogs values ('3', 'blah');
insert into Blogs values ('4', 'blah');
insert into Blogs values ('12', 'blah');
insert into Blogs values ('34', 'blah');
insert into Blogs values ('35', 'blah');
insert into Blogs values ('67', 'blah');
insert into Episodes values (1, 3, '', '', 'Episode 1, blog 3');
insert into Episodes values (2, 3, '', '', 'Episode 2, blog 3');
insert into Episodes values (3, 3, '', '', 'Episode 3, blog 3');
insert into Episodes values (4, 3, '', '', 'Episode 4, blog 3');
insert into Episodes values (5, 1, '', '', 'Episode 5, blog 1');
insert into Episodes values (6, 1, '', '', 'Episode 6, blog 1');
insert into Episodes values (7, 1, '', '', 'Episode 7, blog 1');
insert into Episodes values (8, 4, '', '', 'Episode 8, blog 4');
insert into Episodes values (9, 4, '', '', 'Episode 9, blog 4');
insert into Episodes values (10, 4, '', '', 'Episode 10, blog 4');
insert into Episodes values (11, 4, '', '', 'Episode 11, blog 4');
insert into Episodes values (12, 4, '', '', 'Episode 12, blog 4');
insert into Episodes values (13, 12, '', '', 'Episode 13, blog 12');
insert into Episodes values (14, 12, '', '', 'Episode 14, blog 12');
insert into Episodes values (15, 12, '', '', 'Episode 15, blog 12');
insert into Episodes values (16, 12, '', '', 'Episode 16, blog 12');
insert into Episodes values (17, 12, '', '', 'Episode 17, blog 12');
insert into Episodes values (18, 34, '', '', 'Episode 18, blog 34');
insert into Episodes values (19, 34, '', '', 'Episode 19, blog 34');
insert into Episodes values (20, 34, '', '', 'Episode 20, blog 34');
insert into Episodes values (21, 34, '', '', 'Episode 21, blog 34');
insert into Episodes values (22, 35, '', '', 'Episode 22, blog 35');
insert into Episodes values (23, 35, '', '', 'Episode 23, blog 35');
insert into Episodes values (24, 35, '', '', 'Episode 24, blog 35');
insert into Episodes values (25, 35, '', '', 'Episode 25, blog 35');
insert into Episodes values (26, 67, '', '', 'Episode 26, blog 67');
insert into Episodes values (27, 67, '', '', 'Episode 27, blog 67');
insert into Episodes values (28, 67, '', '', 'Episode 28, blog 67');
insert into Episodes values (29, 67, '', '', 'Episode 29, blog 67');
insert into Episodes values (30, 67, '', '', 'Episode 30, blog 67');
insert into BlogAssociations values (3,'',1);
insert into BlogAssociations values (12,'',1);
There is my above sql script, i'm currently using mysql at home to test things but will convert to work in mssql.
I need to get associated BlogID for Certain ChannelID from the BlogAssociations table.
Then using those BlogID's that it finds for let just say ChannelID "1" list off the 3 most recent EpisodeID's from the Episodes Table per each BlogID.
So if ChannelID "1" has four BlogID's associated with it then in the Episodes table list off the recent 3 EpisodeIDs for BlogID "3", then the 3 most recent EpisodeIDs for BlogID "5" and so on and so on.
I have this which kind of does what I want:
Code:
select
e.BlogID, e.EpisodeID, e.other
FROM
episodes e, BlogAssociations ba
WHERE
e.BlogID = ba.BlogID and ba.ChannelID = '1'
ORDER BY
e.EpisodeID DESC;
It returns:
Code:
+--------+-----------+---------------------+
| BlogID | EpisodeID | other |
+--------+-----------+---------------------+
| 12 | 17 | Episode 17, blog 12 |
| 12 | 16 | Episode 16, blog 12 |
| 12 | 15 | Episode 15, blog 12 |
| 12 | 14 | Episode 14, blog 12 |
| 12 | 13 | Episode 13, blog 12 |
| 3 | 4 | Episode 4, blog 3 |
| 3 | 3 | Episode 3, blog 3 |
| 3 | 2 | Episode 2, blog 3 |
| 3 | 1 | Episode 1, blog 3 |
+--------+-----------+---------------------+
But as you can see it lists off 5 of the Episodes for BlogID "12", I only want the most recent 3 as previously stated. It also lists off more than 3 Episodes for BlogID "3". How in the world do I go about doing this?
I'm making this a stored procedure so I can't use php otherwise I wouldn't even be posting
Thanks so much!
I have a date into format YYYYMM. Data type is int. I need to make query where every time it will return me last 3 months, but without current one. For example I have data for months below
201510
201509
201508
201507
Query should return all records for 201509, 201508, 201507.
I was trying lot of solutions founded in internet like this one:
Date_Column >= DATEADD(MONTH, -3, GETDATE())
or
DATEDIFF(MONTH, my_date_column, GETDATE()) <= 3
but it doesn't work.
I have the following table
FeedBack Type Date
test2 positive 03/15/08
tes3 negative 03/01/08
.. ....
in my page i need to select the number of negative/positive comments within the last
1 month, 6 months, 12 months
How can I accomplish that?
thanks
We have 2 Tables, tblMain, which contains unique products and tblHistory, which contains a history of the product Records are added to tblHistory daily and then the prices in tblMain are updated with those prices. The problem that we have is that since there are multiple instances of the same product in tblHistory, we're not always updating using the most recent price. Below is the current SQL that we are using.
Update TM SET TM.Price = TH.PriceHist,
TM.DateUpdated = TH.DateUpdatedHist
FROM tblHistory TH LEFT JOIN tblMain TM ON TH.ProdCode = TM.ProdCode
Below is a sample of data in tblHistory and the records that I would need to be used for the update.
ProdIDPriceHistDateUpdateHist
115.257/20/2015
117.157/15/2015
120.357/23/2015
520.107/25/2015
With the above. I would need corresponding records in tblMain to be updated with the entry on 7/23/2015 for ProdID 1 and with ProdID 5
This is my query, from my nonono table I need to return the most recent date from attempted (column name) How could I modify this to do such
Select *
FROM [nonono] td
WHERE (NOT EXISTS
(SELECT [First], [second]
FROM [yesyesyes] AS d
WHERE ([First] = td.[First]) AND ([second] = td.[second]) AND ([Worksite] = td.[Worksite])))
Empid 1 has 2 entries for the date 09/01/2015 and my left join returns both of those entries. What do I need to alter to make it so that only the most recent entry is returned not both entries?
Create Table #one
(
empid varchar(100)
,empbadgeid varchar(100)
,emplunchtime decimal(18,4)
,empbreaktime decimal(18,4)
,empworktime decimal(18,4)
[code]....
I have a table with sample data as shown in the table below. for each month end date, I need to aggregate (sum) all the quantities for each product and customer and include the current month plus the past 12 months (including current month).
For example, for customer C123, P123 and for 7/31/2015, the query should add 7/31/2015 quantity and the quantity for 1/31/2015.
DECLARE @TEMP TABLE (Customer VARCHAR (10), Product VARCHAR(10), Month_end_date DATE, Quantity INT)
INSERT INTO @TEMP VALUES ('C123','P123','1/31/2015',10)
INSERT INTO @TEMP VALUES ('C124','P124','2/28/2015',20)
INSERT INTO @TEMP VALUES ('C125','P125','3/31/2015',30)
INSERT INTO @TEMP VALUES ('C126','P126','4/30/2015',40)
[Code] ....
the output for the above example should be
C123 P123 7/31/2015
70+10=80
is this possible?
I simply need to list the most recent value for each employee here... the value at each person's maximum date
Sample Detail Data:
EmpID Date Value
1 1/1/14 27
1 2/12/15 333
2 5/5/15 255
3 5/4/15 110
3 1/1/13 67
3 3/2/14 80
[Code] ....
What is the most efficient way to display the most recent value for each employee ID via MS SQL.?
I have table A(year int, month int, user varchar(20)), and I am trying to write a view that would show number of distinct users in the last 3 months, last 6 months, last 9 months and last 12 months(all from the most recent year/month) in following format:
3 months 6 months 9 months 12 months
number of distinct users x y z w
I would like to display Car records to retrieve last Maintenance request for all customer who did not make request in past 9 months from current date.
MC refers to Maintenance.
MCLog data:
carId: SGY12345 (repeated)
mcDate: 2010-01-30
mcDate: 2012-03-30
carId: SGX55661 (repeated)
mcDate: 2015-05-30
mcDate: 2015-06-15
[Code] .....
Here is my Table:
CREATE TABLE Car (
carId Char (20) NOT NULL,
model Char (20) NOT NULL,
importDate smalldatetime NOT NULL,
custId Varchar (50) NOT NULL,
CONSTRAINT Car_PK PRIMARY KEY (carId));
[code]....
I get a column in hours in a table and when I try to convert it to months and days its giving me incorrect results.
My timehours column in my table has hours recorded timehours value is 5832 and I get results as 11 months and 27 days.
This query is giving me wrong result -select months=(timehours %365)/30,days= (timehours % 365)%30
I have simple query that works fine if I write it every time. I want to get this one time group by each year and week ending. here us the query:
Select count(distinct ID) from table1 where year<=2015 and WeekEnding<='05/09'
Select count(distinct ID) from table1 where year<=2015 and WeekEnding<='05/16'
Select count(distinct ID) from table1 where year<=2015 and WeekEnding<='05/23'
How can I get the count for each week year and each week ending? Weekending 05/16 will include ID's from weekending 05/09 as well and week ending 05/23 will include all the ID's from 05/16 and 05/09 and so on...
I have hours which can be like 32.5 and would like to have them in 1 month 2 Days format.
View 2 Replies View RelatedI need to find the missing months in a table for the earliest and latest start dates per ID_No. As an example:
create table #InputTable (ID_No int ,OccurMonth datetime)
insert into #InputTable (ID_No,OccurMonth)
select 10, '2007-11-01' Union all
select 10, '2007-12-01' Union all
select 10, '2008-01-01' Union all
select 20, '2009-01-01' Union all
select 20, '2009-02-01' Union all
select 20, '2009-04-01' Union all
select 30, '2010-05-01' Union all
select 30, '2010-08-01' Union all
select 30, '2010-09-01' Union all
select 40, '2008-03-01'
For the above table, the answer should be:
ID_No OccurMonth
----- ----------
20 2009-02-01
30 2010-06-01
30 2010-07-01
1) don't include an ID column,
2) don't use the start date/end dates in the data or
3) use cursors, which are forbidden in my environment.
I am having a problem trying to pull data that has apostrophe in them. How can I do this? I get this as an error
Msg 105, Level 15, State 1, Line 14
Unclosed quotation mark after the character string ''.
Select
Name
From Table
Where Name IN (CHILDREN'S ANES ASSOCS-CHOP,CHILDREN'S HEALTHCARE-CHOP,CHILDREN'S PSYCH ASSOC-CHOP,CHILDREN'S SURGICAL ASSOC-CHOP)
I'm trying to pull data from about 30 progress databases using DTS andscheduling the jobs to run monthly. I'd like to pull data betweenspecific dates, but for some reason, I can't figure out how to filterthe data on the progress side.I want to run a query that will pull all data fromprior-month/8/current-yearandcurrent-month/15/current-yearI'll also have to account for when it's january, make it december ofthe year before.Any ideas?thanks,M@
View 1 Replies View RelatedHi MAK,
I got this piece of code from one of your articles:
select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DATABASE=c: est.xls', 'Select * from [Book1$]')
Question is, how can I use a fully qualified path for the xls file name if the file is located on the network elsewhere?is that possible with this option?
Thank you!
Hi friends,
I am new to MSAS world. I need help related to this. I want to pull data from MSAS cube programmatically. Only way I know is thru ODBO, but that won't help me in this case, cause I might have to drill down upto all possible intersections stored in MSAS (at least all of the stored members). Doing this thru MDX could be humongous thing, at least manipulating data taken out using ODBO.
I might be missing something out here. Can anybody help. It would also help if somebody can tell me if any other approach is poosible.
Thank you.
Abhijeet
Can any one tell me how to transfer bulk data from SQL Server6.5 to SQL Server2000.I have tried bcp out and bcp in to SQL Server2000 but it is taking very long time.Is there any way that I can pull the data into SQL Server2000 from SQL Server6.5.Can I use DTS feature of SQL Server2000 to pull the data from SQL Server6.5..
View 2 Replies View RelatedOk I wrote a SSIS package that will pull down data from my AS/400 and populate a SQL Server table with the data.
1)The data is being pulled from my China configured AS/400. It is configured to handle DBCS
2)The SQL Server tables are configured to handle DBCS by using the nvarchar datatype.
3)When I run this package on my machine against the production server, it works perfectly.
4)When I run this package on my test SQL Server against the production server,it works perfectly.
5)When I run this package on my production SQL Server it brings down all the records, but does not bring down all the fields. Most of the character fields are left blank.(not all)
I do not understand why this is doing this. Can anyone shed any light on this problem? Thank you.
eventually I started using DERIVED Tables and Sub queries within them if needed. I like choking down all the queries in the selects joining them and having your result set select there to choose from all the aliases, it also resolves while doing this. So much easier IMO than using CTE's or TEMP Tables. I was big on temp tables for a while...
I'm curious though if you want to count a type of criteria in a column do you use a standard case in your inner query choking it down just for those particular counts? Then do another case on the other criteria and END AS with the alias name?
E.G.
CASE WHEN COLUMN1 = PIZZA THEN COUNT(COLUMN1) END AS PIZZACOUNT
CASE WHEN COLUMN1 = ROOTBEER THEN COUNT(COLUMN!) END AS ROOTBEERCOUNT
ETC...........
I have a table, multiple columns, thousands of rows.
Six of the columns is the data that i need to work with...
col1, col2, col3, col4, col5, col6
col1 and col2 - go together - example. col1 = amount col2 = description
col3 and col4 - go together col3 = amount col4 description
col5 and col6 - go together col5 amount and 6 description
i need to pull search the table based on an auto number "id" and pull in the necessary two columns that correspond with a set value in the description.
example:
if col4 has "fee applied" in the description i need to pull the amount.
Please help...
Thank you in advance
Ok I wrote a SSIS package that will pull down data from my AS/400 and populate a SQL Server table with the data.
1)The data is being pulled from my China configured AS/400. It is configured to handle DBCS
2)The SQL Server tables are configured to handle DBCS by using the nvarchar datatype.
3)When I run this package on my machine against the production server, it works perfectly.
4)When I run this package on my test SQL Server against the production server,it works perfectly.
5)When I run this package on my production SQL Server it brings down all the records, but does not bring down all the fields. Most of the character fields are left blank.(not all)
I do not understand why this is doing this. Can anyone shed any light on this problem? Thank you.
Ok I wrote a SSIS package that will pull down data from my AS/400 and populate a SQL Server table with the data.
1)The data is being pulled from my China configured AS/400. It is configured to handle DBCS
2)The SQL Server tables are configured to handle DBCS by using the nvarchar datatype.
3)When I run this package on my machine against the production server, it works perfectly.
4)When I run this package on my test SQL Server against the production server,it works perfectly.
5)When I run this package on my production SQL Server it brings down all the records, but does not bring down all the fields. Most of the character fields are left blank.(not all)
I do not understand why this is doing this. Can anyone shed any light on this problem? Thank you.
So this could be a long story.
Server1 = Previous "Test box", Windows Server 2003 Standard, SQL 2005 Standard SP1 with hotfixes, 2 processor 4 gig RAM
Server2 = New "Box for production", Windows Server 2003 Enterprise, SQL 2005 Standard SP1 with hotfixes, 2 Dual Core Processor, 16 gig Ram
This is for a datawarehouse environment. I pull a lot of data every night from an Oracle database. On Server1 the process took about an hour to pull all my data accross, on Server2 it takes more than 3 hours. I use SSIS packages to pull the data accross.
Even just running an OPENQUERY statement takes a lot longer. On Server1 returned about 150K record in 1.5 minutes and now on Server2 it only return 40K.
Have I missed a setting on my re-install. It the same SQL build number, SP_Configure has exactly the same settings and everything seems the same. I ftp'ed a file from the oracle box to each of my boxes and had very similar results, so it doesn't seem to be a network issue.
Any help would be much appreciated, is there anything that would cause SQL to pull data 3 times slower from an external datasource?
Thanks in advance guys,
Regards.
I need to get all customer records with the most recent tDate. A customer should never have duplicate tDate records, as only one record per day is allowed per customer, yet there somehow there are duplicates. Given this, I need to get the record with the Max date and Max ID. the ID column is an auto-incrementing Identity column.Below is the code without the Max ID column logic
SELECT tCustID, MAX(tDate) AS tDate--get MAX tDate records
FROM table1
GROUP BY tCustID
Hey guys,
I have created an asp.net page where users can select multiple items and then submit the form. I would like to return related items back. The catch is, I want to only return items that are related to all of the selected items.
I've created a SQL Procedure that puts each of the inputted item's ItemId in to a temp table, I have a second table called RelatedItems which I use as my junction table that has ItemId, and ReleatedItemId, I then have my Item table that has the data I want to get to (I've excluded this because I have no trouble pulling out data once I have an ItemId)
I can pull out all related ItemIds with a simple join, however I don't know where to start when it comes to pulling out only items related to all ItemIds in the @TempTable.
Any help or suggestions would be great.
Thanks,
Matt
I have a table of Projects which have multiple Resources.
PROJ_ID, PROJ_NAME,RESOURCE1,RESOURCE2,RESOURCE3
01 Project1 001 005 088
02 Project2 002 004 005
How can I pull out a list of resources with the projects associated with them?
i.e. the above would return
001 01
002 02
004 02
005 01
005 02
008 01
or
001 01
002 02
004 02
005 01,02
008 01
I am pulling data from table which has date field to it. I am using below query to get the date
select ArrivalDate As[ARRIVE DATE, , Date, 0], ArrivalTime As[ARRIVE TIME, 4, String, 0] from ArrivalInfo
but i am getting date as 20130925 in format but i want date to be in 09/25/2013 (mm/dd/yyyy)..
I need to do some advanced formatting in the text file, because the data in the text file is bit complex. I have some knowledge of using BCP, BULK INSERT and bit about FORMAT FILES but I want to do some advanced formatting(using IF condition and all) to pull the data from text file.
Can anyone please tell me how can I perform bit advanced formatting using BULK INSERT?
Cheers
Hi all,
In an approach of building an ETL tool, we are into a situation wherein, a table has to be loaded on an incremental basis. The first run all the records apporx 100 lacs has to be loaded. From the next run, only the records that got updated since the last run of the package or newly added are to be pulled from the source Database. One idea we had was to have two OLE DB Source components, in one get those records that got updated or was added newly, since we have upddate cols in the DB getting them is fairly simple, in the next OLEDB source load all the records form the Destination, pass it onto a Merge Join then have a Conditional Split down the piple line, and handle the updates cum insert.
Now the question is, how slow the show is gonna be ? Will there be a case that the Source DB returns records pretty fast and Merge Join fails in anticipation of all the records from the destination ?
What might be the ideal way to go about my scenario.. Please advice...
Thanks in advance.
I've got a table full of data like so:
varchar(MAX) as
'MSG|John|J|Smith EVN|2008-02-01|A03 ADD|101 Highland St|Mount Vernon|WA|55231 OBS|Flu|Severe OBS|Mumps|Mild'
To explain this more thoroughly, the data is organized into segments: MSG, EVN, ADD, and OBS.
Each message may have 1+ of each of these segments.
What I'd like to do is pull the contents of all OBS segments and put them into a table.
I know I can identify them using the regex of ' OBS|' but I'm not clear on how to capture anything but the first one easily. Programmatically, it's simple. SQL I'm not so sure.
Can anyone give me a gentle nudge in the right direction?
Thanks!