asked
Re attendance Script
I quite new to the Tsql coding so please pardon my silly questions. I am trying to produce a script the Pull patients re attending an AE hospital. Basically called A&E frequent flyers. can any one assist with the logic? many thanks DD
Difficult to get right without seeing the structure of your table(s). However, you would probably want to use the HAVING clause with a GROUP BY in order to filter out those who, say, attended more than 3 times in a year. See
https://docs.microsoft.com/en-us/sql/t-sql/queries/select-having-transact-sql
I think i may have been doing this the wrong way see script below select * from ( select distinct main.zNHSNumberPseudo as 'CurrID', B.zNHSNumberPseudo as 'PrevID', convert(varchar, main.zArrivalDateTime, 103) as currentAttdate, convert(varchar, b.zArrivalDateTime, 103) as PrevAttdate, main.zFinancialYear as CurrFinYr, B.zFinancialYear as PrevFinYr, case when month (cast (main.ArrivalDate as date)) = 4 then 'April' when month (cast (main.ArrivalDate as date)) = 5 then 'May' when month (cast (main.ArrivalDate as date)) = 6 then 'June' when month (cast (main.ArrivalDate as date)) = 7 then 'July' when month (cast (main.ArrivalDate as date)) = 8 then 'August' when month (cast (main.ArrivalDate as date)) = 9 then 'September' when month (cast (main.ArrivalDate as date)) = 10 then 'October' when month (cast (main.ArrivalDate as date)) = 11 then 'November' when month (cast (main.ArrivalDate as date)) = 12 then 'December' when month (cast (main.ArrivalDate as date)) = 1 then 'January' when month (cast (main.ArrivalDate as date)) = 2 then 'February' when month (cast (main.ArrivalDate as date)) = 3 then 'March' else '' end as CurrMonth, case when month (cast (b.ArrivalDate as date)) = 4 then 'April' when month (cast (b.ArrivalDate as date)) = 5 then 'May' when month (cast (b.ArrivalDate as date)) = 6 then 'June' when month (cast (b.ArrivalDate as date)) = 7 then 'July' when month (cast (b.ArrivalDate as date)) = 8 then 'August' when month (cast (b.ArrivalDate as date)) = 9 then 'September' when month (cast (b.ArrivalDate as date)) = 10 then 'October' when month (cast (b.ArrivalDate as date)) = 11 then 'November' when month (cast (b.ArrivalDate as date)) = 12 then 'December' when month (cast (b.ArrivalDate as date)) = 1 then 'January' when month (cast (b.ArrivalDate as date)) = 2 then 'February' when month (cast (b.ArrivalDate as date)) = 3 then 'March' else '' end as PrevMonth from [dmic_sus_pbrmart].[dbo].[consolidated_ns_ae] main inner join [dmic_sus_pbrmart].[dbo].[consolidated_ns_ae] B on B.zNHSNumberPseudo = main.zNHSNumberPseudo and Left(B.OrganisationCodeCodeofProvider,3) = Left(main.OrganisationCodeCodeofProvider,3) where --and --main.AgeAtCDSActivityDate <16 main.zFinancialYear, main.zNHSNumberPseudo, B.zNHSNumberPseudo, main.zArrivalDateTime, b.zArrivalDateTime, main.zFinancialYear, B.zFinancialYear, case when month (cast (main.ArrivalDate as date)) = 4 then 'April' when month (cast (main.ArrivalDate as date)) = 5 then 'May' when month (cast (main.ArrivalDate as date)) = 6 then 'June' when month (cast (main.ArrivalDate as date)) = 7 then 'July' when month (cast (main.ArrivalDate as date)) = 8 then 'August' when month (cast (main.ArrivalDate as date)) = 9 then 'September' when month (cast (main.ArrivalDate as date)) = 10 then 'October' when month (cast (main.ArrivalDate as date)) = 11 then 'November' when month (cast (main.ArrivalDate as date)) = 12 then 'December' when month (cast (main.ArrivalDate as date)) = 1 then 'January' when month (cast (main.ArrivalDate as date)) = 2 then 'February' when month (cast (main.ArrivalDate as date)) = 3 then 'March' else '' end, case when month (cast (b.ArrivalDate as date)) = 4 then 'April' when month (cast (b.ArrivalDate as date)) = 5 then 'May' when month (cast (b.ArrivalDate as date)) = 6 then 'June' when month (cast (b.ArrivalDate as date)) = 7 then 'July' when month (cast (b.ArrivalDate as date)) = 8 then 'August' when month (cast (b.ArrivalDate as date)) = 9 then 'September' when month (cast (b.ArrivalDate as date)) = 10 then 'October' when month (cast (b.ArrivalDate as date)) = 11 then 'November' when month (cast (b.ArrivalDate as date)) = 12 then 'December' when month (cast (b.ArrivalDate as date)) = 1 then 'January' when month (cast (b.ArrivalDate as date)) = 2 then 'February' when month (cast (b.ArrivalDate as date)) = 3 then 'March' else '' end ) a
Looks as though you've lost your `WHERE` clause and replaced it with the `SELECT` list. :-( Incidentally, you can replace all that `CASE WHEN Month(...)=1 THEN 'January'` stuff with the DATENAME function, so the whole CASE statement goes from 15 lines (or more) of CASE statement down to a single function call. See
https://technet.microsoft.com/en-us/library/ms174395(v=sql.105).aspx for more info.
17 People are following this question.
