September 18, 2014 at 4:08 am
Hi All,
It's time for my weekly post and cry out for help. I do really appreciate your help.
Below is a script that helps me count the amount of Void properties we had in a specific month.
SELECT COUNT(C.[Place Referance]) AS [April Total Voids]
FROM
(
SELECT DISTINCTTOP 100 PERCENT
HIST.[PLACE-REF] AS 'Place Referance'
,PLA.[address1] AS 'Address Line1'
,HIST.[START-DATE] AS 'Void Start Date'
,HIST.[END-DATE] AS 'Void End Date'
,HIST.[END-DATE]+1 AS 'Start Of Tenancy'
,LOC.[mgt-area] AS 'Managment Area'
,LOC.[scheme] AS 'Scheme'
,LOC.[location-sts] AS 'Location Status'
,LOC.[location-type] AS 'Location Type'
,DATEPART(MM,HIST.[START-DATE]) AS 'Void Start Date MONTH'
,DATEPART(YYYY, HIST.[START-DATE]) AS 'Void Start Date YEAR'
FROM[dbo].[IH_IH-LOCATION-HIST] AS HIST
INNER JOIN
[dbo].[IH_IH-LOCATION] AS LOC
ONHIST.[PLACE-REF] = LOC.[place-ref]
INNER JOIN
DBO.[CORE_CO-PLACE] AS PLA
ONHIST.[PLACE-REF] = PLA.[place-ref]
WHEREHIST.[LOCATION-STS] = 'V'
AND HIST.[START-DATE] BETWEEN CONVERT(DATETIME, '2014-04-01 00:00:00', 102) AND CONVERT(DATETIME, '2015-03-31 00:00:00', 102)
AND LOC.[location-type] <> 'D'
AND LOC.[location-type] <> 'Garage'
ORDER BY [Place Referance]
) AS C
WHERE [Void Start Date YEAR] = '2014'
AND [Void Start Date MONTH] = '4'
It tells me I have 53 total voids.
What I also want is a column next to this to say how many of those voids back in April are STILL Void.
So basically the WHERE clause would still be the same -
WHERE [Void Start Date YEAR] = '2014'
AND [Void Start Date MONTH] = '4'
but with the added -
AND HIST.[END-DATE] IS NULL
So ideally I'm after two columns with figures in them and then going forward I can then calculate other months as well.
Thanks
September 18, 2014 at 7:51 am
Let's start with some formatting so that query is legible.
SELECT COUNT(C.[Place Referance]) AS [April Total Voids]
FROM (
SELECT DISTINCT TOP 100 PERCENT HIST.[PLACE-REF] AS 'Place Referance'
,PLA.[address1] AS 'Address Line1'
,HIST.[START-DATE] AS 'Void Start Date'
,HIST.[END-DATE] AS 'Void End Date'
,HIST.[END-DATE] + 1 AS 'Start Of Tenancy'
,LOC.[mgt-area] AS 'Managment Area'
,LOC.[scheme] AS 'Scheme'
,LOC.[location-sts] AS 'Location Status'
,LOC.[location-type] AS 'Location Type'
,DATEPART(MM, HIST.[START-DATE]) AS 'Void Start Date MONTH'
,DATEPART(YYYY, HIST.[START-DATE]) AS 'Void Start Date YEAR'
FROM [dbo].[IH_IH-LOCATION-HIST] AS HIST
INNER JOIN [dbo].[IH_IH-LOCATION] AS LOC ON HIST.[PLACE-REF] = LOC.[place-ref]
INNER JOIN DBO.[CORE_CO-PLACE] AS PLA ON HIST.[PLACE-REF] = PLA.[place-ref]
WHERE HIST.[LOCATION-STS] = 'V'
AND HIST.[START-DATE] BETWEEN CONVERT(DATETIME, '2014-04-01 00:00:00', 102)
AND CONVERT(DATETIME, '2015-03-31 00:00:00', 102)
AND LOC.[location-type] <> 'D'
AND LOC.[location-type] <> 'Garage'
ORDER BY [Place Referance]
) AS C
WHERE [Void Start Date YEAR] = '2014'
AND [Void Start Date MONTH] = '4'
There isn't a lot we can do to help here because you didn't provide much in the way of details. In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
Why do you have a query that has top 100 percent? And if all you are doing is getting a count of rows why do you return so many columns in the subquery? Why do you even have a subquery here?
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 18, 2014 at 8:06 am
Sorry Sean - just find it hard to explain myself and sorry my formatting is poor.
I'm not a natural at all of this as no one has ever taught me properly so I find it difficult sometimes to explain what I am trying to achieve.
I use the code (the subquey alone) as a view and I need the TOP in there as it has an ORDER BY in the view as apparently I need it in a view if I use an order by.
That view is then called by Excel so users can see all the VOID properties for that Financial Year.
Instead of using a Pivot table in Excel I wanted to do the Counts or Sums in TSQL, but never have I been taught how to do this and am trying to work it out as I have no idea how to do it.
I'll read your rules and see if they make sense to a dunse like myself as I've never done simple every day things like CREATE TABLE. Thanks for taking the time to respond.
September 18, 2014 at 8:15 am
Ryan Keast (9/18/2014)
Sorry Sean - just find it hard to explain myself and sorry my formatting is poor.I'm not a natural at all of this as no one has ever taught me properly so I find it difficult sometimes to explain what I am trying to achieve.
I use the code (the subquey alone) as a view and I need the TOP in there as it has an ORDER BY in the view as apparently I need it in a view if I use an order by.
That view is then called by Excel so users can see all the VOID properties for that Financial Year.
Instead of using a Pivot table in Excel I wanted to do the Counts or Sums in TSQL, but never have I been taught how to do this and am trying to work it out as I have no idea how to do it.
I'll read your rules and see if they make sense to a dunse like myself as I've never done simple every day things like CREATE TABLE. Thanks for taking the time to respond.
I see what you mean about the view. However, the actual results are not guaranteed to be in that order. The only way you can guarantee the order of the results is to add an order by to the query when you select from the view. Consider if you joined to the view or something like that. What order would the results be? It might be in the order you specified in the view right now but that is by luck.
The article I referenced explains how you can generate the create table scripts and how to deal with the data. It is a bit of work but it is something that anybody wanting to help you would have to do themselves. If you do the leg work then the people helping can work on the problem instead of the setup.
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 18, 2014 at 4:32 pm
SELECT
COUNT(C.[Place Referance]) AS [April Total Voids],
SUM(CASE WHEN [Void End Date] IS NULL THEN 1 ELSE 0 END) AS [April_Still_Voids]
FROM
(
SELECT DISTINCTTOP 100 PERCENT
HIST.[PLACE-REF] AS 'Place Referance'
,PLA.[address1] AS 'Address Line1'
,HIST.[START-DATE] AS 'Void Start Date'
,HIST.[END-DATE] AS 'Void End Date'
,HIST.[END-DATE]+1 AS 'Start Of Tenancy'
,LOC.[mgt-area] AS 'Managment Area'
,LOC.[scheme] AS 'Scheme'
,LOC.[location-sts] AS 'Location Status'
,LOC.[location-type] AS 'Location Type'
,DATEPART(MM,HIST.[START-DATE]) AS 'Void Start Date MONTH'
,DATEPART(YYYY, HIST.[START-DATE]) AS 'Void Start Date YEAR'
FROM[dbo].[IH_IH-LOCATION-HIST] AS HIST
INNER JOIN
[dbo].[IH_IH-LOCATION] AS LOC
ONHIST.[PLACE-REF] = LOC.[place-ref]
INNER JOIN
DBO.[CORE_CO-PLACE] AS PLA
ONHIST.[PLACE-REF] = PLA.[place-ref]
WHEREHIST.[LOCATION-STS] = 'V'
AND HIST.[START-DATE] BETWEEN CONVERT(DATETIME, '2014-04-01 00:00:00', 102) AND CONVERT(DATETIME, '2015-03-31 00:00:00', 102)
AND LOC.[location-type] <> 'D'
AND LOC.[location-type] <> 'Garage'
ORDER BY [Place Referance]
) AS C
WHERE [Void Start Date YEAR] = '2014'
AND [Void Start Date MONTH] = '4'
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 19, 2014 at 4:26 am
Spot on Scott - Exactly the type of thing I am after.
Again sorry for not explaining myself properly.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply