Viewing 15 posts - 5,401 through 5,415 (of 7,613 total)
I think a "standard tally table" (table of seq numbers from 0 to some big number) is all you really need.
--add up the days
SELECT account, loc, CONVERT(varchar(7), month, 111) AS...
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".
February 3, 2015 at 2:41 pm
Lynn Pettis (2/3/2015)
ScottPletcher (2/3/2015)
SELECT DOC_NO
,ENTER_DATE
FROM SALES_ORDER
WHERE ENTER_DATE >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 12, 0)
AND...
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".
February 3, 2015 at 1:26 pm
Best is to always use >= and < on date/datetimes, not between:
SELECT DOC_NO
,ENTER_DATE
FROM SALES_ORDER
WHERE ENTER_DATE >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 12, 0)
AND ENTER_DATE < DATEADD(DAY,...
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".
February 3, 2015 at 1:14 pm
Again, you need to explain "lev5" to us. We do not know your data.
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".
February 3, 2015 at 10:25 am
You have to remember that I do not know what your data is.
GROUP BY a.bf_fund_cd, a.bf_bdob_cd, b.lev5,b.bf_orgn_cd
...
SS_21111_1 -Lev5 (dont need to show) ---> less than 5,000000
SS_21112_2 -$1,500,000(dont need to show)
SS_21113_2...
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".
February 2, 2015 at 4:53 pm
If you want to see all values, remove the "having" condition:
having sum(a.data)>5000000
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".
February 2, 2015 at 3:41 pm
Rather than use a specific ending value, most people prefer to use < the next day. That method always works, even if the date/datetime format changes later, something like...
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".
February 2, 2015 at 3:32 pm
You'll have to use varchar output rather than a numeric format:
SELECT REPLACE(CAST(pay AS varchar(30)), '.000', '') AS pay,
...
FROM table_name
WHERE ...
...
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".
February 2, 2015 at 3:28 pm
The internet site needs to be able to function stand-alone. You could periodically push data from the intranet to the internet db(s), but you don't want the internet db...
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".
February 2, 2015 at 11:20 am
You can use sp_executesql, something like this:
DECLARE @WIDTH decimal(19, 2)
DECLARE @LONG decimal(19, 2)
DECLARE @RESULT decimal(19, 2)
DECLARE @sql nvarchar(4000)
SET @sql = N'( @WIDTH / 3 ) + ( @LONG / 100)'
SET...
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".
February 2, 2015 at 11:17 am
Since the page count is vastly smaller, I would think it's the highest level of the index rather than the lowest (leaf) level.
But you should definitely change the query to...
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".
February 2, 2015 at 11:05 am
Yeah, not bad, although it does have to do a full index scan for trans date on the TD table.
You can do minor tweaks on the list itself, changing it...
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".
January 30, 2015 at 2:19 pm
Ken Davis (1/30/2015)
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".
January 30, 2015 at 1:19 pm
Is the TabletAuditLog.dbo.TransactionDetails table clustered on ID first?
Just to confirm, the "ID" in the TD table is the same value as the ID in the T table, right?
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".
January 30, 2015 at 1:03 pm
SELECT CustomerNumber,
SUM(CASE WHEN [YearMonth] BETWEEN 201401 AND 201412 THEN TotalAmount ELSE 0 END) AS Sales_2014,
SUM(CASE WHEN [YearMonth] BETWEEN 201201 AND 201312 THEN...
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".
January 30, 2015 at 9:51 am
Viewing 15 posts - 5,401 through 5,415 (of 7,613 total)