September 26, 2011 at 8:25 am
I have the following sample data and need to know the following.
--Data I have to work with --
CREATE TABLE PS_TestForOnline
(
month_name NVARCHAR (20),
test_value NVARCHAR (20),
);
INSERT INTO PS_TestForOnline
VALUES('jan','23' );
INSERT INTO PS_TestForOnline
VALUES('jan','15' );
INSERT INTO PS_TestForOnline
VALUES('jan','26' );
INSERT INTO PS_TestForOnline
VALUES('jan','25' );
INSERT INTO PS_TestForOnline
VALUES('jan','24' );
INSERT INTO PS_TestForOnline
VALUES('jan','45' );
INSERT INTO PS_TestForOnline
VALUES('feb','12' );
INSERT INTO PS_TestForOnline
VALUES('feb','78' );
INSERT INTO PS_TestForOnline
VALUES('feb','78' );
INSERT INTO PS_TestForOnline
VALUES('feb','65' );
INSERT INTO PS_TestForOnline
VALUES('feb','42' );
INSERT INTO PS_TestForOnline
VALUES('feb','12' );
INSERT INTO PS_TestForOnline
VALUES('feb','32' );
INSERT INTO PS_TestForOnline
VALUES('mar','12' );
INSERT INTO PS_TestForOnline
VALUES('mar','55' );
INSERT INTO PS_TestForOnline
VALUES('mar','66' );
INSERT INTO PS_TestForOnline
VALUES('mar','77' );
INSERT INTO PS_TestForOnline
VALUES('mar','12' );
INSERT INTO PS_TestForOnline
VALUES('mar','12' );
INSERT INTO PS_TestForOnline
VALUES('mar','78' );
INSERT INTO PS_TestForOnline
VALUES('mar','12' );
INSERT INTO PS_TestForOnline
VALUES('mar','78' );
select
(select count (*)
from PS_TestForOnline) as count_of_records_per_month,
test_value as average,
month_name
from PS_TestForOnline
drop table PS_TestForOnline
How can i get the results if below to still show 22 rows but in the 1st column have a count of records by month. so instead of "22" showing in each row of col 1 i would have the following...
So you would see the following.
count_of_records_per_monthaveragemonth_name
623jan
615jan
626jan
625jan
624jan
645jan
712feb
778feb
778feb
765feb
742feb
712feb
732feb
912mar
955mar
966mar
977mar
912mar
912mar
978mar
912mar
978mar
Thanks in advance
September 26, 2011 at 8:56 am
You'll probably need a better key than 'month_name' to group by (maybe include a year?), but this does what you asked for:
select
(
select count (*)
from PS_TestForOnline tfo2
where tfo2.month_name = tfo.month_name
) as count_of_records_per_month,
tfo.test_value as average,
tfo.month_name
from PS_TestForOnline tfo
Or to give you an alternative method (everything can be done in multiple ways in SQL):
select
tfo2.cnt as [count_of_records_per_month],
(100.0 * tfo.test_value) / tfo2.[sum] as [month_percentage],
tfo.test_value as average,
tfo.month_name
from PS_TestForOnline tfo
left outer join (
select tfo2.month_name,
count(*) as cnt,
sum(convert(int, test_value)) as [sum]
from PS_TestForOnline tfo2
group by tfo2.month_name
) tfo2 on (tfo2.month_name = tfo.month_name)
I took the liberty to demonstrate that this way you can get totals, averages etc for the month, combined with the single value per row for example to demonstrate the row's percentage of the month's total. The type conversion was needed because yous 'test_value' column is of type nvarchar(20) instead of int or some other numerical type. This is not intended as a part of the demo.
September 26, 2011 at 9:15 am
Thanks for your speedy reply.
I've tried to be clever ( i should know better by now, not to go for shortcuts) and give you sample data, when perhaps i should have given you the bigger picture.
This i sthe full script i currently am running which in effect sho9ws the total running down the first column.
There are a couple of self join tables at the end of the query so my question is how do I incorporate your solution into my original script????
Thanks for your help in this matter,
Full script
SELECT
(
select COUNT(*)
from inf.vw_AE_PSNEW_Main_Attendance_View
where (CONVERT(date, arrival, 101) >= '2011-04-01 00:00')
AND (CONVERT(date, arrival, 101) <= '2011-04-30 23:59')
and [Att_Type] = 'New Attender') as New_attender_Count,
F.pat_pid as Patient_ID,
F.Arrival as F_Att_Date,
F.Dept as F_Att_Site,
F.atd_id as F_Att_ID,
F.atd_num as F_Att_Num,
F.Att_Seq_No as F_Att_Seq_No,
F.Att_Type as F_Att_Att_Type,
R.Arrival as R_Att_Date,
R.Dept as R_Att_Site,
R.atd_id as R_Att_ID,
R.atd_num as R_Att_Num,
R.Att_Seq_No as R_Att_Seq_No,
R.Att_Type as R_Att_Att_Type,
R.Return_Type as R_Att__return_Type,
DATEDIFF(dd, F.arrival, R.arrival) as Days_Diff
from
--- F = previous R = readmission
--previous attendance F
(SELECT arrival, atd_id, atd_num, pat_pid, Att_Seq_No, Att_Type, Return_Type, Dept
FROM inf.vw_AE_PSNEW_Main_Attendance_View
) AS F
INNER JOIN
-- re attendance R
(SELECT arrival, atd_id, atd_num, pat_pid, Att_Seq_No, Att_Type, Return_Type, Dept
FROM inf.vw_AE_PSNEW_Main_Attendance_View AS re_att_1
where (Att_Type = 'Reattender') AND (Return_Type = 'Unplanned') ---------- HES gets this wrong, they are counting all reattenders regardless of planned or unplanned, hence 5% value
AND (CONVERT(date, arrival, 101) >= '2011-04-01 00:00')
AND (CONVERT(date, arrival, 101) <= '2011-04-30 23:59')) AS R
ON F.pat_pid = R.pat_pid
AND F.Att_Seq_No + 1 = R.Att_Seq_No
AND DATEDIFF(dd, F.arrival, R.arrival) < '8'
September 26, 2011 at 9:59 am
Ok, first some remarks on that code (I'm trying to be constructive here, not listing this to put you down):
- numeric constants are not enclosed in quotes (''), i.e. '8' is simply written as 8.
- date/time constants are written inside quotes and you did a great job ensuring your values are locale independent: excellent!
- comparing dates is however best done on their binary values, you should not convert them into text format before comparing them. Converting them into strings makes SQL not use indexes, i.e. makes your queries slow. So "CONVERT(date, arrival, 101) >= '2011-04-01 00:00'" can be written as "arrival >= '2011-04-01 00:00'".
- comparing date/time ranges is best done using a combination of >= and <, i.e. specify the lower limit inclusive the specified lower value, the upper limit exclusive the specified upper value. Your range excludes for example 2011-04-30 23:59:30, which should clearly have been included. The proper way to specify the same date range would be: ... and arrival >= '2011-04-01' and arrival < '2011-05-01'.
- Please specify a table alias on all your column references. I know it's not required, but it makes the code so much more readable. So instead of "where Att_Type = 'Reatttender'", please write "where re_att_1.Att_Type = 'Reattender'". You'll help yourself and anyone who needs to read your code.
- The where clause contains a datediff(), please rewrite this using dateadd and a comparison operator (i.e. <, <=, >, >= and such). Again, using datediff makes SQL not use any indexes, i.e. makes your query slow.
Then for the functionality. I think I see where you are going with this: it seems that inf.vw_AE_PSNEW_Main_Attendance_View has both "first attendances" and "re-attendances" in it and what makes any one attendance a first attendance is that there exists another attendance with a Att_seq_No of one more than that attendance. What happens if for an attendance for patient X no 1st attendance exists with Att_seq_no - 1?
Please take the time to create some ddl and test data for your complete query, as I think we'll be discussing this query for some time and others will jump in soon, too. And also properly describe the intended purpose of this query. Without that we'll be guessing.
September 26, 2011 at 10:00 am
Does this give the same results as your query?
SELECT
[New_attender_Count] = 0, -- maybe COUNT(*) OVER(PARTITION BY something),
F.pat_pid as Patient_ID,
F.Arrival as F_Att_Date,
F.Dept as F_Att_Site,
F.atd_id as F_Att_ID,
F.atd_num as F_Att_Num,
F.Att_Seq_No as F_Att_Seq_No,
F.Att_Type as F_Att_Att_Type,
R.Arrival as R_Att_Date,
R.Dept as R_Att_Site,
R.atd_id as R_Att_ID,
R.atd_num as R_Att_Num,
R.Att_Seq_No as R_Att_Seq_No,
R.Att_Type as R_Att_Att_Type,
R.Return_Type as R_Att__return_Type,
[Days_Diff] = DATEDIFF(dd, F.arrival, R.arrival)
--- F = previous R = readmission
FROM inf.vw_AE_PSNEW_Main_Attendance_View f --previous attendance
INNER JOIN inf.vw_AE_PSNEW_Main_Attendance_View r -- re attendance
ON F.pat_pid = R.pat_pid
AND F.Att_Seq_No + 1 = R.Att_Seq_No
AND DATEDIFF(dd, F.arrival, R.arrival) < '8'
WHERE (r.Att_Type = 'Reattender')
AND (r.Return_Type = 'Unplanned') ---------- HES gets this wrong, they are counting all reattenders regardless of planned or unplanned, hence 5% value
AND (CONVERT(date, r.arrival, 101) >= '2011-04-01 00:00') -- don't do it like this: CONVERT the constant, not the column
AND (CONVERT(date, r.arrival, 101) <= '2011-04-30 23:59') -- a function around a column will almost always prevent an index on it from being used
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 26, 2011 at 10:23 am
If you can supply us with suitable test data, I'm sure this can be solved in a nice set-based approach.
All I've done below is reformatted your code a little, to make it easier to read.
SELECT (
SELECT COUNT(*)
FROM inf.vw_AE_PSNEW_Main_Attendance_View
WHERE arrival >= '2011-04-01 00:00:00'
AND arrival < '2011-04-31 00:00:00'
AND [Att_Type] = 'New Attender'
) AS New_attender_Count,
F.pat_pid AS Patient_ID,
F.Arrival AS F_Att_Date,
F.Dept AS F_Att_Site,
F.atd_id AS F_Att_ID,
F.atd_num AS F_Att_Num,
F.Att_Seq_No AS F_Att_Seq_No,
F.Att_Type AS F_Att_Att_Type,
R.Arrival AS R_Att_Date,
R.Dept AS R_Att_Site,
R.atd_id AS R_Att_ID,
R.atd_num AS R_Att_Num,
R.Att_Seq_No AS R_Att_Seq_No,
R.Att_Type AS R_Att_Att_Type,
R.Return_Type AS R_Att__return_Type,
DATEDIFF(dd, F.arrival, R.arrival) AS Days_Diff
FROM --- F = previous R = readmission
--previous attendance F
(
SELECT arrival, atd_id, atd_num, pat_pid, Att_Seq_No,
Att_Type, Return_Type, Dept
FROM inf.vw_AE_PSNEW_Main_Attendance_View
) AS F
INNER JOIN -- re attendance R
(
SELECT arrival, atd_id, atd_num, pat_pid,
Att_Seq_No, Att_Type, Return_Type, Dept
FROM inf.vw_AE_PSNEW_Main_Attendance_View AS re_att_1
WHERE (Att_Type = 'Reattender')
AND (Return_Type = 'Unplanned') -- HES gets this wrong, they are counting all reattenders regardless of planned or unplanned, hence 5% value
AND arrival >= '2011-04-01 00:00:00'
AND arrival < '2011-04-31 00:00:00'
) AS R ON F.pat_pid = R.pat_pid
AND F.Att_Seq_No + 1 = R.Att_Seq_No
AND DATEDIFF(dd, F.arrival, R.arrival) < 8
September 26, 2011 at 2:10 pm
Hi
Following on from R.P.Rozema's post I have attached a copy of the data I'm working with.
Also below is my original query based on the attached dataset.
R.P.Rozema is correct in his understanding of what i'm tring to achieve. The query below is what i'm after but in the first column i need the total for each month based on the arrival date.
for example April 2010 = 57 rows where the patient re-attended in under 8 days but in the first column we would see the figure of 458, May woudl be 62 rows with value of 478 in col 1. (if i've done my maths right).
I hav let the script below looking at May 2010 but you will need to expand period to see what i mean
Obviously I can get these figures by ruuning the query one month at a time but need to be able to expand the period but maintain the monthly count.
I understand the point about query performances but the queries I do are for a Data Warehouse which refreshes on overnight builds which create views from which we then report from. Therefore performance isnt top of the list at the moment. (thanks for the tips however)
Thanks for help and advice in advance
SCRIPT TARGETING MAY 2010 - CHANGE DATES ACCORDINGLY
SELECT
(
select COUNT(*)
from dbo.sample_data_for_SQL_Central
where (CONVERT(date, arrival, 120) >= '2010-05-01 00:00')
AND (CONVERT(date, arrival, 120) <= '2010-05-31 23:59')
and [Att_Type] = 'New Attender') as New_attender_Count,
F.pat_pid as Patient_ID,
F.Arrival as F_Att_Date,
F.Dept as F_Att_Site,
F.atd_id as F_Att_ID,
F.atd_num as F_Att_Num,
F.Att_Seq_No as F_Att_Seq_No,
F.Att_Type as F_Att_Att_Type,
R.Arrival as R_Att_Date,
R.Dept as R_Att_Site,
R.atd_id as R_Att_ID,
R.atd_num as R_Att_Num,
R.Att_Seq_No as R_Att_Seq_No,
R.Att_Type as R_Att_Att_Type,
R.Return_Type as R_Att__return_Type,
DATEDIFF(dd, F.arrival, R.arrival) as Days_Diff
from
--- F = previous R = readmission
--previous attendance F
(SELECT arrival, atd_id, atd_num, pat_pid, Att_Seq_No, Att_Type, Return_Type, Dept
FROM dbo.sample_data_for_SQL_Central
) AS F
INNER JOIN
-- re attendance R
(SELECT arrival, atd_id, atd_num, pat_pid, Att_Seq_No, Att_Type, Return_Type, Dept
FROM dbo.sample_data_for_SQL_Central AS re_att_1
where (Att_Type = 'Reattender') AND (Return_Type = 'Unplanned') ---------- HES gets this wrong, they are counting all reattenders regardless of planned or unplanned, hence 5% value
AND (CONVERT(date, arrival, 101) >= '2010-05-01 00:00')
AND (CONVERT(date, arrival, 101) <= '2010-05-31 23:59')) AS R
ON F.pat_pid = R.pat_pid
AND F.Att_Seq_No + 1 = R.Att_Seq_No
AND DATEDIFF(dd, F.arrival, R.arrival) < '8'
order by R.Arrival
September 27, 2011 at 2:39 am
Can you please add the DDL (= create table statement) too? So we can see the proper types, keys, foreign keys, indexes and load the data into it?
Have a look at the link 'Posting Data Etiquette by Jeff Moden' in my footer text for how to best deliver your test set to us. Keep in mind that we're all volunteers here, we've got our own jobs to finish too. The more time we need to spend on re-creating your test bed, the less time we'll have left to look at your actual problem. If you don't deliver the DDL ready to roll, loads of people will discard your question without even giving it a further look and that's a missed opportunity for you...
September 27, 2011 at 2:44 am
OK, I know we've had this discussion before 😉
If you post readily consumable data and DDL scripts, then people will fall over themselves attempting to help. If you post a excel spreadsheet of sample data, people will generally ignore you.
Luckily, I got into work today to find all of my servers behaving themselves!!
So, this is what we expected from you. I've only kept the first 49 rows from your sample of 10,000. When you want to give us 10,000 rows of data, your best bet is to figure out a script that replicates it, which I'll explain.
IF OBJECT_ID('tempdb..#sample_data_for_SQL_Central') IS NOT NULL
DROP TABLE #sample_data_for_SQL_Central
CREATE TABLE #sample_data_for_SQL_Central (
--No idea what the primary key is supposed to be
arrival DATETIME,
atd_id INT,
atd_num CHAR(16),
pat_pid INT,
Att_Seq_No INT,
Att_Type VARCHAR(12),
Return_Type VARCHAR(9),
Dept CHAR(3))
INSERT INTO #sample_data_for_SQL_Central
SELECT '2009-12-10 17:56:00', 1511, 'XYZ-123-002643-1',766, 1, 'New Attender', 'New', 'ABC'
UNION ALL SELECT '2010-05-03 18:38:00', 36363, 'XYZ-456-013325-1',766, 2, 'New Attender', 'New', 'ABC'
UNION ALL SELECT '2011-09-14 12:34:00', 1317896, 'XYZ-789-028825-1',797, 1, 'New Attender', 'New', 'ABC'
UNION ALL SELECT '2010-02-01 19:33:00', 11471, 'XYZ-456-003376-1',99856, 1, 'New Attender', 'New', 'ABC'
UNION ALL SELECT '2009-10-02 14:31:00', 763, 'RST-123-022538-1',99857, 1, 'New Attender', 'New', 'DEF'
UNION ALL SELECT '2009-11-17 12:18:00', 4373, 'RST-123-025993-1',99857, 2, 'New Attender', 'New', 'DEF'
UNION ALL SELECT '2010-02-01 20:41:00', 11491, 'RST-456-002285-1',99857, 3, 'Reattender', 'New', 'DEF'
UNION ALL SELECT '2010-02-03 22:15:00', 11170, 'RST-456-002285-2',99857, 4, 'New Attender', 'Planned', 'DEF'
UNION ALL SELECT '2010-03-01 01:06:00', 789326, 'RST-456-004163-1',99857, 5, 'New Attender', 'New', 'DEF'
UNION ALL SELECT '2010-03-06 21:44:00', 24435, 'RST-456-004612-1',99857, 6, 'New Attender', 'New', 'DEF'
UNION ALL SELECT '2010-04-04 07:57:00', 1789013, 'RST-456-006771-1',99857, 7, 'New Attender', 'New', 'DEF'
UNION ALL SELECT '2010-06-20 21:50:00', 46746, 'RST-456-013348-1',99857, 8, 'Reattender', 'New', 'DEF'
UNION ALL SELECT '2010-06-22 16:31:00', 47113, 'RST-456-013348-2',99857, 9, 'New Attender', 'Planned', 'DEF'
UNION ALL SELECT '2010-09-07 00:53:00', 62116, 'RST-456-019697-1',99857, 10, 'New Attender', 'New', 'DEF'
UNION ALL SELECT '2011-01-10 20:30:00', 17654, 'RST-789-000801-1',99857, 11, 'Reattender', 'New', 'DEF'
UNION ALL SELECT '2011-01-16 20:30:00', 11727, 'RST-789-000801-2',99857, 12, 'New Attender', 'Unplanned', 'DEF'
UNION ALL SELECT '2011-01-18 12:14:00', 19021, 'RST-789-001332-1',99857, 13, 'New Attender', 'New', 'DEF'
UNION ALL SELECT '2011-03-05 22:01:00', 97637, 'RST-789-004757-1',99857, 14, 'New Attender', 'New', 'DEF'
UNION ALL SELECT '2011-03-13 20:24:00', 99245, 'RST-789-005409-1',99857, 15, 'New Attender', 'New', 'DEF'
UNION ALL SELECT '2011-03-18 09:54:00', 110047, 'RST-789-005727-1',99857, 16, 'New Attender', 'New', 'DEF'
UNION ALL SELECT '2011-04-15 21:26:00', 116215, 'RST-789-008169-1',99857, 17, 'New Attender', 'New', 'DEF'
UNION ALL SELECT '2011-04-27 00:56:00', 111697, 'RST-789-009140-1',99857, 18, 'New Attender', 'New', 'DEF'
UNION ALL SELECT '2011-04-30 19:37:00', 119512, 'RST-789-009478-1',99857, 19, 'New Attender', 'New', 'DEF'
UNION ALL SELECT '2011-05-13 12:08:00', 1178991, 'RST-789-010565-1',99857, 20, 'New Attender', 'New', 'DEF'
UNION ALL SELECT '2011-05-19 15:30:00', 113762, 'RST-789-011110-1',99857, 21, 'New Attender', 'New', 'DEF'
UNION ALL SELECT '2011-07-10 22:01:00', 124111, 'RST-789-015466-1',99857, 22, 'New Attender', 'New', 'DEF'
UNION ALL SELECT '2011-09-16 10:04:00', 131601, 'RST-789-021008-1',99857, 23, 'New Attender', 'New', 'DEF'
UNION ALL SELECT '2010-10-02 11:04:00', 61191, 'XYZ-456-030872-1',94579, 1, 'New Attender', 'New', 'ABC'
UNION ALL SELECT '2011-07-17 14:12:00', 126147, 'RST-789-016050-1',94579, 2, 'New Attender', 'New', 'DEF'
UNION ALL SELECT '2009-10-08 15:42:00', 17891, 'RST-123-023002-1',966, 1, 'New Attender', 'New', 'DEF'
UNION ALL SELECT '2010-03-18 21:44:00', 26144, 'XYZ-456-008042-1',99985, 1, 'New Attender', 'New', 'ABC'
UNION ALL SELECT '2011-06-11 08:04:00', 111361, 'XYZ-789-017973-1',997, 1, 'New Attender', 'New', 'ABC'
UNION ALL SELECT '2011-02-11 12:57:00', 93413, 'XYZ-789-004354-1',999, 1, 'New Attender', 'New', 'ABC'
UNION ALL SELECT '2010-07-17 21:25:00', 578966, 'RST-456-015543-1',977, 1, 'New Attender', 'New', 'DEF'
UNION ALL SELECT '2011-01-10 20:53:00', 17656, 'RST-789-000803-1',977, 2, 'New Attender', 'New', 'DEF'
UNION ALL SELECT '2010-05-10 11:39:00', 37742, 'XYZ-456-014080-1',966, 1, 'New Attender', 'New', 'ABC'
UNION ALL SELECT '2010-03-12 12:07:00', 25492, 'RST-456-005035-1',966, 1, 'New Attender', 'New', 'DEF'
UNION ALL SELECT '2010-09-05 14:22:00', 62566, 'XYZ-456-027779-1',996, 1, 'Reattender', 'New', 'ABC'
UNION ALL SELECT '2010-09-09 14:46:00', 63376, 'XYZ-456-027779-2',996, 2, 'New Attender', 'Unplanned', 'ABC'
UNION ALL SELECT '2010-07-24 16:19:00', 53661, 'RST-456-016073-1',996, 1, 'New Attender', 'New', 'DEF'
UNION ALL SELECT '2010-10-04 12:19:00', 61529, 'RST-456-021891-1',996, 2, 'New Attender', 'New', 'DEF'
UNION ALL SELECT '2010-09-28 13:25:00', 67346, 'RST-456-021435-1',6985457, 1, 'New Attender', 'New', 'DEF'
UNION ALL SELECT '2011-07-26 17:23:00', 121169, 'RST-789-016844-1',69859, 1, 'New Attender', 'New', 'DEF'
UNION ALL SELECT '2010-11-17 18:50:00', 77427, 'XYZ-456-035979-1',66985, 1, 'New Attender', 'New', 'ABC'
UNION ALL SELECT '2010-04-22 20:35:00', 34049, 'XYZ-456-012073-1',66457, 1, 'New Attender', 'New', 'ABC'
UNION ALL SELECT '2010-06-01 13:03:00', 42600, 'RST-456-011738-1',666, 1, 'New Attender', 'New', 'DEF'
UNION ALL SELECT '2010-03-05 04:51:00', 24066, 'XYZ-456-006522-1',6457985, 1, 'New Attender', 'New', 'ABC'
UNION ALL SELECT '2010-10-13 11:33:00', 70436, 'RST-456-022685-1',64576, 1, 'New Attender', 'New', 'DEF'
UNION ALL SELECT '2011-03-04 20:27:00', 97421, 'RST-789-004665-1',66985, 1, '', 'New', 'DEF'
So, from the above, anyone wishing to help can now just execute that script and they've got a copy of your data. Your best bet is to then show us what your expected result from the above is, thus allowing people to get the correct answer.
September 27, 2011 at 2:54 am
Hi,
Thanks for your reply and apologies for my poor exectution of this post.
Please find attached the Create table script below.
---------------------------------------------------------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[sample_data_for_SQL_Central](
[arrival] [datetime] NULL,
[atd_id] [float] NULL,
[atd_num] [nvarchar](50) NULL,
[pat_pid] [float] NULL,
[Att_Seq_No] [float] NULL,
[Att_Type] [nvarchar](50) NULL,
[Return_Type] [nvarchar](50) NULL,
[Dept] [nvarchar](10) NULL
) ON [PRIMARY]
GO
-----------------------------------------------------------------------------
If there is anything else you require please let me know.
many Thanks in adavance
September 27, 2011 at 3:53 am
p.stevens76 (9/27/2011)
If there is anything else you require please let me know.many Thanks in adavance
I'll try again, obviously I wasn't clear.
Thankyou for your very helpful DDL script! But unfortunately, it seems that the INSERT data script has become detached from your post 😉
When you add it back, it should be written as an INSERT statement, so that any of the users of this forum can copy and paste the whole script into SSMS and have a working test area to help with your problem (see my previous post).
If you're unsure of the best way to do this, there is a very helpful article describing the process here!![/url]
Once you've set this up, please provide us with your expected results from the sample data that you've provided.
Thanks!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply